The art of designing a Power BI model

Home Power BI The art of designing a Power BI model

Before you start building a new Power BI model, it is crucial to first establish the context. I have facilitated many Lean VSM (Value Stream Mapping) initiatives in order to make a process (more) lean. I have learned and loved the SIPOC (Supplier – Input – Process – Output – Client) approach as a Green Belt and have adjusted it in order to make it useable in designing the dataflow in and out of a BI model. So before you open up an empty BI model and start inputting data, first go to a large white-board, grab a few markers and start designing your model using the SIMOC approach.

This is a creative process starting by determining who the Client of the Model is. In this phase also determine the goal of the Client: what decisions are they going to base upon the Management Information delivered by the model?

Afterwards, determine the Output that best fits that goal. Actually start drafting the charts on your whiteboard. Don’t mistake trying to understand your clients information requirements for asking which visuals they would like to see and how to lay them out on a canvas. Remember the quote by Henry Ford: If I would have asked my clients what they wanted, they would have answered “faster horses”. Visualizing data in Power BI is an art that requires knowledge and experience with the nearly endless possibilities offered by Power BI.

The next step is determining the Input that is required for creating the dashboard. Power BI offers a wide range of ETL (Extract, Transform & Load) functionality in order to shape, clean and mash-up your data so don’t worry to much about data that doesn’t come in a nice and clean structure.

Afterwards, establish your data suppliers. If they are existing suppliers make sure to sit down with them and make them aware of the fact you’re building a Power BI model and are therefore automating the process. This requires you and your client to team up since more often than not Power BI is going to completely change the current process. Typically, a manual process where CSV or XLS files are exported and imported will be replaced by an interface between the source system and the Power BI model.

Last but not least, design your model. Before you start building, draft the kind of model you’re going to develop on your whiteboard. Often you’ll be managing a model with a Fact table in the centre surrounded by Dimension tables. But this could very well be the other way around, where a dimension table is bridging multiple fact tables. Also, a header – detail model could be best fitting to your scenario.

To be sure, don’t make doing a SIMOC into a formal process where output is documented and potentially audited. A SIMOC only serves to structure the thinking process of the modeller during the design phase. Also, it is highly advisably to do the SIMOC together with your client and suppliers in order to improve communication and understanding. After all, if you have decided to build a Power BI model, you have decided to try and realize the potential offered by Power BI in creating a far more efficient and robust dataflow in your organization.