E-Commerce Specialty Features

Source: https://www.linkedin.com/pulse/microsoft-power-bi-retail-ecommerce-neal-levin

Neal Levin @ Rightpoint

The next step is to develop an entity model for our requirements. Data modeling is a science in and of itself so I will not attempt to teach that in this post. Suffice it to say, the following table distills the requirements into facts (what happened) and dimensions (how we want to view the data).

The Calendar/Date dimension is usually very important in a retail/eCommerce environment. Most retailers do not operate on a traditional monthly calendar. Typically, retailers compare results on a weekly basis so that more accurate comparisons can be achieved. Therefore, the calendar/date dimension should include a week number of year column and day number of week column so that valid year-over-year comparisons can be achieved. Furthermore, most retailers who operate physical stores, include same-store sales comparisons, which effectively exclude stores that have been opened (or closed) in the past year. In order to accomplish this, the store dimension should include an open and close date column which can be used to create comp sales measures and calculations.

The next step is to create relationships between the entities. The following diagram shows an example data model (created within the Power BI Desktop) for one of our eCommerce clients. One of the exciting data modeling features of Power BI is its ability to handle both one-to-many and many-to-many relationships. A common many-to-many relationship in retail is between inventory and warehouse (i.e. there are many inventory items in each warehouse and multiple warehouses can support each inventory item).

The next step is to trace each data entity and associated data attribute to the data source. The illustration below highlights a sample mapping of data sources to data entities.

Step 3 – Loading Data

There are two approaches to populating data in Power BI: Import or DirectQuery. If your data set is less than 1 gb (compressed), you should consider importing the data to Power BI as this will provide the highest level of performance for your reports and dashboards. If your data set is greater than 1 gb (compressed) or is nearing the 1 gb limit, you should utilize DirectQuery from your data source(s). Power BI can support the following DirectQuery sources: SQL Server, Azure SQL, Azure SQL Data Warehouse, Oracle, SAP HANA and Teradata.

If you are able to import your data to Power BI, you can take advantage of the excellent data shaping and query capabilities within Power BI including:

· Ensuring that attributes used for data relationships are of the same data type

· Merge or concatenate columns as needed

· Remove unnecessary columns

· Remove duplicate rows

· Rename columns to be more user friendly

· Replace values

· Group or split columns

· Pivot or un-pivot columns

As you load data, Power BI can automatically detect relationships between tables and you can edit these relationships or add new ones as needed. Having the proper relationships between tables is essential for creating reports and dashboards.

Step 4 – Creating Calculations and Measures

If you import your data to Power BI, you can utilize DAX (Digital Analysis Expressions) to create measures and calculated columns. A simple calculation would be if you have a column called “Sales” and another called “Cost”, you could create a calculated column called “Margin” which is “Sales” – “Cost”. Much more sophisticated columns and measures might include:

· 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

If you utilize DirectQuery to populate Power BI, you currently must create the calculated columns and measures in the data source. However, Microsoft has indicated that the capability to add calculations within Power BI to data from DirectQuery sources will be made available some time in 2017.

Step 5 – Developing Reports and Dashboards

Once you have developed the necessary calculated columns and measures, the next step is to create reports and dashboards which deliver key insights to your audience. Some recommended dashboards might include:

· Sales by store, product category and item

· Excess inventory or stock-out/potential stock-out conditions

· Sales and margin trends

· Sales by hour (time of day)

· Coupon/promotion performance

· Marketing campaign performance

· Manufacturer rebates

· Web site performance

· Social media engagement trends

· Purchase order variance by vendor

Power BI provides more than 20 “out-of-the-box” visuals to help you deliver insights. In addition there are more than 50 custom visuals available in the Power BI Custom Visuals gallery:

Benefits and Conclusion

The retail industry has a real need for real-time information on their business. Hopefully this post helps you understand how Power BI can deliver the key insights necessary to improve organizational performance and profitability.