locked
Analysis Services and Referenced Dimensions RRS feed

  • Question

  • Hi

    I have a curious problem which I can only trace back to Referenced Dimensions. I have a fact table which can reference my Site dimension via two possible routes:

    1. Fact Table -> Customer -> Site
    2. Fact Table -> Cost Centre -> Site

    We first used to go via the Customer route but because of incomplete data we have now resorted to the Cost Centre route. We have changed the reference dimension for Site from Customer to Cost Centre. The materialized view check box is still checked.

    We are still having data funnies and after hours of head scratching and reprocessing various objects, I have narrowed it down to the fact that the SQL script that processes the partitions still used the customer route. I inspected the SQL statement that you can see when pressing the View details button when processing a partition and it showed definite inner joins to the Customer instead of Cost Centre dimension.

    It seems that it does not want to honour the new referenced dimension and is determined to use the old route. Has anybody also had this problem and how do I tell AS to use the new mappings instead of the old ones. My dimension usage tab in BIDS does not have any reference to the old customer dimension except when used in other measure groups that are not related to this particular measure group sourced from the fact table.

    Help!!

    Friday, January 14, 2011 9:54 AM

Answers

  • Can you check the relationship in the data source view. I think the queries are formed more based on how the relationship exists in the dsv rather than the dimension usage tab. I did some experimenting months back and quite not sure..you can double check.
    vinu
    Friday, January 14, 2011 12:43 PM
  • Does this blog post explain what happened?
    http://sqlblog.com/blogs/alberto_ferrari/archive/2009/02/25/ssas-reference-materialized-dimension-might-produce-incorrect-results.aspx

    I personally prefer putting the Site key into the fact table instead of using reference dimensions, if possible.


    http://artisconsulting.com/Blogs/GregGalloway
    Sunday, January 16, 2011 12:09 AM
  • Hi Trevor,

    While it sounds like a bug, have you also tried to remove the checkbox? Does it solve the problem with the data after processing? If you then check it back on does it fix the problem? For some reason there may be a bug in BIDS when doing this, so as a workaround I would try to remove the tick, then remove the dimension relationship altogether and then redeploy with the relationship and with the tick in place...At least if you report the bug on Connect you can provide a workaround if it works out.


    Boyan Penev --- http://www.bp-msbi.com
    Monday, January 17, 2011 9:47 AM
  • Trevor-

    Of course, it could be a bug. But I would do the following research first. You realize that when you process a partition that uses a materialized reference dimension, when it runs the SQL query to get the partition data, it does a join to the intermediate dimension to get the reference dimension key. So process a partition in SSMS and expand down until you see the SQL query it's running. Then try running that SQL query yourself to see what it returns. It could be it's using the wrong join path due to DSV relationships or something crazy like that.


    http://artisconsulting.com/Blogs/GregGalloway
    Monday, January 17, 2011 4:28 PM
  • Sounds like it's definitely not a bug, then. I would try all of the following:

    1. Redeploy the cube from BIDS so that the latest reference dimension relationship gets deployed to the server.

    2. Check the relationships between tables in the DSV as I believe they help control the SQL join logic for things like reference dimensions and snowflaked dimensions.

     


    http://artisconsulting.com/Blogs/GregGalloway
    Tuesday, January 18, 2011 1:14 PM
  • Vlad, that's new information for this thread, as far as I understood. So you've got a role-playing date dimension (i.e. a database dimension that's used more than once as a cube dimension). And that role-playing dimension is a reference dimension. I bet that's the problem. I bet the role-playing date dimension and the relationships you've built in the DSV are confusing SSAS. Whether it's a bug or not a supported scenario, I can't say.

    Feel free to report this on connect and post the connect link here.

    Another workaround would be to change the fact table to a named query and do the joins yourself and return CreatedDateKey and ModifiedDateKey as columns in the fact table. That way you wouldn't need a reference dimension at all. (Or do the above in your ETL and physically store those two columns in the fact table.)


    http://artisconsulting.com/Blogs/GregGalloway
    Thursday, February 3, 2011 1:56 PM

All replies

  • Can you check the relationship in the data source view. I think the queries are formed more based on how the relationship exists in the dsv rather than the dimension usage tab. I did some experimenting months back and quite not sure..you can double check.
    vinu
    Friday, January 14, 2011 12:43 PM
  • Thanks for the reply. Yes I was thinking that it somehow derived these relationships based on the DSV or FK constraints on the table. I was actually going to delete the Customer FK from the fact table entirely but thought that was a bit of a drastic measure. Anyone else got any ideas. I have read a few articles about issues around referenced dimensions, seems like Microsoft should put this on their "list". I might head off to the Connect site and log it with them.
    Friday, January 14, 2011 12:47 PM
  • Does this blog post explain what happened?
    http://sqlblog.com/blogs/alberto_ferrari/archive/2009/02/25/ssas-reference-materialized-dimension-might-produce-incorrect-results.aspx

    I personally prefer putting the Site key into the fact table instead of using reference dimensions, if possible.


    http://artisconsulting.com/Blogs/GregGalloway
    Sunday, January 16, 2011 12:09 AM
  • Hi furmangg. Thaks for the link. I had looked at it before. This is very interesting but I dont think it solves my problem. I essentially want to remove AS's "memory" of my previous reference dimension setting and use the new one. I did think it was related to the materialised view checkbox, but I have reprocessed all my partitions and it is still insisting on using the old relationship.

    Monday, January 17, 2011 4:33 AM
  • Hi Trevor,

    While it sounds like a bug, have you also tried to remove the checkbox? Does it solve the problem with the data after processing? If you then check it back on does it fix the problem? For some reason there may be a bug in BIDS when doing this, so as a workaround I would try to remove the tick, then remove the dimension relationship altogether and then redeploy with the relationship and with the tick in place...At least if you report the bug on Connect you can provide a workaround if it works out.


    Boyan Penev --- http://www.bp-msbi.com
    Monday, January 17, 2011 9:47 AM
  • Trevor-

    Of course, it could be a bug. But I would do the following research first. You realize that when you process a partition that uses a materialized reference dimension, when it runs the SQL query to get the partition data, it does a join to the intermediate dimension to get the reference dimension key. So process a partition in SSMS and expand down until you see the SQL query it's running. Then try running that SQL query yourself to see what it returns. It could be it's using the wrong join path due to DSV relationships or something crazy like that.


    http://artisconsulting.com/Blogs/GregGalloway
    Monday, January 17, 2011 4:28 PM
  • Hi. I did trace the SQL query from the partition processing and it is using the join to the first/old/original intermediate dimension (customer) not the newer/current one that is setup in BIDS (cost centre).

    Tuesday, January 18, 2011 7:04 AM
  • Sounds like it's definitely not a bug, then. I would try all of the following:

    1. Redeploy the cube from BIDS so that the latest reference dimension relationship gets deployed to the server.

    2. Check the relationships between tables in the DSV as I believe they help control the SQL join logic for things like reference dimensions and snowflaked dimensions.

     


    http://artisconsulting.com/Blogs/GregGalloway
    Tuesday, January 18, 2011 1:14 PM
  • Greg, I meant a bug in BIDS not SSAS :) Something like not deploying the changes correctly, or not saving them correctly...


    Boyan Penev --- http://www.bp-msbi.com
    Thursday, January 20, 2011 9:45 AM
  • Hi guys,

    Having similar problem.

    Simplified fact table:

    EventDateKey, CustomerKey

    Customer dimension in turn has two attributes -- CreatedDateKey, ModifiedDateKey that reference Date dimension.

    So, for CreatedDateKey and ModifiedDateKey attributes I'd like the NameColumn to reference Date dimension. What I've found out is that for both attributes SSAS generates SQL query that joins Customer dimension to Date dimension by CreatedDateKey (in fact, by the first one I add as an attribute, if I add ModifiedDateKey first, then join goes on it). Relationships both in the DSV and in the cube are correct.

    As a workaround I replaced Customer dimension with a view that joins to Date dimension to obtain dates names. Don't really like such approach though.

    Should this go to "connect"?

    Thanks,

    Vlad

    Thursday, February 3, 2011 9:37 AM
  • Vlad, that's new information for this thread, as far as I understood. So you've got a role-playing date dimension (i.e. a database dimension that's used more than once as a cube dimension). And that role-playing dimension is a reference dimension. I bet that's the problem. I bet the role-playing date dimension and the relationships you've built in the DSV are confusing SSAS. Whether it's a bug or not a supported scenario, I can't say.

    Feel free to report this on connect and post the connect link here.

    Another workaround would be to change the fact table to a named query and do the joins yourself and return CreatedDateKey and ModifiedDateKey as columns in the fact table. That way you wouldn't need a reference dimension at all. (Or do the above in your ETL and physically store those two columns in the fact table.)


    http://artisconsulting.com/Blogs/GregGalloway
    Thursday, February 3, 2011 1:56 PM