The Importance of Modelling Data in Power BI

Daryl Wenman-Bateson, 4th May 2021

Importing Data to Power BI

Power BI is a great business intelligence, data visualization cloud-based service that Microsoft provides at an attractive price point. It is relatively easy to get started with Power BI, especially for anyone with database or Excel analysis knowledge. You can point Power BI desktop at a data source, import and enrich data quickly; data comes to life with attractive and interactive charts played back to business.

For those not experienced in business intelligence, there is a temptation to treat Power BI as an extension to the existing data source, merely modelling data as it exists in source databases or Excel spreadsheets. At first, this ‘appears’ to work as either fully normalised database tables or Excel-style slabs of data which can be related into Power BI and used to drive visualisations, however for the ‘have a go hero’ of Power BI the modelling stage is often overlooked.

The danger of skipping the modelling stage is although the dashboard may work initially, the data model quickly becomes a problem as the system grows. Wide flat tables will be inefficient to load and hard to cross-reference across datasets and creating horrible many-to-many relationships between data sets for all but the simplest of models.

Alternatively, highly normalised, transaction type tables are inefficient and cumbersome to use within Power BI, resulting in too many small tables and no summarisation.

Modelling with Facts and Dimensions

The problem requires a Power BI development team to take a step back and consider how the data is going to be used. You need to model your data to suit your business analytics needs, rather than merely represent your source system.

Star schemas are commonly used to produce optimal models within Power BI. Star schema origins are carried over from enterprise data warehouse where Ralph Kimball has championed their benefits, they proved invaluable for business intelligence modelling.

Star schemas are commonly used to produce optimal models within Power BI. Star schema origins are carried over from enterprise data warehouse where Ralph Kimball has championed their benefits, they proved invaluable for business intelligence modelling.

Model and Everything else Flows

If you are using Power BI, ensure that you are modelling your data correctly, ideally as Facts and Dimensions. Having slabs of data with a mixture of Metrics and descriptive Attributes will work whilst a system is small, not requiring the cross-referencing of multiple data, but will quickly become unwieldy as data sources are added, usage increases and the systems are used by multiple business entities.

If you can, starting with a well-modelled environment is always more efficient than converting an already existing implementation. Always try to look at how the business is likely to use the data so a model can be optimised appropriately for the business’s analysis. A bad data model becomes a weighted chain around the implementation, with constant workarounds, poor relationships, and performance.

A good data model will make Power BI fly.

 

At WB Data Focus we enjoy nothing more than seeing a well-planned Power BI implementation bring data and actionable analytics to life within a company. If you are at the beginning of your Power BI journey or wanting to take make better use of Power BI, feel free to contact us to discuss the next steps.