Data Analysis in Power BI: How to Create Dimensional Queries and Decomposition Tree

Guided project on OLTP to OLAP data transformation and Decomposition Tree using US Super Store Sales data

Image for post
Image from Unsplash

Earlier, for any data transformation or data conversion project, we used different ETL tools. Yes, for large-scale enterprise projects, definitely we need ETL or data warehouse tools. But, using Power BI we can very well transform any transactional data to analytical data that means OLTP data model to OLAP data model.

In this blog, we are going to learn how we can create a Star-Schema data model from a transactional data file. We will explore Decomposition Tree AI visual.

Table of Contents

1. Data Set

2. Abstraction

3. Learning Objective

4. Import Data

5. Transform Data and Data Model Build up

6. Data Visualization

7. Conclusion

Data Set

I have taken US Super Store data from Kaggle for this project.

https://www.kaggle.com/juhi1994/superstore

Abstraction

There are 21 columns in this data set.

Image for post
Image by Author

Data set contains the below information

  1. Order details
  2. Customer details
  3. Product details
  4. Sales, Quantity, Discount, Profit information
  5. Shipping details
  6. Geography details

Learning Objective

The primary objectives of this case study are following

  1. Dimensiontional Data Model Build(OLTP to OLAP transformation)
  2. AI Visuals — Decomposition Tree

Import Data

  • Let’s start with the Get Data option under the Home tab. As this is a CSV file, select the Text/CSV option from the drop-down list
  • Select the file named US Superstore data.csv
  • After selecting the file, data will be displayed in the below format
Image for post
Image by Author
  • Click on Load and save data.

Transform Data and Data Model Build up

  1. Go to Power Query Editor and verify the data type of all columns whether it is in the required format or not. If not, then make those changes.
  2. Go to Left Panel → Right-click on the table under Queries and click on Reference.
  3. A new reference table will be created.
Image for post
Image by Author

4. Rename this table with Dim — Product.

5. Under the Home tab → Go to the Manage Column group → Click on Choose Columns drop down → then click on Choose Columns and one dialog selection box will open with all column lists.

6. For the Dim — Product table, select only Product IDCategory, Sub-Category, Product Name columns. Unselect other columns and click on the Ok button.

Image for post
Image By Author

7. Now if we select any Product ID from the drop-down, we can observe multiple rows for one Product ID. This is a dimension table, so we have to create unique for each product.

Image for post
Image By Author

8. To create unique rows, go to the Reduce Rows group under the Home tab. Click on Remove Rows → Remove Duplicates and unique Product rows will be generated. Please note, during the above selection, make sure you have selected the right column which will be denoted as the primary key of this dimension table.

Image for post
Image by Author

9. Product Dimension table is ready. Click on the Close & Apply button and come out from Power Query Editor.

10. Now in Desktop mode, click on Model under the left panel section and create the relationship between two tables.

Image for post
Image by Author

11. Now in Desktop mode, go to the Fields section and select Dim — Product table to create a dimensional hierarchy.

Image for post
Image by Author

12. We need to follow the same above steps to create Dim — Customer and Dim — Geography tables and rename the US Superstore Data table to the Fact — Sales table.

Image for post
Image By Author

13. Create two date tables using the New Table feature under the Data section of Desktop mode. One date table is for the Order date and another for the Ship date. You can check my other blog to know about the Date table creation processhttps://medium.com/analytics-vidhya/data-analysis-in-power-bi-how-to-build-forecasting-dashboard-with-retail-data-2f7918819dcc

14. To follow best practices, we will hide those fields in the fact table which have been already selected for dimension tables. For example, Customer Name, Product Name, Country, etc.

Image for post
Image by author

Data Visualization (AI Visual)

Power BI offers different AI visuals to leverage AI features in simple charts such as bar and column. Decomposition Tree is one of them.

Decomposition Tree

It relates to a tree with branches. It allows a user to breakdown a measure across multiple dimensions in any order using the drill-down feature. For any type of ad-hoc analysis, it can be used.

Image for post
Image By Author

This visual allows users to identify the split of the profit and sales based on product category, sub-category, and product name. This would help business teams to target those groups accordingly in their future campaigns in order to maximize the net profits.

Download

Please download the code file from the below location

https://github.com/arpitag1/Power-BI

Conclusion

In this guided project we learned about the following things.

1. How to build a dimensional data model from transactional data.

2. Dimensional Hierarchy creation.

3. AI-Visuals — How to create Decomposition Tree visuals

If you have any questions related to this project, please feel free to post your comments.

3 comments

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

%d bloggers like this: