This article targets junior data engineers and data analysts who are familiar with:

• ELT pipelines
• Data warehouses
• Star schemas
• Snowflake

This article demonstrates basic data analysis and visualization in Power BI using the Online Retail data warehouse.

Note: This analysis uses the Online Retail dataset by Daqing Chen, made available through the UCI Machine Learning Repository under the CC BY 4.0 license.

Power BI: Online Retail Sales & Cancellation Analysis

1. Connect Power BI to Snowflake

I start by creating a Power BI role in Snowflake using the following statements.

CREATE ROLE POWERBI;

GRANT USAGE
ON WAREHOUSE ONLINE_RETAIL_WAREHOUSE
TO ROLE POWERBI;

GRANT USAGE
ON DATABASE ONLINE_RETAIL_DB
TO ROLE POWERBI;

GRANT USAGE
ON SCHEMA ONLINE_RETAIL_DB.RETAIL
TO ROLE POWERBI;

GRANT SELECT
ON ALL TABLES IN SCHEMA ONLINE_RETAIL_DB.RETAIL
TO ROLE POWERBI;

These queries create POWERBI, which is a new Power BI role. Then I grant the role privileges on the online_retail_warehouse and online_retail_db objects.

In Power BI, I select Snowflake as the data source option, as shown in Figure 1.

Figure 1. Select Snowflake as the data source in Power BI.

Next, I fill the required inputs to establish the connection with Snowflake. Figure 2 is an example of this step.

Figure 2. Submit Snowflake credentials to establish connection.

The required inputs are:

• A server value, which is found in the account information section in Snowsight.
• A warehouse identifier, which in this case is online_retail_warehouse.
• An optional role, which I created in the previous SQL statements.

Once the connection is established, I select the fact table and dimension tables, and choose the load option. Figure 3 is a representation of this step.

Figure 3. Select the Online Retail fact table and dimension tables.

Next I choose the load method as shown in Figure 4.

Figure 4. Select Import method.

I choose Import because the data is relatively small and static. The Power BI Import option creates a copy of the data in its in-memory VertiPaq storage engine to ensure faster performance.

Figure 5 shows the imported tables in the Power BI data pane, confirming that the data has successfully been loaded.

Figure 5. Imported Online Retail tables in Power BI.

2. Verify the Star Schema

Figure 6 shows the star schema inside the Power BI model section.

Figure 6. Online Retail star schema on Power BI.

I verify that fact_transaction and its relationships with the dimension tables are correctly defined.

3. Define the business questions

To guide the analysis, I organize the business questions in four categories: customer analysis, time-based cancellation analysis, product cancellation analysis, geographic cancellation analysis.

Customer Analysis

• Which customers generate the most cancelled transactions?
• Which customers generate the most transactions while having the lowest cancellation rate?
• Which customers generate the most revenue?
• Which customers purchase the largest quantity of products?
• Which customers purchase products with the highest average price per unit?
• Which customers generate high revenue while maintaining a low cancellation rate?

Time-based Cancellation Analysis

• Which periods experience the highest number of cancellations?
• How does the cancellation rate vary throughout the year?

Product Cancellation Analysis

• Which products are most frequently associated with cancellations?

Geographic Cancellation Analysis

• Which countries have the highest cancellation rate, and which have the lowest?

4. Define the analytical measures

I define the following analytical measures required to answer the business questions:

• Total transactions: total number of transactions.
• Cancelled transactions: number of cancelled transactions.
• Cancellation rate: percentage of transactions that were cancelled.
• Total revenue: total revenue generated.
• Total quantity: total quantity of products purchased.
• Average unit price: average price per unit.

With the measures identified, I can now implement them in Power BI using Data Analysis Expressions as follows.

In order to create a new measure, I right-click the fact table in the model view on Power BI, and select New measure.

Total transactions

Total Transaction = COUNTROWS(FACT_TRANSACTION)

I use the COUNTROWS aggregation function to count all the rows in fact_transaction and return the total number of transactions.

Cancelled transactions

Cancelled Transactions =
CALCULATE(
    COUNTROWS(FACT_TRANSACTION),
    FACT_TRANSACTION[is_cancelled] = True
)

I use the CALCULATE function to evaluate the COUNTROWS function under a modified filter context. The filter checks the value of the is_cancelled column, and only counts rows where its value is True.

Cancellation rate

Cancellation Rate = DIVIDE([Cancelled transactions], [Total Transaction])

To calculate the cancellation rate, I use the DIVIDE function to divide the Cancelled transactions measure by the Total transaction measure.

Total revenue

Total Revenue = SUM('FACT_TRANSACTION'[total_cost])

I use the SUM aggregation function to calculate the sum of the values in the total_cost column.

Total quantity

Total Quantity = SUM('FACT_TRANSACTION'[QUANTITY])

I use the SUM aggregation function to calculate the sum of the values in the quantity column.

Average unit price

Average Unit Price = AVERAGE('FACT_TRANSACTION'[UNIT_PRICE])

I use the AVERAGE aggregation function to calculate the arithmetic mean of the unit_price values.

5. Build the visualizations

5.1 Customer Analysis

Which customers generate the most cancelled transactions?

To answer this question, I create a clustered bar chart. I navigate to the Report view in Power BI. Figure 7 shows the resulting visualization.

Figure 7. Clustered bar chart representing the Top ten customers by cancelled transactions.

I select the Cancelled transactions measure for the X-axis. The Y-axis represents customer_id from dim_customer. I filter the chart to display the top ten customers with the highest number of cancelled transactions.

Which customers generate the most transactions while having the lowest cancellation rate?

I choose a scatter chart to represent the transaction volume vs cancellation rate by customer. Figure 8 shows the resulting visualization.

Figure 8. Scatter chart representing the transaction volume vs. cancellation rate by customer.

I select the Total transaction measure for the X-axis, the Cancellation Rate measure for the Y-axis, and customer_id from dim_customer for the value.

Customers with the highest total transaction volumes and the lowest cancellation rates are positioned toward the bottom-right.

Which customers generate the most revenue?

I add a clustered bar chart to visualize the customers who generate the most revenue. Figure 9 shows the resulting visualization.

Figure 9. Clustered bar chart representing the top ten customers by total revenue.

I select the Total Revenue measure for the X-axis, while the Y-axis represents customer_id from dim_customer. I filter the chart to display the top ten customers with the highest total revenue.

Which customers purchase the largest quantity of products?

I create a clustered bar chart to represent the customers who purchased the largest quantities of products. Figure 10 shows the resulting visualization.

Figure 10. Clustered bar chart representing the top ten customers by total quantities purchased.

I select the Total Quantity measure for the X-axis, and customer_id from dim_customer for the Y-axis.

Which customers purchase products with the highest average price per unit?

I add a clustered bar chart to visualize the customers who purchased the products with the highest average price per unit. Figure 11 shows the resulting visualization.

Figure 11. Clustered bar chart representing the top ten customers by average price per unit.

I select the Average Unit Price measure for the X-axis, and customer_id from dim_customer for the Y-axis.

Which customers generate high revenue while maintaining a low cancellation rate?

I select a scatter chart to represent the revenue vs the cancellation rate by customer. Figure 12 shows the resulting visualization.

Figure 12. Scatter chart representing revenue vs. cancellation rate by customer.

I select the Cancellation Rate measure for the X-axis, the Total Revenue measure for the Y-axis, and customer_id from dim_customer for the value.

Customers with high revenues and low cancellation rates appear toward the upper-left.

5.2 Time-based Cancellation Analysis

Which periods experience the highest number of cancellations?

To answer this time-based question, I use a clustered column chart as shown in Figure 13.

Figure 13. Clustered column chart showcasing cancelled transactions by month.

I choose the Cancelled transactions measure for the Y-axis, and i_month from dim_date for the X-axis.

How does the cancellation rate vary throughout the year?

I create a line chart to visualize the fluctuation of the cancellation rate throughout the year. Figure 14 shows the resulting visualization.

Figure 14. Line chart showcasing monthly variation in the cancellation rate.

I select Cancellation Rate for the Y-axis, and i_month from dim_date for the X-axis.

Observation:

When the number of cancelled transactions and the cancellation rate are compared, it becomes clear that a higher number of cancellations does not always translate into a higher cancellation rate.

For instance, November's cancellation rate is 1% compared to January's 2%, and there are 1,073 cancelled transactions compared to 700 in January.

The reason for this discrepancy is that the cancellation rate takes into consideration the total number of transactions that took place during each period.

5.3 Product Cancellation Analysis

Which products are most frequently associated with cancellations?

I select a treemap to visualize the products that are frequently associated with cancellations as shown in Figure 15.

Figure 15. Treemap visualization of top ten products ranked by number of cancelled transactions.

I select Cancelled transactions for values, and stock_code from dim_product for category.

5.4 Geographic Cancellation Analysis

Which countries have the highest cancellation rate, and which have the lowest?

I select the Map to highlight the cancellation rates by country. Figure 16 shows the resulting visualization.

Figure 16. Map showing the cancellation rates by country.

6. Final Report

Figure 17 shows the final and Power BI report, regrouping different statistics and visualizations developped throughout the analysis.

Figure 17. Complete Power BI report.

Conclusion

During this exercise, I practiced

• Connecting Power BI and Snowflake.
• Importing Snowflake data into Power BI.
• Developing analytical measures with Data Analysis Expressions.
• Creating graphics to examine data, including:
  • Bar Charts- Column charts.
  • Scatter plots.
  • Line charts.
  • TreeMaps
  • Maps

This workshop demonstrated how to utilize Power BI as an analytical and visualization layer on top of a data warehouse.

References

• Desining and Building an Online Retail Data Warehouse with dbt and Snowflake
• Online Retail dataset - by Daqing Chen