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.
There's a lot to digest and determine what is the best method moving forward for you and the client. If I can offer my feedback it would be to have an appropriate planning session with content creators (and even stakeholders) to identify the tables you're going to utilise, where the crossover occurs, and looking for opportunities to streamline and consolidate.
My personal experience - I've been fortunate to be part of implementation of PowerBI and it's been a changing and adaptive program of work. It started with native query implementation, and then import/transformation of specific requirements which led to multiple datasets being created. Our solution was to create a large dataset but optimise aggressively with Power Query (to the point of doing validation checks in Power Query instead of DAX, and reviewing DAX columns periodically to see if they can be added to Power Query). This led to ~86% reduction in PBIX file size.
My experience leads to a recommendation to identify crossovers and where possible build a single dataset to direct connect PBIX files to which should give you a single source of truth. Each direct connected PBIX can service specific needs, but it means everything you have is consolidated in 1 location. Performance would be impacted by the amount of data and timeframe etc. that you need to bring in, as well as sheer number of tables.. but so far I've been impressed by PowerBI's ability to handle things (I'm at about ~40 tables and 3-5 years of data and it's still performing quite well).
Thanks for the reply !
When you're saying "build a single dataset to direct connect PBIX files to which should give you a single source of truth", you're suggesting to have only one main dataset for all reports in the end ?
If so, I am not against this idea, but to me I would two main problems :
- Huge data model, not so easy to understand (even though we can create model views to isolate thematics)- When business users will need to create reports on their own by connecting to the dataset, they will see a huge amount of tables and columns, which could be intimidating whenever they need to create a basic report based one thematic only (but I might be wrong)
By the way, I totally agree on the process being an adaptive program, and that our first solution might be evolving over time. I just want to take the most appropriate choice in the first place, and ideally the most adaptive one !Yes I would be recommending a larger dataset.
With your response it sounds like there's another layer in the organisational structure that I haven't accounted for. My understanding now is that you're trying to design a reporting solution for content creators who will then utilise that to create reports for users to consume.
I would still be of the belief that a large dataset is the best way to go. There's a large number of variables, but limiting a scenario due to user intimidation would only be valid if their feedback clearly indicates that (e.g. too complex/difficult to use). The only way to really determine that is feedback sessions with them to understand what best solution can be implemented for them to capitalise on.
Once again, thanks for the feedback !
We had a few meetings regarding this topic, and someone from the team has been explaining that a huge dataset (with all tables, including billions-of-rows tables) could potentially be a bad practice since the simpliest report, needing only one subset of data, would target that big dataset, which we would be harmful in terms of performance.
I don't know your point of view on this claim ?
After all the discussions we already had internally (and that are still going), I believe the path we will be taking is many "extended datasets", which means not one dataset per thematic, but also with tables from other related thematics. Even though this way will imply having the same tables in many datasets, it will be easier to use the dataset for everyone, because many information will be accessible, without having ALL data at the same place.
Do you believe this "hybrid" approach is a good one ?
Once again, thanks for taking some of your time !
