Changing your Star schema into a Snowflake schema in Power Bi.

Photo by Tomas Yates on Unsplash

Overview.

· You’ll walk through the steps to convert a Star schema into a Snowflake schema in Power BI using a real-world example of Adventure Works.

· The goal is to change the schema type so Adventure Works can perform more accurate data analysis and visualization.

Case study

Adventure Works has created a Star schema to store its sales data. However, the Star schema poses several issues with data analysis and visualization. A solution to these issues is to change the Star schema to a Snowflake schema. A Snowflake schema will create a more complex structure, leading to better performance and easier maintenance.

Adventure Works provides you with an Excel file called Adventure Works Data. The Excel file contains four tables. These tables are called Sales, Product, Region, and Salesperson.

Step 1: Open the Power BI project you created in the previous exercise, Configuring a Star schema.

· Access the project from the file path in which it was saved and open it in Power BI.

Step 2: Identify the dimension tables in the star schema that can be normalized further into related tables.

1. In the case of the Adventure Works star schema, two separate dimension tables can be normalized into look-up tables. These are Product and Region. You must normalize the Product table into Category and Subcategory tables to generate a Product hierarchy.

1. In the Power BI Data view, within the Calculations group, select New Table. Copy and paste the following DAX codes in the formula bar to create a new Category table. Tip: If you encounter an error with copy/paste, manually type the query. You’ll explore DAX in more detail in a later module.

Category = GROUPBY (‘Product’, ‘Product’[Category ID], ‘Product’[Category])

3. Once input, the DAX code generates a new table, as shown in the following image.

4. Repeat the same process to create a Subcategory table using the following DAX query. Tip: If you encounter an error with copy/paste, manually type the query.

Subcategory = GROUPBY (‘Product’, ‘Product’[Subcategory ID], ‘Product’[Category ID], ‘Product’[Subcategory])

5. Once input, the DAX code generates a new table, as shown in the following image.

Step 3: Configure the Snowflake schema.

1. Once you finish creating new tables, Power BI attempts to autodetect and establish the relationships between these newly created tables and the already-existing tables. If relationships were automatically created, you need to remove these relationships. Select and right-click the relationship connector, then select delete.

1. Create relationships between the Product table and Subcategory tables based on the Subcategory ID. Create further relationships between the Category and Subcategory tables based on the Category ID. You can create new relationships from the model view of Power BI desktop by selecting Manage relationships in the Home tab of the top Ribbon menu.

1. To configure these relationships, access the Manage relationships dialog box from the Model view of Power BI desktop. Make sure the cardinality for each relationship is set to Many-to-one and that all cross-filter directions are set to Single. These new relationships create a Product hierarchy. Any filter applied to the Category table now propagates to the Sales fact table to compute the Sales figures based on each Product category. This helps to analyze top-performing product categories and make strategic decisions.

Step 4: Save the project.

· Save the Snowflake schema as a new project. Ensure to provide an appropriate name and path to the folder on your local computer.

Conclusion

Congratulations! You have successfully migrated a Star schema to a Snowflake schema in Microsoft Power BI using the Adventure Works database. This new schema will allow for better performance and easier maintenance of your data model.

As an entry-level data analyst, mastering these techniques will help you build efficient and scalable data models for your organization.