Forum Discussion
Best Practice for pulling in 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.
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.
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.
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!
