Mar 31, 2010

All Member Formula

Dimensions in Analysis Services 2000 have property that defines value of All member:

All Member Formula = [Reporting Period].[All reporting Period].FirstChild

To convert it to Analysis Services 2005 or Analysis Services 2008 you will need to set it as calculation on cube designer:

CALCULATE;

[Reporting Period].[Reporting Period].[All Reporting Period] = [Reporting Period].[Reporting Period].[All Reporting Period].FirstChild

What is default member of dimension

Simply put the name of the dimension in the select statement:

select [Product Completion Date] on 0

from [SBCD New Business]

You will get a result set with default member of the dimension and default measure.

MDX Error: "Permission to Access the Referenced Mining Model"

If you get an error like the following back from SSAS server:

Either the user, DEV\Smith, does not have permission to access the referenced mining model, ABCD New Business, or the object does not exist.

This occurs when you omit to specify axis of your MDX query:

select [Product Completion Date] - -on 0

from [SBCD New Business]

Mar 9, 2010

Dimension Grouping and Ordering in SSAS

Relatively complex example of how to set properties of dimension attributes and hierarchies to get a time dimension that has groupings and sorting.

Scenario

We will use following dimension and hierarchy to demonstrate in this text:

clip_image002

It is based on the following table:

clip_image004

We want to create a complex hierarchy that is ordered using descending order:

clip_image006

Sorting sequence will be used to define order of members.

Ordering by Attribute

You probably know that an attribute in SSAS dimension can be sorted by Key and Name. That is controlled using OrderBy property of the attribute.

You can also sort it using another attribute. You fist have to specify the attribute using OrderByAttribute property and then to specify in OrderBy to be sorted by AttributeKey or AttributeName.

For sorting attribute to appear in the OrderByAttribute list, it must have attribute relationship with the attribute.

Attribute Relationships

The following picture shows how you can set attribute relationships between attributes in a hierarchy. This can be used in both SQL Server 2005 and 2008.

Most people have a trouble to visualize relationships between attributes. Should Month Alias be under Quarter or Quarter under Month Alias? The way I remember this is to say to myself sentence about aggregating. Something like Months aggregate (or roll up or are part) of Quarter. And Quarters are part of Year.

clip_image008

In SQL Server 2008 there is now a new interface that is easier to use. It will visuale manage relation like:

Month ----> Quarter ------> Year

To be able to use sorting attribute it needs to be included in attribute relationship. The following picture shows one way to do this:

clip_image010

Sort Seq will take place in relationship between Moth and Quarter

Month ----> Sort Seq ----> Quarter ------> Year

 

Combining Grouping & Sorting

Since Quarters and Years also have to be sorted in Descending order, you could use two more instances of Sort Seq attribute to order them. Just include them in attribute relationship:

Month ----> Sort Seq ----> Quarter ------> Sort Seq 1 ----> Year ----> Sort Seq 2

clip_image012

Introduction

I am a consultant that works on SQL Server and Analysis Services in Toronto. I would like to share some of my experiences.

Articles will be focused on Analysis Services 2005 and Analysis Services 2008.

I would like to get feedback from readers, so please contact me or leave comment.