A New Episode of the Series Bad Practices In Power BI
I have been in this situation a hundred times. A client meeting. I am invited as the Power BI expert to discuss the client’s needs and explain how Power BI is the right tool. And it’s always the same.
“Meet Gil, our data visualization expert and Microsoft MVP”, announces my colleague, who owns the relationship with the client and oversights a dozen of other non-analytic projects with this account. I don’t expect him to recognize the subtle nuances here. But then the client speaks. “We really like Power BI as a visualization tool and would like to replace our current XYZ tools”. The discussion goes on, but the misconception is almost always the same. Power BI is still considered as a front-end data visualization tool. As a result, depending on the phase of the project, it may already be too late to use Power BI beyond the front end visualization gig. How can you harness Power BI’s true powers when an entire organization already has developed a data warehouse with complex ETL and DevOps processes to provide the data for a visualization layer?
When so much time and money are already invested in the traditional development of backend business logic, how can you fight the cognitive dissonance and tell your client or project lead that Power BI can be better utilized? Do you know that Power BI can pave the way in any analytics project, reducing ETL development time, increasing data integrity, improving QA effectiveness and enabling an agile development culture where your end-users can provide feedback from the first sprint and continuously after each sprint?
So, today as a new episode of “Bad Practices in Power BI” we will focus on one of the worst bad practices in Power BI – when we treat it as a visualization tool.
As a project lead or data architect that gets to decide how to implement a new BI solution, your bad practice starts during the planning phase when you choose to involve the Power BI guys at the end of the project. Your Power BI experts should start on day 1.
IT Architects, decision-makers and many Power BI practitioners are completely unaware of the data transformation (Power Query, dataflows, M) and modeling (Tabular, DAX) capabilities of Power BI and their key roles in creating a game-changer data culture that can truly combine self-service and enterprise analytics. For most of them, Power BI is a Tableau-compete tool, with very attractive interactive visualizations and Pie Charts that can be presented to their executives. As a result, they approach Power BI practitioners in the later stages of the project, and in many cases, they hire Power BI practitioners that lack skills in data preparations and modeling.
But we cannot blame IT and data architects for the lack of awareness. The lack of awareness starts with Power BI practitioners. Unaware of the potential of Power BI to turn them into full-stack BI demigods, these practitioners keep doing what they know the best: Manual data crunching and development of mesmerizing pixel-perfect visualizations.
Are you such a practitioner? You almost always rely on others to provide you the data in the desired format. You may have stumbled upon Power Query Editor and know how to remove columns and perform basic transformations, but nothing beyond it.
You focus on real relationships with your stakeholders and ignore model relationships. “Bring me the data in a single flat table”, you often say to your data pal. “Don’t you need it in a Star Schema?”, he may ask (and you are lucky to have him if he knows that Star Schema can help you), but you will probably reply back “A Star Wars schema? I prefer my lightsaber visualizations”.
You may know that there is a DAX language with Excel-like functions such as SUM and AVERAGE, but you always prefer to drag and drop numerical columns to the values section of your visuals (When you do it you create implicit measures – and this is a bad practice by itself – but you may not be ready to appreciate this nuance in your journey with Power BI. Instead, you will frown and redirect me to the beautiful and colorful reports you have made.
You are proud of the visualizations you have created. Your boss is also very proud. As you read these words, he may be showing off your visuals to his boss and stakeholders and may even take credit for your work, but you don’t care. Your job is secured for a decade. Life is beautiful. So are your reports.
I agree that Power Query and modeling may be perceived as topics that should be practiced by data engineers and not by data visualizations experts. It is not meant for everyone. So, if you are a data visualization expert, it is OK that you will decide to keep your focus on visualizations and avoid learning these tools. But know this – without having a strong data modeler in your team, your success may be short-lived.
Do you know what are the most important traits of a good modeler? I suspect that perseverance and humility are these traits. Why? Because unlike other software programming languages, the syntax of DAX and M are always sufficient to master these techniques. It takes practice and a long time of trials and errors. During this journey, many practitioners, reach a certain level and are not aware of the new pitfalls that await them. As they make a bigger impact in their organization, they become mission-critical in their projects. Filled with a sense of accomplishment, they may stop their learning journey. Knowing nothing about the opportunities that await them.
This is where we start seeing Power BI reports that are overly complicated. At the start, the heroes behind these reports are admirable by their team members and managers. With so many sophisticated calculated columns and measures in DAX that are barely readable, the authors of these reports become the envy of their peers. But over-time, these Power BI reports fail to keep up with their promise and eventually they become unsustainable. If you are a developer of an overly complicated report and you refuse to know what you don’t know, your short-term success will be defined by how fast you move to a new project. If you are never assessed by the sustainability or performance of your work, you will never realize you are applying the next bad practice.
You can see that there is a repetitive theme here – The lack of awareness that Power BI is a full-stack BI platform and the short-term focus on the visualization requirements as the only driver for success. This is, in my mind, the root cause of many bad practices in Power BI.
So, how did we get here? I think that it’s all related to one important element in Power BI – Its ability to enable any user to start fast. Power BI was initially designed to be a self-service analytics tool with a very low-barrier for beginners. If you have experience with PowerPoint and PivotTables in Excel, you will find it easy to drag and drop fields in visuals and start delivering amazing reports. If you know Excel formulas, you may think you know enough in DAX to create calculated columns and simple measures. So, why to bother to do more.
Another source for the lack of awareness of the data preparation and modeling’s true capabilities in Power BI lies in the fact that other BI tools simply lack such capabilities. Yes, as a BI developer in IT you may be able to do some ETL and modeling in other tools. But in Power BI you can do it using a unique technology that will allow your reports to easily be migrated across different products such as Excel, Power BI, Power BI dataflows, SQL Server Analysis Services, PowerApps, Azure Data Factory, and SSIS.
Now, if you would ask me what is the single missing behavior in Power BI that can prevent the bad practice by users to treat Power BI as a data visualizations tool, I will tell you a simple answer. It is the default Load button that should be replaced by Transform Data to expose new users to the data preparation elements of Power BI.
Microsoft recently changed the name of the button from Edit to Transform Data to help new users discover the opportunity they have, and launch the Power Query Editor to enable them to start preparing their data with few clicks of the UI. I hope this change will increase the use of Power Query, but I would go beyond such change and be more aggressive here. I would place the Transform Data as the default key and place the Load as a second key. I would even change the Transform Data to Transform now, my data is messy and change Load to Skip data transformation.
Please share your experience in the comments below. Did you encounter difficult situations following the misconception of Power BI as a data visualization tool? Do you think Power Query Editor should be more visible in the UI?
In the next episode of “Bad Practices in Power BI”, we will demonstrate the over-arching bad practice of “not knowing what you don’t know” through a sequence of badly implemented import steps of a simple Excel workbook. Stay tuned.