“Project Executives” in Power BI? Please, no!

Few years ago I experienced a methodology of implementing Power BI for the enterprise which I just couldn’t understand, why.

Let’s call it “Project Executives”. It was a stream which’s purpose was to deliver Power BI reporting solution only for executives, and there were another stream – call it “Project Operations” – which was dedicated for lower level employees in the company. The two projects had completely different teams, only connected on the data director level. I was also told that I should create Power BI reports which are aggregated and contains only the necessary level of data which is important for that particular view, and the model size should not exceed 10 MB’s…

Why in my opinion is it a problem?

I think because it completely disconnects the organization. Executives have one set of reports where they define couple of KPI’s, and Operations have another stream with their KPI’s. Maybe at the beginning they define those KPI’s the same way, but moving forward it is almost impossible to keep them synchronized. When exceptions, scopes are coming into place, the two stream’s KPI’s would start being different and asynchronous, and the two groups will decide on different KPI’s. When an executive shows a KPI’s to their team, they will no understand that as their KPI’s are different. The result? Everyone in the organization will lose trust in the data we provide and nobody will use our reports at the end.

How to resolve that?

The basic datasets we are operating should be common for the entire organization, that’s my principle. We can create 100 different reports, but we need to think about the data models wisely we we would like to ensure consistency. Consistency here means couple of different things:

– Using the same data source: either the ultimate dataset or enterprise data warehouse.
– Using the same scope – applying the same filters, inclusions, exclusions
– Using the same definitions – for KPI’s, calculations, groups, etc.
– Having the same refresh time – a lot of times I saw that the only issue between two different data was the refresh time

In my organization my Power BI Architecture become the following. We have created appr a dozen of different data models out of which appr 5-6 is serving the 95% of all reports we have – roughly 70-80 reports for 7-8 different departments. The data models are the central repository of data and the most important KPI’s and those data models are stored and refreshed in a model repository workspace. With that my experience is that we hardly experience the above mentioned consistency considerations as issues. Here is the schema:

With this architecture we are still capable to deliver reports for different level of the organization but as this is a bottom-up approach to setup KPI’s and calculations, whatever we show for executives in their set of reports will be exactly the same as the Sales Team, the Operations Team or the Customer Service Team will see on their ones.

Any issues with the size of the models? I don’t think so. We should definitely consider that the model size will be higher, with it, but ultimately we will have one model with let’s say 150MB, which serves 15 reports which are less than 1MB with Live connection to the centralized data model, which reports would have been 5-15MB on their own with the different level of aggregations. This setup is working perfectly at an organization where it is enough to refresh data every hour, but for internal reporting it should be enough mostly.

Another consideration for this is the Accessibility for the reports. With the above structure the Power BI Services is built having one workspace for the data models and this workspace needs to be a Premium one enabling the hourly refreshment. The reports themselves are placed into departmental Report Apps so we have a Sales, and Operations, a Quality, workspace/app etc. The security has two layers. The first layer is the report security. It means that every department has an access manager who decides who can access the departmental reports. The other and maybe more important layer is the data level security. I personally prefer data democracy so I believe the most data people can work with is the better decisions for the company, but definitely some data should be restricted, like payroll data. For that we can setup Row Level Security or Object Level Security in the data models and with that we can make sure that anyone is creating report from that data model, the sensitive data will be accessible only for the appropriate people.

So that’s my approach which is working very well for my organization and which I can recommend for anyone for business reporting.