Working with Azure SQL and Power Bi (and DirectQuery)

[Anonymous]

Hi Everyone,

I'm after some input from people that have worked with Power Bi service and Azure SQL using DirectQuery.

In short, I'm looking at using Power Bi as an interactive way for a small number of users in my organisation to view a dynamic dashboard on financial data based on a data set containing a number of different businesses that is updated weekly. The database schema will be pretty simplistic but the number of rows may grow to millions over time.

I haven't used Power Bi on a largish project like this with SQL so I'm not sure how Power Bi will go as a solution.

My aim is to have a dynamic report showing key metrics for all businesses (or just one single business), with the user able to toggle between all or individual businesses using a control (i.e. slicer) at the top of the report - with the rest of the dashboard populated accordingly. The data would ideally not need to be imported into Power Bi before the selection is made.

My main concern is that as I haven't used Power Bi Service on a cloud based SQL that I'm missing something obvious or there may be some limitations on taking this approach - in which case i'd be better off looking for an alternative solution.

I'd love to hear anyones experience on similar projects (successes or failures) or thoughts and any resoruces you'd recommend.

Thanks! 🙂

[v-xulin-mstf]

Hi @Anonymous

If you want to apply slicer with columns in database, you need you import data into Power Bi first.

Without knowing the structure of your data, the following are workarounds in two cases.

Scenario 1: All businesses were in one database.

You can select the data you need with the sql statement as:

In this scenario, dynamic selection ofbusiness is not supported.

Scenario 2: Each business was in different database.

You can create parameters of server name and database.

In this case, the data would not need to be imported into Power Bi before the selection is made.

You can refer: https://radacad.com/change-the-source-of-power-bi-datasets-dynamically-using-power-query-parameters

Best Regards,
Link

If this post helps then please consider Accept it as the solution to help the other members find it more quickly.