FACT Design

View previous topic View next topic Go down

FACT Design

Post  newbie2011 on Wed Sep 07, 2011 4:42 am

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?....


newbie2011

Posts : 3
Join date : 2011-09-07

View user profile

Back to top Go down

Re: FACT Design

Post  ngalemmo on Wed Sep 07, 2011 3:49 pm

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

Posts : 3000
Join date : 2009-05-15
Location : Los Angeles

View user profile http://aginity.com

Back to top Go down

Flattening

Post  KS_EDW on Wed Sep 07, 2011 11:07 pm

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, Ive 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

Id suggest researching the differences between flat and leveled (bridge) schemas.
avatar
KS_EDW

Posts : 20
Join date : 2011-09-07
Age : 42
Location : Kansas

View user profile

Back to top Go down

Re: FACT Design

Post  newbie2011 on Thu Sep 08, 2011 12:39 am

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

newbie2011

Posts : 3
Join date : 2011-09-07

View user profile

Back to top Go down

Re: FACT Design

Post  newbie2011 on Thu Sep 08, 2011 1:50 am

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

newbie2011

Posts : 3
Join date : 2011-09-07

View user profile

Back to top Go down

Re: FACT Design

Post  Sponsored content


Sponsored content


Back to top Go down

View previous topic View next topic Back to top

- Similar topics

 
Permissions in this forum:
You cannot reply to topics in this forum