1

I have a small data warehouse for Sales. Here i have Fact Table for Sales Invoices and Dimensions like customer, Date Time, Sales Geo, Product Code.

Fact Table: Sales - >

Invoice Date, Customer code, Product Code, Sales Geo Code, Billing qty, Amount, Tax, Total Amount

For Sales Geo dimension - >

Sales Geo Code, City Name, Regional Code, Zone Code, Regional Manager Code, Zonal Manager code

I have confusion in how to link my sales persons like Regional Manager and Zonal Managers etc.

Regional Manager is leading one region of multiple cities, zonal manager is leading multiple region.

Sometimes we change the regional area or zonal area, they get promoted, they left etc.

How to create dimension and link sales team with Sales Fact to get correct Sales report.

regards

Elletlar
  • 3,136
  • 7
  • 32
  • 38
Casabi
  • 11
  • 1

2 Answers2

0

there are a few options I can think of:

  1. Denormalise the Regional and Zonal Manager information into your Sales Geo Dimension
  2. Create a hierarchical Manager dimension keyed on Regional Manager and including their zonal manager details
  3. Create a Person Dim and associate it twice to the Fact - once in the role of Regional Manager and once in the Role of Zonal Manager

If you will never want to link Manager information to a fact except in the context of the Sales Geo then option 1 probably makes more sense - as you have fewer potential joins in queries using this fact table.

Option 2 is more flexible as you can associate manager information to a fact without also using the Sales Geo

Option 3 is the most flexible but also likely to give the worst query performance (for any query that needs both types of manager) and also the only link between Regional and Zonal managers is via the Fact; there is no hierarchical information held in the Dimensions. Therefore option 3 is the one I would be least likely to choose

NickW
  • 8,430
  • 2
  • 6
  • 19
0

Denormalize the Regional Manager Code and Zonal Manager code in your fact table.

So basically you will store in each fact row along with the Sales Geo Code also the current assignment of the two manager roles at the time of the sales (more precise at the time of the loading the record).

This model allows both types of the reports using

  • managers assigned at the transaction time (direct from the fact table) and

  • current managers (join from the fact table to Sales Geo dimension to get the code of the both managers)

Now your setup allows only the second type of the reporting, which could be suboptimal in case that the managers are frequently re-assigned.

If you prefer not to denormalize the fact table you can always switch the Sales Geo dimension to SCD type 2 which will introduce a historical view on the dimension and the assignment of the managers.

You'll have to join not only with the Sales Geo Code from the fact table to the Sales Geo dimension but also considering the transaction date...

Invoice_Date between sales_geo.validfrom_date and sales_geo.validto_date

... to get the managers assigned at the time of the transaction.

The decision has a typical tradeoff between storage plus maintaining the consistency and more complex joins plus maintaing the dimension history on the other side.

Marmite Bomber
  • 19,886
  • 4
  • 26
  • 53