In the last posting I hopefully explained some of the basic concepts behind OLAP. In this posting I want to explore how those basic concepts are exposed by the various OLAP aware ETL and reporting tools provided by Oracle and other BI vendors.
Architecture of Oracle OLAP
For a long time now Oracle has been unique in the marketplace. With Oracle Database 9i, 10g and 11g, all data (relational and multidimensional) is stored in one Oracle database. Only Oracle OLAP provides native multidimensional data types within the database.
A high-level architectural view of the Oracle OLAP option contains three parts:
Oracle Database 10g OLAP option, which comprises:
Administrative tools used to create and manage multidimensional data types. Oracle provides two administrative tools that can be used to create multidimensional data types in Oracle Database 10g:
Components of Oracle OLAPWith the Oracle OLAP option, you get two powerful arenas of functionality:
Analytic Workspace The Analytic Workspace is the container for the multidimensional data types and leverages the multidimensional calculation engine of the OLAP Option. The AW also provides a standard SQL interface to the multi-dimensional model. This provides an industry standard access layer that can be used by any BI reporting tool that generates SQL commands, from SQL Developer, to Application Express on to more sophisticated tools such as BI EE.
The AW also povides an XML API for administration, and a programming language (OLAP DML).
Multidimensional Data Store The OLAP option provides true array-based multidimensional data types within the Oracle database. These multidimensional data types are contained in special tables in Oracle called analytic workspaces. Some data types are used to store data, whereas others are calculated instantaneously using the multidimensional engine.
Multidimensional Calculation Engine The OLAP Option’s multidimensional engine includes an impressive library of multidimensional-aware calculation functions and support for planning functionalities such as statistical forecasts, models, allocations, projections, and “what-if”scenarios, in the context of Analytic Workspaces. The multidimensional engine interacts with the multidimensional data types in the analytic workspace in the Oracle database.
The Oracle OLAP option provides a specialized Java API that developers can use to exploit the full power of the Oracle OLAP option by using advanced dimensionally-aware tools and applications. This API is used by Oracle Business Intelligence tools such as OracleBI Beans, OracleBI Discoverer, OracleBI Spreadsheet Add-In, and Oracle Reports OLAP Plug-in to provide a true multi-dimensional query and calculation environment.
OLAP DML The OLAP DML is an extremely powerful and analytically rich feature of the AW. It is a dimensionally-aware, high-level procedural language that runs in the database and exploits the multidimensional engine and multidimensional data types.
With the Oracle OLAP option developers can exploit the OLAP DML to add more sophisticated calculations and analysis to AWs and to extend the functionality of applications that access them. The OLAP DML is briefly introduced in the lesson titled “Previewing Advanced Oracle OLAP Features.”
AW API The AW API is a Java API that is used to define and physically build multidimensional analytic workspaces inside Oracle Database 10g. The AW API is used by administrative tools such as Analytic Workspace Manager and may be used by developers, if required, to enhance and extend analytic workspaces as necessary for a specific application.
Query Access to Oracle OLAPDifferent users with different end-user tool requirements can all access the same data, taking advantage of the same calculations, and benefit from the same security, scalability, performance, and availability of the Oracle database.
OLAP API The OLAP API is designed to work with both multidimensional data types and relational data types in the Oracle database.
Therefore, tools that use the OLAP API can be deployed against multidimensional analytic workspaces or suitable relational schemas that are registered to the Oracle OLAP option’s metadata layer.
Many Oracle source business intelligence tools take full advantage of the multidimensional query data model provided by the Oracle OLAP option through the OLAP API. These include OracleBI Spreadsheet Add-In, OracleBI Discoverer, OracleBI Beans, and OracleBI Reports.
SQL Interface
Unlike other multidimensional OLAP server products, Oracle OLAP provides not only a specialized API but also industry-standard SQL to access multidimensional data types.
You can use a simple SQL query with relationally oriented tools and applications to gain access to the multidimensional data types in the Oracle database. As a result, your SQL-based applications (such as report generators and ad hoc query tools) can access multidimensional data and calculations managed by the Oracle OLAP option.
SQL and PL/SQL are also used to manage and maintain multidimensional analytic workspaces and to move data between relational and multidimensional data types within the Oracle database.
For example, Oracle Application Express is an easy-to-use tool that is supplied with Oracle Database 10g for Web access to the Oracle database. Oracle Application Express is an example of a SQL-based application with no built-in OLAP knowledge that can nevertheless leverage the power of Oracle OLAP.
Many third-party tools from independent software vendors and Oracle partners, such as Arcplan, Business Objects, Cognos, and a large and growing number of business intelligence vendors throughout the world, access OLAP data through the Oracle OLAP option. Some of these vendors’ tools leverage the OLAP API, whereas others exploit the SQL query interface.
Dimensionally Aware Products
Oracle BI Spreadsheet Addin OracleBI Spreadsheet Add-In makes it easy to access OLAP data through the familiar spreadsheet environment of Microsoft Excel. After installation of OracleBI Spreadsheet Add-In, “OracleBI” appears as a new menu item in Excel. By using OracleBI Spreadsheet Add-In, you can establish a secure connection to the OLAP data source and use Excel as the front-end access tool to the data in the database.
Here are some of the features of OracleBI Spreadsheet Add-In:
When using Excel OLAP calculations are performed directly in the database: The benefit of using OracleBI Spreadsheet Add-In is that you no longer need to download massive amounts of data to your spreadsheet. Oracle Database 10g OLAP performs all the OLAP calculations quickly and efficiently in the database. The calculations and business logic are defined only once in the database and then shared across the user community.
SOURCE:http://oraclebi.blogspot.com/2007/11/olap-workshop-part-2-understanding-olap.html
Architecture of Oracle OLAP
For a long time now Oracle has been unique in the marketplace. With Oracle Database 9i, 10g and 11g, all data (relational and multidimensional) is stored in one Oracle database. Only Oracle OLAP provides native multidimensional data types within the database.
A high-level architectural view of the Oracle OLAP option contains three parts:
Oracle Database 10g OLAP option, which comprises:
- Multidimensional data types
- OLAP calculation engine
- Open-access interfaces
Administrative tools used to create and manage multidimensional data types. Oracle provides two administrative tools that can be used to create multidimensional data types in Oracle Database 10g:
- Oracle Warehouse Builder
- Analytic Workspace Manager
Components of Oracle OLAPWith the Oracle OLAP option, you get two powerful arenas of functionality:
- OLAP API and the analytic workspace (AW).
- OLAP API Functionality
Analytic Workspace The Analytic Workspace is the container for the multidimensional data types and leverages the multidimensional calculation engine of the OLAP Option. The AW also provides a standard SQL interface to the multi-dimensional model. This provides an industry standard access layer that can be used by any BI reporting tool that generates SQL commands, from SQL Developer, to Application Express on to more sophisticated tools such as BI EE.
The AW also povides an XML API for administration, and a programming language (OLAP DML).
Multidimensional Data Store The OLAP option provides true array-based multidimensional data types within the Oracle database. These multidimensional data types are contained in special tables in Oracle called analytic workspaces. Some data types are used to store data, whereas others are calculated instantaneously using the multidimensional engine.
Multidimensional Calculation Engine The OLAP Option’s multidimensional engine includes an impressive library of multidimensional-aware calculation functions and support for planning functionalities such as statistical forecasts, models, allocations, projections, and “what-if”scenarios, in the context of Analytic Workspaces. The multidimensional engine interacts with the multidimensional data types in the analytic workspace in the Oracle database.
The Oracle OLAP option provides a specialized Java API that developers can use to exploit the full power of the Oracle OLAP option by using advanced dimensionally-aware tools and applications. This API is used by Oracle Business Intelligence tools such as OracleBI Beans, OracleBI Discoverer, OracleBI Spreadsheet Add-In, and Oracle Reports OLAP Plug-in to provide a true multi-dimensional query and calculation environment.
OLAP DML The OLAP DML is an extremely powerful and analytically rich feature of the AW. It is a dimensionally-aware, high-level procedural language that runs in the database and exploits the multidimensional engine and multidimensional data types.
With the Oracle OLAP option developers can exploit the OLAP DML to add more sophisticated calculations and analysis to AWs and to extend the functionality of applications that access them. The OLAP DML is briefly introduced in the lesson titled “Previewing Advanced Oracle OLAP Features.”
AW API The AW API is a Java API that is used to define and physically build multidimensional analytic workspaces inside Oracle Database 10g. The AW API is used by administrative tools such as Analytic Workspace Manager and may be used by developers, if required, to enhance and extend analytic workspaces as necessary for a specific application.
Query Access to Oracle OLAPDifferent users with different end-user tool requirements can all access the same data, taking advantage of the same calculations, and benefit from the same security, scalability, performance, and availability of the Oracle database.
OLAP API The OLAP API is designed to work with both multidimensional data types and relational data types in the Oracle database.
- The API enables you to directly access multidimensional data types in the AW.
- To access relational data types, you can register a relational physical dimensional model (such as a star or snowflake schema) to the Oracle Database OLAP Catalog.
Therefore, tools that use the OLAP API can be deployed against multidimensional analytic workspaces or suitable relational schemas that are registered to the Oracle OLAP option’s metadata layer.
Many Oracle source business intelligence tools take full advantage of the multidimensional query data model provided by the Oracle OLAP option through the OLAP API. These include OracleBI Spreadsheet Add-In, OracleBI Discoverer, OracleBI Beans, and OracleBI Reports.
SQL Interface
Unlike other multidimensional OLAP server products, Oracle OLAP provides not only a specialized API but also industry-standard SQL to access multidimensional data types.
You can use a simple SQL query with relationally oriented tools and applications to gain access to the multidimensional data types in the Oracle database. As a result, your SQL-based applications (such as report generators and ad hoc query tools) can access multidimensional data and calculations managed by the Oracle OLAP option.
SQL and PL/SQL are also used to manage and maintain multidimensional analytic workspaces and to move data between relational and multidimensional data types within the Oracle database.
For example, Oracle Application Express is an easy-to-use tool that is supplied with Oracle Database 10g for Web access to the Oracle database. Oracle Application Express is an example of a SQL-based application with no built-in OLAP knowledge that can nevertheless leverage the power of Oracle OLAP.
Many third-party tools from independent software vendors and Oracle partners, such as Arcplan, Business Objects, Cognos, and a large and growing number of business intelligence vendors throughout the world, access OLAP data through the Oracle OLAP option. Some of these vendors’ tools leverage the OLAP API, whereas others exploit the SQL query interface.
Dimensionally Aware Products
Oracle BI Spreadsheet Addin OracleBI Spreadsheet Add-In makes it easy to access OLAP data through the familiar spreadsheet environment of Microsoft Excel. After installation of OracleBI Spreadsheet Add-In, “OracleBI” appears as a new menu item in Excel. By using OracleBI Spreadsheet Add-In, you can establish a secure connection to the OLAP data source and use Excel as the front-end access tool to the data in the database.
Here are some of the features of OracleBI Spreadsheet Add-In:
- It combines the flexibility and familiarity of Excel and the power, scalability, and security of the Oracle OLAP option.
- OracleBI Query and Calculation Builders: After the connection is established, you can use the wizard-driven interface to drill, pivot, page through large cubes, and create reports.
- Access to native Excel features
- Powerful data-formatting features of Excel,
- Combine Oracle OLAP data with other Excel data
- Write Excel macros that leverage all your data.
- Create formulas and graphs in Excel
When using Excel OLAP calculations are performed directly in the database: The benefit of using OracleBI Spreadsheet Add-In is that you no longer need to download massive amounts of data to your spreadsheet. Oracle Database 10g OLAP performs all the OLAP calculations quickly and efficiently in the database. The calculations and business logic are defined only once in the database and then shared across the user community.
SOURCE:http://oraclebi.blogspot.com/2007/11/olap-workshop-part-2-understanding-olap.html
0 comments:
Post a Comment