Microsoft Analysis Services 2000 Tips

The answer is, it depends. Shared dimensions are in effect "conformed" dimensions that can be shared across the organization. In most cases, you will want to create dimensions as shared rather than private. Shared dimensions across cubes allow for low maintenance and conformity. 

One reason why people use private dimensions is to decrease the reprocessing of numerous cubes. (If a full reprocess is performed on the shared dimension the data is deleted in all of the cubes that share that dimension.) 

Example: If a Shared Time Dimension is included in 4 cubes, when the time dimension is fully reprocessed, all 4 cubes would lose data and have to be re-processed. This is not the case with an "incremental" processing of the shared dimensions. Incremental processing will ADD new members, but not re-organize them. As an example, if Salesperson A goes from the West Region to the East Region, an incremental processing would not make the change, but a full reprocessing would.

Here are some general guidelines:

Only use Virtual Dimension with dimensions that have a small amount of members.  Virtual Dimensions are 100% calculated on retrieval, so if you create a virtual dimension that contains 5,000 members your retrieval times will be impacted.  (Or if you create a virtual dimension that is based on a regular dimension which has over 100,000 members)

Do not use Virtual Dimensions if the value changes over time and you want to keep history.  An example could be a dimension called "Patient Status" that contains the members "Alive" and "Dead".  If the Patient Status dimension is created as a virtual dimension, then when the patient has the status of "Dead", they will have that status for all time periods.  (In other words, it will give you the latest snapshot)

Virtual dimension should be used when the member attributes do not change over time and when there are not many choices among the attributes.  A good example would be "Sex".  People are either M or F and it doesn't change over time. 

Named Sets are customized "lists" of members that can be either fixed or dynamic.  Most end-user analysis tools, such as ProClarity or NovaView, expose server side named sets so the organization can use the sets across the enterprise.  Oftentimes named sets are useful when using the Time dimension.   Here is an example of a named set called "Last 3 Months".

Last 3 Months:  tail( Filter( Time.Month.Members, Not IsEmpty(Time.CurrentMember) ), 3 )

The above MDX is dynamic so that with each monthly update the "set" of members may change.

Parent / Child hierarchies are powerful in that they allow for nonsymmetrical dimensions.  But when users query parent / child dimensions it is difficult to easily grab all of the members at the bottom of each branch. 

An easy way to solve this is to create a named set on the server which filters the leaf level members.

Example: There is a  "Sales Rep" dimension which contains 5 parent / child levels.  The "salespeople" can be found on levels 3, 4 or 5.  The following MDX will grab all of the members from the "Sales Rep" dimension that are at the leaf level.

Salespeople Named Set: {FILTER([Sales Rep].members,IsLeaf([Sales Rep]))}

When using a front end OLAP tool you can query "levels" within a dimension.  One example would be to query all of the "stores" in a dimension.  When the query is displayed, the member names are displayed in the order of the hierarchy.  Oftentimes users will want the list sorted alphabetically.  You can easily create an Analysis Services Named Set on the server which accomplishes this task by sorting the members using a store member property.

Store list by alpha: ORDER ( {[Store].[Store Name].members} ,  strtovalue([Store].currentmember.properties("store_number")) , BASC)

In this example, "store_number" is a member property within the Store dimension.

Most Analysis Services client side tools are very "level" oriented.  They allow you to choose one level at a time.  But what if you want to show the entire dimension hierarchy?   A simple Analysis Services Named Set can provide your users with the entire hierarchy of a dimension.

Entire Store Hierarchy: [Store].members

You may want to show a hierarchy starting from the middle of a dimension.  The Descendants function with the SELF_AND_AFTER flag will do the trick. 

Example: The Store dimension has the following levels:

• Division

• Region

• District

• Store

To create a named set which includes the hiearchy starting from the region down to the stores, theMDX expression would be:

Region Hierarchy: DESCENDANTS ([Store].[All Store], [Store].[Region], SELF_AND_AFTER)

named set which resolves the "current month" is very useful when building reports because the report can reference the named set rather than an individual month, which would need to be continually changed.  Here is a piece of MDX code which returns the last month in the database which contains data.

Current Month = Tail( Filter( [Time].[Month].Members, Not IsEmpty([Time].CurrentMember)), 1)

Note: If you want to set the time dimension's "default member" to use the "current month" you will need to include .item(0) at the end of the statement to convert the set to an individual member.

If you create a calculated member which divides by null or zero, you will get a funny looking result like -1#I.ND%.  To remedy the problem, just check the denominator for zero before dividing.

Sales Average: IIF([Measures].[Sales Count] = 0, null, [Measures].[Store Sales]/[Measures].[Sales Count])

One of the properties of a calculated member is solve order. A calculated member with a solve order set to 20 will calculate AFTER a calculated member with a solve order set to 10. 

Hint: When setting the solve order property it is a good practice to do so in increments of 10. This insures that if you need to add a future calculation "in the middle" of the two that you have room to wiggle.

There are two things to check.  First, make sure that the field you are "counting" has a numeric data type.  Second, Analysis Services only allows ONE Distinct Count measure per cube, so check to see if there are any other measures tagged as Distinct Count within the cube. 

1. Distinct count measures should be put in their "own" cube.  Distinct count measures add a lot of overhead to an Analysis Services cube.  You will get MUCH better performance if you put the distinct count measures into their own cubes.  This will improve processing times and retrieval times.  You can either use "virtual cubes" to join the distinct count member back in with the rest of the "regular" measures or you can use the "LookupCube" MDX function.

2. Distinct count measures must be numeric.

3. Optimize Distinct Count Cubes for Performance - Because distinct count cubes usually take longer to process, it is important that the cube be "optimized" using numeric keys for each of the dimensions.