On starting a Power BI development project, most if not all the talk will be about reports and dashboards. This naturally means that reports and dashboards are seen as the deliverable’s of the project, particularly by the stakeholders and end users. This isn’t necessarily wrong, in fact its understandable. However, this does then mean that little or no time is spent talking about data sets as deliverable’s
And just in case you’re new to Power BI, data sets are data cubes, identical in fact to tabular SQL cubes and Power Pivot data models.
If we compare building a Power BI solution to building a house, we could view the data sets as the foundations, the reports as the walls and the dashboards as the roof. And on making this comparison, it’s perhaps easier to understand why the data set is so important. OK, the foundations aren’t the most sexy or exciting thing in the world, but get the foundations wrong and the walls and the roof will probably come crashing down at some point.
My 18 years of BI development experience has taught me that most BI development teams approach BI development, including Power BI development, in the following way:
- Take some existing reports/dashboards, or some mock-ups of these.
- Throw together a data set (or equivalent) that allows these reports/dashboards to be developed.
- Release the reports/dashboards to users for testing
- The users (eventually!) sign off the reports/dashboards, and other than scheduling some data loads and refreshes that’s the job done.
This approach may sound familiar, and you may say it works. However, consider this:
What will happen when a change to one of those reports or dashboards is needed, or when a new report or dashboard is required. And what will happen when the nightmare of all nightmares for us BI developers occurs and the users tell us “the numbers look wrong”!
The users don’t know or understand that there’s a data set sitting behind the reports/dashboards; And even if they did, and they have report development skills, the data set is so ambiguous, messy and quite frankly scary looking that they haven’t got a chance of being self sufficient. So who does the buck pass to in this scenario? That’s right! people like you and I (the BI developers). And do we have time to fulfil such requests? no usually not!
So what do BI development teams need to do to address these problems?
We need to ensure that users are aware of the data sets and have some data set documentation to refer to. If we’re really thorough, we’ll establish data set super users who the developers work closely with during the development. These users can then help and guide other users.
If you approach the BI development in what I believe to be the correct way, then I predict you’ll spend approx.70% of your development time developing data sets.
Approx. 20% and 10% respectively will be spent developing reports and dashboards. Just to emphasise this point even more, I’ve used three Power BI data visualisations to present this breakdown (why not I thought!).
10% for dashboards may sound a little high, and until recently I would have said 5%. However, recently I developed some dashboards with various text boxes and images applied to them, and there was a series of dashboards all interlinked, so the development time was greater. Having well thought through mobile layouts also tends to be more important on dashboards than reports .
But what will we spend that 70% doing exactly. Well here’s some suggestions:
- Base the data set design on a more thorough and structured data requirements exercise, which goes beyond analysing some existing reports/dashboards or mock-up’s of these. Focus on the measure and its related data not the report – as I so often say.
- Hide all columns and measures in the data set that don’t need to be visible at the reporting layer.
- Ensure all columns and measures that are visible at the reporting layer have correct formats and summarisation levels applied to them.
- Ensure all columns and measures that are visible at the reporting layer have meaningful and intuitive names (remove underscores, add spaces etc.).
- Add commonly used reporting hierarchies to the data set; Year > Quarter > Month > Date being a common one of course.
- Ensure the data set contains all commonly used measures, including those that need predefined and hard-coded filters applying to them (specified within the DAX formula). Such measures will help to ensure accuracy and consistency in reporting.
If you’re hoping users will use the natural query language Q&A feature in the Power BI Service, then pretty much all of the above are absolutely essential.
As always we’d love to hear from you. Does the context and resulting problems resonate with you? Do you agree with the 70-20-10 breakdown? Do let us know your thoughts and experiences.
We hope you found this useful and/or interesting. If so please like, share or comment.