none
incorrect attribute member names

    Question

  • i'm seeing incorrect attribute member names in my degenerate dimensions.

    the dsv looks like this:

     

    SO records, AR records, and Customer records are all associated with a Location.  SO and AR records are also associated with a Customer.  in some cases, the SO or AR record LocationKey is different from the LocationKey of the customer record that a SO or AR record is associated with.

    when i create a cube and dimensions based on this dsv, it seems like initially everything is fine.  however, inevitably i eventually see the following:

    the AR and SO degenerate dimensions each have a Location attribute, with the fact table LocationKey column as the key, and the Location table LocationName column as the Name.  at some point, the attribute member names will begin to appear as the Location name that is associated with the LocationKey from the Customer table, rather than the appropriate fact table (AR or SO).  it seems that this begins to happen after the relational database is updated. that process generally consists of deleting all fact table rows and repopulating.  when this problem appears, "Process full" on dimensions, partitions, measure groups, or the cube does not seem to resolve the problem.

    i witness this error by browsing the cube, placing SO (or AR) on the rows, showing the "Location" property, and then putting the Location dimension in the subcube area, and filtering for a particular Location.  the SO (or AR) records will be filtered correctly, but the "Location" property for some records will be different from the Location that i have filtered for.

    the Dimension Usage tab in the cube looks like this:

     

    in troubleshooting this problem, I have tried using the BIDS Helper Dimension Health Check tool.  for the SO and AR dimensions, I get this error:

    "Attribute [Location] has key values with multiple names."

    it shows me the "problem rows", and this seems to be what it's referring to:

    it's taking all of the rows in the fact table that have a LocationKey of 1, for example.  it's then getting all of the Customer records associated with any of those fact table records.  it's then joining all of those records to the Location table using the LocationKey column from the Customer table.  and it's taking all of the LocationName values it finds from those joins, and associates them all with the LocationKey "1" from the fact table... then it says it doesn't know which one to choose.

    i don't know what else to do to tell SSAS that for the Location attribute in the degenerate dimensions, i wish for the name to be resolved using the LocationKey from the fact table record that i've designated as the attribute key, not by joining in the Customer table and using its relationship with the Location table... any ideas?

    if i remove the relationship between the LocationKey column in the Customer table and the LocationKey column in the Location table in the dsv, this problem stops happening.  however, i want this relationship because i want to have a Location attribute in the Customer dimension so that each member of the Customer attribute has a property that is the location that customer is assigned to.

    Tuesday, September 28, 2010 12:19 AM

All replies

  • One approach to try is to create 3 role-playing cube Location dimensions like ARLocation, SOLocation and CustomerLocation, corresponding to the single database Location dimension. Then the Dimension Usage above will change so that:

    - ARLocation dimension only relates to Fact AR measure group

    - SOLocation dimension only relates to the Fact Sales Orders measure group

    -  CustomerLocation dimension only relates to the Customer dimension

    With this approach, you also don't need to define Location attributes on the degenerate dimensions, with NameColumns from a separate table - you could simply use the Location attribute of the ARLocation and SOLocation dimensions as needed.


    - Deepak
    Tuesday, September 28, 2010 2:17 AM
    Moderator
  • thanks for your response.  i've certainly experimented with that approach, but it's less than ideal for these reasons:

    1) in general, i want my measure groups to share dimensions as much as possible, so that i can measure different fact data across the shared dimensions.  if none of my measure groups share any dimensions, i might as well just create a separate cube for each fact table.  BIDS even displays a message suggesting this.  in reality, these facts are related through the location dimension, and so i'd like to avoid creating a bunch of copies of my dimensions for each fact table.

    2) suppose i have a set that is comprised of the SOKey attribute members.  when the SO degenerate dimension has the Location attribute, i can create a calculated member defined as the "Location" property of SOKey, drop it in the values area of my excel pivot table, and it displays the location value for each SOKey member.  but if the Location attribute is in a separate "SOLocation" dimension, i will not be able to use it this way.  i will have to bring the Location field of the SOLocation dimension into the row labels area of the pivot table, or add the attribute members to my set. either way, if i then try to add the Location field from the SOLocation dimension as a filter, i will get an error saying that i can't use the field as a filter because the field is already used in the row labels area of the pivot table.  so, i would have to create another attribute in SOLocation..."LocationFilter" or something...and use that field for the filter?  at the end of the day, now i have all these extra cube dimensions and extra attributes in those dimensions.  and my question is why?  what's wrong with the design i described in my original post that's causing me to have to make such a mess of my cube to do anything productive?  how is it that with the attribute's key column set to the LocationKey column of the SO table, and its name column set to the LocationName column of the Location table, the attribute member names are set to something resolved using a join that involves the Customers table? why is SSAS taking such a long road to get that name value when there's a direct FK relationship between the SO table and the Location table?
    Tuesday, September 28, 2010 3:13 AM
  • ".. in general, i want my measure groups to share dimensions as much as possible, so that i can measure different fact data across the shared dimensions .." - but according to your earlier post, the Location for Customer, AR and SO aren't necessarily the same. So how can the same Location dimension role be shared across them - just like multiple independent dates can't be modelled by a single [Date] dimension role?

    Anyway, if you want to continue with your current model, a simple way to address the spedific Location Name issue is to replace each of the 3 tables (AR, Customer and SO) in the DSV with a Named Query that joins with the Location table to derive the necessary Location Name field. That way you don't need to define NameColumn on a single instance of  the joined Location table.


    - Deepak
    Tuesday, September 28, 2010 5:30 AM
    Moderator
  • imagine this pivot table:

    Filter: Location (filtered for location "2")
    Rows: Date
    Columns: AR Amount (measure) & SO Amount (measure)

    that's off the top of my head, but it seems like the type of analysis that could potentially be useful.  when a customer assigned to location "1" (i.e., the LocationKey in the Customers table is "1") makes a sales order at location "2", the amount of that SO will be included in the totals in this pivot table.  then, i might make another pivot table:

     

    Filter: Location (filtered for location "1")
    Rows: Customer
    Columns: AR Amount (measure) & SO Amount (measure)

    because Customer, AR, and SO share the location dimension, i would expect to see only the sum of the AR transactions and SO transactions assigned to location "1" that are for customers assigned to location "1".  this means that any sales orders these customers made at location "2" would not be included in the totals in this pivot table.

    Now, if i wanted to see Customers assigned to location "1" on the rows, and the total amount of the SOs they've made at *any* location, i would need to remove the filter from the pivot table, and instead do a label filter on the location property of the customer field for "equal to: 1".

    does that make sense?  am i failing to understand something about using shared dimensions in this way?

     

    regarding the named query suggestion: i think you're right that this would be a workaround for my issue.  that said, i would still like to understand why i'm having the issue in the first place.  i do want to solve this problem, but i also want to really understand the root cause of the problem!  can you help me to understand the fundamental flaw with the design in my OP, and why SSAS is having so much trouble with the Location attribute in my degenerate dimensions?  thank you!

    Tuesday, September 28, 2010 6:50 AM
  • let me give an account of what i'm currently seeing to give some more background as to why i want to really understand what's going on here.  in the weeks that i've been troubleshooting this issue, i've seen some things, like what i'm seeing now, that:

    a) make me really question my understanding of ssas

    b) make me wonder if my issue isn't more of a processing / cache issue than a schema or cube / dimension design issue

     

    so, i had my cube in a state where the degenerate dimension "Location" attributes were displaying the correct names.  but this was through a workaround that i wasn't happy with.  i had an idea of something to try.  so, i made the changes in BIDS in my development database, deployed, and it appeared that i was getting the correct values.

    at this point, i used the deployment wizard to deploy my development database to my production database.  i just used "default processing".  when this finished, i was NOT getting the correct names in the production database. supposedly i had just used the deployment wizard to copy the development db to the production db, so they should be identical, but i'm getting the correct names in one, and the wrong names in the other.

    i thought maybe it was a processing issue.  so i ran a processing script that basically does an "unprocess" on every object in the db, then a "process full" on every object in the db.  still, i was seeing the wrong values.  so, i tried running the same processing script on my development db, but i was still seeing the correct values there.  so...i now have two databases that are supposedly identical, and i've just attempted to process them both in exactly the same manner, and they're both using the same, unchanged underlying data, but they are giving me completely different query results...

    i then tried using the deployment wizard to copy the db to the production db, only i used the "full processing" option.  still, the production db shows the wrong names.

    so, i have my development database running right now, showing me accurate names.  i've tried to completely unprocess and reprocess it, and it still shows the correct names.  i can deploy it to a new db, and it still shows the correct names.  but when i deploy it to my production db, no matter how i process that db it gives me the wrong names.

    how can this even be possible?  is there some kind of cache that is storing the correct names from when i was using the workaround, persists even after making changes to the dimensions, deploying, processing, unprocessing everything, and process full-ing everything...and that were i to delete this cache, i would see incorrect names in my development db as well?  come on...

    i could just delete my production db and deploy my development db to a db with the same name, but all of my roles are in my production db.  i don't know of any way to import my roles from my production db into my development db.  when i was thinking this through, i wondered if the problems i'm having deploying to my production db could be related to roles. so i copied my production db, deleted all the roles, and used the deployment wizard to deploy my devel db to this new copy of my production db, using the "deploy roles and members" option.  still, i'm seeing the incorrect values in this db.  i'm quite baffled as to how this can even be possible...

     

    Tuesday, September 28, 2010 7:59 AM
  • ok, this is just getting ridiculous.

    continuing from my last post, i found a way to run an xmla script in ssms to copy my roles from my production db into my development db.  i'll list the steps along with the results.  at this point i had used the deployment wizard to deploy my devel db to my production db, but i was seeing incorrect names in my production db and correct names in my devel db.  so, i started with mydb (incorrect names) and mydb-dev (correct names):

    * used xmla to copy roles from mydb to mydb-dev. mydb-dev still showing correct names

    * imported mydb-dev into BIDS. deleted mydb.  deployed from BIDS to mydb.  mydb shows incorrect names

    * deleted mydb. in ssms, made backup of mydb-dev, and restored backup to mydb. mydb shows correct names

    * imported mydb into BIDS. deployed to mydb-test. mydb-test shows incorrect names.

    *deployed from BIDS back to mydb.  mydb shows correct names.

    so, now i have a database in BIDS that when i deploy to one ssas db, i get the correct names, and when i deploy to a different ssas db, i get incorrect names.  again, i ask: how is this possible?

     

    Tuesday, September 28, 2010 10:20 AM
  • >>

    Filter: Location (filtered for location "2")
    Rows: Date
    Columns: AR Amount (measure) & SO Amount (measure)

    when a customer assigned to location "1" (i.e., the LocationKey in the Customers table is "1") makes a sales order at location "2", the amount of that SO will be included in the totals in this pivot table. 

    >>

    I don't think so - only customers assigned to location "2" will be included, based on your model of a single Location dimension.

     

    >>

    Filter: Location (filtered for location "1")
    Rows: Customer
    Columns: AR Amount (measure) & SO Amount (measure)

    because Customer, AR, and SO share the location dimension, i would expect to see only the sum of the AR transactions and SO transactions assigned to location "1" that are for customers assigned to location "1".  this means that any sales orders these customers made at location "2" would not be included in the totals in this pivot table.

    >>

    That's also my understanding.

     

    >>

    Now, if i wanted to see Customers assigned to location "1" on the rows, and the total amount of the SOs they've made at *any* location, i would need to remove the filter from the pivot table, and instead do a label filter on the location property of the customer field for "equal to: 1".

    does that make sense?

    >>

    Unfortunately, it doesn't seem to, based on a single Location dimension role. When you select Customer rows with Location "1", that means both AR and SO Locations are also "1", not *any" location.

     


    - Deepak
    Tuesday, September 28, 2010 9:22 PM
    Moderator