How to model with a requirement for multiple grains?

View previous topic View next topic Go down

How to model with a requirement for multiple grains?

Post  Dr. Warehouse on Tue Oct 28, 2014 3:00 am

While trying to model our Call Management process, I found this solution. I'm wondering however if this is the correct way of modelling this.

The Model:


Because there are measures on both Snapshot as Transaction grain, I created two fact tables. Next I created a Transaction Dimension DimCall which holds one record for every call (just like FactCall).

Need to model the Change Management and Problem Management process next, and there will be a relation to Call Management because of questions like:
- What Calls were caused by Change x?
- What Changes solved Call y?
- Which Calls are linked to Problem z?

Because of the design now, I'm required to also model Changes and Problems as 1 dimension, 2 fact tables (transaction, snapshot). However there are multiple ways of creating the relations. Example:
What Calls were caused by Change x?
DimChange(ChangeKey) -> FactCall(CausedByChangeKey)
FactChange(ChangeKey) -> DimCall(CausedByChangeKey)

This scenario makes me wonder if I chose the correct design in the first place. Could the Dimension be removed by using Degenerates in both Fact tables? But how to relate to other Facts then?

The book offers many solutions which I think might apply to my design issue. However, due to my own inexperience, I'm unable to select the correct one.

Could someone guide me in the right direction? Many thanks.

Dr. Warehouse

Posts : 5
Join date : 2014-10-28

View user profile

Back to top Go down

Re: How to model with a requirement for multiple grains?

Post  BoxesAndLines on Tue Oct 28, 2014 10:35 am

Why do you have a snapshot fact? This data seems very transactional to me.
avatar
BoxesAndLines

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

View user profile

Back to top Go down

Re: How to model with a requirement for multiple grains?

Post  Dr. Warehouse on Tue Oct 28, 2014 10:38 am

BoxesAndLines wrote:Why do you have a snapshot fact?  This data seems very transactional to me.

In order to answer questions like:
- What Calls/Changes/Problems were open on Date X
- Average age of backlog on Date Y
- Backlog per department growth since period Z
Etc

Dr. Warehouse

Posts : 5
Join date : 2014-10-28

View user profile

Back to top Go down

Re: How to model with a requirement for multiple grains?

Post  nick_white on Wed Oct 29, 2014 3:36 am

I would put a 'Closed Date' (or something similar) on your transaction fact table. I don't think snapshots are the solution as they will tie the queries you can answer very closely to how you implement the snapshots and you will have a very inflexible model e.g.
You create a daily snapshot at 6pm. A call is closed at 6:05pm. That call would presumably appear as closed in tomorrow's snapshot i.e. your snapshot is defining your business day. If this definition every change then you can't apply the new business day logic to historic records.
Queries that span multiple snapshot records are not that easy to run - at least not compared to a pure transactional fact table.

nick_white

Posts : 366
Join date : 2014-01-06
Location : London

View user profile

Back to top Go down

Re: How to model with a requirement for multiple grains?

Post  Dr. Warehouse on Wed Oct 29, 2014 3:59 am

nick_white wrote:I would put a 'Closed Date' (or something similar) on your transaction fact table. I don't think snapshots are the solution as they will tie the queries you can answer very closely to how you implement the snapshots and you will have a very inflexible model e.g.
You create a daily snapshot at 6pm. A call is closed at 6:05pm. That call would presumably appear as closed in tomorrow's snapshot i.e. your snapshot is defining your business day. If this definition every change then you can't apply the new business day logic to historic records.
Queries that span multiple snapshot records are not that easy to run - at least not compared to a pure transactional fact table.

Thanks for your reply. I'm not very experienced at dimensional modelling so my apologies if the answers to my questions seem obvious.

I will elaborate a bit on the requirements.

I think snapshots (being periodic or accumulating) are required because of future requirements like:
- What was the backlog per employee during time, and how did he perform (booked hours per call/calls closed/customer satisfaction)
- What was the status per call at the end of the day, and how long has it been at that state?
- If the status is on-hold, subtract the on-hold duration from the total time open
- How many calls does a customer log per user (using FactEnduser with a monthy snapshot)
- Does the backlog grow or shrink? And why?
- How does a contract perform (Calls + Booking time vs Customer -> Contract (monthly snapshot of worth over contract period))

The snapshot as it is, is always made at the real end of the day (not business day). By leveraging DimCall (no measures, but dimension keys) and FactCall (snapshots with FK's CallKey, DateKey, PhaseKey, EmployeeKey and Measures Logged, Open, Closed, CurrentDaysOpen, TotalDaysOpen(logged at closed event), etc).

Having to report on calls open per day, status and employee-backlog pushed me towards this design, as I did not see another solution on how to report this using a transaction fact table. Please tell me, how would you model this?

Dr. Warehouse

Posts : 5
Join date : 2014-10-28

View user profile

Back to top Go down

Re: How to model with a requirement for multiple grains?

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