Granularity - One Fact Table or Two
2 posters
Page 1 of 1
Granularity - One Fact Table or Two
I am warehousing data from a distribution system. The distribution system allows scheduling orders and it has a number of tables that store the delivery (ticket) information. The order / schedule tables look something like this:
Order Header > Order Lines > Schedule Header > Schedule Lines
(one schedule line for each delivery which eventually equates to a ticket, some order lines will not have schedules)
Ticket tables look something like this:
Ticket Header > Ticket Line > Ticket Line Associated Products
.....................................> Ticket Line Associated Charges (related to the line, not the associated product)
...................> Ticket Charges (related to the ticket, not the ticket line)
...................> Ticket Taxes (related to the ticket, not the line or the charges)
There are a few facts in the Ticket Header (distance, cash due totals) along with 7 status timestamps that provide information about the delivery. The revenue and costs facts are in the child tables. In the datamart I have created one fact table for the Ticket Header and a separate fact table for the Ticket Lines. In the ETL process I merge the various ticket child tables into the single ticket line fact table.
Question 1 - does two fact tables sound appropriate? I was trying to keep the granularity of all the measures the same in each table. Ticket grain in the one, Ticket line granularity in the other. I've ignored taxes for now, looks like they would become a separate fact table in the future.
In order to facilitate something as simple as ticket report, I have created a Ticket dimension. The Ticket dimension contains a surrogate key, the order code and the ticket code.
Question 2 -does the ticket dimension sound appropriate? I think it will eventually be one of the conformed dimensions between the "Order Schedule Star" and the "Ticket Line Star"
Question 3 -should the delivery timestamps be in the Ticket dimension or in the Ticket fact table? Because of the difference in granularity, if I put the timestamps in the fact table I may get a ticket list like this:
Order Code Ticket Code Delivered Time Item Code
.......1.................1.......... 8:00 am...........A
.......1.................1.......... blank .............B
If I put the timestamps in the dimension table I can get a ticket list like this:
Order Code Ticket Code Delivered Time Item Code
.......1.................1.......... 8:00 am...........A
.......1.................1.......... 8:00 am...........B
I am using IBM Cognos tools for the datawarehouse and analysis.
Thank you for your advice and comments - and I apologize for such a long post, I'll try to keep them shorter in the future.
Order Header > Order Lines > Schedule Header > Schedule Lines
(one schedule line for each delivery which eventually equates to a ticket, some order lines will not have schedules)
Ticket tables look something like this:
Ticket Header > Ticket Line > Ticket Line Associated Products
.....................................> Ticket Line Associated Charges (related to the line, not the associated product)
...................> Ticket Charges (related to the ticket, not the ticket line)
...................> Ticket Taxes (related to the ticket, not the line or the charges)
There are a few facts in the Ticket Header (distance, cash due totals) along with 7 status timestamps that provide information about the delivery. The revenue and costs facts are in the child tables. In the datamart I have created one fact table for the Ticket Header and a separate fact table for the Ticket Lines. In the ETL process I merge the various ticket child tables into the single ticket line fact table.
Question 1 - does two fact tables sound appropriate? I was trying to keep the granularity of all the measures the same in each table. Ticket grain in the one, Ticket line granularity in the other. I've ignored taxes for now, looks like they would become a separate fact table in the future.
In order to facilitate something as simple as ticket report, I have created a Ticket dimension. The Ticket dimension contains a surrogate key, the order code and the ticket code.
Question 2 -does the ticket dimension sound appropriate? I think it will eventually be one of the conformed dimensions between the "Order Schedule Star" and the "Ticket Line Star"
Question 3 -should the delivery timestamps be in the Ticket dimension or in the Ticket fact table? Because of the difference in granularity, if I put the timestamps in the fact table I may get a ticket list like this:
Order Code Ticket Code Delivered Time Item Code
.......1.................1.......... 8:00 am...........A
.......1.................1.......... blank .............B
If I put the timestamps in the dimension table I can get a ticket list like this:
Order Code Ticket Code Delivered Time Item Code
.......1.................1.......... 8:00 am...........A
.......1.................1.......... 8:00 am...........B
I am using IBM Cognos tools for the datawarehouse and analysis.
Thank you for your advice and comments - and I apologize for such a long post, I'll try to keep them shorter in the future.
bberryhill- Posts : 1
Join date : 2010-11-18
RE: Granularity - One Fact Table or Two
Have you considered 1 fact table? Force the data from ticket header to the same granualarity as the lines.
g8rpal- Posts : 10
Join date : 2010-12-03
Location : Jacksonville, FL
Similar topics
» Granularity of Fact table
» Defining the granularity for a Fact Table
» Is it necessary to have define granularity for a factless fact table?
» Aggregate Table Granularity
» How to create fact table with measures derived from comparing two fact table rows
» Defining the granularity for a Fact Table
» Is it necessary to have define granularity for a factless fact table?
» Aggregate Table Granularity
» How to create fact table with measures derived from comparing two fact table rows
Page 1 of 1
Permissions in this forum:
You cannot reply to topics in this forum