FACT Design

Hi All,

New to this forum and DM learner. My experience is more on BO reporting.

Need suggestion on the FACT design for Pharma sales domain.

Current table design is

Code:

 
    Country Product Position  Time              Sales measure
    C1      P1        Rep 1    01 Jan 2011    50
    C1      P1        Rep 2    01 Jan 2011    100
    C1      P1        Rep 3    01 Jan 2011    100
    C2      P1        Rep 4    01 Jan 2011    50
   

Postion is a hierarchy (Rep -->District Manager --> Area Manager -->Director)

Any design approach suggestions?....

Position would reference the employee dimension and you would have a hierarchy bridge table between the fact and employee for rollups.

One option would be to flatten the data such that Rep, District Manager, Area Manager, and Director all occurred on the same row – appear as four columns rather than one. This would handle any rollups and leveling within a single fact table thus avoiding a bridge. But, bridges are nice to have for future fact tables (and supporting single column conformity among the employee dimension). Additionally, there are potential difficulties for inexperienced users where data has been flattened. Some folks are NOT big fans of flattening data (just FYI).

Although, I’ve personally been able to pull off some amazing things by flattening data. In one scenario we came from a wild snowflake having potentially 5 bridges and 18 dimensions down to no bridges and 12 dimensions. I will say though, some of the dimensions got REALLY wide.

Basically you have several good options – here are two of them:

Option 1:
Fact: Pharma Sales
-Sales (measure)
-Date (as surrogate key)
-Position (surrogate)
-Position Employee (bridge surrogate, bridge maintaining surrogate to Employee – leveled appropriate to known permutations and possibly having dates or other metrics specific to Employee)
-Product (surrogate)
-Location (surrogate of country level)
Dimensions:
-Date
-Position
-Employee
-Product
-Location

Option 2:
Fact: Pharma Sales
-Sales (measure)
-Date (as surrogate key)
-Rep (position surrogate)
-District Manager (position surrogate)
-Area Manager (position surrogate)
-Director (position surrogate)
-Rep (employee surrogate)
-District Manager (employee surrogate)
-Area Manager (employee surrogate)
-Director (employee surrogate)
-Product (surrogate)
-Location (surrogate of country level)
Dimensions (same as previous model):
-Date
-Position
-Employee
-Product
-Location

I’d suggest researching the differences between flat and leveled (bridge) schemas.

Thanks folks for your valuable suggestion.. Now i need to understand the Hierarchy bridge table (do some search on the topic).
I will do some search differences between flat and leveled (bridge) schemas and decide..

Thanks again

One more problem which i missed in previous thread is

Based on the datasource: Separate sales measure for each level.
The sales measure can't be rolled up for each level of Position hierarchy.

Postion is a hierarchy (Rep -->District Manager(DM) --> Area Manager (AM)-->Director(D))
Business scenario:
e.g
1. DM ----> has 3 Reps under him
2. 3 reps share same sales measure for their area

Code:


Rep level data
    R1 ---> 50
    R2----> 50
    R3----> 50
 DM  level data
  DM1 ---> 50 (At DM level sales is still 50, as 3 reps share the same 50)



their is another business concern from DM Level data to AM Level (as location involved and its sharing).
SO DEsign decision to go with separate sales measure for each levels...

Any thoughts on design approach Guys!!!

thanks again KS_EDW ,ngalemmo