Fact table or Dimension?? Need help

View previous topic View next topic Go down

Fact table or Dimension?? Need help

Post  kirk@hoodriversoftware.co on Sat Oct 20, 2012 3:54 am

Hello,

I'm new to dimensional modelling and have a question on how best to design my schema.

Background:

.1 The transactional system is an issue tracking tool.

.1.1 When a new record is created, it gets stamped with the following attributes:

.1.1.1 Ticket ID
.1.1.2 Physical Status of 'Assigned'
.1.1.3 Logic Status of 'Opened' (note: Multiple Physical status values equate to a Logic Status)
.1.1.4 Estimated Hours
.1.1.5 Actual Hours (defaults to zero)
.1.1.6 Date Created
.1.1.7 Assignee

.1.2 Later on, the record can be updated, such as the Actual Hours value changed from zero to some integer representing actual hours worked on the issue. Another update will set the Physical Status to 'Closed', which correlates to a Logic Status of 'Closed', as well.


.2 Reporting Requirement:

.2.1 The goal is to capture the data such that I can:
.2.1.1 Generate Ticket counts rolled up by:
.2.1.1.1 Logical Status
.2.1.1.2 Assignee
.2.1.1.2 Monthly counts of total tickets
.2.1.2 Sums of Estimated and Actual Hours
.2.1.3 Averages of Estimated and Actual Hours
.2.1.4 Comparisons between the Estimated and Actual Hours.


.3 Schema Design

Our thinking is that we want a Transactional Fact table, but were unsure of how best to design the system.

Questions

.3.1 Is the Ticket a Fact table, or Dimension?

.3.1.1 Our assumption ins that it's the Fact table with the following attributes:

.3.1.1.1 Ticket ID (natural key)
.3.1.1.2 ID (surg key)
.3.1.1.3 Logical Status
.3.1.1.4 Est. Hours
.3.1.1.5 Actual Hours
.3.1.1.5 Date Created ID (f. key to Date Created dimension)
.3.1.1.6 Date Closed ID (f. key to Date Closed dimension)

.3.2.2 This would allow us to aggregate tickets by date range (ex: 100 tickets created during March that have a status of Open)

.4 However, we are unsure of at least 3 things, and could use some guidance:

.4.1 Should the Logical Status be its own dimension, or an attribute in the Fact table?
.4.2 There is a third logical Status value named 'Backlog'. It's defined as those Tickets created prior to the current month that do not have a Physical Status of 'Closed'. What's the best way to handle logic that is derived such as this? Should that be done by the report query and/or SSAS? Or should we handle that in some type of ETL logical process and store the data in the model? If we store it in the model, should it be another dimension, or an attribute of some more general dimension, or an attribute of the Fact table?
.4.3 What is the best way to handle modifications to the transactional data? Are those new Fact table entries for the same Ticket? For example, when the status changes, or if the Actual Hours value changes, what's the best way to design the schema?

Thank you for any guidance.

Cap



kirk@hoodriversoftware.co

Posts : 3
Join date : 2012-10-20

View user profile

Back to top Go down

Re: Fact table or Dimension?? Need help

Post  ngalemmo on Thu Oct 25, 2012 6:33 pm

Actions on tickets are events, so they belong in a fact table. The ticket number itself would be a degenerate dimension.

There are two choices to implement this: a snapshot or an accumulating snapshot. A snapshot would have one row per ticket and as things change, you perform an update in place. There would be no history.

An accumulating snapshot adds a new row and carry a current flag or a date range. You would need to update the flag or date ranges on the previous version of the row when you add a new current row. This gives you history.

As far as the context goes, they are dimensions. If things like status have attributes (descriptions and other codes) they should be in a dimension table. If there are no attributes, such as the aforementioned ticket number, they can be placed as degenerate dimensions in the fact table. But be careful, long text should not go into a fact table, even if it is unique to the fact.
avatar
ngalemmo

Posts : 3000
Join date : 2009-05-15
Location : Los Angeles

View user profile http://aginity.com

Back to top Go down

reply

Post  kirk@hoodriversoftware.co on Thu Oct 25, 2012 7:20 pm

We implemented a design that has what we refer to as a TicketTransaction. This is our Fact table, and contains a couple of values that will be measurements (Est Hrs, Act Hrs)

We created a Ticket dimension with the idea that our tickets have lots of attributes, and we don't want those to be stored in the Fact table. A few of the attributes are type 2.

We have a few other Dimension tables as well, such as Employee, which is the person assigned to the Ticket.

Is our design reasonable, or are we way off?

Thanks


kirk@hoodriversoftware.co

Posts : 3
Join date : 2012-10-20

View user profile

Back to top Go down

Re: Fact table or Dimension?? Need help

Post  BoxesAndLines on Fri Oct 26, 2012 9:26 am

The transaction fact is fine. I would also build the accumulating snapshot to easier answer some of the questions your asking (e.g. lag time, fallout stage, total time, stage time).
avatar
BoxesAndLines

Posts : 1212
Join date : 2009-02-03
Location : USA

View user profile

Back to top Go down

Re: Fact table or Dimension?? Need help

Post  Sponsored content


Sponsored content


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