Role Playing vs single generic Conformed (for drill-across) time dimension

Hi All

I've been working with Kimball & BI technologieas for a few years now but something I've always struggled with is the trade-off between Role Playing vs Conformed time dimensions & wondered if I could get peoples opinions/advice on the best approach.

Lets say we have 2 fact tables for a fictitious insurance company: Policy and Claim

There is a conformed Client dim that allows me to do drill-across (based on Client attributes) to compare Policy and Claim facts. This works well in OLAP browsers and as usual the drill-across is very powerful for analysis.

But I struggle whether to add the Time dim as a single shared dim or as seperate Role Playing dims.

Lets say that:
- Policy facts have a key date attribute: Policy Start Date
- Claim facts have a key date attribute: Claim Date

If I add the related Time dim as 2 Role Playing Dims ("Policy Start Date Details" & "Claim Date Details") when I browse the resulting OLAP cube I cannot drill-across betweent the two facts by time - Policy facts don't have Claim Dates & vice versa. This seems very limiting functionaly. Many users will want to compare Policy measures against Claim measures for specific calendar periods.

The alternative seems to be to impliment the Time dim as as a single generic "Date Details" dim. This enables very useful drill-across between the two sets of measures *BUT* because we're using just a single generic "Date Details" dim, the dimension & attribute names do not give any clue to the user as to what the date values actualy mean i.e. Is it the "Policy Renewal Date", the "Policy Start Date" or the "Policy Lapsed Date"? Similarly for the Claim facts: is it the "Claim Opened Date", "Claim Approved Date", "Claim Rejected Date", "Claim Settled Date", etc. How does the user know that we only have 1 key date per fact & what that date happens be?

The use of a generic Time dim to enable drill-across seems to sacrifice ease of use specifically explicitness & clarity.

This doesn't happen with other conformed dims as far as I can tell - only with Role Playing dims. The Client Dim is always the Client dim & when you drill across both Policy & Claim measures by Client Postcode then the attribute is clearly named as "Client Postcode" & its explicit what you are comparing the seperate measure by.

I'm rambling now so I'll stop.

Much appreciate any thoughts even if it's to say "you're a moron - this is how you do it"

Thanks in advance.

Steve

Policy start date and claim date are two different dates meaning two different things... why would you want to treat them as the same?

From a claims perspective, if you included policy information as part of the claims fact, then policy and policy start would be dimensions. However, that is atypical. Usually enrollment is its own fact with policy/coverage information as dimensions with little or no policy information in the claims fact. If you were to drill across, you would want to look at claims in the context of the policy in force at the time of the claim, which has little to do with policy start date and everything to do with enrollment period.

As far as implementing a date dimension in a relational database, it is always just one physical table. Each use of date is handled by assigning role based names to FK columns. Some modelers create views or synonyms for each role, but that is overkill if you are using any reasonable BI tool.

Hi ngalemmo

Many thanks for your response but it doesn't really clarify things for me.

I understand what you are saying and agree that Policies & Claims are two different entites & therefore I shouldn't want to slice them by the same Date attributes but the reality I find (in many businesses & many BI projects) is that organisations are often interested in very simplistic views of their business i.e. the requirement is "I want to see the value of new Policies & the value of Claims by month for last year so I get an idea of the 'ebb and flow' of my business".

I'm not working for an insurance company so don't want to get involved in a detailed discussion of the business entites but the organisation I work for wants to perform many comparitive reports across months for multiple facts and many of the facts have 6+ key miilestone dates each could be an FK to a seperate Role Playing time dim.

But if I impliment multiple Role Playing time dims (and I'd only do this at the OLAP/presentation tier) then the user is not able to drill-across these multilpe facts to produce the kind of reports they want.

My alternative seems to be only have a single generic time dim that does allow the user to drill-across but it's not explicit to the user what the dates represent for each measure.

I was really just looking for advice/opinion as to whether this was something other people have struggled with or if there was a standard modelling way of approaching this that I'd missed.

Thanks in advance.

Steve

I shouldn't want to slice them by the same Date attributes

Sure you would. For example you may want to look at claims incurred this year for policies that were purchased last year. But such a query requires the dates (claim date and policy start date) be treated independently.

I don't really understand what you mean by a "seperate Role Playing time dim". Do you mean a logical or physical separation?

But if I impliment multiple Role Playing time dims (and I'd only do this at the OLAP/presentation tier) then the user is not able to drill-across these multilpe facts to produce the kind of reports they want.

Why not? If I had both claim date and policy start date available to me, why could I not use them as axis in an analysis? Why would a role not assume all attributes in the dimension?

My alternative seems to be only have a single generic time dim that does allow the user to drill-across but it's not explicit to the user what the dates represent for each measure.

Why would you want to do that? It makes no sense.

I am concerned with your use of "what the dates represent for each measure". This leads me to think what you are taking about is a mixed grain fact, which is something that should be avoided. In a proper fact table or cube, all dimensions apply equally to all measures. If you have semi-additive measures (such as policy premium) either your BI tool should have facilities to deal with it, or you need to allocate higher level measures to the lower grain, or you need to educate the users warning them the measure should not be summed in certain circumstances. The other approach is to aggregate the lower grain measures (claim measures) to the higher grain measures (policy). In such a cube, you would not have access to individual claim data unless your BI tool has drill through capability to access claim level facts in another cube or table.