Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.

Reply
h2022
Frequent Visitor

Best Practice for pulling in data?

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....

1 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.





Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




View solution in original post

4 REPLIES 4
DataInsights
Super User
Super User

@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.





Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




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?

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.





Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




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!

Helpful resources

Announcements
Microsoft Fabric Learn Together

Microsoft Fabric Learn Together

Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City

PBI_APRIL_CAROUSEL1

Power BI Monthly Update - April 2024

Check out the April 2024 Power BI update to learn about new features.

April Fabric Community Update

Fabric Community Update - April 2024

Find out what's new and trending in the Fabric Community.