Use OracleDiscoverer and OLAP options

Welcome to the Oracle community forum and interact with 2 million technical staff to access Oracle Business Intelligence Discoverer (OracleBIDiscoverer), which is a set of business intelligence tools, allows you to query and analyze data stored in Oracle and other databases. As a part of Oracle Business Intelligence 10 Gb, OracleBIDiscoverer is Or

Welcome to the Oracle community forum and interact with 2 million technical staff> Oracle Business Intelligence Discoverer (OracleBI Discoverer) is a set of business intelligence tools, allows you to query and analyze data stored in Oracle and other databases. As part of Oracle Business Intelligence 10g, OracleBI Discoverer is Or

Welcome to the Oracle community forum and interact with 2 million technical staff> enter

Oracle Business Intelligence (OracleBI Discoverer) is a set of business intelligence tools that allow users to query and analyze data stored in Oracle and other databases. As part of Oracle Business Intelligence 10g, OracleBI Discoverer adds OLAP options for Oracle Database 10g Enterprise Edition. This option extends the analysis function of the Oracle database and adds a logical dimension model and a dedicated multi-dimensional data type to it.

This article explains the benefits of adding OLAP options to OracleBI Discoverer reports and analysis, and introduces the Analytic Workspace Manager 10g tool, the steps taken by the OracleBI Discoverer Administrator to provide OLAP reports to their users.

Organization view

When analyzing the performance of an organization, you must consider many aspects. Take the sales manager as an example. They are responsible for the profit of the region. The performance of the region may be affected by the performance of the sales team, the product portfolio they sell, the customers, and the sales overview for a period of time. Another example is school administrators who want to measure the average grade of a school. Factors that affect the average grade include the student background, course type, teachers of each course, and subjects used, in addition, the Management Personnel certainly want to know the change of the average level in a school year.

Such a situation can be seen as a multi-dimensional problem. In the example of school administrators, the problem is to find a way to raise the average grade of a grade, and the problem dimension includes different teachers, students, courses, and teaching departments for many years. For the sales manager, the problem is to improve the sales performance of the region. The problem dimension includes the salesperson, sales region, product portfolio, distribution channel and time. In each case, related personnel are working to solve multidimensional problems, and in order to obtain the answer, all these aspects of the problem must be kept in mind.

Figure 1: multidimensional nature of the problem

OracleBI Discoverer for OLAP and Oracle Database 10g Enterprise Edition OLAP options enable you to use the familiar OracleBI Discoverer tool suite to create a multi-dimensional data model in the Oracle database, and query and analyze these models in place. In addition, you can use Microsoft Excel to access these multidimensional models and combine the powerful analysis functions of Oracle with the standard Excel functions.

Logical Dimension Model

OracleBI Discoverer users and administrators will be familiar with the concepts of the end user layer, business data area, folder, item, connection, level, and item class. OracleBI Discoverer allows administrators to define business data areas and folders, which are mapped to tables and columns in database mode. The Administrator defines the connection paths between folders and can create complex folders that combine projects from the join table. The end user layer and business data area enable administrators to clear the complexity of relational databases and provide data items to users using familiar business terms.

With its OLAP support, OracleBI Discoverer provides OLAP data for Administrators and Users by using a dimension model created in the Oracle database. This dimension model reflects the way users describe their businesses.

Looking back at the issue of sales managers, the sales situations and trends they try to understand are produced by the interaction between products, customers, and channels over a period of time. Sales managers need multi-dimensional thinking, because they need to consider how these factors interact, and OracleBI Discoverer provides data to users in a way that reflects this multi-dimensional thinking.

In a logical dimension model, data is organized and measured based on one or more dimensions, such as products, customers, sales areas, and time, such as the number of sales orders and average prices. A dimension can have one or more members (a single customer, product category, and sales region) and be organized into one or more layers. A hierarchy defines how underlying data is accumulated. It can contain a level (product, product group, or product category), and attributes can be used to describe the features of dimension members, such as size, color, or product code. A logical model can contain multidimensional, multidimensional database sets, measurements, layers, levels, and attributes. Generally, data in various parts of an organization will be aggregated in the future.

Figure 2: Use OracleBI Discoverer to view the OLAP logic model

When you consider data from the dimension perspective, you can use the terms familiar to business personnel to create a dimension query. For example,

"For each region in the world, what is the change in the percentage of income of the most important 20% of our products compared to the same period of the year from the past three months ?"

Or

"Who is the most important 10 customers ?"

You don't have to worry about how data is stored in the database.

Oracle OLAP multi-dimensional Engine

The Oracle Database 10 Gb OLAP option provides a logical dimension model that stores data in relational and multidimensional data types. When OLAP data is saved as links, the OLAP option layer the dimension metadata in the Relational Tables and columns, and provides a dimension Java olap api for data access. When OLAP data is stored in multiple dimensions, it is saved in the form of Multidimensional Data Types stored in the analysis workspace. This is a technology from the Oracle Express Server product series, but now this technology is embedded in Oracle RDBMS and accessed through the same Java olap api or SQL.

The multidimensional engine provided by the OLAP option provides functions that are superior to relational implementation in terms of OLAP analysis. Multidimensional databases are especially suitable for ad hoc analysis. They do not limit the data that users can analyze, and can define any type of computing at any time. Multi-dimensional data types store data in an array-based dedicated data structure, which provides performance advantages in an ad hoc query environment, and because the measurement and dimension are pre-linked, therefore, the process of applying conditions to the query is extremely efficient. OLAP option multi-dimensional engine includes Optimization for processing large sparse datasets and automatic navigation between basic data and storage aggregation.

In addition, the multidimensional engine provides access to a variety of commonly used complex computing tasks on dedicated OLAP servers, such as distribution, prediction, intra-dimension equality systems, and time series computing. In short, if you want to use the OLAP option and use multiple types of analysis and statistical computing to provide fast and flexible report services, your OLAP data should be stored in a dedicated multi-dimensional analysis workspace, rather than in a relational data type that does not provide such flexibility or functionality. But how do you start using this feature?

Analytic Workspace Manager 10 GB

Although the OracleBI Discoverer administrator may not be familiar with all these discussions about the analytic workspace and multidimensional data types, Oracle has long provided two tools, it makes creating and maintaining multidimensional data simple and straightforward. Oracle Warehouse Builder is a complete Oracle extract, transform, and load (ETL) tool. It provides the function of loading data into the multi-dimensional analysis workspace, which is usually used by data Warehouse developers and DBAs. However, generally, the administrator of the OracleBI Discoverer system does not need all the features of tools such as Oracle Warehouse Builder because their data has been extracted and prepared. On the contrary, they prefer tools dedicated to maintaining their OracleBI Discoverer metadata and workbooks. OracleBI Discoverer administrators and department superusers who want to use the OLAP option and use the Analytic Workspace to save their OLAP data can download Analytic Workspace Manager 10 Gb for free on OTN.

With Analytic Workspace Manager 10 Gb, you can build a logical dimension model that represents OLAP data to define dimensions, levels, layers, attributes, and measurements to facilitate data organization.

Figure 3: Analytic Workspace Manager 10g Model View

Once the logical model is built, you can use Analytic Workspace Manager 10 Gb to map the data source to this logical model, and then use this tool to fill and maintain your analysis Workspace. The Analytic Workspace Manager interface is intuitive and simple. It follows the logic workflow for creating multidimensional OLAP datasets and creates OLAP multidimensional datasets and dimensions that can be used immediately for OracleBI Discoverer for OLAP.

We will gradually create a multi-dimensional OLAP dataset for OracleBI Discoverer for OLAP. This example uses the GLOBAL Sample mode. You can download it from OTN immediately.This mode.

[1] [2] [3] [4] [5]