Managing Date_FROM/Date_TO measures
5 posters
Page 1 of 1
Managing Date_FROM/Date_TO measures
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
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
Re: Managing Date_FROM/Date_TO measures
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
Re: Managing Date_FROM/Date_TO measures
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
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
Re: Managing Date_FROM/Date_TO measures
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
Re: Managing Date_FROM/Date_TO measures
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.
BoxesAndLines- Posts : 1212
Join date : 2009-02-03
Location : USA
Re: Managing Date_FROM/Date_TO measures
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.
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.
Re: Managing Date_FROM/Date_TO measures
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
Re: Managing Date_FROM/Date_TO measures
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.
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.
Re: Managing Date_FROM/Date_TO measures
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?
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
Re: Managing Date_FROM/Date_TO measures
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.
BoxesAndLines- Posts : 1212
Join date : 2009-02-03
Location : USA
Re: Managing Date_FROM/Date_TO measures
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
Similar topics
» Data Vault vs Kimball
» Cumulative measures
» Managing conformed dimensions
» Measures in Dimension?
» Managing column and data conformities
» Cumulative measures
» Managing conformed dimensions
» Measures in Dimension?
» Managing column and data conformities
Page 1 of 1
Permissions in this forum:
You cannot reply to topics in this forum
|
|