Enterprises globally rely on insights from analysed raw data in their lines of operation to survive.
This survival necessitates credible business intelligence on future predictions, which must be developed through visualisation-friendly tools.
Proper business intelligence determines the direction the organisation will take and how it will survive in its industry.
From my desire as a data analyst to understand what tools are important in the field of business intelligence and visualisation, I have conducted an analysis and found out that - Power BI is a great tool!
Data visualisation is a cornerstone of effective data storytelling.
With the use of Power BI, all who are not data analytics-savvy are open to consuming information through trends and representations of pie charts, line graphs and columnar charts.
Power BI has incorporated key concepts to ensure the ease of all users in learning through data models and visualisations - these concepts include: the star and snowflake schema, fact and dimension tables and how important it is to create relationships between data types for easier use. All of these concepts will be explained further below:
Data Modelling and Visualisation Importance in Business Modelling
The BI in Power BI stands for business intelligence.
Business intelligence (BI) is a wide range of tools, procedures, and methods that concentrate on analysing corporate data to produce useful insights that aid in operational and strategic decision-making.
Decisions determine the direction of an organisation. These decisions are only helpful when they are arrived at in a timely and cost-effective manner.
Power BI helps board members in enterprises to arrive at meaningful conclusions on the right direction to be taken by the organisation through descriptive analytics, reports and dashboards.
To arrive at data models, Power BI embraces the star and snowflake schema concepts.
What is a Schema?
A schema in Power BI is the arrangement and structure of data inside a data model.
Schemas impact the effectiveness and performance of data searches and reporting by defining the connections and relationships between data within the model.
Designing the best data models that facilitate thorough analysis requires an understanding of schemas.
Understanding the Star Schema Concept in Power BI
This is a fundamental principle in Power BI.
A star schema is a type of database schema that is used in data warehousing and business intelligence.
Its name is coined from the star-shaped output displayed after the organisation of data into dimension tables and fact tables.
It eases data organisation and helps improve the analytical capabilities of the data.
Features of a Star Schema
Simplicity
Since the star schema is simple and easy to comprehend, even individuals with less technical expertise can utilise it.Flexibility
It makes it simple to add new facts or dimensions without drastically changing the current schema.Performance
Since fewer joins are required to get data, the decentralised structure speeds up queries.
Dimension Tables Vs. Fact Tables
Both of these concepts are crucial to understanding the star schema approach in data warehousing and business intelligence.
Dimension Tables
Dimension tables offer characteristics that are descriptive of the information in the fact table.
During analysis, they aid in the classification and filtering of data.
Features of Dimension Tables
Primary Keys
Every record in a dimension table is uniquely identified by its main key.Attributes
These are descriptive fields that provide context (e.g., customer demographics and product name).
Using a retail shop example once more, a product dimension table could resemble this:
| Item ID | Item Name | Item Category | Price (Ksh) |
|---|---|---|---|
| 203 | Bread | Consumables | 130 |
| 204 | Bulb | Electricals | 250 |
| 205 | Cussons | Toiletries | 390 |
| 206 | Hammer | Tools | 450 |
In this case, the information about each product enhances the fact table's analysis of sales transactions.
Fact Tables
A key component of the star schema is fact tables, which include quantitative information that may be examined.
Features of Fact Tables
Foreign Keys
The keys connect to dimension tables and offer context for the measures.Measures
The provided figures are numerical data that quantify business performance indicators, such as sales income and units sold.
| Order ID | Product ID | Customer ID | Quantity sold | All SalesKES |
|---|---|---|---|---|
| 101 | 1001 | 10001 | 220 | 220000 |
| 102 | 1002 | 10002 | 600 | 90000 |
| 103 | 1003 | 10003 | 960 | 480000 |
| 104 | 1004 | 10004 | 2002 | 260260 |
Every record in a dimension table is uniquely identified by its main key.
Each row in this table denotes a transaction (a fact), and the columns offer metrics related to that transaction.
Outstanding Differences between Dimension Table and Fact Table
Structure
Dimension tables are narrower with more attributes, whereas fact tables are often bigger with fewer attributes.Purpose
Dimension tables give events or transactions context, whereas fact tables document them.
Understanding the Snowflakes Schema Concept in Power BI
This is a revised version of the star schema.
A snowflake schema is a multi-dimensional data model in which dimension tables are divided into subdimensions.
Dimension tables under snowflakes schema are organised into sub-dimensions.
Data engineers can divide each dimension table into logical subdimensions in a snowflake structure. Although this increases the complexity of the data model, data analysts may find it simpler to deal with, particularly for some types of data.
Strengths of Snowflakes Schemas in Power BI
Fast data retrieval
Dimensions link to sub-dimesnsions making it easier to retrieve.Enforces Data Quality
Since data is interlinked, its integrity is reliable.
Weaknesses of Snowflakes Schema in Power BI
Cost Implications on Initial Set up
This is high since the architecture "expands" from the centre.Rigid Data Model
The data model remains repetitive, hence this cannot be easily remodelled to fit emerging needs.
From the discussed schemas, relationships are created amongst the data dimensions and sub-dimensions, making a database of interconnected data of an organisation.
Good Models are necessary for Accurate reporting and Performance
For easier data analysis, it is important to develop well-thought-out dashboards and trend reports.
These dashboards and trend reports can only be developed using good models for easier visualisations by the necessary decision makers.
Without the development of good models, there will be no accurate reporting and therefore no ease of performance of plans to actualise the KPI's in any organisation.
These are my thoughts from analysing Power BI as a guide to developing models through schemas.
I will be happy when, upon going through this writing, you will be kind enough to share your comments or improvements to my write-up.
Happy learning!
I look forward to hearing from you!
Just let me know 🚀
Top comments (2)
Great read kev
Great article—very clear and informative. Well done.