Model log tables.

View previous topic View next topic Go down

Model log tables.

Post  arfoot653@gmail.com on Mon Feb 06, 2012 4:55 am

I am working on designing a dimensional model for a helpdesk ticketing system. Fact table
is an accumulating snapshot with one row per ticket and around 11 dimensions. Fact table
consists of various dates signifying various stages a ticket goes through in its short
life cycle before it gets closed. There are a few other role playing dimensions associated
with people and groups associated with various stages of a ticket.

Now i have situation where i have to model a log table which contains log entries created
while working through a ticket. Each ticket could contain many log entries. Also, there are
history tables which contain the history of the ticket as it goes through various stages with
corresponding time stamps. How do i model these?

Should i create separate fact tables for these log and history tables and have dim ids for the
current state of the ticket, which will allow us to join the log and history records with the
main ticket record and do analysis if required? Or should i just model these as dim tables with
ids in the main ticket accumulating snapshot? If i do that, then i will have 1:1 - 1:n from fact
to dimension which is a no-no. I could use bridge tables, but iam not sure if that's the right
thing to do. Even if i use a bridge, since every ticket is likely to have entries in the log table
bridge will almost of the same size as the fact table itself and any allocation factor doesn't make
any sense.

Reporting requirements from these log and history tables are minimal expect for operational listing
at the ticket level. They are mostly required by etl processes for downstream processing.

Any help is appreciated.

arfoot653@gmail.com

Posts : 9
Join date : 2012-02-01

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