Granularity - One Fact Table or Two

View previous topic View next topic Go down

Granularity - One Fact Table or Two

Post  bberryhill on Thu Nov 18, 2010 11:10 am

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.

bberryhill

Posts : 1
Join date : 2010-11-18

View user profile

Back to top Go down

RE: Granularity - One Fact Table or Two

Post  g8rpal on Mon Dec 06, 2010 11:42 am

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

View user profile

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