Sign in
 
Home20122008 R2200820052000LibraryForums
Note: Forums will be making significant UX changes to address key usability improvements surrounding search, discoverability and navigation. To learn more about these changes please visit the announcement which can be found HERE.
SQL Server >
> 
> 
Dimension reload breaks pivot table references

Unanswered Dimension reload breaks pivot table references

  • Wednesday, February 13, 2013 3:19 AM
     
     
    Sign In to Vote
    0

    Hi all,
    First, a bit of background: In our cubes, all dimensions have the surrogate key defined as the dimension key and we typically only update or insert rows in our data warehouse dimension tables. Recently we needed to reload the data warehouse due to a problem with the server, and this has had the effect of breaking existing pivot table reports. In looking at the underlying MDX, the surrogate key value is referenced in the queries, but because the dimension tables were reloaded, the surrogate keys have changed and the sid references are now invalid, or incorrect.

    Is there a recommended way to fix this? Everything I've read indicates that the sid should be the key column for a cube dimension. Is this not correct?

    Luckily we do not yet have heavy usage of the cubes, but that fact that a reload appears to break all reports makes me very nervous. Have I missed something fundamental in our cube design?

    That's a few questions, but I guess the one that's paramount is: how do I fix this?

    Thanks,
    -Mark

     

All Replies

  • Wednesday, February 13, 2013 3:34 AM
    Moderator
     
     
    Sign In to Vote
    0

    I've definitely run into this. I typically don't reload dimension tables, but I've run into this in developing an Excel PivotTable against our QA server then changing the connection to point to our production server (which has different surrogate keys than QA).

    The best practice I've adopted is to create a hidden key attribute for each dimension which uses the surrogate key as the key. Set it to AttributeHierarchyVisible=false then create other attributes that expose the business key and other attributes. This way, the surrogate keys don't really come into play.

    On dimensions like Date where the surrogate key is really a smart key like 20131231 which won't change, I don't bother with the above.

    http://artisconsulting.com/Blogs/GregGalloway

     
  • Wednesday, February 13, 2013 3:57 PM
     
     
    Sign In to Vote
    0

    Thanks Greg,
    We don't typically reload dimension tables either, which I guess is why I haven't run into this before. Your suggestion makes sense. Would you include the surrogate key column in the attribute relationship?