Crystal Report from OLAP Data Introduction

If we had to deal with the data residing in databases and data marts all at once, we’d quickly get bogged down. Fortunately, there are programs that allow us to generate reports from this data, thus turning it into information we can use. This article walks you through generating a report from an OLAP server using Crystal Reports.

Although there may be a lot of data residing in databases/data marts, most of the time the interest is in generating reports in the form of hard copies, or interactive reports that can be generated on the fly on client platforms, or reports that can be seen on a web page. There are many options for generating reports such as MS Access, Crystal Reports, Designer objects in VB, SQL Reporting Services, Actuate, and so forth. This tutorial is a step-by-step guide to generating reports from an OLAP server such as Microsoft’s Analysis Server using Crystal Reports (ver. 8.5). Only some major steps are shown in this tutorial to minimize the size of the tutorial. However, you may download (only if you give permission to download) an executable which includes all the screen shots for this tutorial without annotations.

OLAP and SQL 2000 Server’s Analysis Server

OLAP stands for Online Analytic Processing. Data warehouses and data marts extract information from Online Transaction Processing (OLTP) systems to provide the required information to users, or feed into reporting systems for report generation.

OLAP does not install when the SQL Server is installed, it needs to be installed from the same SQL Server Disc.

OLAP’s main task is to provide an online reporting system, consolidating the business intelligence into a reportable format which is essentially a ‘read-only’ activity. 

OLAP’s main representation of business facts is via what is known as a ‘Cube’. A Cube is a multidimensional representation of business facts that can be accessed quickly to provide specific information. (This can be accomplished by properly written queries in a relational database, but the overhead involved in processing the query, which may involve a large number of ‘joins’, is simply not efficient). While a relational model is more suited to OLTP, a different model is necessary for OLAP. Whereas highly normalized tables are a norm for OLTP, the model for OLAP does not require normalization. 

Here is an example of  a cube named ‘Sales’. It consolidates information from various tables, or groups of tables to obtain information about sales figures for 1998. It has information in several dimensions, namely, Customer, Product, Store, and time. Also it looks at certain ‘Measures’ (Business Intelligence) to extract from the tables, namely, ‘Store Sales’, ‘Store Cost’, and ‘Unit Sales’. The next screen shot shows this information for the cube called ‘Sales.’

With regard to the measures, each dimension (which is the same as a table or groups of tables) may have several columns, but only certain information needs to be included in the final consolidated Cube. For example, Customer tables may have a lot more information than ‘Country’, ‘State’, ‘City’, and ‘Last Name’. This is seen in the next screen shot:

<– Screen shot of the ‘CUBE’
Screen shot of Customer table–>

The Screen shot on the left shows what the ‘Cube’ has set up to process, and the screen shot on the right shows all fields available in the “Customers” table.

After the ‘Cube’ is configured, the ‘Cube’ needs to be processed, which may take quite a bit of time depending on the size of the database and the various ‘Measures’ and ‘Dimensions’. When the processing is finished an instance of the ‘Cube’ will be available for the user.

This screen shot shows the processed ‘Cube’ called ‘Sales.’

Processed Cube

 

From this screen (the screen in the Analysis Server), for any of the dimensions from the drop down boxes, the summarized information from the OLAP can be obtained.

{mospagebreak title=Using Crystal Reports with OLAP Data}

Crystal Reports version 8.5 was used to generate a sample report from the ‘Sales’ Cube in the SQL 2000 Analysis Server. The report generation is completely driven by a wizard. The main steps involved in generating this report are as follows:

Connection to OLAP Server

As soon as you open the Crystal report program, you get the Welcome to Crystal Reports screen. If you choose the Using the Report Expert radio button, you will get access to the wizard, which walks you through creating a report.

There are different types of OLAP sources, such as Essbase Cube, OLE DB for OLAP, IBM DB for OLAP, Informix’s MetaCube, and so on. For SQL 2000 Analysis server the correct type is the OLE DB for OLAP (Provider msolap) needs to be chosen.

Supplying connection information

Once the OLAP type is chosen, then the connection information needs to be furnished in the following format:

Server Source

{mospagebreak title=Choosing a CUBE}

The Server may have many databases, each with their own ‘Cubes’. In this case, “Cubes” in the “Tutorial” database will be used, therefore “Tutorial” is chosen:

Tutorial database

For the report, the data is obtained from a ‘Cube’ called ‘Sales’ and this serves the data for the report

Cross-tab report from OLAP Report Expert

OLAP is closely related to the ‘Cross-tab’ report used in the financial and accounting fields. The OLAP report generated by Crystal Reports resembles the ‘Cross-tab’ report and follows similar lines. For example, the dimensions are used in defining rows and columns of the report as follows (make use of the drag and drop features for rows and columns):

Choosing the data to report

This is followed by going into more specifics of the report’s requirements. Usually a subset of the data from each of these dimensions may be the only reporting items needed. This is carried out as shown here for the dimension ‘Stores’ (for example: only Stores in California are needed in the report):

Selected items of the STORES

Similar selections are made for the other dimensions.

{mospagebreak title=Choosing a style for the report}

Next, you choose a reporting style from a set of templates available in Crystal Reports as shown here:

Design Pane of the report

When all of this completed, the ‘Design’ plane of the Crystal Report would look similar to this:

  Preview Pane of report

The ‘Preview’ plane of the finished report would appear like this:

Summary

Crystal reports’ built-in GUI for generating reports from OLAP sources is very easy to use and configure. Besides MS SQL Analytical Services, data from many other sources can also be used in report generation.

The following slide show shows all the steps needed for generating Reports from Crystal Reports based on OLAP Data. You may need to permit downloading the executable from this link.

6 thoughts on “Crystal Report from OLAP Data Introduction

  1. I am welcoming you again for a fruitful discussion of this topic. The version of Crystal Reports used is 8.5, but right now it may be 10.0 or 11.5. As shown in the tutorial it is really very easy to create reports using Crystal Reports. If you come up with a problem let me know. Good luck with reporting!

  2. I’ve created cube In Sql sever.. when i try to attach the report with
    OLAP Database i’m getting this error .

    i ‘m unable to make connection with OLAP server in order to proceed further,

  3. Your question is somewhat vague. Could you let me know at what stage of the wizard you get the error? It’s possible that you do not have permissions. In the tutorial the OLPA Server was on the same machine as the Crystal Reports.
    As I sadi earlier, could you give me few more details.
    OLAP Server version
    Crystal Reports Version
    Do you have the OLEDB for Olap on your machine? etc

  4. can I use an Oracle OLAP cube with Crystal REports in the same manner as demostrated above in your article?

[gp-comments width="770" linklove="off" ]