Forum Discussion
Best approach to split datasets and reports
It really comes down to what the business requires. If the data being made available for reporting purposes has crossover points then I would be looking to merge them together and filter out what's not useful so they are only working with information that can add value.
In my experience it comes down to what information needs to be displayed (does it cross themes? if so - merge it together), as well as the rate of refresh that will determine impacts server side.
As an extra note that I didn't cover before - having a single large dataset and allowing people to 'connect to the dataset' doesn't cause slowness in design/creation. This may be slightly wrong but it's my understanding the connected PBIX files does not store any of the data/transformation. That's done on the original PBIX file, and subsequent PBIX files that are connected are incredibly small (example: I have a 280mb file as my main dataset, but every connected PBIX file is less than 500kb). This also helps manage single source of truth which is a common business issue.
Solution SageFrom wha you are describing, I strongly feel the need for a Semantic Layer/Model. See the link blow for details.
I am not a fan of "relying on the DirectQuery functionality on datasets to cross-reference them". if you are needing to link two different datasets for your reporting requirements, then it is a strong indication that those datasets need to be one dataset.
Thanks for the reply !
In my understanding, Power BI datasets reprensent a semantic layer, so this is already what we are planning to do, except we're not sure about creating :- One dataset per thematic (a cross-reference them with DQ)
- One dataset per thematic + other datasets that combine each others (ie : sales + calls)
- One big main dataset with all tables inside of it
From your last sentence, you seem to be suggesting to have only one big dataset, or at least datasets that combine data from multiple thematics, am I right ?
- Shahfaisal
Emerick wrote:From your last sentence, you seem to be suggesting to have only one big dataset, or at least datasets that combine data from multiple thematics, am I right ?
Correct, if you are finding the need to query/report/analyze across multiple data sets. The downside of one large/complex model is that it is a bit difficult to use and navigate various objects. If you have worked with Analysis Services, you know that Perspectives were very helpful in breaking down a complex model into small *virtual* models without psychically creating separate models.
Though perspectives are not supported out of the box in Power BI Desktop, there is a solution. See this post https://data-marc.com/2020/08/18/power-bi-visual-customization-using-perspectives/
Yes, I know about perspective, such a shame we cannot have them "easily" on Power BI, but hopefully someday... 🙂
Anyway, thanks for the feedback.
Someone from the team is arguing that a very big dataset with all data (some tables having billions of rows) could be a very bad idea, performance wise, especially when "little" reports will need to query that huge dataset.
I got to admit that I don't see why it would be bad in terms of performance, since the report will only query a subpart of the data model ... or do you think that person is right ?
To me, the huge drawback of big datasets (without perspectives) is the navigation experience.
