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.

http://www.businessweek.com/technology/content/nov2007/tc20071112_678294.htm?chan=top+news_top+news+index_businessweek+exclusives

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.

Tuesday, October 16, 2007

From where do my cubes get their data?

SQL Server Analysis Services 2005 cubes may get their data from a variety of sources, including the following:

SQL Server 7.0/2000/2005 (including SSAS 2005)
Oracle 9.0 (x86 only)
IBM DB2 8.1 - only available for SS 2005 Enterprise Edition or SS 2005 Developer
Access with Microsoft Jet 4.0 OLE DB provider (x86 only)
Teradata v2R6 (x86 only).

More details available here.

In addition to having the ability to obtain data from a variety of sources, SSAS 2005 has an abstraction layer. This abstraction layer was not available in earlier versions of SSAS. The abstraction layer is developed as a data source view. A data source view is a logical data model of one or more data sources. Some of the items that may be included in a data source view include tables, named queries, relationships, and named calculations. Even more, the data source view may contain items originating from multiple data sources.

More details available here.

Tuesday, October 9, 2007

Getting Started with SSAS 2005

Earlier this year, I had the opportunity to take on a BI Developer position. In this position I have done quite a bit of development with SQL Server Analysis Services (SSAS 2005).

Before taking on this position I had 8 years programming experience with a variety of languages, mostly OO. In all those cases, the systems used SQL Server as the backend. Thus, I gained 8 years solid SQL Server T-SQL development experience, plus some database administration experience. I've worked with SQL Server 6.5, 7.0, 2000, and 2005 from the RDBMS side.

This year, while networking with other BI folks, I discovered one of the biggest difficulties in taking on a BI Developer position can be lack of other resources available at your company for assistance. While companies with internal software developers usually have several of them, the amount of BI developers at a company can easily be 1 or 2. Thus, a need for networking and awareness of on-line resources became apparent.

The initial purpose of this blog is going to be assisting people, generally programmers or database architects in attaining knowledge needed to develop SSAS 2005 cubes. If you are already an intermediate or advanced SSAS 2005 developer, there are many intermediate/advanced blogs on my BI blogroll that have a greater chance of meeting your needs. If you are looking to break into the SSAS 2005 space, I hope to assist you in your learning experiences here.


As I blog, I plan to post resources that took me several months to find. Hopefully, they can save you quite a bit of ramp up time. As this blog is just now starting, please let me know ideas on how you would like to see it develop.