Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!
  • Students Click Here

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Students Click Here


MDX for getting history based on last dimension characteristic for SCD

MDX for getting history based on last dimension characteristic for SCD

MDX for getting history based on last dimension characteristic for SCD

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:
DimPolicyId     BusinessPolicyid    PolicyType
100        1            Simple policy
101        1            Advanced policy

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

RE: MDX for getting history based on last dimension characteristic for SCD

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.

RE: MDX for getting history based on last dimension characteristic for SCD

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:
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.

RE: MDX for getting history based on last dimension characteristic for SCD

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
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.


RE: MDX for getting history based on last dimension characteristic for SCD

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.

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members!

Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close