Can't query data from database directly - have to use Stored Procedures...

[louism]

Ok so I know this topic has been brought up before but after scouring forums and blog posts for the last couple of hours I am yet to come up with a way around my issue...

Current Situation

I am working on a set of financial reports which are currently mostly written in SSRS. We would like to modernize these reports and make them far more interactive than they currently are.

The Problem

I am working on a very complex 3rd party SQL database in which almost all data must be extracted by SQL Procedure instead of querying the tables directly. The database makes heavy use of CLR and works out almost all the required information on the fly. These Procedures requirenumber of parameters (including ID's, Date Ranges etc) in order to return data for the correct records on the correct dates. This data is often changing and there are a huge number of variables so it is impossible for us to get a full "static" version to report on. In SSRS we get around this by simply calling the Procedures and passing in SQL parameters to get the data we require.

What I was hoping to do...

So my hope was that I would be able to harness Power BI parameters to call the procedures and return the correct data - however this has proved difficult as Power BI parameters and Stored Procedure Paramaters don't seem to play nice... It just doesn't really match the design of how Power BI was supposed to work!

My plan B - anyone have any thoughts on this

I was looking into Power BI Embed (which looks awesome) and was wondering if I could programatically change the connection string of a report to produce something similar to that of  SSRS Parameters. I could expose the data via a Web API or similar so it would just be changing the connection source to a different URL. Perhaps even set the data source to a parameter so all I had to do was change the parameter and refresh?

If anyone has any thoughts or experience doing something similar I would really appretiate some thoughts!!

[Eric_Zhang]

@louism

Power BI Embedded at this moment is not a good option for this case I think. As it doesn't has refresh feature except DirectQuery mode to Azure SQL DB/DW. And even  your SQL happens to be Azure SQL and not a on-premises one, as per my experience and test, stored procedure doesn't work well with DirectQuery mode.

What are the parameters in your case? Is it possible load all data in the report?

[louism]

Unfortunately loading all the data is not really an option as so much is calculated on the fly. For example we may want to query financial data in a number of different currencies (the stored procedures work out the currency exchange for a trade/transaction for the date that the trade happened meaning it's not as simple as running the report in just USD or GBP and then converting. 

While it theoretically could be possible to extract this information from the database and work out these calculations within Power BI the number of possible computations would be extremely high leading to a huge amount of overhead as opposed to doing the "heavy lifting" in SQL as we do now.

Is there no way of getting Power BI to refresh data from database? My goal is to have some form of wizard (that I will write myself) which takes the required parameters and passes them through to the report... I can get to the point of automatically generating the SQL but the next bit seems to be impossible at the moment...

[louism]

@Eric_Zhang

I had an idea... what if I made my "wizard" run the SQL queries and right the result out to a file? I would then just need a way of getting the report to refresh on command (does such functionality exist?)

The reports are generally only going to be viewed by one user (they are client specific) so I could use a file as the data source as this makes it flat file but I would need to be able to get my "wizard" to be able to tell Power BI to refresh it's data from this file...

[Eric_Zhang · Accepted solution]

@louism

The wizard sounds promising, but it is still very limited in Power BI side, as there's no any desiner API you can use to change a PBIX file. Check this idea Power BI Designer API and vote it up if you have interest.

For Power BI Embeded, The only avaiable approach I can think of is, instead of result out to a file, save the result into a Azure SQL DB. Then use a direct query to the DB and you have no worry about data refresh.

For Power BI Service, you can result out to a file and then use a gateway to schedule refresh.

[louism]

Azure SQL is probably the way to go (although due to the nature of the data being so confidential there has been a.... reluctance.... to go to cloud which I am trying to overcome.

This is definitely enough for me to explore further, thanks very much for your help!