More power for your Power BI dashboard
29. August 2023
Data analysis allows us to learn from the past, make predictions and adjust course - that's no secret. But not all data is created equal. With interactive tools like Power BI, almost anyone can set up a dashboard today, but developing a reporting that transforms data into information requires expertise and finesse.
Daniel Keys Moran, programmer & science fiction author
In my current project, I am assisting a government agency in evaluating data from multiple departments and services. The focus is on IT service, asset and incident management (e.g. the ticketing system). The goal is a dynamic, automated evaluation that provides an overview of the telephone system and resource utilization, supplier management and much more, in order to enable targeted monitoring and control. The challenge here is the large amount of data that flows into the reporting from various sources (e.g. Matrix42). My side quest: The data quality definitely has room for improvement.
The Microsoft service "Power BI" is currently on everyone's lips as a tool for data analysis with interactive visualization and can be conveniently shared with other users via MS Teams or also via the Power BI Reporting Server. Today I would like to share my top three tips on how I go about building a new dashboard with Power BI.
First things first: think about exactly what you want to achieve with the dashboard and what story you want to tell. The goal is never to evaluate numbers for the sake of evaluating numbers. Metrics are great and all, but without context, they're worthless.
For our ticketing system, we not only evaluate how many new tickets were opened, but also how many were closed in the same time frame and what their overall volume is. This is the only way to interpret the numbers in a meaningful way.
However, this does not give us the full picture. This is where historical data comes into play. For example, we could compare the amount of new tickets this month with the amount of new tickets in the previous month or with the same month from the previous year. That way, piece by piece, we'll get a clearer picture of how the count - new tickets opened - should be interpreted in the overall context.
Now we have done exactly the thing we weren't supposed to do and started right in the middle of it. The question is, why do we need the amount of new tickets in the first place? So let's take a step back and talk about KPIs and CPFs. When I develop a new dashboard, that's always my first approach: are there already any KPIs? If so, what are they? If not, what would be a meaningful performance indicator?
Only when the management-level KPIs have been established do we go down a level. The key figure for our ITSM dashboard is simple: achieving our objectives as defined in the service level agreement. These then need to be broken down and put into the right context.
The number of new tickets is an indicator of the SLA performance. We make this key figure interpretable by reporting the total volume and comparing it with historical data. In addition, we categorize the number of new tickets and show from which departments they originate, for example, to better explain an exceptionally high ticket volume in a certain period.
Once our KPIs are defined, the next step is to better understand the data behind them. This sequence is not set in stone, by the way. Sometimes, once you've sifted through the data, you have to go back and adjust the KPIs or metrics derived from them.
Especially within large organizations or with large amounts of data, there is usually not the one person who has a complete overview of which data is (or should be) evaluated and which is not. Thus, it is necessary to involve relevant stakeholders, to identify the required data, to check KPIs for their meaningfulness, and to make the necessary decisions. With this new understanding, the previously defined KPIs can be gradually refined.
This is where data quality comes into play. Our evaluation shows that 90% of the 10,000 tickets come from Department A (one of seven). My gut feeling tells me: That can't really be the case. A look at the data shows: I am right. Only five of the 10,000 entries have a department in them at all. Of course, this example is completely exaggerated, but it illustrates the underlying problem. Poor data quality distorts the result enormously and must be taken into account in the evaluation, for example by filtering out the empty data records.
In summary, our task is to gain an overview of the data, to question the logic of the key figures determined from it, and to check the data sets behind it in detail. Since, as we have already learned, data without context is worthless.
More is not always better. The added value of a dashboard is that it helps the viewer to gain insight into the overall situation within a very short time, to identify problems and to take necessary actions. What applies to the frontend is also true for the backend.
From a technical perspective, this is not strictly necessary, but in order to maintain an overview as the person responsible and to be able to work more efficiently with the data, I recommend reducing the data set to the bare essentials.
In my case, I categorize the data by topic. Everything related to incident management goes to incident management, everything related to service request management logically goes to service request management, and so on. The data is transformed, filtered, and narrowed down so that my reduced data set really only contains the data that is included in the evaluation.
With these three steps - defining the KPIs, understanding the data behind them, and reducing the data set accordingly - I'm well prepared to get down to the business of effectively implementing the dashboard. Sometimes the Q&A function also helps me with this.
Strictly speaking, Power BI's Q&A feature isn't really a tip for building a dashboard, but it's one of the most exciting features for me.
For example, I could feed the data sets for the number of tickets and the categorization of the departments into the AI-based tool. I then ask: "How many tickets were opened per department?", and Power BI provides me with the corresponding answer.
When using the Q&A feature, it's important to name data sets correctly and learn the appropriate vocabulary, but once you're familiar with it, the AI tool makes the technical implementation of the dashboard so much easier.
However, there is one thing that artificial intelligence cannot do (yet): performing the preceding mental work, screening key figures and checking them for meaningfulness.
Author
Simon Hoefler
Simon's consultancy focus is on the design and documentation of IT service management processes and the recording of business requirements. In addition, he also acts as a central coordination point for stakeholders and team in support of the project management.