Modeling advice for Dimension Table

View previous topic View next topic Go down

Modeling advice for Dimension Table

Post  mcha on Mon Jan 21, 2013 7:02 am

Hello,

I have set of data as shown in FIG1 below:



For my report needs (FIG2 and FIG3) i need to display:
  • nbAvailable: number of already available cases treated by manager in parameter in the beginning of the month
  • nbEntry: number of new cases entered during the month
  • nbOut: number of cases ended during the month

Actually I have a dimension table "Managers" and a snapshot fact table "Cases", in the fact table is stored FK manager_id which refers to the actual manager for a given date (FIG4).
I can calculate with this architecture nbAvailable and nbEntry with a SQL query, but i have a problem to calculate nbOut as I can't tell when the case was finished for a given manager/date.

Any help would be appreciated to achieve this result (new Dimension, change in Fact table ...)
Is it possible to achieve this by modeling a new SCD type2? If yes, how to proceed?

Thanks in advance,
mcha

mcha

Posts : 2
Join date : 2012-10-17

View user profile

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