Modelling parent-child relationship source tables to Fact with correct grain

Hi,

I have a question which is similar to the post titled 'How do we design parent-child relationship of the lowest grain in the fact' made on May 18, 2012, but unfortunately I don't fully understand the answer.

It seems my modelling question is very similar. I have two operational source tables which have a parent-child relationship. The parent is the 'Case' table and the child is the 'Actions' table. Therefore a Case can have 1 or many Actions.
Ideally, according to my understanding of the Kimball modelling methodology, we could place all the data in one fact table, with the Action record being the fact grain record.

However, it is possible that a Case record may not necessarily have an Action record (so the relationship is in fact 0,1, or many) so therefore in this scenario the Action record does not exist which creates a problem for the Fact table to have this as the lowest grain.

My thought was in this case to add a dummy key for the Action record. By this I mean, a Case No. is unique which identifies the Case and an ActionId is unique which identifes the Action. The combination of Case No. and ActionId is the unique identifier for the Fact row. Where an ActionId does not exist for a Case, a record could be created in the Fact table with the CaseNo. and an ActionId of say -1 and any Action measures blank. This would be the same for all Case No.s.

Is this correct ? If not what is the correct solution.

Regards,
prw

I'm a little surprised to see quite a few views but not any response to the question. I would have thought this was a fairly common modelling requirement as 1:0,1,many relationships are very common in transactional systems.

Have I not put the question correctly or explained it well ?

Would be grateful for any feedback, however small !

Thanks in advance.

If the thing you are measuring does not exist, then you won't have a measurement for it in your fact table. In this case you have a case (dimension) but no action (fact). What would normally happen in this scenario is a dimension row would be created and no facts would be related until the source creates them.

Hi prw,

It is not clear if you have any facts on the Case level. Can you give more details on the dimensions and measures you have in the model?
I guess by "place all the data in one fact table" you refer to the process which R. Kimball calls "allocation". If you give more details on you model, it would be easier to understand if allocation can be done. Without all the details I'm guessing that you are trying to mix fact granularities.

Thanks,
Alisher
www.streebo.com

Hi yuldashev,

Thanks for reply. You are on the correct track when talking about 'allocation' though I don't think that is exactly the problem.
Let me try and explain using the Order and Order Line Item example (as in Kimball :-)). If a value such as Tax occurs at the Order level e.g. £50, and there are 5 Order Line items for that Order, because of the different grain of the facts between Order and Order Line Item, then for each Line item the value for tax would in this case need to be £10 in order to aggregate correctly up to the Order Level (subject to business agreement that this allocation is acceptable).

In our instance, Action is the lower grain (consider it the same as Order Line Item), so e.g. if we had a fact value named 'Adjustment Amount' for £50 that exists at the grain of the 'Case' we would allocate appropriately at the Action Level as in the example above.

However, this is not really a problem as the users do not want to analyse any fact data down to the Action level, so as long as aggregation up to the 'Case' level is correct (as it would be following this example) then allocation is not an issue.
What the question I have is the point that a Case can exist without an Action. This is therefore different from the Order and Order Line Item example where an Order if it exists must have an Order Line Item detail record and therefore as long as allocation occurs (as you have pointed out) then the two sources of different grain can be combined into the same fact table.
How can we therefore combine 'Case' source data and 'Action' source data into the same fact table in the instance where for a particular 'Case' record we do not have an 'Action' source record. As indicated, my proposal in this case is to use a dummy ActionId of say -1. If this is not feasible then the only solution I can see is to have two separate fact tables but these then would need to be joined (as the users would wish to drill down in reports from the Case No. to see the associated Actions), which has performance implications.

Apologies for the long-winded response but I hope that better explains the requirement.

Hi:

I think the dummy field is one option when mixing case and action in the same table, and you can filter records when doing aggregation with this dummy id.

If your db is oracle, maybe you can also use the 'connect by' sql to generate a parent-child reports, and in the fact table, actions need a field to indicate their parents, case.

Thanks
Larry