The Ten Commandments for (Power BI) Reporting
Several times, I have been asked about some basic rules when creating a reporting solution. Usually, I answer, "Well, that's not so easy." But here I am, starting to write them down.
Introduction
Although I'm not religious, the principle of having a nice and even number of rules is fascinating. Therefore, here you find a rule set of ten rules for creating reporting solutions.
As a Power BI specialist, I know most about this tool, but I'll do my best to formulate the rules in the most generic way possible.
Bear with me if I'm not 100 % successful at this.
1. You shall ask the User for his questions to answer
When starting a new BI/Reporting/AI project, the essential question to pose to the client, stakeholders, or users is: What questions do you want to answer with the new solution?
If we don't know what is requested, we can't judge if the data can support providing the answer.
Or we can't evaluate which data we need from the source system(s).
Don't be afraid to ask more questions instead of less. One more question can save you from failure or doing more work than expected.
2. You shall know the data
Before loading, we should analyze the data to understand its structure and patterns.
We should find the possible hierarchies, key columns, and, most importantly, the candidates for the key columns, which we can use for relationships.
Moreover, we should check for non-matching and missing keys in the relationships between the tables.
This is much easier if the data comes as one large table, as there wouldn't be any relationships if we left it that way. But more on this in the fourth commandment.
3. You shall manipulate your data as soon as possible
Usually, we must manipulate the data when loading them into our reporting tool of choice.
Regardless of the tool, we must perform these manipulations as early as possible.
- If possible, when pulling the data from the source.
- If this is not possible, manipulate the data during the load, for example, by crafting SQL Statements containing the manipulations if the source is a relational database.
- If possible, build an intermediary data storage, like a data warehouse, to store the data in the cleanest way possible.
- If this is not possible, use the tools included in the reporting tool. For example, when using Power BI, do it in Power Query.
- As a last resort, use your tool's possibilities, like calculated columns or tables.
Sometimes, manipulating the data before loading them into the data model is impossible or too complicated.
For example, we may need to calculate data based on aggregations or complex business logic.
Of course, this is a valid reason for an exception.
The worst I have ever seen was a large number of data manipulations in Power Query while loading data from a data warehouse. The load takes a very long time, and we must plan to rebuild the load using SQL queries.
4. You shall stick to a simple Star Schema
A Star Schema is the way to go when building a user-friendly Data model for Reporting.
Here is a simplified drawing of a Star Schema:
In case you don't know this approach, here is a short introduction:
A Star Schema consists of at least one Fact table. The Fact table sits at the center and contains columns for all measurable values. For example, it can be a value of money or a count of units. Additionally, it contains so-called Foreign keys to Dimension tables.
A Fact table contains a list of Events. An event can be a transaction, a measurement, or a booking for a travel trip.
Dimension tables contain a Primary Key and descriptive data, such as data about customers, products, stores, etc.
The Fact table is linked to the Dimension table through the Key columns.
As you can see in the drawing above, each Star Schema contains at least one Date table. A Date table has a list of Dates. Each event in the Fact table is assigned to a Date.
If events must be analyzed at the hour or minute level, a Time table is needed, which contains a list of hours, minutes, and even seconds, if necessary.
The advantage of Dimension tables is that one person or a group can concentrate on one Dimension while others can work on another. For example, the Product team works on the Product Dimension, while the Customer Center works on the Customer Dimension. This way, the most competent people can concentrate on their competence to deliver the best possible result.
There is much more to know about Star Schemas.
You can find a link to the Microsoft Data Warehouse Toolkit in the Reference Section below.
Although it is relatively old and written with Microsoft Products in mind, the principles described there are still valid and valuable.
4.1. You shall not be afraid of data redundancies
This is a subset of the fourth commandment; therefore, it's number 4.1.
Look at the following Data model:
Look at the red-marked part.
As you can see, the Geography Dimension is linked to the Store and the Customer Dimension.
While this can make sense from a Database perspective and for reducing redundancies, this can lead to issues when trying to analyze the data by the store geography and the Customer Geography at the same time. Moreover, depending on the tool, this can need additional coding as both relationships cause ambiguity in the data model (The Fact table is reachable from the Geography table from both the Customer and the Store Dimension).
One solution is to copy all needed attributes from the Geography Dimension to both tables, Store and Customer.
Although this causes data redundancy, it makes life easier for reporting.
Additionally, it is easier for the data model user, for example, the Report Designer or a Data Analyst, to understand which Country attribute is tied to the Store or the Customer.
Now, the data model looks like this:
5. You shall prepare the Data model for easy use
As the first commandment mentions, the central point is "How to answer the user's questions?"
Consequently, the data model should be built with this in mind:
How can I best support the Data Analysis? Or how can I build the data model to support the search for answers?
Moreover, in my projects, I almost always receive the same question from business users: How can I create my own reports or data analysis?
I need to build the data model beyond the technical requirements to fulfill this request. I must consider the users' requirements and, more importantly, their capabilities, habits, and points of view.
This brings me back to the fourth commandment: Build a star schema.
This approach is advantageous because it separates the descriptive data from the transactions and business events, which makes it easier to search for specific attributes.
6. You shall keep your calculations as simple as possible
When you follow the third commandment, this can be an easy one.
It can help keep your calculations in the data model less complex as long as you offload as much of the calculations between the data source and the data model, your calculations in the data model.
This might not be practical in any case, nor is it always the best approach, as described here:
Pre-calculated aggregations for Power BI – Why should you avoid them
Nevertheless, this can be challenging.
My approach in Power Bi is to add additional columns or create calculated columns to support my Measures.
For example, I create additional columns in my date table to make my Time Intelligence calculations easier:
3 Ways to Improve your reporting with an expanded date table
And sometimes, complex calculations are unavoidable.
On the last day, I had a challenge with a Measure.
As I progressed during the development, I stopped at one point as the code became increasingly complex.
I took a break, talked with my colleagues, and found a less complex approach. The new approach shortened the DAX to Code to a third, as it was before.
Avoid the trap of writing complex expressions. There might be a more elegant and straightforward solution.
7. You shall define a template for your standard reporting page
This one is relevant when working as a team and developing several reports.
Start with a template.
In Power BI, we can create a template from an existing report:
Create and use report templates in Power BI Desktop – Power BI
Such a template should contain:
- The default font of your company.
- The default colors, according to your Corporate Design.
- Other elements, like your company logo or a background picture, according to your Corporate identity and design.
- If you're building a data model, a standard Date table.
- Other tables that every developer should use.
- As long as you don't use a versioning tool, like GitHub, GitLab Azure DevOps, or any other tool, add a report page with a Change Log or another place to store the change log.
Ensure all developers know where the template is stored and that they're using it.
8. You shall not overuse colors in the report
Do you know IBCS?
This is a rule set describing how to design efficient reports.
One rule is to use colors sparingly.
As soon as you start using colors, think about visually impaired users.
I mean those people who suffer from color blindness.
Look at these statistics to learn how many people suffer from color blindness:
Prevalence of Color Blindness: Global and Regional Statistics
Create a PDF without colors and only with gray scales. If you struggle with distinguishing colors, you may need to consider changing the colors.
Moreover, any colors with meanings in everyday life should be avoided when using them with different meanings.
For example, red, green, and yellow.
These colors have well-defined meanings in everyday life and should be used accordingly: red for negative numbers, yellow for warnings, and green for positive numbers.
If you want to show a number without a meaning, use blue. For example, when displaying a deviation without a definition, if a positive number is good or bad.
Do not use overly bright colors, like neon green or similar colors. They only distract the user from the message you want to convey with the report.
9. You shall concentrate on answering the user's questions
As mentioned in the first and fifth commandments, the user is at the center.
It's easy to show the data attractively.
However, when the numbers and how they are presented are useless to the users, they are meaningless and a waste of time.
- Ask them what they need to know.
- Do it as early in the project as possible.
- Emphasize the importance of their decisions.
- Make sure to understand the working processes and what they need to know.
- Talk with them!
The more insight you can gain from the users, the more the project will be a success.
It's unfortunate when you've spent countless hours and days building a good solution, and nobody uses it.
10. You shall not strictly follow any rule set
There are always exceptions.
Most of the commandments above are subject to scrutiny when you start your project.
Sometimes, it makes no sense to follow a strict rule when it would be better to deviate from it.
The art is to know when and to what extent you should deviate.
But use your knowledge and gut feeling to decide which approach is correct for your specific scenario and the existing requirements.
This is not a religion, and these rules have no dogma.
But be warned: Deviating too early might be the wrong approach. I have already experienced a situation in which I deviated from my standard approach too early, only to find out it was the wrong decision.
Conclusion
I hope you enjoyed reading this piece as much as I enjoyed writing it.
Here are all the commandments:
- You shall ask the User for his questions to answer
- You shall know the data
- You shall manipulate your data as soon as possible
- You shall stick to a simple Star Schema
- You shall prepare the Data model for easy use
- You shall keep your calculations as simple as possible
- You shall define a template for your standard Reporting page
- You shall not overuse colors in the report
- You shall concentrate on answering the user's questions
- You shall not strictly follow any rule set
Formulating these rules, which I follow instinctively, was a challenge. I also realized something: The tenth commandment is the most important.
As a consultant, my favorite answer is: "It depends."
While some commandments are fundamental, like the first, second, and ninth.
The others have some room for movement.
For example, take the fourth commandment. I stick to it as much as possible.
But sometimes, a Star schema has performance drawbacks, which can only be solved by alternative modeling. When the report's performance is better, nobody will judge you badly for your modeling approach.
However, when building reporting solutions, regardless of the tool available, start with these commandments to create a strong foundation.
Then, with your growing knowledge, you will gain confidence to know when to deviate from them.
References
The Microsoft Data Warehouse Toolkit:
The Microsoft Data Warehouse Toolkit, 2nd Edition – Kimball Group
Requirements for a Date table in Power BI:
Design guidance for date tables in Power BI Desktop – Power BI
