Introduction to OLAP Concepts and OLAP Reporting
OLAP is an analysis-oriented technology that enables rapid analysis of large sets of aggregated data. Instead of representing information in the common two-dimensional row and column format of traditional relational databases, OLAP databases store their aggregated data in logical structures called hypercubes—more commonly referenced simply as cubes. These OLAP cubes are created around specific business areas or problems and contain an appropriate number of dimensions to satisfy analysis in that particular area of interest or for a specific business issue. OLAP is a technology that facilitates data viewing, analysis, and navigation. More than a particular storage technology, OLAP is a conceptual model for viewing and analyzing data.
Business Areas and Commonly Associated OLAP Dimensions
| Sales | Sales Employees, Products, Regions, Sales Channels, Time, Customers, Measures |
| Finance | Company Divisions, Regions, Products, Time, Measures |
| Manufacturing | Suppliers, Product Parts, Plants, Products, Time, Measures |
OLAP cubes pre-aggregate data at the intersection points of all of their associated dimension's members. A member is a valid field value for a dimension. (For example: Members of a time dimension could be 2000, 2001, Q1, or Q2; and members of a product dimension could be Gadget1, Gizmo2, DooDah1, and so on.) This pre-aggregation facilitates the speed-of-thought analysis associated with OLAP.
Precalculating all the numbers at the intersection points of all of an OLAP cube's associated dimension members enables rapid high-level analysis of large volumes of underlying data that would not be practical with traditional relational databases. Considering the example of analysis on several years of sales data by year, quarter, and month and by region, sales manager, and product, the preaggregated nature of OLAP facilitates quick speed-of-thought analysis on this data that otherwise would not be practical working with the phenomenal amount of storage space required in a traditional relational (OLTP) database system.
New OLAP Features in Crystal Reports 9 This section is specifically targeted for users of previous versions of Crystal Reports. lists the new OLAP oriented features of version 9 and their practical use or benefit. If you are a new user to Crystal Reports or you have not previously used the OLAP reporting features in the product, you might want to skip directly to the next section. OLAP Features in Crystal Reports 9 | Filter/Page Dimension Parameter links | This Productivity feature enables for the direct linking of report parameters to pages and filters in the OLAP grid. This enables the end user to dynamically specify the values of filters and pages in the OLAP grid. The feature is accessed in either the OLAP Report Creation Wizard or the OLAP Report Settings option under the Report menu. | | Interactive OLAP Worksheet (Analyzer) in new Cube tab | The New OLAP Analyzer feature (a Cube tab in Crystal Reports Designer) is accessed by right-clicking on an existing OLAP grid object and selecting the Launch Analyzer option. The Cube tab provides a fully functioning drag-and-drop OLAP worksheet that enables rapid selection of the most appropriate OLAP viewpoint for the Crystal Report. All changes made in the Analyzer worksheet are reflected in the associated Crystal Reports OLAP grid, where advanced formatting can be applied. | | Interactive drill-down of OLAP grids in Preview Tab | The OLAP grid presented in the Crystal Reports Preview tab has now been made more fully functional. In addition to having access to advanced OLAP grid functionality from the right-click button including calculations, exception highlighting, sorting, filtering, and member reordering, the OLAP grid now enables the report designer to expand (drill-down) and contract members directly from within the Preview tab. | | New and improved data sources | At the time of writing, it was expected that Crystal Reports 9 would have improved and flexible OLAP connectivity to Hyperion Essbase and new connectivity to SAP BW. | The following sections explore the creation of an OLAP report through the OLAP Report Creation Wizard, the added value of the OLAP Expert, and the advanced interactivity features of version 9. 
OLAP Report Creation Wizard and OLAP Expert Crystal Reports 9 provides two easy ways to create reports against OLAP data sources. As introduced in "Using the Default Report Wizards," Crystal provides several report wizards to step you through the creation of some popular types of reports—one of those is OLAP. The OLAP Wizard involves five steps/screens and walks you through the process of creating an OLAP grid and an optional supporting graphic based on an existing data source. The OLAP Wizard is accessible when you are creating a new report. The second method of creating an OLAP based report is through the OLAP Expert that is accessed from the Insert OLAP Grid on the Insert menu. This expert provides six tabs that step through the creation of an OLAP grid to be placed anywhere on a report. The two methods of creation offer very similar degrees of functionality, and their respective dialog screens and tabs are almost identical. The OLAP Report Creation Wizard does provide a built-in Charting screen not found in the OLAP Expert, whereas the OLAP Expert provides Style Customization and Label tabs not found in the OLAP Wizard.
Advanced OLAP Reporting Up to this point, the OLAP Expert and OLAP Report Creation Wizard have demonstrated the capability of Crystal Reports to rapidly create OLAP based reports. More than these capabilities, Crystal Decisions has provided advanced analytic capabilities against OLAP data sources through some advanced OLAP-oriented features in Crystal Reports and through a sister product called Crystal Analysis. The last four sections of this hour introduce some of these advanced features. Interacting with the OLAP Grid Crystal Reports provides some powerful interactive OLAP features from directly within the Crystal Reports Preview and Design tabs. displays the right-click menu that appears when right-clicking on the year 1998 member in this hour's sample report. Introduction to Crystal Analysis Crystal Analysis Professional is a new reporting tool from Crystal Decisions that enables organizations to deliver action-based OLAP analysis to business users. It enables better insights to help decision makers affect business performance through interactive analysis. Crystal Analysis Professional takes OLAP reporting to the next level by allowing report designers to create intuitive and highly interactive reports that offer a guided analysis approach to business issues. Power users implementing Crystal Analysis can create analytical reports, based on OLAP data, using a powerful designer (similar in concept to Crystal Reports). Crystal Analytic Reports can contain many pages, each presenting a different predefined view of the OLAP cube. Data can be presented in tables or visualized through a wide range of charts, exception highlights, data sorts, filters, and/or analytic transition buttons. Business managers can use the resulting analytical reports to drive the business decisions they need to make every day. |
COM-based Data Sources Crystal Reports provides direct access or native drivers for some databases. These drivers are written specifically for a particular database and are often the best choice. However, because hundreds of types of databases exist, Crystal Decisions can't possibly write direct access drivers for all of them. So often, users turn to using standard data access layers such as ODBC or OLEDB to connect to their databases. Often, the vendor of a database will provide an ODBC driver or OLEDB provider so that other applications can access the vendor's database. Sometimes though, even this is not enough. Customers have data that they would like to report off of that is not accessible by any Crystal Reports data source driver or via ODBC or OLEDB. To this, customers often turn to the COM Data Source driver or the Java Data Source driver. This section describes the COM version of the driver, but much of the theory applies to the Java Data Source driver as well. |
Java-based Data Sources The COM Data Source Driver is targeted at Visual Basic and Visual C++ developers. Because Crystal Reports 9 has a full Java SDK, an equivalent Java Data Source driver provides equivalent functionality of the COM driver for developers using the Java platform. The process of creating a Java Data Source driver is conceptually similar to that of creating a COM Data Source driver. A Java class needs to be created that has a public function with a return type of java.sql.ResultSet.
import java.lang.*;
import java.sql.*;
public class XtremeDataProvider
{
public ResultSet Employee()
{
// connect to the database
Class.forName("sun.jdbc.odbc.JdbcOdbcDriver");
String url = "jdbc:odbc:Xtreme Sample Database 9";
Connection con = DriverManager.getConnection(url, "", "");
// run a SQL query
Statement stmt = con.createStatement(ResultSet.TYPE_SCROLL_SENSITIVE,
ResultSet.CONCUR_READ_ONLY);
String query = "SELECT * FROM Employee";
ResultSet rs = stmt.executeQuery(query);
// return the results of the query
return rs;
}
}
To identify a Java class, instead of typing in its name, place the .class file into a given directory, and add that directory's name to the following registry key of your Windows operating system: HKEY_LOCAL_MACHINE\Software\Crystal Decisions\Crystal Reports
9\DatabaseOptions\JavaUserClassPath
During the process of creating a report, Crystal Reports will search through all classes contained in the folder(s) specified in the registry key discussed previously. It will then provide a list of methods with return types of ResultSet. The same rules about function arguments apply. Any arguments to the Java method will be mapped to report parameter fields. Using Java code, you can control exactly what data comes back. XML as a Data Source With the emergence of XML as a data interchange format, many customers wanted to create reports on XML documents. So in Crystal Reports 8.5, a new driver was released that allowed just this scenario. This ODBC driver reads certain types of XML documents. A new feature of this driver in version 9 of Crystal Reports is the ability to read multiple XML files, most commonly a folder of XML files that have the same schema. When using this driver, you will specify either a folder name or a file path to an XML file. Once connected, XML elements at the first level will be represented as fields that you can place on a report.
If you require more flexibility around reading XML files, a good approach to take is to write a COM or Java Data Provider to read the XML. This Data Provider can use one of the many readily available XML parsers to read in the XML and choose exactly what fields to return to Crystal Reports. A COM Data Provider that Reads XML Data' Loads an XML document with the following structure:
'
'
' X
' X
' X
'
'
Public Function SimpleXML(fileName As String) As ADODB.Recordset
Dim rs As New ADODB.Recordset
Dim xmlDoc As New MSXML2.DOMDocument
xmlDoc.Load (fileName)
rs.fields.Append "Name", adBSTR
rs.fields.Append "Dept", adBSTR
rs.fields.Append "Salary", adCurrency
rs.Open
' Loop through each employee element
Dim employeeNode As MSXML2.IXMLDOMElement
Dim childNode As MSXML2.IXMLDOMElement
For Each employeeNode In xmlDoc.documentElement.childNodes
rs.AddNew
For Each childNode In employeeNode.childNodes
rs(childNode.nodeName).Value = childNode.Text
Next
rs.Update
Next
Set SimpleXML = rs
End Function
| |
No comments:
Post a Comment