July 29, 2013 at 7:53 am
SSAS 2008 R2
Excel 2010
In SSQL Management Studio connected to an SSAS instance i am able to browse a cube. I can drag a single level of a user defined hierarchy onto the active area and have only that level display and see the data slice by only that level.
If I browse the same cube via an Excel 2010 pivot table and drag that same level I get the whole hierarchy not just the level I dragged.
Is this by design? Can I prevent this behavior? If not do I have to set "AttributeHierarchyVisible" = true to allow this?
Thanks
Jonathan
July 30, 2013 at 6:35 am
I don't know if it's by design, but what you describe is the expected behavior in Excel. If you want to be able to bring in the attributes individually, you need to set AttributeHierarchyVisible to True, as you indicate. Some people don't think this is a good idea. I don't agree with them, but thought I'd point that out and let someone who takes the position tell you why.
July 30, 2013 at 7:16 am
Thanks for your reply. I had come to the conclusion that this was the case and set the visibility attribute to true. I created some of these user defined hierarchies and attribute relationships mostly because I felt I should for cube performance. Not because any user was calling for them. I wish you could create the hierarchies and relationships for performance but make them invisible to the user. Just the opposite of the actual choices.
July 30, 2013 at 7:27 am
Not sure what you mean by you wish you could set the relationships for performace but make them invisible. You can set the attribute relationships to each other to increase the efficiency. By default all attributes are tied to the key value. This can and where possible (most of the time it is possible) be modified so that the SSAS engine can aggregate better. If you're using 2005, you'll find them under the attribute themselves. This is a little tricky and considering it's importance a little tucked away. There's a relationship tab in 2008 R2. Can't speak to 2008, but it's probably the same as R2.
I don't know why you would create a user heirarchy and yet have it be invisible. Creating one doesn't tell SSAS anything about the relationship. The items I outlined above do that.
July 30, 2013 at 7:40 am
Here is my confusion. If I want to tell the SSAS engine that businesses are in states to maximize the drill down of business by state do I have to have a user defined hierarchy called maybe "Geography" that has a state level and a business level with the appropriate attribute relationship set? Or is it possible to set the attribute relationships without creating "Geography"?
I hope this makes sense!
July 30, 2013 at 8:26 am
It is not only possible to set an attribute relationship without creating a user heirarchy, but creating a user heirarchy does nothing towards telling the SSAS engine about the relationships.
July 30, 2013 at 8:48 am
Thanks again. I think what had me confused is how the relationships are displayed in the designer. If I want to use "BusinessName" in more than one attribute relationship, lets say AccountManager -> BusinessName and StateOrProvince -> BusinessName, without user defined hierarchies for each the designer just lists both AccountManager and StateOrProvince under BusinessName. With user defined hierarchies there are two separate paths to BusinessName, one through AccountManager and one through StateOrProvince.
Is this just a display difference or is there a real difference?
July 30, 2013 at 8:59 am
You'll have to figure out your own relationships as I don't know your data. However, if there are two true relationships, then whether they intersect at some point or have the same start and end point but diverge in the middle, you will be able to create the appropriate relationship. In a time dimension where 2 hours, 3 hours, 4 hours, and 6 hours are time increments in the table, 2 and 3 relate directly to the key. The 2 relates to the 4 and the 6. The 3 relates to the 6. Hopefully this gives you enough to go on.
July 31, 2013 at 7:42 am
Please see the attached screenshots. They show the attribute relationship designer. The "With" is with user hierarchies defined, the "Without" is with them deleted. Does the SSAS engine view these as the same relationships?
Thanks for you help!
July 31, 2013 at 7:54 am
I don't know enough about the internals to be sure of the answer. If the relationship had been something other than both joining directly to organization name, I could have easily said that they are viewed differently. But in both cases, the state and account manager will be seen as related attributes.
As it is, though, these represent different relationships. The one with the actual relationship tells me that each state and each account manager have organizations that role up to them. The other one tells me that organizations is the top of the heirarchy, but each organization had an individual property of a state and an account manager. I can't make a determination w/o looking at the data. That is a call you make based on the data and discussions with your business users.
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply