Search code examples
ssasmdx

Understanding the Cube display in SQL Studio Manager

I have built a small data warehouse using the Adventure works database. I have deployed it to SQL Studio Manager. I have written my first MDX query

select 
customer.[full name].members on rows,
order (measures.[sales amount],asc) on columns
from [Adventure Works DW2012]

Please see the screenshot below:

enter image description here

I understand that the top level of the hierarchy are dimensions i.e. Customer, Date, Due Date, Interne Sales, Order Date, Product and Ship Date. I understand that dimensions have attributes. For example: Model Name, Product Line, Product Name are attributes of the Product dimension and Product Model Lines is a hierarchy of the Product dimension.

What is meant by: Financial; History and Stocking?

Solution

  • You've come up against something I think is a genuinely confusing and ill-designed aspect of SSAS.

    You're correct that Model Name, Product Line and Product Name are attributes of the Product dimension. But what you're seeing here (in your screenshot) is hierarchies called Model Name, Product Line and Product Name.

    These are not "hierarchies" in the sense that most people use the term (a structure with more than one level). They're the "attribute hierarchies" based on the attributes of the same name. They only have one level two levels. (EDIT: as whytheq pointed out, they have one leaf level, and almost always also have an "All" level).

    (EDIT) Product Model Lines is a "real" (aka "user") hierarchy, with multiple levels apart from the All and leaf levels, based on multiple attributes.

    Financial, History and Stocking are "folders". They get "created" by the setting of any AttributeHierarchyDisplayFolder property of any Attribute in the Dimension design (or the DisplayFolder property of any "real" hierarchy). They have nothing to do with any dimension structure - they're just for display convenience. Probably necessary because, as becomes clearer the more I try to explain it, the structure of Dimensions in SSAS is really unnecessarily complicated.

    You can hide the "attribute hierarchies" from client applications (e.g. Excel) by setting the AttributeHierarchyVisible property of the attribute to False. But they'll still show up in the MDX "helper" screen you're looking at.