October 16, 2013 at 8:30 am
Evening all,
I have a curious problem where dimension hierarchies are not being built in the way I expect. My data relates to vehicle types and the structure should follow as:
Vehicle Category ( Truck, Car, Motorcycle etc)
Vehicle Type (saloon, SUV, Sports, Supersports etc)
Make (Ford, Yamaha...)
Model (vehicle Model title)
VehicleCode (e.g. 1234567)
I have defined attribute relationships to suit the above:
VehicleCode > Model > Make > VehicleType > Vehicle Category
....but if I build the dimension I have the problem that all values for Yamaha lets say appear under the first vehicle type where there is data?? (so again as a data example all yamaha models -even if they have a vehicle type of classic or super sport are appearing under Quad??? I get no design warnings!
I've tried various different design for the dimension, but I alway appear to end up with one or more data items appearing in the wrong place.
To me this seems like it should be a natural hierarchy but I think SSAS disagress and I'm not sure how I can build the hierarchy I'm looking for. Any tips / suggestions would be appreciated.
_____________________________________________________________________________MCITP: Business Intelligence Developer (2005)
October 16, 2013 at 9:19 am
Have you tried using composite keys for the different attributes of the hierarchy?
So at the Vehicle Code level the attribute key would be:
VehicleCategoryKey+
VehicleTypeKey+
MakeKey+
ModelKey+
VehicleCodeKey
If you don't do that then you can end up with some funky looking hierarchies 🙂
October 17, 2013 at 5:19 am
My results are definitely a little funky! 😀
I tried having separate fields and defining the 3 fields (i think it was three I had) as a logical key to join onto another level. I even went back and looked at the AdventureWorks2008DW example for geography (very similar to what I'm doing), and still couldn't get it working :crazy:
so just to clarify would that composite key need to be as a single field? i.e.
CompositeKey = VehicleCategoryKey + VehicleTypeKey
Vehicle Category = Vehicle Category Description
_____________________________________________________________________________MCITP: Business Intelligence Developer (2005)
October 17, 2013 at 6:37 am
Apologies I don't think my first post was that clear!
When I say composite key I mean you set it in the Key Columns property of the dimension attribute. So in there you would have all of the attributes in your hierarchy that are above the current attribute and then you set the Name Column property to the actual attribute that you want to display at that level. No need to add anything to your source tables.
So for example if you had a hierarchy that had three levels; Category, Sub-Category and Product, then your Key columns for Product would be Category, Sub-Category and Product and your name column would be Product.
Let me know if that sorts it 🙂
October 17, 2013 at 6:42 am
ah - gotcha.
Ok - Will give that a go - might not be today, but I'll let you know if it works.
thanks
_____________________________________________________________________________MCITP: Business Intelligence Developer (2005)
October 17, 2013 at 9:39 am
Top man yayomayn! :Wow:
My dimension is working now. Really appreciate your help.
➡ For anyone reading this thread with a similar issue - I have attached a xlsx doc with details and screen shots of the solution.
_____________________________________________________________________________MCITP: Business Intelligence Developer (2005)
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply