MDX for getting history based on last dimension characteristic for SCD

[individ]

MDX for getting history based on last dimension characteristic for SCD.

Hi everyone,
I am implementing the cube and I run into a problem with slowly changing dimensions(SCD). I guess it might be resolved somehow with MDX, maybe you guys can help me to resolve it. The problem: I have SCD, say Policy with dimension attribute PolicyType. I have a measure, say NumberOfActivePolicies. To simplify, I have 1 policy in my dimension which was activated in January with PolicyType=’Simple policy’. In February policy was amended and PolicyType become ‘Advanced Policy’. When I browse my cube, I have NumberOfActivePolicies = 1 for PolicyType=’Simple policy’ and I have NumberOfActivePolicies = 0 for PolicyType = ‘Advanced Policy’. This result reflect exactly what I have in my fact table: 1 ‘Simple Policy’ was activated and 0 ‘Advanced Policies’ was activated. But this result does not reflect the business situation – after amendment I have 1 ‘Advanced Policy’ activated and 0 ‘Simple policy’ activated. And now the question: is that possible to count NumberOfActivePolicies not according to the facts, but according to the last dimension characteristic (I mean if I query the cube till January only, I have 1 ‘Simple policy’ and 0 ‘Advanced policy’, but if I query the cube till February I have 0 ‘Simple policy’ and 1 ‘Advanced policy’). How can I implement this logic?

Here is what I have approximately on data level:
DimPolicy:
DimPolicyId BusinessPolicyid PolicyType
100 1 Simple policy
101 1 Advanced policy

FactPolicy
Id DimPolicyId FactDate Activation
1000 100 jan 2010 1
1001 101 feb 2010 0

So if I just summarize all activations and group them by PolicyType I get wrong answer from business point of view
Thanks

 

[RiverGuy]

I don't see how you would employ MDX to solve this. To MDX, DimPolicyID 100 and 101 are just two different members in the dimension. It doesn't know that 101 is what 100 became.

How large is your fact table? If it is not too large, you could change it to a named query in your data source view with logic like the following:

SELECT a.*, c.DimPolicyID AS CurrentPolicyID
FROM FactPolicy a
INNER JOIN dimPolicy b
  ON a.DimPolicyID = b.DimPolicyID
INNER JOIN dimPolicy c
  ON b.PolicyID = c.PolicyID
  AND c.CurrentMember = 1  --Logic here depending on how you denote the current member of your SCD

So you end up with two foreign keys to DimPolicy, and one of them points to your current DimPolicy member. You could then create a RolePlaying dimension for Policy, name it Current Policy, and connect it to your Policy measure group on the CurrentPolicyID column.

Another option would be to simply alter your Policy Dimension to include an attribute called Current Policy Type. Have your ETL keep that updated.

 

[individ]

Hi RiverGuy, yes, the idea of current DimPolicy member looks good, but the problem is the ‘current’ depends on the MDX query. If I query the cube for jan only, the current is DimPolicyId=100, if I query the cube for period jan-feb, the current is DimPolicyId=101. Or you suggest to somehow reprocess this fact table every time I query the cube(I am not sure if it possible at all)?
And actually I simplified the description a bit, I have other measures which needs to be calculated with the similar logic, for example money:
FactPolicy
Id DimPolicyId FactDate Activation Debit Credit Balance
1000 100 jan 2010 1 100 60 40
1001 101 feb 2010 0 80 0 120
Here I have 1-st record link to ‘Simple policy’ and 2-nd to ‘Advanced policy’. Now if you ask the cube what was the Debit for ‘Advanced policy’ for the period jan-feb, you will have the answer 80. Which is wrong from the business point of view, and the right answer is 180. So I am looking for the possibility to calculate dimensions, depending on the query period, take the last one in this period and link all facts to this one.

 

[RiverGuy]

I'm not sure I understand your requirement. In your latest example, you have different surrogate keys in addition to different business keys. So this tells me the two policies you have listed cannot be different versions of the same policy, but two completely separate policies. Usually, people want to look at things one of two ways:

1. Measurements by attributes as they exist today
or
2. Measurements by attributes as they existed historically.

Type-2 SCD takes care of #2, which I assume is what you have. I gave you a couple of solutions for #1.

 

[individ]

no, you got it not correct – my last example it is the copy of previous example with 3 additional columns(measures) in fact table – Debit, Credit and Balance – just to give you an idea that that might be other measures which requires this logic. All that records in fact table are about the same policy(different versions of the same policy).

Yes, I know, usually people looks at things those 2 ways, but we require 3-rd way. I have read all books in ‘chapter’, I have read all internet, and everywhere there are only 2 ways. I need the 3-rd way. Our business requires 3-rd way, where the current record depends on the time frame of the particular MDX query!
Actually we are just trying to decide if we going to use SSAS or not and we have already done a lots work in this direction, and this problem just killing the whole project… This logic is actually simple to implement in a usual report, writing a usual stored procedure, but looks like it is impossible implement in SSAS (

But any way, thanks for your answers.