Many companies today have an awful lot of data (internal+external) at their disposal. But is that data accessible and clear to everyone in the organization? Research shows that only 10% of data is actually visualized within companies. And of these visuals, only 4% is often used. Data can be visualized in different ways but the question is how to maximize the business value of visualizations so that the data is understandable for everyone and everyone can draw the same conclusions… This last point is perhaps the most important argument for a company to undertake such a project.
In a recent project we used Power BI to transform, combine and visualize different data sources. In this blog we will go a little deeper into the approach of the project and the final result.
Like most projects, we start with an AS-IS mapping. This mapping is done on both a technical and functional level.
For a data driven company, the technical mapping is usually pretty obvious. Provided that it is possible to connect to the internal server, this is usually limited to the search for various sources: from excel files with manual transformations to SQL databases with relational models (star schema’s) e.g. in an (online) Azure environment.
Based on this input, a first mock-up can be made very quickly. A subsequent presentation with all stakeholders brings everyone up to speed on the project.
Setting the scene
An initial mock-up gives an idea around potential outcomes of the project. Questions such as: “What exactly does this entail?” are answered.
In addition, this demo avoids discrepancy between any promises made in a PowerPoint presentation with reality and reduces initial inertia and skepticism among the people involved.
Engaging and enabling the organization
The organization is further engaged and stimulated through workshops with different teams to individual brainstorm sessions.
Gradually the TO-BE mapping takes shape and the scope of the project can be finalized. Note that thanks to these phases of “setting the scene” together with “engaging and enabling the organization”, it has just become a sort of partnership is created between the external consultant and the company with its stakeholders who, later on, are the main ingredients for a successful project.
“The Analytics Translator role is a must-have role here”
A defined scope with a corresponding timeline kick off the next step/phase: development
Development in PowerBI
The first steps are always done in Power Query Editor. Data from various sources is collected, cleaned and finally formatted. Of course, this requires tapping into the right data sources.
“Garbage In-garbage Out” paradigm is an incredibly well known paradigm that means no more or less than that the quality of the output is determined by the quality of the input. So make sure that some form of quality control takes place before working with the data. Finally, the data can be cleaned and formatted e.g. by adding columns of calculations.
“Power Query M vs DAX performance is often an overlooked key indicator!”
Once the data is collected, cleaned and formatted or not, DAX comes into play. Although for certain queries it is advisable to create the data model first (especially in the case of time intelligence).
DAX, Data Analysis Expressions, a language specially created for handling data models through the use of formulas and expressions, developed by Microsoft. This language can be used primarily to calculate measurements and KPIs which, in turn, can be used to create the necessary visualizations…
Power BI relies on data that is arranged in tables with clearly defined rows and columns whereas with Excel you can analyze almost any combination of rows, columns and cells. Especially in terms of flexibility, this is an important difference to keep in mind from stakeholders.
Make the change sustainable
The last phase is characterized by training and education where it is incredibly important to get all stakeholders on the same wavelength. Some stakeholders are quicker to embrace the change than others, so it is also incredibly important to get this message across:
“Try to embrace the project and make the change sustainable”.
In line with this proclamation, it is therefore also appropriate to provide the necessary post-Go-Live support so that any errors that have not been identified in development nor in testing are resolved as quickly as possible. Again, this is directly related to the adoption and embrace of a sustainable change by all stakeholders as well as the creation of a positive impact within the company.