Forum Discussion
shared datasets usage in power bi paginated reports
Hi, imranasif
Shared datasets in Power BI allow for centralization and reuse of data models across multiple reports. This approach aims to ensure consistency, reduce redundancy, and simplify data management. However, as you've noted, this can lead to inefficiencies when reports only need a subset of the data in the dataset.
Best Practices to Mitigate Efficiency Issues:
Instead of creating a single shared dataset with all possible columns, consider creating multiple shared datasets optimized for different reports. Each dataset should only include the columns required for the reports that will use it.
Example:
Dataset 1: Columns A, B, C for Report 1.
Dataset 2: Columns D, E, F for Report 2.If using Power BI Premium, consider using Aggregations to reduce the amount of data being processed and fetched. Aggregations can help by pre-aggregating data at a higher level of granularity and serving queries from the aggregated data instead of the detailed dataset.
For large datasets, using DirectQuery or Live Connections can improve performance. With DirectQuery, queries are sent directly to the data source for execution, and only the necessary data is returned. Live Connections to SSAS or Azure Analysis Services provide similar benefits.
Using RLS can ensure that only relevant data is fetched based on the user context, reducing the volume of data transferred and processed.
In SQL Server Analysis Services (SSAS), models are typically designed to support a wide range of queries without fetching all possible data. Measures and dimensions are defined in a way that queries only pull the data required based on the filter context. Perspectives in SSAS Tabular Models allow you to create subsets of the model tailored to specific reporting needs. Reports using these perspectives only see and query the relevant parts of the model.
If this post helps, then please consider Accept it as the solution and kudos to this post to help the other members find it more quickly
d_gosbell thank for the reply. This is the case for Paginated reports. But, Microsoft has same approach for power BI desktop and service where the resueability concept is same.
we develop a model (it generates set of queries and model is created), if we reuse the model in multiple reports, all reports will trigger the whole model and then use a set of data required for the report ( eventhough one report require less data)
don't you think it is less efficient approach?
Efficent approach should be like in SSAS, create models and generate queries from the usability of columns in different reports on top of that model. This way we can use centerlize model without triggering full model for each report.
Thanks
Memorable MemberHi, imranasif
Shared datasets in Power BI allow for centralization and reuse of data models across multiple reports. This approach aims to ensure consistency, reduce redundancy, and simplify data management. However, as you've noted, this can lead to inefficiencies when reports only need a subset of the data in the dataset.
Best Practices to Mitigate Efficiency Issues:
Instead of creating a single shared dataset with all possible columns, consider creating multiple shared datasets optimized for different reports. Each dataset should only include the columns required for the reports that will use it.
Example:
Dataset 1: Columns A, B, C for Report 1.
Dataset 2: Columns D, E, F for Report 2.
If using Power BI Premium, consider using Aggregations to reduce the amount of data being processed and fetched. Aggregations can help by pre-aggregating data at a higher level of granularity and serving queries from the aggregated data instead of the detailed dataset.
For large datasets, using DirectQuery or Live Connections can improve performance. With DirectQuery, queries are sent directly to the data source for execution, and only the necessary data is returned. Live Connections to SSAS or Azure Analysis Services provide similar benefits.
Using RLS can ensure that only relevant data is fetched based on the user context, reducing the volume of data transferred and processed.
In SQL Server Analysis Services (SSAS), models are typically designed to support a wide range of queries without fetching all possible data. Measures and dimensions are defined in a way that queries only pull the data required based on the filter context. Perspectives in SSAS Tabular Models allow you to create subsets of the model tailored to specific reporting needs. Reports using these perspectives only see and query the relevant parts of the model.
If this post helps, then please consider Accept it as the solution and kudos to this post to help the other members find it more quickly