SQL Server Analysis Services 2005/2008 articles,blogs,scripts,FAQs,tools,books

Link to source

This weekend I had some time to play with SQL Server 2008 (Katmai) June CTP, and I am posting here my first impressions (focus on Analysis Services).

Installation

As a first attempt I tried to setup SQL Server 2008 on the computer that already had SQL Server 2005 default instance installed. I wanted to install SQL 2008 as a named instance and compare SQL 2005 and 2008 side by side. After answering few standard questions I got message that because SQL Server 2005 was found on the machine, SQL Server 2008 cannot install client tools. I proceed with installation without client tools. After installation I tried to use SQL Server 2005 BIDS environment and connect to Katmai instance, but somehow that did not work. Then finally I decided that maybe I should read readmesqlkatmai.htm file. Found that side by side installation is not supported. From readme file:

• Side-by-side tools are not supported.
  Presence of SQL Server 2005 Management Tools or BI Development Studio will block installation of SQL Server “Katmai” Management Tools and BI Development Studio.
• Side-by-side installation together with SQL Server 2000 not supported. An instance of SQL Server 2000, including MSDE, will block installation of this release of SQL Server “Katmai”.
• Side-by-side installation together with default instance of SQL Server 2005 Analysis Services (SSAS) or SQL Server 2005 Reporting Services (SSRS) not supported
  If you install a named instance of SQL Server “Katmai” the June CTP SSAS or SSRS, installation of a default instance of SQL Server 2005 Analysis Services (SSAS) or SQL Server 2005 Reporting Services (SSRS) will fail.
• Multiple instances of this release of SQL Server “Katmai” are not supported on a single computer.

So, I un-installed Katmai, then SQL Server 2005 and started fresh Katmai installation again. Interface and most questions were identical to SQL Server 2005 installation. As I wanted to have sample Adventure Works database installed, I made sure I selected “Install sample databases” check-box in the advanced selection tab. Other that that I choose mostly default answers. Installation went without any problems.

I started SQL Server Management Studio and connected to SQL Server and then to Analysis services. No sample databases were found. Again back to reading  readmesqlkatmai.htm  file and found that actually samples are not longer included:

The code samples and sample databases for Microsoft SQL Server are no longer included with the product. Instead, you can locate and download the samples and sample databases from the Microsoft SQL Server Samples and Community Projects Web site at http://www.codepex.com/sqlserversamples.

 In the section Microsoft SQL Server Product Samples, there are hyperlinks to samples for the SQL Server components and to the official SQL Server sample databases. This Web page also has sections that provide links to samples and projects that are available in the SQL Server community. 

Hmm, then why there is a option to select to “install sample databases” during setup? Now samples and sample databases are separate installations on codeplex. To make things more confusing, when you go to page “SQL Server Sample Databases”, you can see in the description that they apply just to SQL Server 2005. Just when you are in the “Releases” tab, you can see that there are sample databases for Katmai. I downloaded and installed AdventureWorksBI.msi file, then deployed SSAS 2008 project from installed folder: C:\Program Files\Microsoft SQL Server\100\Tools\Samples\AdventureWorks Analysis Services Project\enterprise. Finally, I am ready to test Katmai SSAS 2008 release.

First Impressions

When I first moved from SQL Server Analysis Services 2000 to 2005, there was a big shock - almost everything was different: environment, AS database structure, etc. The new Katmai BIDS and SQL Server Management studio looks the same as in SQL Server 2005 release. That means switching to new SSAS 2008 will be much easier.

Defining design warnings

First thing I noticed in Dimension editor was the fact that many attributes had a small blue wave under them that indicated that there is some sort of warning. Warning message said: 

Set the dimension type to match the “Account” or “Time” attribute types.

Message looked strange to me, as it appeared on many different dimensions. I found that you can turn ON or OFF many warnings for Analysis Service database, cubes, data sources, dimensions, partitions, aggregations. You can do that by choosing menu “Database” and then “Edit database”. To turn off above warning message I uncheked dimension warning “Set at least one of the attribute types to match the ‘Account’ or ‘Time’ dimension.”  There are many more warning types, each of them have importance set. Warnings are grouped into 5 groups. I provided some examples of warnings in each group:

Cube Design
- Avoid creating measure groups that have the same dimensionality and granularity.
- Break distinct count measures into separate measure groups.
… Total 10 warnings

Data Source Design
- For faster performance, use the Microsoft OLE DB Provider for SQL Server or the SQL - Native Client provider to connect to a SQL Server data source instead of the .NET Data Provider for SQL
… Total 2 warnings

Database Design
- Avoid having 10 or more dimensions with a single attribute. Consider unifying them.
… Total 3 warnings

Dimension Design
- Avoid creating hierarchies where attribute relationships do not exists between one or more levels.
- Avoid specifying more than one non-aggregatable attributes per dimension.
… Total 24 warnings

Partition and Aggregation Design
- Design aggregations for partitions with 500000 or more rows.
- Do not build more than 500 aggregations for a partition.
… Total 9 warnings

Adding these “best practice” warnings into SSAS product really looks nice and makes so much sense. That is very nice improvement.

Other differences 

After spending few hours with SSAS 2008 I found that most obvious difference from 2005 release is that you can see new “Attribute Relationships” tab in dimension editor. As Mosha Pasumansky already described this new tab in his blog, I am not going to repeat description here.

I was not able to find any other major differences I’ll have to read BOL and spend more time latter. I’ll report in my blog if I’ll find anything interesting.