Why CurrentMember Can Be Used Only On Hierarchies

I used to wonder why the MDX function CurrentMember applies only to hierarchies and not to levels.  For example, the Members function can be used on either a hierarchy or a level, and returns the appropriate set of members.  I thought it would make sense if CurrentMember worked in a similar way.

Today while reading Chapter 10 - MDX Concepts of the book Microsoft SQL Server 2008 Analysis Services Unleashed, it suddenly hit me.  I realised why CurrentMember should only be applied to hierarchies and not to levels.

The book was explaining the concept of Execution Context and how SSAS works out the current coordinates for a query.  The SSAS engine starts by setting the current coordinates to the default member of each attribute hierarchy.  In most cases this would be the All member.  Then it takes the members that have been specified in the query and overwrites the appropriate attributes.  However, the coordinates for those attributes not specified in the query will remain at their default member.  Therefore in most cases, these attributes' CurrentMember is still the All member.


So that's why the CurrentMember function can be applied only to hierarchies and not to a specific level: because it needs to be able to return any member from the whole hierarchy, including the All member.  Applying the function to a specific level could return invalid results.


Rethinking about this, it all seems be a bit obvious now, but I guess it never clicked for me before.  So I am happy to share it in case you have wondered about the same thing.

Date Filters in Excel's Pivot Table

When I browse OLAP cubes using Excel 2007 and put date dimensions in the column or row area, some of them have a Date filter, while others only have a normal Label filter.

I was curious about what makes Excel recognise the dimension as a date dimension and thus makes the Date filter available. So I went about googling and found this post in the MSDN forum: http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=2174795&SiteID=1. Essentially, setting the ValueColumn of the date dimension's key field to a date type column will help Excel recognise the dimension as a date dimension.

I decided to experiment a bit more with a few different but common combinations of Key, Name and Value columns, to see their effects in Excel. Here are the results:

KeyColumn	NameColumn	ValueColumn	Date Filter?
Date	Date	(none)	Yes
Date	Char	(none)	No (*)
Date	Char	Date	Yes
Int	Int	(none)	No
Int	Char	(none)	No
Int	Char	Date	Yes

(*) Note: in this combination, if you also set the attribute's AttributeHierarchyVisible to false, then Excel makes date filter available for the dimension. However, it seem the filtering doesn't actually work correctly. For example, even if you apply a filter of "after 31/12/9999", every member is still coming through unfiltered.

Analysis Services Security with Richard Lees

Tonight I attended this month's SQL Server user group session in Sydney on the topic of Analysis Services Security. The presenter was Richard Lees, the managing director of EasternMining.

Initially there was a bit of problem with the projector and the presentation got delayed. But they managed to source another projector so that the presentation could start.

Richard started with a few slides followed by plenty of demos. He ran through the standard security options in SSAS. Then he moved on to explaining how you could utilise the security features in interesting ways. Here are some bullet points:

• All SSAS connections use Windows authentication - unless RequireClientAuthentication is set to 0.
• SSAS service account needs to be able to read the data source.
• SSAS security has several levels of granularity: system (OLAP Administrators), database (Administrators), data source, cube, dimension and cell.
  

One of the interesting ways to utilise security was what he called 'Functionality by Security'. This is by giving user the right amount of access to the data in the cube. Then, with the report authored to fit that security model, it could show different results to different people.

For example, a data warehouse may contain all the data from different stores of a franchise chain. You could have a dimension with the different store IDs, and link the store IDs to the store owners' logins in some way. In the cube you can use a filter to restrict the access based on the login/store ID. Then just one version of a report could show the data appropriate just to that store owner.

One interesting demo he showed was that he's been collecting the various data points using PerfMon on his machine. He then used SSAS and ThinSlicer to drill into the various performance characteristics of his machine. We could see SQL Server was the program using up most CPU, with another program call 'Gw' at the second spot. He explained that 'Gw' is a game called Guild Wars, which his son has been playing quite a lot. He showed that he could even drill down into the process's details to see what time his son has been playing.

At the end of the presentation, he said that building cubes without knowing MDX is like creating SQL Server tables without knowing T-SQL. You can certainly do it, but having MDX knowledge will broaden your understanding that much more. He went on to recommended a couple of MDX books:

• Fast Track to MDX by Mark Whitehorn, Robert Zare and Mosha Pasumansky. This is an introductory book to MDX. It is great for people just starting out on the language and explains clearly various concepts for beginners.

• MDX Solutions: With Microsoft SQL Server Analysis Services 2005 and Hyperion Essbase by five authors. This is a more advanced book and goes into greater depth on MDX. Some would consider this the MDX bible.