How to create a dimensional model with different metrics depending of the hierarchical level

Question

I need to create a dimensional environment for sales analysis for a retail company.

The hierarchy that will be present in my Sales fact is:

1 - Country
1.1 - Region
1.1.1 - State
1.1.1.1 - City
1.1.1.1.1 - Neighbourhood
1.1.1.1.1.1 - Store
1.1.1.1.1.1.1 - Section
1.1.1.1.1.1.1.1 - Catgory
1.1.1.1.1.1.1.1.1 - Subcatgory
1.1.1.1.1.1.1.1.1.1 - Product

Metrics such as Number of Sales, Revenue and Medium Ticket (Revenue / Number of Sales) makes sense up to the Subcategory level, because if I reach the Product level the agreggation composition will need to change (I guess).

Also, metrics such as Productivity, which is Revenue / Number of Store Staff, won't make sense to existe in this fact table, because it only works up to the Store level (also, I guess).

I'd like to know the best solution resolve this question because all of it are about Sales, but some makes sense until a specifict level of my hierarchy and others don't.

Waiting for the reply and Thanks in advance!

Answer 1

You should split your hierarchy into 2 dimensions, Stores and Products

The Stores dimension is all about the Location of the sale, and you can put the number of employees in this dimension

Store_Key  STORE    Neighbourhood    City  Country  Num_Staff
1          Store1   4th Street       LA       US    10
2          Store2   Main Street      NY       US    2

The products dimension looks like

Product_Key  Prod_Name       SubCat   Category     Unit_Cost
1            Cheese Sticks   Diary    Food         $2.00
2            Timer           Software Computing    $25.00

The your fact table has a record for each Sale, and is keyed to the above dimensions

Store_Key  Product_Key  Date      Quantity  Tot_Amount
1          1            31/7/2014   5         $10.00   (store1 sells 5 cheese)
1          2            31/7/2014   1         $25.00   (store1 sells 1 timer)
2          1            31/7/2014   3          $6.00   (store2 sells 3 cheese)
2          2            31/7/2014   1         $25.00   (store2 sells 1 timer)

Now that your data is in place you can use your reporting tool to get the measures you need. Example SQL is something like below

SELECT store.STORE, 
       SUM(fact.tot_amount) as revenue, 
       COUNT(*) as num_sales
       SUM(fact.tot_amount) / store.NumStaff as Productivity
FROM tbl_Store store, tb_Fact fact
WHERE fact.Store_key = store.Store_key
GROUP BY store.STORE

should return the following result

STORE    revenue    num_sales    Productivity
Store1   $35.00     2            3.5
Store2   $31.00     2            15.5