There is a natural progression in Power BI adoption that I see time and again within enterprise customers of all sizes. A small team starts to build a core dataset in a Power BI desktop file (.pbix) with reports that transform their ability to manage their area of business, along with enormous time savings, increased trust in the numbers and a growing sense of calm that at least some things are now under control.
Word gets out and more aspects of the business are added to the dataset, along with more really valuable report pages. Before long, the number of tab in this master Power BI file has grown to 20 tabs (report pages) covering a range of topics, and there are loads of tables and oddly named measures scattered throughout the dataset.
It gets difficult to maintain. If we change this measure, which report pages will be affected?
I want to clean up these measures, have they been used?
People are asking for self-service access to the dataset. How will they know which of these measures is the right one to use?
People are asking for the report pages to be split out into separate 'Apps' according to subject matter content (e.g. HR, Sales, Procurement). Do we duplicate business logic across multiple pbix files, how do we manage keeping these in sync?
Some will claim a-ha! That's why you shouldn't let the business use Power BI themselves. It's true, this is a problem of Power BI's own making. The business gets hooked on the rapid pace of improvement. There needs to be a grow-up story where we can take the enthusiasm and early buy-in and transfer the momentum towards a more governed set of processes that can scale as the business becomes more reliant on key datasets.
Many of us in the Power BI world know that in theory, the next step is to move toward a hub and spoke model whereby the solution is split into separate pbix files. One contains the central 'golden dataset' which is published to the Power BI Service, and several 'thin reports' that connect to this dataset. Melissa Coates describes the advantages of separating datasets and reports beautifully in her post and accompanying video.
In truth, Power BI doesn't make this easy for us. There are significant gaps in the tooling available, just search online for Power BI version control. You'll find great articles explaining how to use OneDrive/Teams/SharePoint Online which will allow you access previous versions or your files, but good luck in understanding what has actually changed from one version of a Power BI file to another.
So. Now what?
You have a complex dataset and a whole bunch of report tabs and somehow you need to straighten out the model (the 'golden dataset'), and to split out the report tabs into various 'thin reports'. You need to be able to support ongoing development of the golden dataset whilst ensuring that the reports aren't going to break.
It reminds me of the tale of the person who when asked if they know how to get to Dublin, replies "Yes, but I wouldn't start from here!"
The journey I am going to set out on here will be to explore how to do this whilst staying entirely in the Power BI world.
I know that enterprise IT would head over towards Visual Studio, SSDT, SQL Server Analysis Server or Azure Analysis Services etc. I've dabbled there and know my way around, but at that point, understand that you're on a one-way trip because you've lost the ability to work with the dataset in Power BI Desktop.
I'm betting on a world, perhaps foolishly(!), where the Power BI team and the community continue to close the gap to a point where we can develop our 'golden dataset' in Power BI Desktop and publish it to the service. There's a bunch of reasons why currently that is going to be tough going for anything more than a simple model with one developer.
I eventually want to understand the work flow of having multiple report and dataset developers working together, all using Power BI Desktop, Tabular Editor and a collection of other productivity tools created for and by the community.
If there are things that block us along the way, we'll either overcome them or at least sign post them clearly. I'm not sure if this journey ends well, but I know there'll be bunch of useful learning along the way!!
In closing, I think the next steps are to explore the world beyond designing great reports that communicate clearly to the report reader. We already understand the responsibility of being clear, unambiguous and truthful when presenting data to our users.
We must understand that our customer has changed. Now we must aim to serve and communicate with report designers, some of whom we may never meet.
If I'm honest, that realisation was a bit of shock for me. I don't think I'll ever design a model the same way again.
Hope that helps! 😊