Crystal Report from OLAP Data Introduction
(Page 1 of 4 )
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 ServerOLAP 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.'
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.
Next: Using Crystal Reports with OLAP Data >>
More Database Articles
More By Jayaram Krishnaswamy
/ 18 
