Are you new to Power BI? Maybe you've been using it for some time but you're still not sure how the end to end process works?
This flowchart was made for the users that are still in the beginning of their Power BI journey!
In my diagram, I divided the report development process into four main steps:
1) Analyse data and build your data model
2) Report Design
3) Prepare to share your report
4) Share your report
In this post, I'm going to guide you through each one of the steps, and share some useful tips and information on the way.
Curious? Check the flowchart below:
Colours legend:
Blue Squares - These steps are usually mandatory in most reporting projects
Orange Squares - These are optional steps for advanced visualisation features
Pink Squares - These are optional steps for Q&A Setup
Green Squares - These are optional steps for Row Level Security
Purple Squares - This is a mandatory step for On-Premises data sources
You probably noticed I have a small section on the right called Power BI Integrations.
I decided to add this section because I think it's important to also know what integrations with other tools are there, and how this can be useful in your use case.
I won't be talking about the Power BI Integrations in this post though. If you want to find out more about it, stay tuned!
If you've been following the latest blog posts, you probably recognised this flowchart from the Power BI Report Performance Best Practices post.
Even though the post is similar, this one will be more focused on getting started with Power BI, not on performance. However, I highly recommend you to check the Performance Best Practices post too!
1) Analyse data and build your data model
🞂 Requirements gathering:
This is one of the most important steps in any reporting project! Before you start to build anything, you need to understand your user's requirements and understand the data you will have available. A lot of times this part of this first step is neglected! You need to always have in mind that the end user wants insights, and not a "dump" of what you have in your database, try to understand him/her and the data before building anything 😉
All the other steps will be dependent on how good you understood the business user, the problem you're trying to solve and the data, so take your time.
Where to start:
Learning paths:
By the end of this step, you should have:
> Understood the audience that is going to be using your report to make decisions
> Understood the problem you're trying to solve
> Defined the data you're going to need to solve the problem (list of data sources)
> Understood the data you have to solve the problem
> Defined the success criteria for your project
🞂 Connect to data:
After you've done your requirements gathering, you should now be thinking about connecting to your data sources. With Power BI, you can connect to many different types of data. These sources include basic data sources, such as an Excel file or CSV. You can also connect to online services that contain all sorts of data, such as Salesforce, Microsoft Dynamics, Azure Blob Storage, and many more.
When connecting to data, it's also important to define what type of connection you're going to use. There are three types of connection modes: Import, Direct Query and Live Connection.
As a rule of thumb, you should:
> Consider using Import Mode when your dataset is less than 1GB, your data only needs to be refreshed a few times a day max, your data model is complex (lots of data transformations, lots of measures), you want the best performance
> Consider using Direct Query when you have a large dataset (>1GB), you want your data to be updated in near real time, your data is stored in a source that supports Direct Query (link), you are not planning to do complex data transformations (e.g. combining tables), you are not planning to build measures with complex logic
> Consider using Live Connection when Analysis Services is already in use, you have Analysis Services 2016+ Tabular Instances, Analysis Services instances is on the cloud, you are not planning to do transformations over the data or complex measures
Where to start:
What data connectors are there (aka what data sources can I connect to?)
Import vs Direct Query vs Live Connection
Learning paths:
By the end of this step, you should have:
> Defined the type of connection (Import, Direct Query, Live Connection) you're going to use for each one of your data sources, according to the business needs and the data itself
> Connected to your data sources
🞂 Transform your data:
Now that you've connected to your data sources, it's time to shape and combine your data. After all, who doesn't like to have a cleaned and shiny dataset?
In this step, we are going to shape the data to meet our reporting needs. Shaping data means transforming the data: renaming columns or tables, changing text to numbers, removing rows, setting the first row as headers, removing errors and so on.
We can also combine data. Combining data means connecting to two or more data sources (tables), shaping them as needed, then consolidating them into a single useful query. In other words, combining multiple tables together, and if you're asking yourself "What if one of my tables comes from an Excel file and another one from SQL Server??", worry not! With Power BI you can combine tables from different sources, combine files from a folder...
Where to start:
Learning paths:
By the end of this step, you should have:
> Your queries (tables) cleaned and ready for the modelling part (please remember to only include in your tables the data you will be using in your report! In Power BI less is more!)
🞂 Build your data model:
Creating a great data model is one of the most important tasks that an analyst can perform in Power BI. By doing this job well, you help make it easier for people to understand and use your data, which will make building valuable Power BI reports easier for them and for you.
The idea in this step is for you to design a data model that is intuitive, high-performing and simple to maintain. To achieve this, you should aim for a star schema model.
What is a schema?
Often, data is stored in database tables which have relationships with each other. A schema is simply a group of related tables.
What is a star schema?
A star schema is the simplest form of a dimensional model, in which data is organised into fact tables and dimension tables. A dimension table contains reference information about the fact table, such as date, product, or customer. A star schema is diagrammed by surrounding each fact table with its associated dimensions tables. The resulting diagram resembles a star:
So, what should I do now that I know what a star schema is? You should start building the relationships between your tables, aiming to get a star schema in the end!
In simplistic terms, these relationships will work as an Excel "VLookup".
Learn more about Start Schemas:
Where to start:
Learning paths:
By the end of this step, you should have:
> A relational data model (tables related to each other through relationships) - preferably you should have a star schema model
🞂 DAX measures:
In this step, we are going to create the business logic behind the figures we want to present in our report using DAX.
Data Analysis Expressions (DAX) is a programming language that is used throughout Microsoft Power BI for creating calculated columns, measures, and custom tables. You can use DAX to solve a number of calculations and data analysis problems, which can help you create new information from data that is already in your model.
The DAX topic is so huge I decided to keep it simple here. If you want to get to the depths of DAX, Alberto Ferrari and Marco Russo from SQL BI are your guys! Trust me!
Where to start:
Learning paths:
By the end of this step, you should have:
> All the business logic (DAX measures) for the figures/values you are planning to use/present in your report
2) Report design
Report design is not just about adding charts and slicers, it's about presenting information. Always think about how you're going to present the information and align your report with the branding of your company (good looking reports makes end users happier!).
🞂 Branding
Choose colour theme
One of the key things when creating your Power BI report design is the colour scheme/theme. Colours, when used correctly, can make an average report look very compelling and insightful!
Build your Power BI colour theme
Power BI allows you to import themes, or change it directly in Power BI Desktop. To know how to change your colour theme, check this link.
Use the Power BI Theme Generator to create customised themes.
Get company logos, icons and related images
Having logos of your organisation in your report doesn’t sound like a “big deal”. However, the logos, in combination with the appropriate colour theme, are the best way to show the brand identity of your organisation in your reports. They will also look a lot nicer!
Company images are good for:
>Page background (make sure the image is faded so the report is easier to read – bright colours and images will take away the attention to important KPIs etc, will make the report difficult to read)
> “Home pages” – in this case you can use the original image without fading as this pages tend to have just the index, little information displayed
Icons are good for:
> Visual reading, interpretation and highlighting of values
Where to start:
By the end of this step, you should have:
> Defined the colour scheme your will be using and imported it to your Power BI report
> Have a few images related to your company (and some icons if you choose to use them)
🞂 Define Report Layout
In this step, we are going to define how we are going to display and organise the information in our report.
To achieve this, I'm going to introduce a concept I love, and that will help you on this journey: the User Centred Design.
User-centred design is a collection of processes that focus on putting users at the center of report design and development, in other words, it's the process of developing a report from the perspective of how it will be understood and used by the end user.
Employing User Centred Design to a report design results in a report that delivers a more efficient, satisfying, and user-friendly experience for the user.
Asking yourself the questions in the image above will help you frame what you need to do and focus on in terms of defining the layout and usability of your report.
Where to start:
Learning paths:
By the end of this step, you should have:
> A clearly defined wireframe of your report layout or, in other words, a prototype (try not to skip this step, even if you have to do this exercise mentally only or in a piece of paper!)
🞂 Build your report (add visualisations)
Visuals allow you to share data insights more effectively and increase comprehension, retention, and appeal. They are a fundamental part of your report because they help your audience connect and interact with the information to make informed business decisions quickly.
What you should be thinking about in this step:
> Add visualisations (charts, KPIs, tables etc) to reports
> Choose an effective visualisation to display information
> Format and configure those visualisations so that they meet the UX criteria
Avoid visualisation variety for the sake of variety. Visualisations should paint a picture and be easy to "read" and interpret. For some data and visualisations, a simple graphic visualisation is enough. But other data may call for a more-complex visualisation - be sure to make use of titles and labels and other customisation to help the reader.
Bonus: AI Visuals
Because numbers don't tell you everything, you can use the AI features in Power BI to examine your data further, to see what people are saying, and to get more constructive and meaningful results, which will help you ultimately make better business decisions.
AI visuals available in Power BI (April 2021):
Decomposition Tree, Key Influencers, Smart Narrative, Q&A
Where to start:
Learning paths:
By the end of this step, you should have:
> Most of the report designed and ready for the final tuning (next step). Your report should, ideally, look pretty much like a finished product already.
🞂 Advanced visualisation features (Optional)
This is the step where we tie everything we've done so far in terms of report design together. Here, we will be looking at some final details such as visual interactions, defining bookmarks, building custom tooltips, drill throughs and others.
Visual Interactions (link)
By default, visualisations on a report page can be used to cross-filter and cross-highlight the other visualisations on the page. For example, selecting a state on a map visualisation highlights a column chart and filters a line chart to display only data that applies to that one state.
You should think carefully about the interactions between the visuals, as this might affect not only the design, but also the performance of your report.
Bookmarks + Buttons+ Selection Pane (link)
With bookmarks, you capture the currently configured view of a report page, including filtering and the state of visuals. Later, you can go back to that state by selecting the saved bookmark.
Tooltips (link)
You can create visually rich report tooltips that appear when you hover over visuals, based on report pages you create in Power BI Desktop. By creating a report page that serves as your tooltip, your custom tooltips can include visuals, images, and any other collection of items you create in the report page.
Drill throughs (link)
With drill throughs, you can create a page in your report that focuses on a specific entity such as a supplier, customer, or manufacturer. When your report readers use drill through, they right-click a data point in other report pages, and drill through to the focused page to get details that are filtered to that context. You can also create a button that drills through to details when they click it.
Personalise visuals (link)
When you share a report with a broad audience, some of your users may want to see slightly different views of particular visuals. Maybe they'd want to swap what's on the axis, change the visual type, or add something to the tooltip. It's hard to make one visual that satisfies everyone's requirements. With this capability, you can empower your business users to explore and personalise visuals, all in report reading view. They can adjust the visual the way they want and save it as a bookmark to come back to.
Where to start:
Learning paths:
By the end of this step, you should have:
> A finished report, displaying the data the users will be using for decision making in an easy and user friendly way 😄
Some Learning Materials that can help you:
Pluralsight Learning Path: Microsoft Power BI for Analysts Path | Pluralsight
Udemy: I love Udemy, so I can't really recommend just one course. Check the Power BI courses available and the reviews, that's usually how I make my decision :)
Guy In a Cube: Link
SQL BI: Link
And many many others!
Interested in learning more about Power BI Report Design? Then the Power BI Report Design Bootcamp is for you!
