Text/Date only facts
Page 1 of 1
Text/Date only facts
I have been researching other threads on dealing with textual data in fact tables but I have not found one that presents fact tables that are strictly text/date data. Usually it is 1 date or maybe a comment field. Though we may do counts across data population for a summary page on a report, the business is very detail oriented and we always include a detail page with bucket fields that help them relate back to the counts.
The facts in this case are usually answers to questions or perhaps milestone dates (which we also do odd cycle time calculations). The answer may tie to multiple dimensions (job, application, project, etc.) . The raw data is usually one record per question or milestone date. For reporting performance we would like to flatten these out into business groups determined by the business (usually relating to a list of questions within a BPM process). I am thinking this is a factless fact table, but there seems to be a lot of comments that textual facts don’t belong in any fact table and that factless facts are just counts. I would like opinions on this scenario and present my design ideas:
Raw Data:
Task ID Question ID Question Answer (Question Text – stored in the column meta data on the text field)
1 Q001 4/5/2012 Building Permit Date
1 Q002 A7X Building Permit ID
1 Q003 Jon Davis Building Inspector
Proposed Fact?? Table
Task_dim_seq job_dim_seq app_dim_seq prjct_dim_seq building_permit_date building_permit_id building_inspector
1000 1234 777 40005 4/5/2012 A7X Jon Davis
A lot of the analysis is done ADHOC or specific for different customers. For instance the customer (business or external) may want to know for Project X (tied to project id 40005), how many building permits were done in April. Another internal customer might want to know how many jobs that Jon Davis was the inspector for. The questions are endless and therefore pre-aggregated counts are often pointless unless we have a standardized report that we might capture data for.
With this, where does this really belong? Is this truly a factless fact or a fact table at all? Do we have a factless fact that ties all of the dimensions together but then the text values become a dimension? That seems a bit of a waste since it would be 1 for 1 with the dimension and a traditional factless fact.
Anyway I would appreciate input from others on how you go about addressing this scenario.
The facts in this case are usually answers to questions or perhaps milestone dates (which we also do odd cycle time calculations). The answer may tie to multiple dimensions (job, application, project, etc.) . The raw data is usually one record per question or milestone date. For reporting performance we would like to flatten these out into business groups determined by the business (usually relating to a list of questions within a BPM process). I am thinking this is a factless fact table, but there seems to be a lot of comments that textual facts don’t belong in any fact table and that factless facts are just counts. I would like opinions on this scenario and present my design ideas:
Raw Data:
Task ID Question ID Question Answer (Question Text – stored in the column meta data on the text field)
1 Q001 4/5/2012 Building Permit Date
1 Q002 A7X Building Permit ID
1 Q003 Jon Davis Building Inspector
Proposed Fact?? Table
Task_dim_seq job_dim_seq app_dim_seq prjct_dim_seq building_permit_date building_permit_id building_inspector
1000 1234 777 40005 4/5/2012 A7X Jon Davis
A lot of the analysis is done ADHOC or specific for different customers. For instance the customer (business or external) may want to know for Project X (tied to project id 40005), how many building permits were done in April. Another internal customer might want to know how many jobs that Jon Davis was the inspector for. The questions are endless and therefore pre-aggregated counts are often pointless unless we have a standardized report that we might capture data for.
With this, where does this really belong? Is this truly a factless fact or a fact table at all? Do we have a factless fact that ties all of the dimensions together but then the text values become a dimension? That seems a bit of a waste since it would be 1 for 1 with the dimension and a traditional factless fact.
Anyway I would appreciate input from others on how you go about addressing this scenario.
darylm74- Posts : 7
Join date : 2012-09-17
Similar topics
» Date Dimension: Representing partial dates/Imputing date values
» Adding a fact table with start date and end date to a (SSAS) multidimensional cube
» Initial date of effective date column for SCD 2 implementation
» scd2 effective date, end date data type
» Loading dimension when source already has effective to and from dates
» Adding a fact table with start date and end date to a (SSAS) multidimensional cube
» Initial date of effective date column for SCD 2 implementation
» scd2 effective date, end date data type
» Loading dimension when source already has effective to and from dates
Page 1 of 1
Permissions in this forum:
You cannot reply to topics in this forum