Managing Date_FROM/Date_TO measures

View previous topic View next topic Go down

Managing Date_FROM/Date_TO measures

Post  Christophe on Tue Feb 22, 2011 8:00 am

Hello,

What's the best approach for managing Date_FROM/Date_TO measures.

I've a table with Employees assign on specific Projects regarding a specific Date range

In my Dimensional model:

Dimensions:
dEmployee
dProjects
dTime

Fact:
fAssignment

Must I make 1 line for every day in the fact table (meaning that an assignment of 1 people on 4 years will generate 1460 lines)?
Is there other way to manage "range date" measures?

Thank you

Christophe

Posts : 4
Join date : 2011-02-22

View user profile

Back to top Go down

Re: Managing Date_FROM/Date_TO measures

Post  Jeff Smith on Tue Feb 22, 2011 9:55 am

Why don't you add a Number of Days measure to the fact table? If the Employee was on a project from 1/1/2010 to 12/31/2010, the value would be 365.

Jeff Smith

Posts : 471
Join date : 2009-02-03

View user profile

Back to top Go down

Re: Managing Date_FROM/Date_TO measures

Post  Christophe on Tue Feb 22, 2011 10:03 am

Thank you for your reply Jeff.

Small precision, I've other measures related like daily rate, daily cost.

Users want to be able to drill around the time dimension in every possible ways (yearly, monthly report, daily focus...).
And I can for instance have an employee assigned from the 15/01/2011 to the 21/2/2012.

thank you

Christophe

Posts : 4
Join date : 2011-02-22

View user profile

Back to top Go down

Re: Managing Date_FROM/Date_TO measures

Post  Jeff Smith on Tue Feb 22, 2011 10:11 am

What about setting your date dimension between the 2 dates? This would give you a cartesian product. You could create a view or set it up in the reporting software.

Jeff Smith

Posts : 471
Join date : 2009-02-03

View user profile

Back to top Go down

Re: Managing Date_FROM/Date_TO measures

Post  BoxesAndLines on Tue Feb 22, 2011 10:12 am

Build an accumulating snapshot fact. Each assignment is one row with the various dates that you want to track. When a particular end date shows up, just update the row. You can also add metrics correspond to the date ranges to make summing even easier.
avatar
BoxesAndLines

Posts : 1212
Join date : 2009-02-03
Location : USA

View user profile

Back to top Go down

Re: Managing Date_FROM/Date_TO measures

Post  ngalemmo on Tue Feb 22, 2011 10:24 am

The dates are not measures, they are dimensions. Store the start and end dates on the fact, either as degenerate dimensions or FKs to the date dimension.

No reason user could not query on any kind of date range. And, if you have your date dimension set up correctly (i.e. including a date sequence attribute) it is easy to calculate period lengths and averages.
avatar
ngalemmo

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

View user profile http://aginity.com

Back to top Go down

Re: Managing Date_FROM/Date_TO measures

Post  Christophe on Tue Feb 22, 2011 12:05 pm

ngalemmo wrote:The dates are not measures, they are dimensions. Store the start and end dates on the fact, either as degenerate dimensions or FKs to the date dimension.

No reason user could not query on any kind of date range. And, if you have your date dimension set up correctly (i.e. including a date sequence attribute) it is easy to calculate period lengths and averages.

Hello,

you're approach seems very interesting, however I do not understand it completely.

How can I degenerate a Time dimension from Start_Date and End_date?

What do you suggest for the relationship between the fact and the time dimension?



Thank you

Christophe

Posts : 4
Join date : 2011-02-22

View user profile

Back to top Go down

Re: Managing Date_FROM/Date_TO measures

Post  ngalemmo on Tue Feb 22, 2011 2:08 pm

They already are degenerate dimensions as they exist in your table.

My comment about measures was a technical one. The Start_Date and End_Date columns in your table are not measures, they are degenerate dimensions. These dates provide context, the period of time the particular assignment is effective. Dimensions provide context, not measures. It is degenerate because it stands alone, there is no related dimension table.

If all you are concerned about is dates (i.e. the time of day is not important) then you could formalize the dimensions and replace the two columns with FKs to the date dimension. This could offer some advantages, particularly if you need to calculate the length of the assignment and need to take into account weekends and holidays.

When I design a date dimension table I always include a day sequence column (among others). Basically you chronologically assign a sequence number to each row. If needed, you can provide a sequence based on the business calendar, skipping days not considered 'business days'. The difference between this number on two different days is the number of days between them.
avatar
ngalemmo

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

View user profile http://aginity.com

Back to top Go down

Re: Managing Date_FROM/Date_TO measures

Post  wlehman on Fri Aug 10, 2012 7:21 pm

I'm relatively new to the dimensional world so please be gentle...

We have a similar requirement where we need to record Access Levels that have been granted to People. A person may be granted more than one Access Level at a time. It is also possible that an Access Level could be granted to a Person and revoked from that same person more than once in a day. I'm considering creating a factless fact table with relationships to Person, Access Level, Granted Date (Role Dim), Revoked Date (Role Dim) along with "granted timestamp" and "revoked timestamp". We have another fact table that stores access card swipe events at RFID card readers - columns include the access card id, person id, event timestamp and event response (access permitted, access denied, etc.).

We want to ask questions like "Each month, how many people attempted to gain access to a room that they were authorized for but were denied access?" or "Each month, how many people attempted to access a room that there were not authorized for?" I can't see how we can answer these questions without knowing the exact time that access was granted or revoked so we can compare the event timestamp against the specific authorized period. Since the time range of the factless fact is undefined, the grain of the fact is not clear. Is this okay or is there a better way to do this? Also, is my proposed table a factless fact?

wlehman

Posts : 2
Join date : 2012-08-10
Location : Calgary, Alberta, Canada

View user profile

Back to top Go down

Re: Managing Date_FROM/Date_TO measures

Post  BoxesAndLines on Mon Aug 13, 2012 10:48 pm

You could always figure out in the ETL process if they were authorized by maintaining a staging table or even doing a lookup to another fact table. So then authorized just becomes a metric on the fact table with a value for 0 for denied access or 1 for access granted.
avatar
BoxesAndLines

Posts : 1212
Join date : 2009-02-03
Location : USA

View user profile

Back to top Go down

Re: Managing Date_FROM/Date_TO measures

Post  wlehman on Tue Aug 14, 2012 12:16 pm

BoxesAndLines wrote:You could always figure out in the ETL process if they were authorized by maintaining a staging table or even doing a lookup to another fact table. So then authorized just becomes a metric on the fact table with a value for 0 for denied access or 1 for access granted.

Thanks. I think this will work for most of the reporting requirements. The one shortcoming I can think of is that we won't be able to see who had authorization but never attempted to gain access. I believe Kimball refers to this as coverage.

wlehman

Posts : 2
Join date : 2012-08-10
Location : Calgary, Alberta, Canada

View user profile

Back to top Go down

Re: Managing Date_FROM/Date_TO measures

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