May 30, 2012 at 3:06 am
Ok this may be hard to describe but here goes. I'll try to simplify the problem to make it easier to describe.
There are 2 tables. The first is a Fact table called MAIN_FACT_CONTACT. This is basically a record of contacts in a call centre CRM. It records #s of contacts, enquiries, and essentially, the number of the contacts that resulted in a case being created. Because of that, I have several foreign keys relevant to this problem.
InteractionID (for the contact)
EnquiryID (for the enquiry, 1 contact can have many enquiries)
CaseID (for the case, you can create 1 case per enquiry).
When a case is created, a number of events are recorded in a seperate table called CASE_EVENTSLOG. This table does not have a unique primary key, but it has CaseID duplicated per event for the table, for each eventTitle, eventDescription, Date and Time. So it looks like this...
CASEID, EventTitle, EventDescription, Date, Time
10001, Case Opened, Agent 001 Created Case, 01/01/1900, 09:00
10001, Case ReAllocated, Agent 001 allocated to Supervisor, 01/01/1900, 09:20
10001, Case Closed, Completed, 01/01/1900, 15:00
10002, Case Opened, Agent 226 Created Case, 02/01/1900, 11:00
etc etc.
The MAIN_FACT_CONTACT is deployed in SSAS as a fact table in SSAS. CASE_EVENTSLOG is deployed as a dimension, however as there is now unqiue key linking these 2 tables I am stuck how to deal with this.
Potential solution I can think of is to make the caseID unique in the table, and have the events and descriptions as columns, but i'm not sure if this feels a little clumsy. Are there any other idea's? I've just ordered a Kimball guide that a colleague suggested may help but could do with some insightful guru's on here to give me a general direction to head in.
Please help!
May 30, 2012 at 5:21 am
At first sight the problem is that the grain of the fact table doesn't match with the grain of the case table. The fact table only goes as deep as the individual CASEIDs, while the case table goes a level deeper, providing more details about a single case.
Either add another level to your fact table, or "aggregate" the case table.
Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP
May 30, 2012 at 5:31 am
Koen - thanks for your suggestions.
I understand the principle of adding another layer to the fact table to include a Business Key to the case events - honestly i'm reluctant to do that because the fact table is already some 2 million rows, and the case events table some 2 million rows so it will increase the size of my fact table considerably.
When you say aggregate the case table, what do you mean, what techniques does this require?
May 30, 2012 at 5:51 am
Well, I'm not 100% sure how your fact table is related to your dimension.
For example, case 10001 has 3 detail rows. Are there 3 rows in the fact table pointing to those, or only one? Do you keep a "history" somewhere in your facts? With this I mean, do you have a fact for the 9:00 event, a fact for the 09:20 event and a last one for the 15:00 event?
Or a single fact who points to 10001?
(I'm guessing it is this one)
Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP
May 30, 2012 at 6:07 am
I only have that single fact reference, CaseID, in my fact table so when a case is created, a row is added with CaseID = 10001 and all subsequent case related activity is stored in that different table.
May 30, 2012 at 6:16 am
At least the subsequent case information should have different IDs. You need to add a surrogate key to you dimension table and keep the case ID as some sort of business key.
However, if you just add a new identifier, facts will only link to that first dimension member, so you won't be able to analyze data in the subsequent members.
I would try to add columns to the initial row which are blank at creation.
When subsequent information is added, this row is updated instead of other rows being inserted.
Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP
May 30, 2012 at 6:37 am
Thankyou for your help here. I did think it would be more of an ETL process than something I could do within SSAS to accommodate the way the structure lies at the moment.
It is frustrating that there has to be a 1 - 1 link with fact row and dimension row, you almost want something along the lines of parent-child, where the parent is the caseid and the child(ren) are the events.
I'll look at the ETL process to modify the fact table, including case events.
JK
May 30, 2012 at 6:41 am
It's actually a 1-M relationship: a dimension member can be related to multiple facts. However, an M-1 relationship (one fact related to multiple members of the same dimension) would lead to inconsistencies and a very difficult time to analyze data correctly.
Parent-child hierarchies are possible, but usually the leaf members (the childs) are linked to the measures and data is aggregated up to the parents, not the other way around.
Read the Kimball book if you have the chance, I can recommend it.
Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP
May 30, 2012 at 7:14 am
Here is a different approach. It looks to me like your case table should be an accumulating snapshot fact table. There were several articles on this just recently on TDWI's (The Data Warehouse Institute) website.
May 31, 2012 at 6:55 am
What exactly is the grain of the fact table? Once you know that, you can decide how to solve this issue. It's not clear to me what you intend the grain to be.
May 31, 2012 at 7:40 am
RonKyle (5/31/2012)
What exactly is the grain of the fact table? Once you know that, you can decide how to solve this issue. It's not clear to me what you intend the grain to be.
The grain of the fact table currently is EnquiryID. As one enquiry can have 1 case, the grain is set at the case level.
The dimension reflects the case-events so is a level of granularity deeper. I realise that to solve my issue requires a restructure of my fact granularity.
May 31, 2012 at 2:31 pm
Sorry, but I'm not seeing how that's the grain. As best as I understand it, there is something that goes through a certain lifecycle that includes an open date, some dates in between, and eventually a close date.
If this is correct, and this something is the grain, you should have a fact table with this item as the grain and columns representing the key dates. When first created, the record might look so:
Equiry ID, Open Date: 1/1/12, Other dates: N/A, Close Date: N/A.
The dates are updated through the lifecycle and eventually the record will look so:
Enquiry ID, Open Date: 1/1/12, Other dates: may be filled in or not, Close Date: 5/1/12.
This is the third type of fact table Ralph Kimball talks about. In his view it's the least common, but in my limited view it's been the one I most often create.
Does this help?
May 31, 2012 at 2:32 pm
Forgot to make clear that the dates are not stored in the fact table as dates, but as pointers to your calendar table. You can join the same table multiple times.
June 5, 2012 at 12:01 am
So we have:
A M:1 relationship from dimension to fact.
A dimension tracking events.
I'm getting a Mr Squiggle feeling here.
Your case events log table sounds an awful lot like a fact table.
I'm not clear on the structure of your Main table but I'm thinking you perhaps have a header/detail scenario.
June 5, 2012 at 11:31 am
sam.dahl (6/5/2012)
So we have:A M:1 relationship from dimension to fact.
A dimension tracking events.
I'm getting a Mr Squiggle feeling here.
Your case events log table sounds an awful lot like a fact table.
I'm not clear on the structure of your Main table but I'm thinking you perhaps have a header/detail scenario.
That's exactly what I said I needed to amend. I need to include FKs in the fact table to refer to the case events dimension. Previously the fact table didn't track individual events, and it should. I also need to amend the case events table to cross tab the information in a columnar format.
Thanks all
Viewing 15 posts - 1 through 15 (of 15 total)
You must be logged in to reply to this topic. Login to reply