Data and AI Training

Home | Power BI | Excel | Python | SQL | Generative AI | Visualising Data | Analysing Data

Superstore Dataset

The Superstore dataset represents a retail store selling to customers in the US from 2018 to 2012. It is fictional data. It is useful as an exercise in data modelling and DAX.

Data

There are several files – assume that of these are a download of a table as if they were generated by the operational IT system of this retailer. The files are:

• transaction – this is a fact table. It contains several numerical fields, sales, Quantity and Profit. These are values that we want to group aggregate. It has a lookup field (foreign key) into the four tables below
• product – a dimension table that lists each product and grouping into categories and subcategories
• customer – a dimension table that lists each customer and their segment
• geography – a dimension table that lists the post code and broader geographical attributes (City, State, Region)
• order – a dimension table that lists each individual order, the dates when it was ordered and shipped and the ship mode (how it was shipped)

These files are available as separate CSV files

• transaction
• product
• customer
• geography
• order

Alternatively, they are on separate tabs in a single Excel spreadsheet here.

If your organisation blocks access to GitHub then here is an alternative location.

• transaction
• product
• customer
• geography
• order

You will also need a Dates table.

Suggested Approach (Power BI Labs Data Model Course)

Build a data model in Power BI step by step.

Import each table into the model and then build visuals to show that the model works well. Import the tables in the order they are listed below.

Improve the model after each step: consider relationships, DAX measures and calculations, which fields to hide, hierarchies and so on.

You may want to consider some design aspects such as:

• The pattern of tables that you are building e.g. star schema
• Using the Calendar table rather than the auto – date – time feature
• Placing all measures in a separate Calculations (measure) table

Once the Dates Table is imported, the data model becomes a snowflake rather than a star pattern. Is there any benefit of a reverting back to a star schema design? If so, how would you achieve this?