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.

Windows 7's Boot from VHD - Transferring the VHD to a New Hard Drive

You might be aware that Windows 7 and Windows Server 2008 R2 are capable of booting from a VHD.  The advantages of this set up include ease of backing up the whole O/S partition and the ability to dual-boot (or multi-boot) without the need to create multiple partitions.

If you search for terms such as "Windows 7 boot VHD", you will get lots of hits on how to install a new instance of the O/S using this feature.  But what if you have already set up such a booting arrangement, and now wants to move the VHD from one drive to another?  For example if you have bought a new faster hard drive and want to transfer over the VHD.  I haven't found any post describing how to do this.

One way to do this would be to run through the O/S install process again on the new hard drive, creating a brand new VHD boot.  Then just copy your existing VHD onto the new hard drive, overwriting the new VHD you just created.  However, this meant going through the whole install process again, and I wanted to find if there is a faster way to do this.  After some experiments and some searches on the web, the following steps worked for me (assuming you have connected up the new hard drive, created a primary partition and formatted it):

1. Boot up your machine in Windows 7 install DVD.
2. Press shift-F10 to bring up the command prompt.
3. Copy the VHD to the new hard drive.  (You could do this beforehand if you have another O/S you could boot up in, so that the VHD could be copied.)
4. Power down and disconnect your old hard drive.  This step is optional, but it avoid any confusion between new and old drives.  Then repeat steps 1 and 2 to get back to the command prompt.
5. Run diskpart, then enter these commands (where C:\Win7.vhd is the location of your VHD on the new hard drive):
• select vdisk file=C:\Win7.vhd
• attach vdisk
• exit
6. From the command prompt, enter:
• bootsect /nt60 C: /mbr
7. From the command prompt, enter (where E: is the drive letter of the attached VHD):
• bcdboot E:\Windows /s C:
8. Exist the command prompt
9. Go back and follow the main Windows 7 install screens.  On the 2nd screen, click the "Repair your computer" link.
10. On System Recovery Options screen, choose "Use recovery tools" then click Next.
11. Choose the "Startup Repair" option.
12. After it has done its work, click the Finish button.  It will reboot.

Now the machine should boot up in your original VHD, running on your new hard drive.

Some additional notes:

• Instead of pressing shift-F10 (or if you don't remember which keys to press), you can also go through the "Repair your computer" option (steps 9-10) to get to the option to bring up the command prompt.
• These steps assumes you have formatted your new hard drive while running in Windows.  That would be easier and safer.  If you consider yourself hardcore, you can do the formatting from within diskpart in step 5.  But be very careful, because if you select the wrong disk, you would wipe out your existing hard drive.
• If you are running on a physical partition, you can convert it to booting from VHD.  Firstly, use Disk2vhd to copy your existing partition to a VHD.  Then follow the steps similar to above to set up booting from the VHD.

Excel Services: I Ticked "Trust Children," Why Doesn't It Work?

At work we have a BI system based on Microsoft technologies: SharePoint 2007/Excel Services as the front end and SQL Analysis Services as the back end.

A couple of colleges did most of the setup for the SharePoint server, including Excel Services. Based on their experience, they have always advised others that the "Trust Children" setting in Trusted File Locations did not work. Therefore, we have always been creating an individual entry for each document library we want to use with Excel Services.

Earlier this year we were setting up a QA environment for the BI system. I had the chance to test the Excel Services settings for myself. What I found was that the setting to "Trust Children" does work, it just doesn't always work right away. Sometimes it might take up to 30 seconds or so before it would take effect.

I guess this short delay explains why they thought the setting didn't work. But at least now we no longer need to create all those entries in Trusted File Locations.

Note: for security best practice, you should still consider carefully whether to enable this setting based on your deployment scenario. Refer to the guidelines in this article.

Running SSIS 2005 Packages Under SSIS 2008

Today I tried to run some SSIS 2005 packages on a machine with only SSIS 2008. I used dtexec.exe from the command line to run them. As it was starting with the first one, I could see some messages about the provider reference being changed from SQLNCLI.1 to SQLNCLI10.

This was quite normal, I thought, as SSIS 2008 was just performing on-the-fly upgrade of the packages to 2008 format before running it. However, as the actual execution started, it failed with an error about SQLNCLI.1 not being registered.

That was odd. Didn't SSIS (dtexec.exe) just tell me that it had upgrade the SQLNCLI.1 references to SQLNCLI10?

After a bit of head-scratching and fiddling, I realised that the problems was in my configuration files. I am using indirect XML configurations for these packages. The configuration files had the complete connection strings, including the Provider, which still referred to SQLNCLI.1.

Once I updated the configuration files to use SQLNCLI10.1, all the packages ran without problems.

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.

Silverlight with SharePoint

Starting from today I am attending a four day MOSS 2007 Boot Camp. The instructor is Mick Badran of Breeze Training. He was pretty excited about Silverlight and encouraged us, the students, to start learning it and utilising it together with SharePoint.

He said deploying Silverlight on SharePoint will bring great benefits to the user experience aspect of a site. Instead of transferring a web page of several hundred kilo bytes each time, Silverlight can make the interaction much smoother by transferring a smaller chunk of data. And even though AJAX can also achieve similar reductions in network traffic, for a .NET developer, coding in Silverlight will be much easier than JavaScript.

I was already quite interested in learning Silverlight. After hearing him extolling its benefit, I am even more excited to deep dive into this technology.

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.