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


SCD Type II : MicroStrategy v.9

SCD Type II : MicroStrategy v.9

SCD Type II : MicroStrategy v.9


I am having an issue with slowly changing dimensions Type II in MSTR. My dimension table is updated about once a week (so I guess it would be a quickly changing dimension, lol) and when user's run reports in MSTR, they see multiple records for the same store.

The dimension table consists of a:
[surrogate key],
[Store ID],
[store description],
[zone id],
[end effective date]
(there are more fields, but for simplicity I will limit the fields).

The only way my fact tables relate back to which store the items were sold from is by the surrogate key (store_key).

Everything was running smooth until I started updated the DIM table, now my reports show the same store_id and description multiple times.

My MSTR environment has a SQL Server 2008 R2 backend, and is pointed to views. I created a new view with everything in the current DIM table EXCEPT the surrogate key. Then I modified the hierarchy in MSTR so that all the parents of the store attribute would flow through the newly generated attribute (which is pointed at the new view). I also assigned the appropriate Parent/Child relationships to this newly created attribute.

This has worked for me on some reports, but on reports that pull revenue, per store, my report fails. The fact table with revenue only has a store_key relating back to the dimStore table, and my newly created attribute is pointed at a new table that does not have the store_key.

Maybe I have tried to explain this too much and made this thread too long... but the question is - How do you deal with SCD type II in MSTR, when you want to SUM the sales of a duplicated attribute?

The only info I could find online is how to report on 'AS IS' or 'AS WAS'. Meaning creating two attributes, one pointed to the old record, and another pointing to the new. But this seems like a maintenance nightmare.

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! Already a Member? Login

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