Model log tables.
Page 1 of 1
Model log tables.
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.
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
Similar topics
» Handling History tables in Dimensional Model
» Student Course Registration Fact and Dimension Tables : How to model
» data model for 2 fact tables (Header / Detail scenario)
» OLTP Dailty Pricing tables: How best to model ceiling, floor and average daily price data?
» Single fact table vs multiple fact tables - what is the right way in a dimensional model?
» Student Course Registration Fact and Dimension Tables : How to model
» data model for 2 fact tables (Header / Detail scenario)
» OLTP Dailty Pricing tables: How best to model ceiling, floor and average daily price data?
» Single fact table vs multiple fact tables - what is the right way in a dimensional model?
Page 1 of 1
Permissions in this forum:
You cannot reply to topics in this forum