How to use OLAP cubes for reporting, and get your business users the data they need today.

This is a showcase review for our sponsors at CodeProject. These reviews are intended to provide you with information on products and services that we consider useful and of value to developers.

Introduction

Creating reports today has become a nightmare for a lot of developers. I can contest to the late nights and many lines of code in order to build custom reports, then have a user never use a report because it was slow or did not contain the data they wanted to see.

In today's environment there are a lot of options available for reporting. You can use reporting packages like Crystal Reports, Reporting Services or build custom reports using chart and grid components. I want to take a minute and go over good reporting architecture. If you are developing reporting systems for large databases or applications that have a lot of users, you should be concerned with building reports off your transaction tables. Here are the main reasons to stay away from this design:

1. It negatively impacts application performance when people run reports.

2. As data in the tables get bigger, the reports slow down.

3. It can be very difficult to manage and might require "summary" tables in your OLTP (Online Transaction Processing Database) – This can be difficult to manage, and new developers may have a hard time understanding the tables

4. There is already a solution available for putting together a summary and rollup of data, and it is called OLAP (Online Analytical Processing). You can use OLAP out of the box with SQL 2000 and SQL 2005 (SQL 2005 is a complete rewrite and offers a wealth of new features and better performance though).

What is OLAP?

OLAP is an acronym for On Line Analytical Processing. It is an approach to quickly provide the answer to analytical queries that are dimensional in nature. It is part of the broader category business intelligence, which also includes Extract transform load (ETL), relational reporting and data mining. The typical applications of OLAP are in business reporting for sales, marketing, management reporting, business process management (BPM), budgeting and forecasting, financial reporting and similar areas. The term OLAP was created as a slight modification of the traditional database term OLTP (On Line Transaction Processing). More on Wikipedia

Now that we have covered the reasons to not build reports on top of your OLTP database, I want to point you to a simple 10 minute lesson on creating an OLAP database. Watch the video

We have the perfect developer tool for viewing this OLAP database. It is called BIViewer. Here are the simple steps to adding BIViewer to your project:

1. Download the demo version from our site: Click here to download
2. Create or install a simple OLAP Cube.
   
   
   1. You can download a SQL 2005 sample here
   2. Watch a short video on how to create one here.
      
3. Add a reference to the control in your toolbox or use the sample project included in the demo.
4. Set the connection string to the OLAP server and database you are using. Some important notes on security.
   
   1. Microsoft OLAP uses Windows Authentication ONLY!
   2. You have to setup security in one of the following ways
      
      1. Your users can login to IIS using Windows Authentication
         You will need to add all the users to the Roles in the cube with the correct access rights, or you can use their AD group
      2. You can map your IIS account to a domain account
         You will need to add all the users to the Roles in the cube with the correct access rights, or you can use their AD group
      3. You can impersonate your application to use a domain account
         You will need to add all the users to the Roles in the cube with the correct access rights, or you can use their AD group

Once you get through the steps above, you will have a very robust and advanced reporting system for your application. Let's go over some of the features of BIViewer and how you can use them.

The interface is designed to be very user friendly. Here is the basic layout:

Some important definitions / terms:

1. Cube: This is an OLAP database that you have developed in Analysis Services.
2. Drill-Down: This is used to drill into a hierarchical set of data. For example: Product Categories contain a group of Products.
3. Measures: These are the metrics you are interested in reporting on.
4. Dimensions: These are the items or elements you want to include in your report. Examples: product category, color, or size.
5. Drill-Though: This context menu item enables you to see the underlining OLAP data rows that make up the summary data you see in a cell of the report.

One of the hardest parts for users of reporting packages is the ability to understand the data they are looking at. We have tried to help users to understand report data by providing special 3D charting options. Here is a view of the data (we used excel export to get it):

Most business users have a hard time looking at tables of numbers. For that reason, we have added some 3D reporting options. My favorite option is the 3D bar charts.

Some other features worth mentioning in BIViewer: