Enabling an Analytic Workspace for an Application - OLAP

Oracle applications are typically designed to run against relational tables in the Oracle Database. The relational tables must conform to certain standards set by the application, and some form of metadata is used to identify the data to the application. For example, the BI Beans requires a star or snowflake schema with embedded total dimension views for solved data, and OLAP Catalog metadata to describe the schema.

The same applications, without modification, can run against analytic workspaces which have been enabled for their use. Enabling an analytic workspace means that you have:

  • Created views of analytic workspace data that conform to the same criteria as the relational tables typically used by the application. These views use the OLAP_TABLE function to extract workspace data.
  • Created any metadata required by the application in order to access the views.

How to Enable an Analytic Workspace

To enable an analytic workspace, complete these steps:

  1. Expand the OLAP Catalog View sufficiently to see the workspaces for your schema.
  2. Right-click the name of the analytic workspace you want to enable.
  3. Choose Enable Workspace for OLAP API & BI Beans.
    or
    Enable Workspace for Oracle Discoverer Using Wizard.
    Complete the steps of the wizard. Click the Help button to get specific information about each step.

About Enabling for the BI Beans

When you enable an analytic workspace for the BI Beans, you create several views that form a star schema. In addition, you create CWM2 metadata, which makes these views accessible to BI Beans applications.

Star Schema of Views

The star schema for a BI Beans-enabled analytic workspace includes:

  • A dimension view for each hierarchy
  • A fact view for each combination of dimension hierarchies

These views are sometimes called embedded total views because dimension members at all levels are listed in a single column. Information about which level a particular member belongs to and the parent-child relationships among members is stored in separate columns. Within the fact tables, summary data is interspersed with (or embedded in) the base-level data. This differs markedly from the source star schema, in which there is no summary data in the fact tables and each level is represented by its own column in the dimension tables. Table describes the views created in the GLOBAL_AW schema when the GLOBAL analytic workspace is enabled.

Views of the GLOBAL Analytic Workspace for the BI Beans

Views of the GLOBAL Analytic Workspace for the BI Beans

OLAP Catalog Metadata for Analytic Workspaces

When an analytic workspace is enabled for the BI Beans, OLAP Catalog metadata is created for the relational views, as described previously. OLAP Catalog metadata for analytic workspaces is stored in a set of tables, which you can access through a set of views owned by OLAPSYS. The public synonyms for these views have a prefix of ALL_OLAP2.

The BI Beans query a special set of views, also owned by OLAPSYS, with the public synonym MRV_OLAP2. These views are created from the CWM2 metadata tables with a special structure that improves performance.

You can browse the metadata in the OLAP Catalog View of Analytic Workspace Manager, or you can use SQL commands to query the views. Example shows the results of a query.

Querying the CWM2 Read API

How to Enable an Analytic Workspace for Oracle Discoverer

To enable an analytic workspace, complete these steps:

  1. Expand the OLAP Catalog View to see the workspaces for your schema.
  2. Right-click the name of the analytic workspace you want to enable.
  3. Choose Enable Workspace for Oracle Discoverer Using Wizard. Complete the steps of the wizard.
    Click the Help button to get specific information about each step.
  4. Detach the analytic workspace, saving your changes first if necessary.
  5. To create the views, execute the SQL script generated by the wizard.
  6. To create an End User Layer (EUL), use Oracle Discoverer Administrator to import the EEX file generated by the wizard.

About Enabling for Oracle Discoverer

The Enable for Discoverer wizard generates two files on your local computer, where you are running Analytic Workspace Manager:

  • A SQL script that creates views of workspace data in the format required by Discoverer.
  • An EEX file that contains XML for creating an End User Layer.

Your analytic workspace is not enabled until you run the script and import the EEX file.
This release supports only one hierarchy for each dimension. If a dimension has multiple hierarchies, you must select one of them for access through Discoverer.

Views Created for Discoverer

Enabling an analytic workspace for Discoverer generates two sets of views. The first set of views contains the OLAP_TABLE function calls that actually extract the data. This set exists to simplify creation of views in the format used by Discoverer (the second set of views), requiring that only a few object types and table types be defined. This set contains two types of views. Both sets use a letter and digit identifier, instead of the names, to identify the schema, the dimensions, and the levels.

  • A view of the complete analytic workspace, with all dimensions and all measures. The name in this format:
    workspace_schema_FULL_VIEW where schema is an S followed by a digit, such as S1.
  • A view for each dimension. The names are in this format:
    TFDV_dimension_VIEW where dimension is a D followed by a digit, such as D5.
    The second set of views selects data from the first set, and presents the analytic workspace data in the format required by Discoverer. This set also has two types of views.
  • A measure view for each combination of dimension levels. This view runs against the FULL_VIEW described earlier. The names are in this format:
    FACTVIEW_schema_level_level_level... where schema is an S followed by a digit, such as S1, and level is an L followed by a digit, such as L1.
  • A view for each dimension. This view runs against the TFDV view described earlier. The names are in this format:
    DV_dimension where dimension is a D followed by a number, such as D1.

Relationships Among Views for Discoverer

Relationships Among Views for Discoverer

Table describes the views of the GLOBAL analytic workspace.

Views of the GLOBAL Analytic Workspace for Oracle Discoverer

Views of the GLOBAL Analytic Workspace for Oracle DiscovererViews of the GLOBAL Analytic Workspace for Oracle Discoverer

Refreshing the Data in an Analytic Workspace

Some build options do not load data, so you must perform an initial refresh before your analytic workspace can be used. Over time, all analytic workspaces need to be refreshed with new data. The data source will have new time periods as well as other new dimension members.

Using the Refresh Wizard

The Refresh Analytic Workspace wizard adds new members for selected dimensions and reloads all of the data for selected measures. The wizard requires the new data to be in the same tables as the original data.

To refresh your data, complete these steps:

  1. Expand the OLAP Catalog View to see the workspaces for your schema.
  2. Right-click the name of the analytic workspace you want to enable.
  3. Choose Refresh Analytic Workspace Using Wizard. Complete the steps of the wizard. You can refresh individual dimensions, or measures, or both. Click the Help button to get specific information about each step.
  4. Re-enable the cube if necessary.
  5. Re-deploy the aggregation plans.

All rights reserved © 2018 Wisdom IT Services India Pvt. Ltd DMCA.com Protection Status

OLAP Topics