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.
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 !It's definitely a valid concern especially if you are looking at billions of rows of data. It may be better to explore an alternative solution prior to PowerBI (for instance the next step i've been informed of for my journey is going to be looking at understanding Azure Data Lake and how that can be utilised).
My experience is with sub-100 million rows of data and if you don't have the appropriate hardware and network to support it you're going to hit walls. The hardest part is initial design and effectively limiting scope creep (for instance you may have access to billions of rows of data, but what's the point of bringing that in when people are only interested in the past 3-6 months for a date range? Limiting the date range using parameters will vastly reduce the size you're working with).
Personal experience again - Realistically I still utilise a mix of datasets with some redundancies but that is to manage data needs of my customers covering 'the beginning of time', short term data (max 6 months), and finally real-time data. Each of those datasets have some redundancies, but most notably they have different refresh rates (real-time has 30 minute refreshes, short term daily, and 'beginning of time' has incremental refresh established looking to update the past 1 month every week to find retrospective changes).
Having the same tables in many datasets is not so much of a problem "IF" your transformation is the same - you can optimise and cleanse a table through Power Query fantastically, and then just open the query editor and copy/paste the code straight over so you're working with the same refined table ready for deployment.
Thanks
I get your point, but for now I don't have much information about the reason some table are very big, but will investigate later to make sure that no data is being unnecessarily loaded !
Regarding your last point about having the same table many times, I would go even further by using data flows, which we are going to do ! This way, transformations will always be centralized.
Anyway, would any of you both be willing to provide a "summary" answer about my initial question (when to split datasets, when not to), so that I can validate it as a "good" answer, and maybe help other people in the future ?
Thanks to you both !