Hi

I really have been racking my brains about this for weeks, so please don't skim-read.

I'm dealing with a deeply weird cube (inherited from someone who's no longer available).  What's weird about it is that many dimensions don't have unique members.  Instead, the dimension source data has duplicates at the leaf level: it's left up to SSAS to aggregate up to the actual leaf level used in hierarchies.

I don't know whether I'm crazy to find this deeply weird.  Every cube I've worked on in the past, a dimension is clearly defined in the source data, with uniqueness already present there: we don't make a dimension out of duplicated, sort of facty data.  This kind of design seems as weird to me as an unnormalised SQL database.

Here's an example to illustrate what I mean; I'll use that Adventureworks database everyone knows.

We have a Geography dimension with a Geography hierarchy.  Levels go like this from top to bottom:

Country

State-Province

City

Postcode

The Geography dimension has a key attribute called Geography Key.  It's there in the cube design as a dimension attribute, but it's not in any of the hierarchies, so I can't query it in MDX.  But that's fine: it has the same cardinality as the lowest level (Postal Code), because the dimension has some kind of normal design.

In the cube I'm dealing with, it's all messed up.  Using the AdventureWorks example above as a parallel, someone made a Geography dimension with source data keyed on [PostalCode, ExactAddress], but only wanted the dimension granularity to be PostalCode.

This makes it very hard to debug why the data in this dimension is incorrect.  I can't match up the dimension members in the cube to the source data, because the dimension doesn't actually go down to the real leaf level!

So I have a dimension attribute called ExactAddressKey, but I can't query on it in MDX, because it's not part of any dimension hierarchy.  Unfortunately changing any part of this cube design is not possible, so I can't even experiment with settings and see what happens.

Does anyone have any idea how I could get to the leaf level of the data imported?  Something like

Geography.Geography.TheInvisibleLeafLevel.Members.Properties('Key')

Or does this kind of dimension design result in SSAS discarding all the data that's more granular than the most granular attribute defined in any hierarchy - so that the data actually isn't there to be queried?

I'd also really appreciate peoples' views on this kind of dimension design.

thanks!

Hi,

If a attribute is Invisible u can already query it via MDX. Just go to the solution and look how the attribute is named.

like Geography.InvisibleAttribute.MEMBER_UNIQUE_NAME.

if u want only the key value, check whether a composite key is defined (two or more columns for the KeyColumn. If so u have to access it this way:Geography.InvisibleAttribute.Properties("Key0")

look at: https://msdn.microsoft.com/en-us/library/ms145528.aspx

Kr Jrgen