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.