shared datasets usage in power bi paginated reports

[imranasif]

Hi Dear Members and experts,

In power BI report server, we create power bi paginated reports on shared dataset. For example if we create a dataset with 10 columns and we create two paginated reports on top of that, report 1 uses 5 columns and report2 uses 4 coulumns out of that dataset, in both reports shared dataset will bring all 15 columns data from the datasource, although we want to use less columns.

So, what is the point of reusing dataset? 

it is much better design, if paginated report generates sql based on selected columns inside report. And dataset should be like a model.

Thanks

[imranasif]

@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

[hackcrr · Accepted solution]

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]

Shared datasets are designed to be used when multiple reports need the same set of columns. If you need different subsets of columns you should just use a local dataset inside each report. If you want a shared model concept on-prem you could look at deploying a semantic model on a SSAS instance.

[imranasif]

@d_gosbell @hackcrr , thanks for the reply, I explored SSAS modeling, I created a model with two tables and used used one column from each table in report builder (paginated reports). when I traced query from DAX studio, I found, SSAS running two queries to bring both tables instead of bringin only two columns used in report. So, I think, Power BI has totally different concept of modeling. Model means dataset in Microsoft. First, it brings dataset ( load all data from model tables) then it shows data in the report for the columns used.