Monday, November 5, 2007

Using SSAS 2005 with Fast Track to MDX

Update: Please see item 7 for how to create a VS2005 solution for the new SSAS 2005 database that this post explains how to create.

I have been having fun learning Multidimensional Expressions (MDX) lately. It is a query language for many OLAP databases, including SSAS 2005. Although it looks a lot like SQL (has Select, From, and Where syntax), I've learned that the logic behind it is very different from SQL. It has tuples, sets, etc. which are not in SQL.

At first I was having difficulty grasping core concepts, such as tuples and sets. I was attempting to use my Microsoft SQL Server 2005 Analysis Services book by SAMS publishing as my initial source of MDX knowledge. The book has 4 chapters on MDX. It's has been a great SSAS 2005 book for me, but it was not great at teaching MDX to an MDX newbie like me.

I researched various blog reviews and book seller reviews and heard a lot of buzz about the following two books:

Fast Track to MDX by Mosha Pasumansky, Mark Whitehorn, Rob Zare (ISBN 1-84628-174-1)

MDX-Solutions: With Microsoft SQL Server Analysis Services 2005 by George Spofford, Sivakumar Harinath, Chris Webb, Dylan Hai Huang, Francesco Civardi (ISBN ISBN 0-471-74808-0)

Fast Track to MDX seemed to have a better reputation for teaching beginning MDX, so I decided to purchase it. My motivation was also helped by the fact Mosha Pasumsky, one of the co-authors, actually invented MDX. His blog is in my blogroll.

The experience has been mostly great, but running the sample queries was initially an issue. The problem was that the sample SSAS database is for SSAS 2000. This was a problem for me, since I only have SSAS 2005 installed on my machine.

I spent an hour or two researching the issue and came up with the below way to use SSAS 2005 with Fast Track to MDX. It basically consists of using an upgraded Foodmart 2000 sample SSAS 2005 database that Darren Gosbell has on his blog and using the FoodMart 2005 relational database from my SAMS book. Instructions may be found below. I hope you can save some time through my experience.

1) Visit the Upgraded Foodmart 2000 sample database for Analysis Services 2005 blog posting by Darren Gosbell. He saw others having this issue and backed up the Foodmart 2000 project and upgraded it to 2005. The blog post has a link to two SSAS 2005 backup files. One is a processed database with data and one that is just the structure.

2) Create a new SSAS 2005 database and attempt to restore the "FoodMart 2000 with data.abf" file. If this works, then you are probably good to go. I received an error, so I went to the next step.

3) Restore "FoodMart 2000 structure only.abf" onto your new SSAS 2005 database. You'll then need a datasource for your new SSAS 2005 database which you should be able to obtain in step 4.

4) Create a FoodMart 2005 database on an instance of SQL Server 2005 database engine. Then dowload the version of the FoodMart 2005 database that SAMS Publishing uses for their book Microsoft SQL Server 2005 Analysis Services. Here is the current link for the book. Restore the foodmart2005_full.bak database over your new FoodMart 2005 database.

5) Open your SSAS 2005 database in Visual Studio 2005. Modify the FoodMart datasource to point to your new FoodMart 2005 database.

6) After saving your changes, process the Sales database. The Sales cube has worked for all the Fast Track to MDX queries that I have tried so far.

7) After processing the Sales cube, it should be available for MDX queries. If you would like to have a Visual Studio Analysis Services solution/project for the SSAS 2005 database, then within VS2005, go to File --> New --> Project. Then select an Import Analysis Services 9.0 Database as your project type. Once you click OK, you should be given an import wizard that will allow you to import your new AS cube into a solution.

I'm about half-way through the book right now and haven't had any problems using the cube created using the instructions above. If anyone else has a solution to using Fast Track to MDX sample queries against SSAS 2005, please let me know.


Anonymous said...

Kristin, Thanks for putting this 'how-to' together. I'm also working through the Fast Track to MDX book. Do you know of another source for the structure-only abf file? The site that Darren refers to no longer exists.


K. Ferrier said...
This comment has been removed by the author.
K. Ferrier said...

I'm guessing the site where Darren posted the code must have been down for a short period. It appears to be back up and running again. Please let me know if it works.


Anonymous said...

Very useful!

Thanks a lot

Anonymous said...

Did you get this error in the cube when you restored it. I did it both ways and had the same result when I re processed the database.

Errors in the OLAP storage engine: The linked measure group with the ID of 'Sales', Name of 'Sales' cannot be processed because it contains MG dimension with the ID of 'Time', Name of 'Time' with different granularity attribute than its source object.

K. Ferrier said...

Which cube were you reprocessing?

Anonymous said...

Hi Kristin, what AS solution did you process for Sales cube? I cant find it.


K. Ferrier said...

Cris, I used the "OLAP examples for Foodmart2005" mentioned in bullet number four. There is a link to it in that bullet point. If you need additional, information, please let me know.

FYI...When you get to webpage the link points to, you need to scroll to the bottom of the page, and click on the Downloads tab.

K. Ferrier said...

Cris, I misspoke. I have updated my post with an explanation as to how to create a VS2005 solution for the new SSAS 2005 database that my posting explains how to create. It is in item 7. Also, from the book site, I use the database samples, not the OLAP, which is what I had said in my previous comment.

Anonymous said...

Kristin, I am attempting to follow your instructions and I am getting an error when I try to restore the database downloaded from the Informit web site. The error message is the backup set holds a backup of a database other than the existing FoodMart 2005 database. Any chance you could provie a backup of yours since you were successful? Thanks!

Anonymous said...

Thanks for this post. Most helpful!!

Anonymous said...

Thanks for writing this.

Will said...

Love your blog! I've been doing a lot of research into business intelligence and working with business intelligence consultants and this has really helped. Thank you!