Best Practice for pulling in data?

[h2022]

I work for a manufacturing company, and most of the data I use is stored in a sql server database. I reguluarly write SQL queries against this database to extract information that people want to know, and if they want to see it regularly I will import that query and then build a report for them in Power BI. For example, I wrote a big query for the sales team to pull in orders, sign off times, product info, customer details, etc. Then logistics asked for a report about trucking, shipping etc. so I did the same for them. Our database is massive so I generally write the query in SQL, filter it with a date parameter of some kind and then import the query to Power BI.

Is there a better way for me to do this? Like should I have tried to use a data flow or something? Or is it ok to house several different datasets for the different reports? I just feel like our database is huge and while sometimes I use the same tables for my queries for different requests, sometimes it's pretty specific and random and I need to pull in tables I don't normally use so then I'm not sure how well a dataflow would work.

Thanks for the advice....

[DataInsights]

@h2022,

A few thoughts:

1. Create a pbix file for each topic area (Sales, Logistics, etc.).

2. Use a star schema for optimal performance.

3. Include relevant tables for the topic area. Depending on data volume, you may want to use DirectQuery. This would enable you to make a large number of tables available to users, allowing them to explore and reducing the need for you to write custom SQL for each request. Consider using Dynamic M Query Parameters.

https://docs.microsoft.com/en-us/power-bi/connect-data/desktop-dynamic-m-query-parameters

4. Designate a separate database for reporting to avoid impairing performance of your application database.

[h2022]

Thanks for the reply! I do write against a reporting database to avoid chugging the live one... but I'm interested in using Directy Query just don't know alot about it. Will check out the link you sent. In my head it's just easier to aggregate my values and apply parameters in my separate queries but I also am not familiar with any other way of doing it. Like there's an Items table I use that is huge, and I normally just aggregate it in SQL first before importing (ie. sum(items) group by date, company or whatever). Do you just have to do these aggregations in Power BI then in order to scale down the amount of data?

[DataInsights · Accepted solution]

You can put that aggregate query in a DirectQuery table (custom SQL). Think of DirectQuery as a pointer to the underlying table; data isn't stored in the pbix, as it is with Import mode. You give up some functionality with DirectQuery, but it may not affect your specific requirements. Dynamic M Query Parameters make your queries interactive by allowing users to pass parameters via slicers or filters. This allows you to write generic SQL and let users narrow down result sets for their particular needs.

[h2022]

Oh that's cool, sounds like a better way to optimize everything and make things faster. I will look into this - thanks for the insight!