Microsoft Power BI - Action Plan for E-commerce

Recently, we have a lot of customers who are interested in taking all the data they collected over the years and make sense of it. This is very common now with our E-commerce customers. This post will shed light on the process of using Power BI as a data reporting tool and the steps that are needed to be taken in order get cohesive and reliable reporting and dashboards. 

Power BI is a suite of business analytics tools that can deliver insights throughout an organization. By connecting Power BI to different data sources, you can generate dashboards and reports that allow you to better understand the current situation and future needs of the entire organization.

In most cases, a standard data set from a source is comprised of a single sales fact table with a few dimensions such as date, vendor, geography. However, in retail and eCommerce, things are a bit more complicated. In order to leverage Power BI in Retail and E-commerce, it is recommended to utilize a  multi-step process.

This post maps out the process of implementing a tool like Power BI for an e-commerce business. These recommendations are geared towards Power BI but not limited only to Power BI.

Step 1 – Understanding the Requirements (Discovery Phase)

This step includes a series of requirements meetings with key stakeholders in the organization. These include sales, marketing, supply chain, finance, merchandising and human resources. To start this discussion, we would start by creating a set of prototype dashboards to elicit feedback and demonstrate some of the capabilities that Power BI can provide.

Here are some examples of key performance measures and metrics that can become part of our requirements.


Category Requirements Benefit
Sales & Margins Provide sales and margins. Include trends, sales per SKU, store sales, DC Sales and Margin, Market Share vs. Competitors. Real-time insight into sales and margin performance of individual stores, brands, and CSRs.
Financial Overall financial results including EBITDA, Labor, Expenses, Cash Flow, Receivables, Payables, Plan vs. Actual Results. Timely financial data helps executives and managers to keep a pulse on the overall operations and enhances the ability to respond to changes in financial results
Inventory Provide Inventory/DC Key metrics and KPIs including delivery performance, defects, out-of-stock. Optimal management of inventory balances and distribution center operations is critical to the overall performance of the business.
Marketing Deliver key metrics for each website on web page views, sessions, web users, web search, Social Media engagement, campaign performance, coupon/promotion analysis, marketing spends vs. budget Ability to visualize the impact of marketing activities and campaigns on website and in store as well coupon redemption and marketing ROI.

Step 2 – Mapping the Requirements to Data Entities and Sources

The next step is to develop an entity model based on the requirements gathered in the first step.

This requires us to model the data to define the data objects and their relationships. In contrast to a relational database, the goal of the data modeling is to use two types of data tables. Fact tables and dimension tables. The fact table contains the data corresponding to a particular business process. Dimension tables contain the descriptive attributes related to each instance of the data. Fact tables and dimension tables are related to each other.

Here is an example list of tables:


After creating the data model and entities, we will need to map the relationships between the entities. This modeling can be done within the Power BI Desktop tool or in SQL Server.

After building the data schema including the tables and their relationships, each data entity needs to be traced and its associated data attributed to a data source.

Step 3 – Loading the Data

There are two approaches to populating data in Power BI: Import or Direct Query. If the dataset is less than 1 GB (compressed), you can import the data directly into Power BI since that will provide the highest level of performance for your reports and dashboards. In the case of a larger data set that is greater than 1 GB (compressed), DirectQuery should be utilized from the data source(s). Power BI can support the following DirectQuery sources: SQL Server, Azure SQL, Azure SQL Data Warehouse, Oracle, SAP HANA and TeraDeta.

Step 4 – Creating Calculations and Measures

With Power BI, DAX (Digital Analysis Expressions) can be used to create measures and calculated columns. However, this is limited only to data stores within Power BI. With larger datasets, DirectQuery will require that the creation of the calculated columns and measures be in the data source. However, Microsoft has indicated that the capability to add calculations within Power BI from DirectQuery sources will be made available sometime in 2017.

Below are some sophisticated columns that might be created:

  • Comp Sales
  • Current Year Results
  • Previous Year Results
  • Year-Over-Year Comparisons
  • Rolling 52-week trend
  • Out-of-Stock or Low Stock Conditions
  • Sales by Hour
  • Expenses as a Percent of Revenue
  • Promotion/Coupon Performance
  • Marketing Campaign Performance

Step 5 – Developing Reports and Dashboards

Once the necessary calculated columns and measures are developed, the next step is to create reports and dashboards to deliver key insights to the organization. Power BI provides more than 20 “out-of-the-box” visuals that can help deliver insights. In addition, there are more than 50 custom visuals available in the Power BI Custom Visuals gallery.


Here are some examples:

Power BI Dashboard Sample 1 Power BI Dashboard Sample 2

Ultimately, as the goal of this post was to provide a sample action plan for an e-commerce business to implement Power BI, it is easy to see that these tools provide a deeper insight into sales, operations etc. We would recommend a multi-step process in which we analyze the requirements and build a separate data store. This unique data store would be built with your data requirements in mind and store data from multiple data sources. After creating the data store, it is connected to Power BI and creates reports and dashboards and you are on your way to a more effective method of forecasting results and managing your organization.