SSAS Hierarchy Question
-
Background:
I have a production SSAS cube that now needs to be modified so that one of the hierarchies displays differently. Basically I have a alignment rollup where the top level is Corporate, then Region, Then District, and then Territory. Each one of these attributes is its own column in the data so each row in the data has this hierarchy predefined. I have been live with this method for 6 months and have had no issues at all.
Problem:
The rollup references the alignment code (for a row it would be something like H0000, then H20000, then H22000, and finally H22A01) and I have now been asked to display the alignment name (East Region, Boston Territory, etc..) instead of the code. I cannot get this to work. I've tried every variation I can think of with the "NameColumn" and "ValueColumn" source properties and I just can't seem to get it to work. I can get it to display the alignment name but then my hierarchy completes breaks down and no longer works.
It seems to me that I should be able to say something like, use this column for the hierarchy rollup but display this value instead.
If anyone has any ideas at all it would be greatly appreciated.
Robert
-
Hey Robert,
When you say the rollup is influenced by the members in this hierarchy, are you referring to an MDX Script in the calculation of the cube? (e.g. a SCPE statement that rolls up Gross Profit == Revenue - Costs). If so, it sounds like a disconnect between your uniquenames, display names and the script. Nearly every cube we build uses a 'clean' display name, a different unique name (and id) and then MDX script referenced the uniquename/id for modifying the rollup.
Steve.
-
Thanks for the reply.
I actually didn't have to write an MDX script. I was lucky enough that SQL was able to figure out the roll-ups on it's own based upon my hierarchies. I plugged them in, went to the browser and voila! everything worked!
Based upon what I've read your statement seems pretty typical, if you write it in MDX then everything is great. I'm wondering if that is the reason I am having problems now.... Perhaps there are limitations by not writing the MDX script.?.? I am an SSAS newbie so MDX scares me... I wrote a few calculations in it for my cube and it took me forever to get the language write.
My hope is that I can stay the course and not have to write an MDX script and that there is some property on the dimension that can be used (name/value).
-
Hey Robert,
I think we've got a nomenclature disconnect 😉 It sounds like you don't have any custom rollups, just standard 'sum' for everything, where Territory rolls in to District rolls in to Region rolls in to Corp.
There are a few things you need to check. First up, you should definitely be able to use columnA for the ID and columnB for the name. You should set these as you want/need them for each attribute. You then need to check your attribute relationships, this is still done in the Dimension editor.
If you're using 05 then the way to read this is, as I expand (say) District, then 'under' District is Region (so region is the 'parent' of district). When you expand the Region attribute you'll see Corp as it's the parent of Region. If all of the relationship elements are still under your key, you can simply click and drag the appropriate ones to where they should belong. If you're using 08, you have hte new tab that makes setting these up a little more intuitive.
Next look at the user hierarchy you created (the center pane). If it has a ! or someother type of warning sign, then you have not got your attribute relationships set up appropriately. If you have changed the key for any of the attributes that are used in the user hierarchy, i would delete the hierarchy and recreate it using the attributes as you have now set them up.
To summarize, you can definitely set the name and ID for each attribute to be sourced from different fields. You need to ensure your attribute relationships are correct. Then you can create as many user hierarchies as you like/need from the attributes you've set up.
HTH,
steve.
Steve.
-
Nomenclature... YEP.
I think I am starting to follow you here but, based upon your example, I don't see where I tie in the "display" value. Corp, Terr, Dist all have values that I would like to replace with something else, the "Name Column". Where do I drag that in??
I should probably have posted in the newbie forum, sorry.
I've attached a screenshot of my alignment dimension. You will see my hierarchy (Corp, Terr, Distr, Code). There is another column on the left called Alignment_Name, that is the values that I want to display.
live anywhere near Dallas/Fort Worth TX? Lunch on me if so!
-
Hey Robert,
Everyones a newbie sometime & at something 🙂 (is that a song?)
ok, glad you posted the image, makes it a lot easier to talk around.
First thing, let's get the name/ID thing sorted. Left click (ie select) the Attribute 'Alignment Code' (first in list on LH pane). In the Properties pane (if not visible, usually bottom RHS, look on your toolbar for the button icon of a hand holding like a lirbary catalog card and click it). In that properties pane, depending on your sort order of props, you will see (in default sort) near the bottom an entry for Key and another for Name. Name is likely empty right now. If you select the field and click the ellipses button (...) this will fire a dialog that you can use to select the 'Alignment_Name' column. So now, your 'Alignment Code' attribute should have a key field sourced from field 'Alignment_Code' and the name from 'Alignment_Name'. You should be able to do the rest exactly the same way. Note, this means you *don't* need the attribute 'Alignment Name' any longer, and it can be deleted from your attributes (LH pane). You can also rename the attributes, so the Attribute named 'Alignment Code' (which has the code as it's key and the name as it's name) could be named 'Alignment Stuff' and it will still be valid.
For the user hierarchy, see the ! in a triangle, (top LHS of hierarchy in center pane)? This is telling you your attribute relationships aren't right. If you expand the key attribute (Dim Alignment), you'll see (amongst many others 🙂 ) Alignment Corp, District and Region. These items (the ones *under* the key attribute) need to be dragged to the appropriate location. Keep in mind (that to me) it's backwards, so you're dragging the 'parent' and placing it 'under' the child. So as mentioned before, the node (again this is the node *under* the key, not the one standing alone under the dimension ie the attribute itself) for Alignment Corp needs to be dragged under the Attribute Alignment Terr. and so on.
Hope this makes sense :hehe:
btw, and 4 hrs south of you down in Htown.
steve.
Steve.
-
This takes me back to pretty much the same problem i had before - although now the explanation (!) point is gone.
What happens after I finished this is that the names are indeed displayed, however the hierarchy (or rollup in my mind) is broken. Things are no longer in the right order that they were before.
I really appreciate your help. If you wouldn't mind glancing at these screenshots to see if you can tell what the problem is....
BTW - I get down to Htown every so often, bro lives down there...
Here's some screenshots:
This is how my hierarchy looks before I try to display the name:
These are the changes I made:
This is how my hierarchy looks now (it's not correct, not even close):
-
Additional Info -
It only breaks after I add "algnment_name" to the Source name property of the attribute. If I do not add this property then the hierarchy runs just fine.
To make certain that I am using the right property, here is one more screen shot that shows the property value that I am modifying :
-
It looks like your trying to use the same attribute as the name for each of your four hierarchy levels. You're going to need a separate attribute for the name of each of your hierarchy levels.
Drew
J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA -
GOTCHA!
That is exactly what I am doing but I didn't think it was an issue since it was just a lookup value, not used as the key column.
This will add some complications on either the front end app or on the SSIS package I use to load the cube but I'll save that for a later date... COMING SOON to a Forum Near You, Newbie needs help loading cube via SSIS!
I sincerely want to thank both of you for your assistance, I have spent the better part of this week trying to figure this out. Maybe another day/time you'll need help on how to make the perfect margharita and I'll be able to bail you out! speaking of margharita's, have a great weekend.
I'll update this post once I have made all the changes and the cube is working as expected.
-
still no luck.
I created individual attributes for each of the hierarchies levels. Everything works great until I modify the NAME property for the attributes that appear in the hierarchy. As soon as this property is modified the "roll-up" displays all the wrong information. It's showing corporate information at the rep level and district info at the territory level, it's all so goofy at that point. There is nothing obvious about what is wrong, it's just all mumbo-jumboed together.
As soon as I purge the NAME properties all is good again.
Could it be some other field in my attributes properties? They are all just set to the default, I didn't modify anything.
If any screen shots would help please let me know and I'll add them.
Thanks!
-
Hey Bob,
Just to confirm, in the datalayer (ie table or view) do you have a seperate column for each name you want to assign to a key?
So where you have columns right now for Alignment Corp *code* , Alignment Terr *code* and Alignment Dsitrict *code* , you need columns also for Alignment Corp *name* , Alignment Terr *name* and Alignment District *name*. You can work from either, but assuming you start with 'code' fields, then you drag the 'XYZ code' field in as an attribute, then change the Name property to use the appropriate XYZ Name field from the table/view. You do this for all of them. (note, you don't *have* to do this but generally it's good to use like an integer field as a key but as this presentsbadly, you often use a string field for the display name).
Lastly, one thing a client of ours got caught on recently - they had ID and Name fields, and set up attributes with the ID as ID and Name as Name, all good. *But* they didn't have uniqueness in the keys (or names), so for e.g. you could have a year field, quarter and month fields. Say you have only 2 rows and the values are (in yr, qtr and mth order)
fiscal Yr fisqtr FisMonth
FY2009 Qtr1 Jan
FY2010 Qtr1 Jan
while this *looks* ok to us, in SSAS this can cause issues as Qtr1 and Jan are the same value, trying to be loaded under different parents (FY09 and 10).
hth,
steve.
Steve.
-
Thanks for the reply Steve.
Yes, in the datalayer I have a separate column for each attribute's name lookup, Alignment_Corp has a corresponding Disp_Align_Corp and so on for the other three alignment values, and the Disp Value has been set as the Name property for the ID field. I do not use integers b/c that is not the naming schema our corporation uses.
Also, these values are unique (in the alignment dimension). Although the "MembersNameUnique" property is set to false (I just accepted defaults). This makes me wonder if it isn't a Property that is set incorrectly which is causing my issues. As the king of the screenshot, see below:
-
Hi Robert,
Quick qn - when you do an Explore Data on the source table for the dimension, does it return the data as you'd expect? everything looks ok there?
Steve.
-
yes - the data looks good when I do Explore Data. Looks just like it does in the Underlying sql table.
What is happening is that everything is perfect until i apply the name property. Once it is applied, everything goes haywire. There is still a hierarchy, but it is no where near correct. Everything gets all mingled, districts - territories - and regions all get mixed up and appear anywhere in the data.
I'm going to try changing that Unique property to see if it does anything but I am not holding my breath.
Viewing 15 posts - 1 through 15 (of 15 total)
You must be logged in to reply to this topic. Login to reply