Advanced Tutorials > Creating a Report Using OLAP Data Cubes

Creating a Report Using OLAP Data Cubes

Note: This tutorial applies to SQL Server 2000.

This tutorial will help you use OfficeWriter Designer to extract data from OLAP data cubes to create reports. It requires that you use Visual Studio to set up an empty report file containing only an OLAP data source. We will then use OfficeWriter Designer to design the layout and render the report. You must have Microsoft Analysis Services and the FoodMart 2000 sample database installed on your server to use this sample. This tutorial will compare gender, promotional media type, and unit sales from the Sales data cube.

Verify that Reporting Services, Analysis Services, and Visual Studio are installed, working properly, and that you have rights to publish and view reports. See your system administrator if you have questions about this.

Step 1. Create a Blank Visual Studio Report

1. Start Visual Studio.
   
   
2. Create a new Project selecting Business Intelligence Projects and Report Project.
   
   
3. In the Solution Explorer, right-click the Reports folder and select Add/Add New Item.
   
   
4. Select Report and click Ok.
   
   
5. When the file opens, make sure that you are on the Data Tab.
   
   
6. From the sata set drop-down list, select New Data Set.
   
   
7. Select the Provider tab and choose one of the Microsoft OLE DB provider for OLAP Services items.
   
   
8. Select the Connection tab.
   
   
9. In the Data Source text box, enter the server name or instance of your Microsoft Analysis Services.
   
   
10. Enter your security information.
    
    
11. For the initial catalog, choose FoodMart 2000.
    
    
12. Click Test Connection to test the connection. Click Ok when you are successful.

Now we must build our query. Data cubes use the MDX query language to extract cube data. Most examples assign fields to rows and columns. But this is pretty limited. Since data cubes and MDX are designed for multidimentional modeling, we will use a query that uses 3 axes instead of two. Unfortunately, in Visual Studio you cannot graphically design queries with MDX. So you will need to type in your query. For now, type in the following query as shown below.

Save your work and exit Visual Studio.

Step 2. Create the Report Layout

1. Start Microsoft Excel. (You can also create the report in Word using OfficeWriter Designer but for this example we will use Excel.)
   
   
2. From the OfficeWriter Designer Toolbar select Open Report.
   
   
3. Navigate to the .RDL file you just created in Visual Studio and select Open.
   
   
4. In cell A1, type Gender.
   
   
5. In cell B1, type Media Type.
   
   
6. In cell C1, type Unit Sales.
   
   
7. Click OfficeWriter Designer's Select Query button and select FooldMart_2000.
   
   
8. Place your cursor in cell A2.
   
   
9. Click OfficeWriter Designer's Insert Field button and select Gender_Gender. The data marker %%=FoodMart_2000.Gender_Gender will be placed in cell A2.
   
   

   The return fields from an MDX query look different from an SQL query. The return from our query for the gender field would look like '[Gender].[MEMBER_CAPTION]'. This type of syntax, though, is illegal in WordWriter and OfficeWriter. So, OfficeWriter Designer converts the fieldname to a legal value, thus we get 'Gender_Gender' in the pulldown.

   
   
10. Insert the Promotion_Media_Media_Type field in cell B2.
    
    
11. Insert the Measures_Unit_Sales field field in cell C2.
    
    
12. Click OfficeWriter Designer's Save As button (not Excel's Save/Save As option) to save the report definition. It should now look like this:
    
    
    
    
13. Publish and view the report.
    
    

Important! Data cubes contain rollups or totalled information. Sometimes when you query a cube, this totalled information is also returned. Be aware of this when you view your results. For example, look below at the columns returned from our query. The left column is gender. Look how the first rows are blank. Then later there are rows with 'F' and rows with 'M'. The blank rows are the totalled rows from the cube. Notice that the first row for 'Daily Paper, Radio, TV' is 9513. The value for 'F' for 'Daily Paper, Radio, TV is 4642 and the corresponding value for 'M' is 4871. Adding 4871 for 'M' and 4642 for 'F' together equals 9513, or the value from the blank line. This means that if you try to total the unit sales column, you will double the actual result, because you will be adding the rollup total to your calculated total. There are ways to limit the results and filter out the totals by making your MDX query more complicated, but that is beyond the scope of this example.

Tip: You can easily filter out any composite data (totals) from your rows using a PivotTable. Follow the instructions on how to use PivotTables with OfficeWriter and when you view your report, use the PivotTable's drop-down list and uncheck the '(blank)' item. This will remove your total rows and the PivotTable will give you the correct value. Also, you can use an Excel filter. From the Excel menu select Data -> Filter -> Autofilter.

Copyright 2005 © SoftArtisans, Inc. All Rights Reserved.