PowerBI Embedded & Direct Query

[Chthonian]

Good morning Datanuts, 

I was wondering and hoping that somebody in this fabulous community can assist me in uderstanding some aspects of PowerBI Embedded and more specifically what considerations I need to keep in mind if I were to put some report tables into direct query mode. 

The scenario is this that in our business we have an embedded capacity that we use to expose reports to our customers via a power pages site using the "App owns data" setup. We are currently on an A1 SKU and we are struggling with import refreshes and database limits on this tier due to the fact that our fact table in the report contains 50M+ records that are housed on an Azure SQL database.

Our DBA is an old school database guy and is vehement that we absoloutely cannot and will not allow direct queries from our embedded setup to our production database. I am trying to get information, factual and hopefully backed with docs that if we were to implement direct query against the database there would not be a massively detrimental load on the server.

I understand that there would be work to do that optimises queries within the report. So as I mentioned above I would also like to get your suggestions and pointers on what is the best way to go about this. The report in question is looking at about 20K views per month, with about 40% of those views happening within a one week window in each calendar month. 

I hope this makes sense and if any clarity is needed just let me know and I will provide as much information as I can 😃.

Thanks in advance,

David

[Brunner_BI]

Do you only have the production report in that workspace or also ther artifacts? Maybe that would be a way to optimize things.

You could try to start turning the capacity off for some times (if there is that option - like during your night) and then upgrade to A2 if nothing else works

Just with DirectQuery I have to say I am not sure that this would be better since users will probably have to wait forever to load the report? You can test it in Power BI Desktop and see the performance and implications on RAM before publishing it to the Embedded workspace

[lbendlin]

will not allow direct queries from our embedded setup to our production database

Create a copy of the database and use the copy for the Direct Query reporting.