Creating a matrix report using the Analysis Services Cube

by Jayaram Krishnaswamy | June 2009 | Microsoft PHP

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.

 Expert Cube Development with Microsoft SQL Server 2008 Analysis Services

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).

Expert Cube Development with Microsoft SQL Server 2008 Analysis ServicesExpert Cube Development with Microsoft SQL Server 2008 Analysis Services

Click OK on the Data Source Properties window.

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.

 Expert Cube Development with Microsoft SQL Server 2008 Analysis Services

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.

 Expert Cube Development with Microsoft SQL Server 2008 Analysis Services

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.

Expert Cube Development with Microsoft SQL Server 2008 Analysis Services

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.

Expert Cube Development with Microsoft SQL Server 2008 Analysis Services

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

Expert Cube Development with Microsoft SQL Server 2008 Analysis Services

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 :


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

Books From Packt


Software Testing with Visual Studio Team System 2008
Software Testing with Visual Studio Team System 2008

SOA Patterns with BizTalk Server 2009
SOA Patterns with BizTalk Server 2009

Magento: Beginner's Guide
Magento: Beginner's Guide

Entity Framework Tutorial
Entity Framework Tutorial

ASP.NET 3.5 Application Architecture and Design
ASP.NET 3.5 Application Architecture and Design

ASP.NET 3.5 Social Networking
ASP.NET 3.5 Social Networking

Mastering phpMyAdmin 3.1 for Effective MySQL Management
Mastering phpMyAdmin 3.1 for Effective MySQL Management

Small Business Server 2008 – Installation, Migration, and Configuration
Small Business Server 2008 Installation, Migration, and Configuration

 


Code Download and Errata
Packt Anytime, Anywhere
Register Books
Print Upgrades
eBook Downloads
Video Support
Contact Us
Awards Voting Nominations Previous Winners
Judges Open Source CMS Hall Of Fame CMS Most Promising Open Source Project Open Source E-Commerce Applications Open Source JavaScript Library Open Source Graphics Software
Resources
Open Source CMS Hall Of Fame CMS Most Promising Open Source Project Open Source E-Commerce Applications Open Source JavaScript Library Open Source Graphics Software