Thursday, November 29, 2007

BIDSHelper 1.2 Release is Out

Recently BIDSHelper 1.2 was released. I just now had a chance to find out and download it. You can download it here.

I found out about BIDSHelper from Chris Webb's blog several months ago and have found it to be very, very useful. I use the Deploy MDX, Edit Aggregations, and Visualize Attribute Lattice functionality quite a bit. For those not familiar with it, I've met several people who aren't, it's a community project BIDS Add-In. Even more, Katmai appears to have some functionality similiar to some of the features found in BIDSHelper.

Monday, November 12, 2007

IBM is buying Cognos

For those that haven't heard yet, IBM is acquiring Cognos, who had been one of the largest remaining independent companies in the BI software space.

Thursday, November 8, 2007

Microsoft BI Conference Resources

Back in May I had the opportunity to attend Microsoft's first BI Conference. It was a blast! I was new (2 months) into the BI field, so it was a great chance to network with some top level BI people. Of course, I first had to find out who they were. I was that new to BI. I didn't even know who Mosha was! If you don't know who Mosha is, no worries, he's in my blog roll along with some other great bloggers. A few of the people I met have been ever-so-nice to provide mentoring over the past few months. Greg Galloway has especially been nice in his help.

For those that didn't have the opportunity to attend the conference, Microsoft has several resources available for free, including videos from 3 of the keynote presentations. If you're interested in learning more about PerformancePoint server, the Jeff Raikes keynote from the first day has a 10 minute demo that starts around minute 23.

There is also an event DVD available for purchase. However, the price is 100 to 300 USD, last I checked, depending upon if anyone from your company attended the event. Hopefully, Microsoft considered the event a success and we'll all be able to meet again next year!

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.