The Analysis Services can also be configured when the SQL Server 2008 is installed. Out of the box the analysis server has no objects and you need to deploy a database to work with this service. Two previous articles described the process of creating a CUBE from scratch using the TestNorthwind database, a copy of the Northwind database. In this article by Dr. Jayaram Krishnaswamy, we will be authoring a report based on an analysis services CUBE.

Reviewing Jayaram's other OLAP related articles may greatly help in understanding this article.

Creating the CUBE will be essential to work with this hands-on. If you are already experienced and have an appropriate CUBE to work with you may directly go to the section on deriving a dataset. The CUBE used in this hands-on is a simple one. It does not even have a "Time" dimension although Microsoft in Visual Studio gives a warning that you should have one. However for the purposes of demonstration and doing the hands-on a CUBE without the "Time" dimension should be adequate. The two previous articles on creating a CUBE provide the necessary background for this article.

Create a data source with a CUBE

Create an Analysis Services Cube (herein called MyNwind.cube) using the TestNorthwind as the database. The Measures and Dimensions for this cube are as shown in the next figure.

Start your Report Builder 2.0. Make sure you have started the reporting services using the Reporting Services Configuration Manager. In Report Builder 2.0 click on New | Data source... under Report Data. Provide the following information:

Name: SrcCube
Select connection type: Microsoft SQL Server Analysis Services
Connection String: Data Source=Hodentek2SANGAM; Initial Catalog=NwindRTM

Herein NwindRTM is the name of the Datasource in the Analysis Server. (Alternately you may build this source as shown).

Click OK on the Data Source Properties window.

Design and implement fast, scalable and maintainable cubes A real-world guide to designing cubes with Analysis Services 2008 Model dimensions and measure groups in BI Development Studio Implement security, drill-through, and MDX calculations Learn how to deploy, monitor, and performance-tune your cube Filled with best practices and useful hints and tips  http://www.packtpub.com/expert-cube-development-with-microsoft-sql-server-2008-analysis-services/book

Add a dataset to the report

Right click on SrcCube and choose Add DataSet... In the Dataset Properties window make the following changes:

Name: QryCube
Data source: SrcCube
Query Type: text

Click on Query Designer... button. At first you will see only the Metadata of the cube displayed as in the left side of the next figure. Expand Customers, Orders, and Products in the dimensions and drop them on the list header in the bottom pane on the right hand side of the next figure.

Drag and drop Freight from Orders under Measures as shown. For each of the dimensions using an operator create a filter expression as shown. For example, the Products included in the query range from Product IDs 3 to 9; the Customer ID (some specific ones are chosen), similarly theCategory ID (only those with ID's 2, 3, 4, and 5 are included in the query).

The design of the query with these filtering choices is as shown in the previous figure.

The filtering tool is very flexible and all the items namely Dimension, Hierarchy, Operator and Filter Expression can be designed in this interface using either drop-down pick lists choices or drop-downs with checkboxes as shown here.

Run the query by clicking on the toolbar item (!) and review the results. Click OK on the Query Designer. The Dataset Properties window shows up with this query designed according to your choices in the query item window as shown. Close the Dataset Properties window.

Report Design

In Report Builder 2.0 click on New Table or Matrix wizard. In the Choose a dataset window accept the default and click Next. Drag and drop Customer_ID in the Row Groups drop area. Drag and drop Order_ID below Customer_ID in the Row Groups drop area. Drag and drop Product_ID in the Column Groups drop area. Drag and drop Freight into the Values drop area and click on Next.

In the Choose the layout window accept Expand/collapse groups and choose the option Stepped, subtotal above.

Click Next and choose some style (herein Ocean) and click Finish. The final report design is as shown here.

Click Home | Run. The report gets displayed after processing as shown here. The orders from a particular customer have been expended in this view.

Summary

Report Builder 2.0 can used to author reports based on Analysis Services Cubes. The interface is very flexible and the Query Designer is very easy to use as shown in this article.

If you have read this article you may be interested to view :

• Creating an Analysis Services Cube with Visual Studio 2008 - Part 1
• Creating an Analysis Services Cube with Visual Studio 2008 - Part 2

Design and implement fast, scalable and maintainable cubes A real-world guide to designing cubes with Analysis Services 2008 Model dimensions and measure groups in BI Development Studio Implement security, drill-through, and MDX calculations Learn how to deploy, monitor, and performance-tune your cube Filled with best practices and useful hints and tips  http://www.packtpub.com/expert-cube-development-with-microsoft-sql-server-2008-analysis-services/book

About the Author

Dr. Jay Krishnaswamy is a graduate of the Indian Institute of Science, Bangalore writes on database and web development related topics to several computer programming related web sites. He is an active participant in several forums and discussion groups. Before working in the IT industry as a Microsoft Certified Trainer and a Siebel Certified consultant he taught at several institutes of technology and universities in India, Australia, Brazil and the USA. Links to his articles may be found at his website or the blog. He lives in Plainsboro, NJ, USA and may be reached at jkrishnaswamy@comcast.net