Location Table Data:
Sales Table Data:
After accepting all the wizard defaults to create a cube, I end up with this Sales (Fact) Dimension:
I change the NameColumn properties like so:
After deploying the database, I create a basic pivot table report in excel, and add the member properties to the report.
The value is incorrect for row 2, for the "Location Warehouse Key" member property. It shows "Location 1" when it should be "Location 2".
I'm looking for any creative ideas on how to work around this problem, without using named queries or views. i want to have a single database dimension, from which i create multiple cube dimensions. And I don't want to use a named query with multiple joins to the Location table to pull in the LocationName column as "Location_Store_Name" and "Location_Warehouse_Name" or whatever. That just seems messy.
- Edited by almaplayera Thursday, April 12, 2012 1:16 AM
Your problem is not actually related to Role-Playing Dimension.
A dimension that is added multiple times to the same cube and can play different roles in the fact table depending on the context is called role-playing dimension.
One of the best solution to your problem could be to de-normalize your tables in datamart and have the location name directly in your sales dimension. If this could not work for you then the other workaround would be use same as you already mentioned named query or views.
When I create a cube using the wizard from that DSV, a single Location database dimension is created, and the cube is created with two Location dimensions: "Location Warehouse", and "Location Store". Those are "role-playing dimensions", no?
Member properties of attributes in a fact dimension work fine, as long as there is only a single cube dimension for each database dimension that relates to the fact table. It's only when there are multiple cube dimensions for a single database dimension that the member properties are displayed incorrectly.
I definitely do not want to store multiple description / name (text) columns in my fact table...
Any other ideas?
In your project, the two location dimensions being created are role-playing dimensions. However, the question asked is not with role-playing dimension. To achieve the purpose, i would suggest creating a named query with following sql statement -
SELECT LocationKey, LocationName FROM Location
and the named query can be named as Location2, then link the named query to the Sales with Location_Warehouse_key and LocationKey. Then, for the sales dimension, the NameColum property of the Location_Warehouse_Key attribute can be set to Location2.LocationName. So, the Location_Warehouse_key can be displayed correctly.
heh, i dunno. i guess i'm confused. the problem i described only occurs with role-playing dimensions. that is, it's only when a fact table has two or more foreign keys to the same dimension table (which is in turn used as the source for multiple role-playing cube dimensions) that this is an issue.
in any case, i've given up and went ahead with the named queries approach when i realized that i could do so while still maintaining a single database dimension for location. i don't like it at all...now i have a bunch of named queries that i have to maintain if i ever want to change my location table...but what else can i do?