Advice on Dimensional Modeling

I am trying to design a dimensional model for the sales Rep Activity. It would be very helpful if somebody could share their knowledge.i am new to the data modeling.

I have 3 Dimension tables and 2 fact tables(One for Timeout and another for Timein )

Dimension table details:

1.A table to indicate the Sales People details Which has Sales person id,Start and and end date.
2.A table to indicate the Sales People alignment which has Sales person id,Org code, start date(says start date of org code assignment to the sales person) and end date(says end date of the org code assignment to the resource)
3. A table to find the org code is active during the and its hierarchy details which has columns org code, parent org code, level,parent level,org code start date,org code end date.

Fact Table:

1. Time Out fact table gives the details of time out activity .Which has columns Sales person id,Org Code,Activity date,Activity,Duration.
2. Time in fact table gives the details of Time In Activity which has coulmns sales Person id, Org code,Activity date, duration.

Now i need to generate a dimensional model which should be helpful to develop the report the following metrics metrics. No of working days of the month for all the valid org code,Time in duration,Time out duration.

problem is with Time dimension i have Start,end date for the sales person,Org code,Sales person org code alignment and also for the fact tables.I am not able to figure out the the best way to design this model. Request somebody to suggest me the best design based on the given coulmns.

Let me know if any additional information is required for the suggestion.

I guess you need to track the person-org relationship in both dimension and fact tables, if they exist independently in person dimension implied by org start/end date, and in fact table by having person id and org code.

If a person can only belong to one org code at one point in time, then you should model org structure (org key) as type 2 in person dimension based on start/end date (the only way to reproduce dimension history) or whatever correlation is on daily base for on-going load. This will give you the entire picture of person-org relationship regardless of the coverage by fact tables. Otherwise you may have to build a bridge table to cater for m-m situation.

In your fact table, you may also have both person id and org code and the correlation may not be in synch with that in the person dimension. I think you need to create an org dimension and have FK in person dimension for type 2 tracking, and in the fact table to reflect the factual correlation. I guess the start/end date in the fact is the base for the duration, then you could include the timestamp along side with date key and duration for granular analysis. If the factual dates are the same dates for person-org alignment then you could use them to drive the type 2 changes in person dimension, or let business decide if they appear in two source tables.

So potentially, you may have two ways to aggregate your facts on org structure, one through single person key, one through both person and org keys in the fact table. Ideally you should let business understand the situation and decide on a single standard, then you don't need org key in the fact table and possibly denormalise the org structure into person dimension altogether.

Thanks for your response. It was really helpful and i have almost compelted my model.One more advice needed. If there is a granular fact and Aggregated fact which one should be in the center of the star schema. Or on what logic this has to be done.

Thanks for your response.