7 Working with Physical Tables, Cubes, and Joins

The Physical layer of the Oracle BI repository contains objects that represent physical data constructs from back-end data sources. The Physical layer defines the objects and relationships available for writing physical queries. This layer encapsulates data source dependencies to enable portability and federation.

Physical tables, cubes, joins, and other objects in the Physical layer are typically created automatically when you import metadata from your data sources. After these objects have been imported, you can perform tasks like creating additional join paths that are not in the data source, create alias tables for physical tables that need to serve in different roles, and adjust properties of physical hierarchies from multidimensional data sources.

This chapter contains the following topics:

Working with the Physical Diagram

In addition to working with Physical layer objects in the right pane of the Administration Tool, you can open the Physical Diagram view to see a graphical model of tables and joins.

To access the Physical Diagram, right-click an object in the Physical layer tree view (such as a physical database or table) and select Physical Diagram. Then, select one of the following options:

  • Selected Object(s) Only. Displays only the selected objects. Joins appear only if they exist between the objects that you select.

  • Object(s) and Direct Joins. Displays the selected objects and any tables that join to the objects that you select.

  • Object(s) and All Joins. Displays the selected objects, as well as each object that is related directly or indirectly to the selected object through some join path. If all the objects in a schema are related, then using this option diagrams every table, even if you only select one table.

Note that the Physical Diagram displays only physical tables and joins. It does not display other Physical layer objects, such as connection pools, physical hierarchies, or levels.

You can also open the Physical Diagram by selecting one or more objects in the tree view and then clicking the Physical Diagram button on the toolbar. Only the objects you selected appear. Joins appear only if they exist between the selected objects.

You can view the physical objects that are associated with a particular logical object by selecting one or more business models, logical tables, or logical table sources in the Business Model and Mapping layer tree view and then clicking the Physical Diagram button on the toolbar. Only physical objects that are related to the objects you selected appear. You can view the same information by right-clicking a logical object and selecting Objects and Direct Join(s) within Business Model from the Physical Diagram submenu. You can also choose one of the other Physical Diagram display options.

To add additional tables to the Physical Diagram, leave the Physical Diagram window open and then right-click the table or tables you want to add. Then, select Physical Diagram and choose one of the display options.

Additional options are available in the right-click menu for the graphical tables and joins displayed in the Physical Diagram. For example, you can delete objects or view their properties, or you can add additional related objects using the right-click options Add Direct Joins, Add Tables Joined to Whole Selection, and Add All Joins. You can also select Find in Tree View to locate a particular object in the Physical layer tree view in the right pane, or check out objects in online mode.

You can also right-click an object in the Physical Diagram view and click Hide to hide particular objects in the diagram. Note that this effect is temporary and does not persist.

Creating Physical Layer Folders

This section contains the following topics:

Creating Physical Layer Catalogs and Schemas

Catalogs are optional ways to group different schemas. A catalog contains all the schemas (metadata) for a physical database object. A schema contains only the metadata information for a particular user or application. Model the Physical layer after the way your data source is structured.

Note the following:

  • You must create a physical database object before you can create a physical catalog object or a physical schema object.

  • After you implement a certain type of grouping, you cannot change it later. For example, if you decide to implement database > schema > table, you cannot add a catalog afterward.

Creating Catalogs

In the Physical layer of a large repository, administrators can create physical catalogs that contain one or more physical schemas.

To create a catalog:

  1. In the Physical layer of the Administration Tool, right-click a physical database and select New Object, then select Physical Catalog.

  2. In the Physical Catalog dialog, type a name for the catalog.

  3. Type a description for the catalog, and then click OK.

Creating Schemas

The schema object contains tables and columns for a physical schema. Schema objects are optional in the Physical layer of the Administration Tool.

To create a schema:

  1. In the Physical layer of the Administration Tool, right-click a physical database or physical catalog and select New Object, then select Physical Schema.

  2. In the Physical Schema dialog, type a name.

  3. Type a description for the schema, and then click OK.

Using a Variable to Specify the Name of a Catalog or Schema

You can use a variable to specify the names of catalog and schema objects. For example, you have data for multiple clients and you structured the data source so that data for each client was in a separate catalog. You would initialize a session variable named Client, for example, that could be used to set the name for the catalog object dynamically when a user signs on to the Oracle BI Server.

You specify the session variable to use in the Dynamic Name tab of the Physical Catalog or Physical Schema dialog.

Note:

The Dynamic Name tab is not active unless at least one session variable is defined.

To specify the session variable to use in the Dynamic Name tab:

  1. In the Name column of the Dynamic Name tab, click the name of the session variable that you want to use. The initial value for the variable (if any) is shown in the Default Initializer column.

  2. To select the highlighted variable, click Select.

    The name of the variable is displayed in the dynamic name field, and the Select button toggles to the Clear button.

To remove assignment for a session variable in the Dynamic Name tab:

  • Click Clear to remove the assignment for the variable as the dynamic name.

    The value not assigned is displayed in the dynamic name field, and the Clear button toggles to the Select button.

To sort column entries in the Dynamic Name tab:

  • You can sort the entries in a column by clicking the Name or Default Initializer column heading. Clicking a column heading toggles the order of the entries in that column between ascending and descending order, according to the column type.

Setting Up Display Folders in the Physical Layer

You can create display folders to organize table objects in the Physical layer. They have no effect on query processing. After you create a display folder, the selected tables appear in the folder as a shortcut and in the Physical layer tree as an object. You can hide the objects so that you only view the shortcuts in the display folder. See the information about the Repository tab of the Options dialog in "Setting Preferences" for more information about hiding these objects.

Note:

Deleting a table in a display folder deletes only the shortcut to that object. When you delete a column in a display folder, however, the column is actually deleted.

To set up a physical display folder:

  1. In the Physical layer of the Administration Tool, right-click a physical database and select New Object, then select Physical Display Folder.

  2. In the Physical Display Folder dialog, type a name for the folder.

  3. To add tables to the display folder, click Add. Then, in the Browse dialog, select the fact or physical tables you want to add to the folder and click Select.

    Alternatively, you can drag one or more physical tables to the display folder after you close the dialog.

  4. Click OK.

Working with Physical Tables

For all data sources, you can define general properties, columns, a primary key, and foreign keys.

This section contains the following topics:

About Physical Tables

A physical table is an object in the Physical layer of the Oracle BI repository that corresponds to a table in a data source. Metadata for physical tables is usually imported from the data source. This metadata enables the Oracle BI Server to access the data source tables with SQL requests.

When you delete a physical table, all dependent objects are deleted (for example, columns, keys, and foreign keys). When you delete a physical cube table, hierarchies are also deleted. The deletion fails if an alias exists on the physical table.

In addition to importing data source tables into the Physical layer, you can create virtual physical tables in the Physical layer, using values in the Table Type field in the Physical Table dialog. Creating virtual tables can provide the Oracle BI Server and the underlying data sources with the proper metadata to perform some advanced query requests.

A virtual physical table can be a stored procedure, or a SELECT statement. A virtual physical table created from a SELECT statement is also called an opaque view. You can define an opaque view, and then deploy it in your data source to create a deployed view. See "Deploying Opaque Views" for more information.

Use the Table Type list in the General tab of the Physical Table dialog to specify the physical table object type. Table 7-1 describes the available object types.

Table 7-1 Table Types for Physical Tables

Table Type Description

Physical Table

Specifies that the physical table object represents a data source table.

Stored Proc

Specifies that the physical table object is a stored procedure. When you select this option, you type the stored procedure in the text box. Requests for this table will call the stored procedure.

For stored procedures that are data source-specific, select Use database specific SQL. When you select this option, the Database column displays supported data sources by brand, with Default as the root. You can enter data source-specific initialization strings by selecting the database type on the left and entering the corresponding string on the right. The initialization string for the Default option is run when the queried database type does not have a corresponding database-specific string defined.

Stored procedures within an Oracle Database do not typically return result sets. Therefore, they cannot be initiated from within Oracle Business Intelligence. You need to rewrite the procedure as an Oracle function, use it in a SELECT statement in the Administration Tool initialization block, and then associate it with the appropriate Oracle BI Server session variables in the Session Variables dialog.

The following example shows a SQL initialization string using the GET_ROLES function that is associated with the USER, GROUP, and DISPLAYNAME variables. The function takes a user Id as a parameter and returns a semicolon-delimited list of group names:

SELECT user_id, get_roles(user_id), first_name || ' ' || last_name
FROM csx_security_table
WHERE user_id = ':USER' and password = ':PASSWORD'

Select

Specifies that the physical table object is a SELECT statement. When you select this option, you type the SELECT statement in the text field, and you also need to manually create the table columns. The column names must match the ones specified in the SELECT statement. Column aliases are required for advanced SQL functions, such as aggregates and CASE statements.

Requests for this table will execute the SELECT statement.

For SELECT statements that are data source-specific, select Use database specific SQL. When you select this option, the Database column displays supported data sources by brand, with Default as the root. You can enter data source-specific initialization strings by selecting the database type on the left and entering the corresponding string on the right. The initialization string for the Default option is run when the queried database type does not have a corresponding database-specific string defined.

This type of table is also called an opaque view. See "Deploying Opaque Views" for more information.


About Physical Cube Tables

Each cube from a multidimensional data source is set up as a physical cube table, a type of physical table. It has all the capabilities of a table, such as physical cube columns and keys (optional) and foreign keys (optional). It also has cube-specific metadata such as hierarchies and levels.

When you import the physical schema, the Oracle BI Server imports the metadata for the cube, including its metrics, hierarchies, and levels. Expanding the hierarchy object in the Physical layer reveals the levels in the hierarchy. In the Physical Cube Table dialog, the Hierarchies tab lists the dimensional hierarchies in the cube.

Each multidimensional catalog in the data source can contain multiple physical cubes. You can import the metadata for one or more of these cubes into your Oracle BI repository. Although it is possible to create a cube table manually, it is recommended that you import metadata for cube tables and their components.

If you do create cubes manually, be sure to build each cube one hierarchy at a time and test each one before building another. For example, create the time hierarchy and a measure, and then test it. When it is correct, create the geography hierarchy and test it. This helps ensure that you have set up each cube correctly, and makes it easier to identify any setup errors.

About Physical Alias Tables

An alias table (alias) is a physical table that references a different physical table as its source (called the original table). Alias tables can be an important part of designing a Physical layer because they enable you to reuse an existing table more than once, without having to import it several times.

There are two main reasons to create an alias table:

  • To set up multiple tables, each with different keys, names, or joins, when a single data source table needs to serve in different semantic roles. Setting up alias tables in this case is a way to avoid triangular or circular joins.

    For example, an order date and a shipping date in a fact table may both point to the same column in the time dimension data source table, but you should alias the dimension table so that each role is presented as a separately labeled alias table with a single join. These separate roles carry over into the business model, so that "Order Date" and "Ship Date" are part of two different logical dimensions. If a single logical query contains both columns, the physical query uses aliases in the SQL statement so that it can include both of them.

    You can also use aliases to enable a data source table to play the role of both a fact table, and a dimension table that joins to another fact table (often called a "fan trap").

  • To include best practice naming conventions for physical table names. For example, you can prefix the alias table name with the table type (such as fact, dimension, or bridge), and leave the original physical table names as-is. Some organizations alias all physical tables to enforce best practice naming conventions. In this case, all mappings and joins are based on the alias tables rather than the original tables.

Alias table names appear in physical SQL queries. Using alias tables to provide meaningful table names can make SQL queries referencing those tables easier to read. For example:

WITH
SAWITH0 AS (select sum(T835.Dollars) as c1
from
     FactsRevT835/*AllRevenue(Billed Time Join)*/)
select distinct 0 as c1,
     D1.c1 as c2
from
     SAWITH0 D1
order by c1

In this query, the meaningful alias table name "A11 Revenue (Billed Time Join)" has been applied to the terse original physical table name "FACTSREV." In this case, the alias table name provides information about which role the table was playing each time it appears in SQL queries.

Alias tables can have cache properties that differ from their original tables. To set different cache properties for an alias table, select the option Override Source Table Caching Properties in the Physical Table dialog for the alias table. In alias tables, columns cannot be added, deleted, or modified. Because columns are automatically synchronized, no manual intervention is required.

Synchronization ensures that the original tables and their related alias tables have the same column definitions. For example, if you delete a column in the original table, the column is automatically removed from the alias table.

You cannot delete an original table unless you delete all its alias tables first. Alternatively, you can select the original table and all its alias tables and delete them at the same time.

You can change the original table of an alias table, if the new original table is a superset of the current original table. However, this could result in an inconsistent repository if changing the original table deletes columns that are being used. If you attempt to do this, a warning message appears to let you know that this could cause a problem and lets you cancel the action. Running a consistency check identifies orphaned aliases.

When you edit a physical table or column in online mode, all alias tables and columns must be checked out. The behavior of online checkout uses the following conventions:

  • If an original table or column is checked out, all its alias tables and columns are checked out.

  • If an alias table or column is checked out, its original table and column are checked out.

  • The checkout option is available for online repositories (if not read-only) and for all original and alias tables and columns.

Alias tables inherit some properties from their original tables. A property that is proxied is a value that is always the same as the original table, and cannot be changed. (In other words, the proxied properties are the ones that are dimmed in the alias table dialog.) If the original table changes its value for that particular property, the same change is applied on the alias table.

The following is a list of the properties that are proxied:

  • Cacheable (the inherited property can be overridden)

  • Cache never expires and Cache persistence time (the inherited properties can be overridden)

  • Row Count

  • Last Updated

  • Table Type

  • External Db Specifications

The following is list of the properties that are not proxied:

  • Name

  • Description

  • Display Folder Containers

  • Foreign Keys

  • Columns

    Note:

    Alias tables and original tables never share columns. Aliases and original tables have distinctly different columns that alias each other.
  • Table Keys

  • Complex Joins

  • Source Connection Pool

  • Polling Frequency

  • All XML attributes

Creating and Managing Physical Tables

Use the General tab of the Physical Table dialog to create or edit a physical table in the Physical layer of the Administration Tool.

This section contains the following topics:

Creating or Editing Physical Tables

This section describes how to create or edit the general properties for a table, including both relational physical tables and physical cube tables.

To create a physical table or edit general properties for tables:

  1. In the Physical layer of the Administration Tool, perform one of the following steps:

    • To create a physical table, right-click the physical database or physical catalog and select New Object, then select Physical Table.

      If your database object has physical schemas defined, right-click the physical schema and select New Physical Table.

    • To create a physical cube table for a multidimensional data source, right-click the physical database and select New Object, then select Cube Table.

      Caution:

      It is strongly recommended that you import cube tables, not create them manually.
    • To edit an existing physical table, double-click the physical table object in the Physical layer.

  2. In the Physical Table dialog, complete the fields using Table 7-2 as a guide.

Table 7-2 General Properties for Physical Tables

Property Description

Name

The name of the physical table.

Table Type

Physical Table values: Physical Table, Stored Proc (stored procedure), or Select.

Physical Cube Table values: Physical Table or Select.

See Table 7-1 for more information.

Use Dynamic Name

Select this option to use a session variable to specify the physical table name, similar to catalog and schema objects. This option is available for non-multidimensional data source tables when you select a table type of Physical Table.

You might want to choose this option if you have a multi-tenancy implementation and you want to define a separate physical table name for each customer. Another example would be to select between primary and shadow tables that are valid at different times in your ETL cycle. In both cases, you can assign session variables to dynamically select the appropriate table.

Default Initialization String / Use database specific SQL

For non-multidimensional data source tables (not alias tables), this option appears if you choose a Table Type of Stored Proc or Select. For multidimensional data source tables, this appears if you choose a Table Type of Select.

When you select this option, you can specify the data source and type the SQL statements.

See Table 7-1 for more information.

Cacheable

Select this option to include the table in the Oracle BI Server query cache. Typically, you should select this option for tables that do not need to be accessed in real time.

When you select this option, the Cache persistence time settings become active.

Note that there are additional configuration settings that affect the behavior of the query cache. See "Configuring Query Caching" in Oracle Fusion Middleware System Administrator's Guide for Oracle Business Intelligence Enterprise Edition for full information.

Cache never expires

When you select this option, cache entries do not automatically expire. This could be useful when a table is important to a large number of queries users might run. For example, if most of your queries have a reference to an account object, keeping it cached indefinitely could actually improve performance rather than compromise it.

Note that selecting this option does not mean that an entry always remains in the cache. Other invalidation techniques, such as manual purging, LRU (Least Recently Used) replacement, metadata changes, or use of the cache polling table can result in entries being removed from the cache.

Cache persistence time

How long table entries should persist in the query cache, or in other words, the cache expiration time.

Setting a cache persistence time is useful for OLTP data sources and other data sources that are updated frequently. For example, you could set this option refresh the underlying physical tables daily for a particular dashboard.

If a query references multiple physical tables with different persistence times, the cache entry for the query exists for the shortest persistence time set for any of the tables referenced in the query. This makes sure that no subsequent query gets a cache hit from an expired cache entry.

For more information, see "Troubleshooting Problems with Event Polling Tables" in Oracle Fusion Middleware System Administrator's Guide for Oracle Business Intelligence Enterprise Edition.

External name

Applies to physical cube tables from multidimensional data sources. The external name is the physical name that is used when referencing the cube table in physical SQL queries. This value must reflect the external name defined in the data source.

Display Column

For Essbase data sources only. See "Working with Essbase Data Sources" for more information.

Hint

Available only for some data sources. See "Using Hints" for more information.


Creating Alias Tables

To create an alias table, right-click an existing physical table and select New Object, then select Alias. You can also create aliases on opaque views and stored procedures.

Table 7-3 describes properties that are specific to alias tables. Refer to Table 7-2 for information about other table properties that are shared between physical tables and alias tables.

Table 7-3 Properties Specific to Physical Alias Tables

Property Description

Source Table

Applies to alias tables. Click Select to choose the original physical table from which to create an alias table.

Override Source Table Caching Properties

Option available for alias tables. When selected, the cacheable properties become available and you can clear or select the appropriate options.


Creating and Managing Columns and Keys for Physical Tables

Each table in the Physical layer of the Administration Tool has one or more physical columns. You can use the Columns, Keys, and Foreign Keys tabs in the Physical Table dialog to view, create new, and edit existing columns, keys, and foreign keys that are associated with the table.

The following list describes the buttons that appear in the tabs:

  • New. Lets you create a new object by opening the dialog that corresponds to the tab.

  • Edit. When you select an object and then click Edit, the dialog that corresponds to the tab appears. You can then edit the properties of the object.

  • Delete. Deletes the selected object.

This section contains the following topics:

About Measures in Multidimensional Data Sources

You need to select the aggregation rule for a physical cube column carefully to make sure your measures are correct. Setting it correctly might improve performance.

Use the following guidelines to verify and assign the aggregation rule correctly:

  • Verify aggregation rules after importing a cube. Typically, aggregation rules are assigned correctly when you import the cube. However, if a measure is a calculated measure, the aggregation rule is reported as None. Therefore, you should examine the aggregation rule for all measures after importing a cube to verify that the aggregation rule has been assigned correctly.

    For all measures assigned an aggregation rule value of None, contact the multidimensional data source administrator to verify that the value of the aggregation rule is accurate. If you need to change the aggregation rule, you can change it in the Physical Cube Column dialog.

  • Setting the aggregation rule when you build the measures manually. Set the aggregation rule to match its definition in the multidimensional data source.

About Externally Aggregated Measures

In a multidimensional data source, some cubes contain very complex, multi-level based measures. If you assign an aggregation rule of External Aggregation, the Oracle BI Server bypasses its internal aggregation mechanisms and uses the pre-aggregated measures. When imported, these measures are assigned an aggregate value of None.

The following are some guidelines for working with pre-aggregated measures:

  • External aggregation only applies to multidimensional data sources (such as Essbase, MS Analysis Services, and SAP/BW) that support these complex calculations.

  • You cannot assign external aggregation to measures from standard data sources (relational). If the measure is supported and can be mapped to a relational data source, then it is not complex and does not require external aggregation.

  • You cannot mix noncomplex measures from standard data sources (relational) with complex measures from multidimensional data sources. In other words, you cannot map a logical column to both an externally aggregated physical cube column and a standard physical column from a relational source.

  • You can mix noncomplex measures from standard data sources (relational) with noncomplex measures from multidimensional data sources if they are aggregated through the Oracle BI Server.

Creating and Editing a Column in a Physical Table

If the column is imported, the properties of the column are set automatically. The following list contains information about nullable and data type values for columns imported into the Physical layer.

  • Nullable. Indicates whether null values are allowed for the column. If null values can exist in the underlying table, you need to select this option. This allows null values to be returned to the user, which is expected with certain functions and with outer joins. It is generally safe to change a non-nullable value to a nullable value in a physical column.

  • Type. Indicates the data type of the column. Use caution when changing the data type. Setting the values to ones that are incorrect in the underlying data source might cause unexpected results. If there are any data type mismatches, correct them in the repository or reimport the columns that have mismatched data types.

    If you reimport columns, you also need to remap any logical column sources that reference the remapped columns. The data type of a logical column in the business model must match the data type of its physical column source. The Oracle BI Server passes these logical column data types to client applications.

Except when stated otherwise, the characteristics and behavior of a physical cube column are the same as for other physical columns.

Note:

Creating, modifying, or deleting a column in an original physical table also creates, modifies, or deletes the same column on all its alias tables.

To create or edit a physical column:

  1. In the Physical layer of the Administration Tool, perform one of the following steps:

    • To create a physical column, right-click a physical table and select New Object, then select Physical Column.

    • To create a physical cube column for a multidimensional data source, right-click a physical cube table and select New Object, then select Physical Cube Column.

    • To edit an existing physical column, double-click the physical column object in the Physical layer.

  2. In the Physical Column dialog, type a name for the physical column.

    For XML data sources, this field stores and displays the unqualified name of a column (attribute) in an XML document.

  3. In the Type field, select a data type for the physical column.

  4. If applicable, specify the length of the data type.

    For multidimensional data sources, if you select VARCHAR, you need to type a value in the Length field.

  5. Select the Nullable option if the column is allowed to have null values.

  6. In the External Name field, type an external name.

    • Required if the same name (such as STATE) is used in multiple hierarchies.

    • Optional for XML documents. The External Name field stores and displays the fully qualified name of a column (attribute).

  7. (Multidimensional data sources) When the physical cube column is a measure, in the Aggregation role list, select the appropriate value.

    A new physical cube column is created as a measure by default. See "Working with Dimensions and Hierarchies in the Physical Layer" for information about changing this behavior.

  8. Click OK.

Specifying a Primary Key for a Physical Table

Use the Physical Key dialog to specify the column or columns that define the primary key of the physical table.

To specify a primary key for a physical table:

  1. In the Physical layer of the Administration Tool, right-click a physical table and select Properties.

  2. In the Physical Table dialog, click the Keys tab.

  3. In the Keys tab, click New.

  4. In the Physical Key dialog, type a name for the key.

  5. Select the column that defines the primary key of the physical table.

  6. (Optional) Type a description for the key.

  7. Click OK.

Deleting Physical Columns for All Data Sources

When you delete a physical column, the following occurs:

  • Multidimensional data sources. If you delete property or key columns from a level, the association is deleted and the column changes to a measure under the parent cube table.

  • Alias tables. Deleting a column in an original physical table deletes the same column on all its alias tables.

Setting Physical Table Properties for XML Data Sources

Use the XML tab to set or edit properties for an XML data source. The XML tab of the Physical Table dialog provides the same functionality as the XML tab of the Connection Pool dialog. However, setting properties in the Physical Table dialog overrides the corresponding settings in the Connection Pool dialog. See "Setting Connection Pool Properties in the XML Tab" for more information.

Working with Cube Variables for SAP/BW Data Sources

In SAP/BW data sources, cube variables are used as a means of parameterizing queries. Cube variable objects are imported into the Physical layer when metadata is imported from Querycubes/Bex Queries in SAP/BW data sources. Typically, you do not edit these objects directly except to keep them synchronized with the Bex queries in the data source, and except to specify overrides for key characteristics values.

The Cube Variables tab of the Cube Table dialog lists the cube variables for the given cube table, along with the cube variable caption. Double-click a cube variable to see more detailed information, or click the Add button to define a new cube variable.

Table 7-4 describes the properties of cube variables for SAP/BW data sources. See your SAP/BW documentation for additional information.

Table 7-4 Cube Variable Properties

Property Description

Name

Name of the cube variable.

Caption

A description (label or caption) associated with the cube variable, mainly used for display purposes.

Variable Type

The type of cube variable. Variable types include:

  • SAP_VAR_TYPE_MEMBER: A placeholder for a selection for MEMBER_UNIQUE_NAMES.

  • SAP_VAR_TYPE_HIERARCHY: A placeholder for a HIERARCHY_UNIQUE_NAME.

  • SAP_VAR_TYPE_NUMERIC: A placeholder for a numeric value in formulas.

Selection Type

The selection type of the cube variable, for cube variables of type SAP_VAR_TYPE_MEMBER.

Selection types include:

  • SAP_VAR_SEL_TYPE_VALUE: The variable is replaced by a single value. Cube variables of type NUMERIC must have this selection type.

  • SAP_VAR_SEL_TYPE_INTERVAL: A placeholder for an interval.

  • SAP_VAR_SEL_TYPE_COMPLEX: A placeholder for a complex selection.

Entry Type

Indicates whether replacing variables is optional or mandatory. Entry types include:

  • SAP_VAR_INPUT_TYPE_OPTIONAL: Specifying a value is optional for this variable.

  • SAP_VAR_INPUT_TYPE_MANDATORY: You must specify a value for this variable.

  • SAP_VAR_INPUT_TYPE_MANDATORY_NOT_INITIAL: You must specify a value for this variable. An initial field is not a valid entry.

Reference Dimension

This column contains a DIMENSION_UNIQUE_NAME for the parameter type SAP_VAR_TYPE_HIERARCHY.

Reference Hierarchy

This column contains a HIERARCHY_UNIQUE_NAME for the variable type SAP_VAR_TYPE_MEMBER.

Default Low

This property contains a default value for the variable or is zero.

Default High

This property contains a default value for the variable or is zero. This property is only important for variables with the selection type SAP_VAR_SEL_TYPE_INTERVAL and SAP_VAR_SEL_TYPE_SELECTION.

Override Default Low

Provide a default value for the cube variable in this field if the Default Low is zero.

You must specify a value for this property for mandatory variables that do not specify a default value.

Override Default High

Provide a default value for the cube variable in this field if the Default High is zero.

You must specify a value for this property for mandatory variables that do not specify a default value.


Viewing Data in Physical Tables or Columns

You can view the data in a physical table or an individual physical column by right-clicking the object and choosing View Data. In online editing mode, you must check in changes before you can use this option.

View Data is not available for physical cube tables or columns. See "Viewing Members in Physical Cube Tables" for more information.

Because the View Data feature issues a row count, it is not available for data sources that do not support row counts. See "Displaying and Updating Row Counts for Physical Tables and Columns" for more information.

Caution:

View Data does not work in online mode if you set the user name and password for connection pools to :USER and :PASSWORD. In offline mode, the Set values for variables dialog appears so that you can populate :USER and :PASSWORD as part of the viewing process.

Working with Essbase Data Sources

This section describes how Essbase data is modeled by default in the Physical layer of the Oracle BI repository, and describes the tasks you can perform to model the data in different ways.

This section contains the following topics:

About Using Essbase Data Sources with Oracle Business Intelligence

When you import metadata from Essbase data sources, the cube metadata is mapped to the Physical layer in a way that supports the Oracle Business Intelligence logical model. Metadata that applies to all members of the dimension, such as aliases, are modeled as dimension properties by default. Level-based properties, such as outline sort/memnor information, are mapped as separate physical cube columns in the dimension.

The following physical column types are used for Essbase metadata:

  • Member Alias: Indicates an Alias column.

  • UDA: Indicates the column is a User Defined Attribute (UDA).

  • Outline Sort: Indicates the column is of memnor type, used for outline sorts in the logical layer. Imported at the lowest level of each dimension.

  • Attribute: Indicates the column is of attribute type, for attribute dimensions.

  • Other: The type is different than those listed, or unknown.

  • Ancestor Reference: References the ancestor of a dimension.

  • Member Key: Indicates the column is a member key.

  • Leaf: Indicates that the column is the lowest member of the hierarchy.

  • Root: Indicates that the column is the root member of the hierarchy.

  • Parent Reference: References the parent of a dimension.

The column types Outline Sort, Ancestor Reference, Member Key, Leaf, Root, and Parent Reference are used internally by the system and should not be changed.

Figure 7-1 shows Essbase data that has been imported into the Physical layer.

Figure 7-1 Essbase Data Modeled in the Physical Layer

Description of Figure 7-1 follows
Description of "Figure 7-1 Essbase Data Modeled in the Physical Layer"

There are different options in the Physical layer that let you control how you want to model certain types of metadata. Choose the option that best meets the needs of your user base. For example, many types of Essbase metadata are modeled as dimension properties by default in the Physical layer. This multidimensional structure works best with the new hierarchical reporting style introduced in the current release.

Alternatively, you can choose to flatten the Essbase metadata in the Physical layer for ease of use with the attribute-style reporting supported in previous releases of Oracle Business Intelligence.

The following list summarizes some of these modeling options:

  • Aliases. Aliases are modeled as dimension properties by default, but you can also choose to flatten them using the Create Columns for Alias Table feature. See "Working with Essbase Alias Tables" for more information.

  • UDAs. UDAs are modeled as dimension properties by default, but you can also choose to flatten them using the Create Columns for UDA feature. See "Modeling User-Defined Attributes" for more information.

  • Alternate Hierarchies. Alternate hierarchies are modeled as separate hierarchies by default, but you can choose to view them in as a single hierarchy using the Convert to single hierarchy view feature. See "Modeling Alternate Hierarchies" for more information.

  • Measure Hierarchies. By default, measures are imported as a single measure column that represents all the measures, but you can also choose to view each measure as an individual column using the Convert measure dimension to flat measures feature. See "Modeling Measure Hierarchies" for more information.

Note the following additional information about using Essbase data sources with Oracle Business Intelligence:

  • Substitution variables. Essbase substitution variables are automatically retrieved and populated into corresponding Oracle BI Server session variables. Depending on the scope of the Essbase variable, the naming convention for the Oracle BI Server variable is as follows:

    Server instance scope: server_name:var_name

    Application scope: server_name:app_name:var_name

    Cube scope: server_name:app_name:cube_name:var_name

    A single initialization block is also created in the repository for the Essbase variables. Set the appropriate refresh interval in the initialization block to reflect anticipated update cycles for Essbase variables.

  • Essbase Generations. Essbase Generations are mapped to physical level objects.

  • Time series functions. The Oracle BI Server time series functions AGO, TODATE, and PERIODROLLING are pushed down to Essbase to take advantage of the native capabilities of the Essbase server.

  • Database functions. You can use the database SQL functions EVALUATE and EVALUATE_AGGREGATE to leverage functions specific to Essbase data sources. See "Examples Using EVALUATE_AGGREGATE and EVALUATE to Leverage Unique Essbase Functions" for more information.

    Note that EVALUATE_PREDICATE is not supported for use with Essbase data sources.

  • Gen 1 levels. By default, Gen 1 levels are included when you drag and drop an Essbase cube or dimension from the Physical layer to the Business Model and Mapping layer. However, because Gen 1 levels are not usually needed for analysis, you can choose to exclude Gen 1 levels when you drag and drop Essbase objects to your business model. To do this, select Skip Gen 1 levels in Essbase drag and drop actions in the General tab of the Options dialog. See "Setting Preferences" for more information.

  • Hierarchy types. For Essbase data sources, all hierarchies are imported as Unbalanced by default. Review the Hierarchy Type property for each physical hierarchy and change the value if necessary. Supported hierarchy types for Essbase are Unbalanced, Fully balanced, and Value.

About Incremental Import

You can choose to incrementally import Essbase metadata. In other words, you can perform an initial import, and then import again. You might want to import incrementally when information in the data source has changed, or when your first import only included a subset of the metadata. Note the following about incremental import:

  • When you re-import metadata that already exists in the Physical layer, a message appears, warning you that your Physical objects will be overwritten.

  • If you delete data in the source, re-importing the metadata does not automatically perform the deletion in the Physical layer. Instead, you must manually delete the corresponding Physical objects.

  • If you rename an object in the source, the renamed object is imported as a new object. In this case, both the old object and the new (renamed) object are displayed in the Physical layer.

  • In general, customizations that you have performed on the Physical layer data, such as determining the alias column to use for display, are retained after an incremental import. If you want to revert to the default imported view, you must delete the existing Physical layer objects and then re-import the metadata.

Working with Essbase Alias Tables

Essbase cubes support the concept of aliases, which are alternate names for members or shared members. For example, the member name might be a product code (100), with a default alias for the product name (Cola) and an additional alias for the long name (Cherry Cola). Often, members have separate aliases for each user language to enable users to view member names in their own language.

In the Essbase cube, aliases are stored in alias tables that map a specific set of alias names to member names. Typically, a Default alias table exists for each cube.

This section contains the following topics:

Determining the Value to Use for Display

When you import metadata from Essbase into the Oracle BI repository, the Essbase cube table object in the Physical layer has a property that determines which value to display for members: the member name, the default alias name, or some other alias name. By default, the columns display the default alias name.

To change the value to display for members:

  1. In the Physical layer of the Administration Tool, double-click an Essbase cube table.

  2. In the General tab of the Cube Table dialog, choose the appropriate value for Display Column. You can select Member Name, or you can select Alias and then choose an alias table name from the drop-down list.

  3. Click OK.

Explicitly Defining Columns for Each Alias

Aliases are modeled as dimension properties in the Physical layer after import. If you want to work with more than one alias, such as when you want to flatten attributes for reporting purposes or externalize strings for translation, you can explicitly define columns for each alias. You can define alias columns at the cube, dimension, or hierarchy level.

To explicitly define columns for each alias:

  1. In the Administration Tool, in the Physical layer, right-click the cube table, physical dimension, or physical hierarchy for which you want to define alias columns.

  2. Select Create Columns for Alias Table. Then, from the sub-list, select the alias table for which you want to create columns.

    Note that the Fetch button is not used.

  3. Click Create.

  4. Drag the new alias columns to the appropriate location in the Business Model and Mapping layer.

If you want to externalize strings for translation based on the alias columns, see "Localizing Oracle Business Intelligence Deployments" in Oracle Fusion Middleware System Administrator's Guide for Oracle Business Intelligence Enterprise Edition for more information.

Modeling User-Defined Attributes

Essbase supports the concept of user-defined attributes (UDAs). A UDA is essentially any arbitrary textual string that can be associated with any member from a dimension. A member can have multiple strings associated to it.

You can choose whether to import UDAs in the Import Metadata Wizard. If you choose to import UDAs, then by default, each UDA is modeled as a dimension property in the Physical layer of the repository.

You can also choose to model each UDA as a separate physical column. To do this, perform one of the following tasks:

  • To model all UDAs in a cube as separate physical columns, right-click the cube table and select Create columns for UDA. All UDAs in the cube are modeled as separate physical columns.

  • To model all UDAs in a dimension as separate physical columns, right-click the dimension object and select Create columns for UDA, then select All UDAs. All UDAs in the dimension are modeled as separate physical columns.

  • To model a particular UDA in a dimension as a separate physical column in each level, right-click the dimension object and select Create columns for UDA, then select the specific UDA you want to model. The selected UDA is modeled as a separate physical column for each level.

Associating Member Attributes to Dimensions and Levels

Member attributes are not automatically associated to corresponding dimensions and levels during the import process. To manually create the association, map the member attribute to the appropriate logical table. In other words, drag and drop the columns from the attribute dimension in the Physical layer to the appropriate logical tables in the Business Model and Mapping layer.

Modeling Alternate Hierarchies

By default, alternate hierarchies are modeled as separate hierarchies in the Physical layer. You can choose to view them as separate hierarchies (called the multi-hierarchy view), or as a single hierarchy.

To view alternate hierarchies as a single hierarchy, right-click the dimension object containing the alternate hierarchies and select Convert to single hierarchy view. To return to the multi-hierarchy view, right-click the dimension object again and select Convert to multi-hierarchy view.

For example, Figure 7-2 shows the multi-hierarchy view for an alternate hierarchy.

Figure 7-2 Essbase Alternate Hierarchy Displayed in Multi-Hierarchy View

Description of Figure 7-2 follows
Description of "Figure 7-2 Essbase Alternate Hierarchy Displayed in Multi-Hierarchy View"

Figure 7-3 shows the single-hierarchy view for the same alternate hierarchy.

Figure 7-3 Essbase Alternate Hierarchy Displayed in Single-Hierarchy View

Description of Figure 7-3 follows
Description of "Figure 7-3 Essbase Alternate Hierarchy Displayed in Single-Hierarchy View"

Modeling Measure Hierarchies

By default, measures are imported as measure hierarchies. In other words, the cube contains a single measure column that represents all the measures.

Alternatively, you can choose to flatten the measure hierarchy to view each measure as an individual column. To do this, right-click the cube object and select Convert measure dimension to flat measures.

Improving Performance by Using Unqualified Member Names

When member names (including aliases) are unique in a given hierarchy, the Oracle BI Server can take advantage of specific MDX syntax to optimize performance. To enable this capability, select Use unqualified member name for better performance in the Hierarchy dialog.

The import process cannot identify that member names are unique for a given hierarchy, so it is the responsibility of the administrator to confirm uniqueness. Note that query errors may result if a hierarchy is specified as having unique members when it does not.

Note:

If you find that the Oracle BI Server is generating incorrect queries for Essbase, check to see whether there are duplicate member names in a given hierarchy. If there are, ensure that the option Use unqualified member name for better performance is not selected for that hierarchy, or perform the following steps:
  1. From the Essbase outline, update each offending member variable by adding a prefix or suffix to make the member name unique.

  2. Update SQL queries as necessary, if references are made to data within SQL.

  3. Reload the data and members in the Essbase outline.

Working with Hyperion Financial Management Data Sources

When you import data from Hyperion Financial Management data sources, both measures and dimensions are imported into the Physical layer. The Hyperion Financial Management hypercube model is exposed in the Physical layer in the following ways:

  • There is only one measure in Hyperion Financial Management, called Value. This measure is modeled as a single fact column in the Physical layer, also called Value.

  • The Value measure has three base properties: CellText, CurrencyType, and Attribute. These properties are all represented as additional fact columns.

  • The Attribute property has additional sub-properties, such as IsReadOnly. These properties are also exposed as additional columns.

Figure 7-4 shows how Hyperion Financial Management data is modeled in the Physical layer.

Figure 7-4 Hyperion Financial Management Metadata in the Physical Layer

Description of Figure 7-4 follows
Description of "Figure 7-4 Hyperion Financial Management Metadata in the Physical Layer"

All Hyperion Financial Management dimensions are modeled as parent-child hierarchies in the Physical layer. Shared members, alternate hierarchies, and unbalanced hierarchies are supported.

Dimension member properties are exposed as columns (such as Name, Description, ShortName, and so on). An additional column called Sort Order is also displayed for each dimension. This column contains custom sort information retrieved from the Hyperion Financial Management data source.

Each Hyperion Financial Management dimension has a corresponding Point of View (POV) value that provides customized information for different users. This POV value is exposed as the Default Member in the Hierarchies tab of the Dimension dialog. Although the Default Member field is populated upon import, note that the default values may need to be updated according to the needs of the user base.

Note:

Because the default member represents a POV value, do not select the Default member type ALL option for Hyperion Financial Management hierarchies. The default member should either be set to one of the values in the dimension, or it should be set to None.

About Query Support for Hyperion Financial Management Data Sources

Both member queries (dimensional browsing) and data queries (measure analysis) are supported for Hyperion Financial Management data sources.

Most Logical SQL functions are performed in the Oracle BI Server. However, you should use EVALUATE_PREDICATE to access the following functions specific to Hyperion Financial Management:

  • PeriodOffset (used to access prior or future periods through an offset)

  • NA Suppression functions specific to Hyperion Financial Management (SuppressDerived, SuppressInvalidIntersection, SuppressMissing, SuppressNoAccess, SuppressZero, SuppressError)

  • Base function (returns the leaf members below a given ancestor member)

  • CommonChildren

  • User-defined functions

See "EVALUATE_PREDICATE" for detailed information about syntax and usage.

Note that there is no native support for time series functions. Time series functions are only supported through data modeling.

Working with Dimensions and Hierarchies in the Physical Layer

Most dimensions and hierarchies are imported into the Physical layer from multidimensional data sources, rather than created manually. If a particular hierarchy is not imported, any columns associated with that hierarchy are also not imported. If users need access to columns that are not imported, first add these columns to the Physical layer by manually creating them, and then associate them with a level in a hierarchy.

Each level in a hierarchy has a level key. The first cube column associated with (added to) the level of a hierarchy is the level key. This must match with the data source definition of the cube. The icon for the column that you select first changes to the key icon after it is associated with the level of a hierarchy.

Oracle Business Intelligence supports unbalanced hierarchies for all multidimensional data sources. In general, you can configure unbalanced hierarchies in the Physical layer by changing the hierarchy type.

You can view and edit properties for physical dimensions and hierarchies by double-clicking physical dimension and physical hierarchy objects in the Physical layer of the Administration Tool. You can also view and edit these objects from the Dimensions and Hierarchies tabs of the Cube Table dialog.

This section contains the following topics:

Working with Physical Dimension Objects

In the Physical Dimension dialog, you can view and edit the name and description of the dimension. You can also add, remove, or edit hierarchies for that dimension, as well as add, remove, or edit columns that represent dimension properties.

Working with Physical Hierarchy Objects

When you select columns to add to a hierarchy, it is recommended that you select them in hierarchical order, starting with the highest level. If you select multiple columns and bring them into the hierarchy at the same time, the order of the selected group of columns remains the same. After adding columns to the hierarchy, you can change the order of the columns in the Browse dialog.

If a query does not explicitly refer to a member of a hierarchy, a default member must be used. Therefore, every hierarchy must be associated with a default member, typically the ALL member. The Hierarchy dialog contains an option (Default member type ALL) that you use when you want to designate the ALL member as the default. Typically, this item is automatically selected when you import the hierarchy. Note that the ALL member is not an appropriate default for some hierarchies. For example, for the Year level, 1997 might be designated as the default member.

In the Physical Hierarchy dialog, you can view and edit the name and description of the hierarchy, along with the properties described in Table 7-5. For level-based hierarchies, you can add, remove, edit, or reorder levels. For value-based hierarchies, click the Column tab to add, remove, or edit columns. To specify a key column, double-click a column name.

In the Physical Level dialog, you can view and edit the name, external name, and description of the level. You can also add, remove, or edit columns for that level. To designate a column as a level key, double-click a column name.

Table 7-5 Options in the Physical Hierarchy Dialog

Property Description

External Name

The physical name that is used when referencing the hierarchy in physical SQL queries. This value must reflect the external name defined in the data source.

Dimension Name

(Dimension Unique Name) Dimension to which the hierarchy belongs.

Dimension Type

Identifies whether this hierarchy belongs to a time dimension, measure dimension, or other type of dimension.

Hierarchy Type

Identifies whether the hierarchy is Fully Balanced, Unbalanced, Ragged Balanced, Network, or Value.

Default member type ALL

Select this option to designate the ALL member as the default. Do not select this option for non-ALL default members.

Use unqualified member name for better performance

Select this option when member names (including aliases) are unique in a given hierarchy so that the Oracle BI Server can take advantage of specific MDX syntax to optimize performance.


Viewing Members in Physical Cube Tables

You can view members of hierarchies or levels in the Physical layer of repositories. To view members, the repository must be open in online mode. The list of members by level in the hierarchy can help you determine if the connection pool is set up properly. You might want to reduce the time it takes to return data or the size of the returned data by specifying a starting point (Starting from option) and the number of rows you want returned (Show option).

To view members:

  1. Open the Administration Tool in online mode.

  2. In the Physical layer, right-click a hierarchy or level.

  3. Select View Members.

    A window opens showing the number of members in the hierarchy and a list of the levels. You might need to enlarge the window and the columns to view all the returned data.

  4. Click Query to display results.

  5. When finished, click Close.

Adding or Removing Cube Columns in a Hierarchy

After importing a hierarchy, you may need to add or remove a column. If you remove a cube column from a hierarchy, it is deleted from the hierarchy but remains in the cube table and is available for selection to add to other levels.

To add or remove a cube column in an existing hierarchy:

  1. In the Physical layer of the Administration Tool, double-click the physical hierarchy that you want to change. The Physical Hierarchy dialog appears.

  2. For level-based hierarchies, double-click the level for which you want to add or remove columns. Then, in the Physical Level dialog, you can add, remove, or edit columns. When you are finished, click OK in the Physical Level dialog.

  3. For value-based hierarchies, click the Columns tab. You can add, remove, or edit columns, as well as designate member key and parent key columns.

  4. Click OK in the Hierarchy dialog.

Working with Physical Foreign Keys and Joins

You can create physical foreign keys and complex joins using either the Physical Diagram, or the Joins Manager. Note that you do not create joins for multidimensional data sources.

This section contains the following topics:

About Physical Joins

All valid physical joins need to be configured in the Physical layer of the Administration Tool.

When you import keys in a physical schema, the primary key-foreign key joins are automatically defined. Any other joins within each data source or between data sources have to be explicitly defined to express relationships between tables in the Physical layer.

Imported key and foreign key joins do not have to be used in metadata. Joins that are defined to enforce referential integrity constraints can result in incorrect joins being specified in queries. For example, joins between a multipurpose lookup table and several other tables can result in unnecessary or invalid circular joins in the SQL queries issued by the Oracle BI Server.

This section contains the following topics:

About Primary Key and Foreign Key Relationships

A primary key and foreign key relationship defines a one-to-many relationship between two tables. A foreign key is a column or a set of columns in one table that references the primary key columns in another table. The primary key is defined as a column or set of columns where each value is unique and identifies a single row of the table.

Note that there are two cases where multiple foreign key columns in a table point to the same table:

  • When the primary key of the foreign table is "concatenated," meaning that it consists of a set of columns. This is a single join between two tables that happens to use multiple columns.

  • When you have created an alias to the foreign table, because the foreign table needs to serve in different roles. In this case, each foreign key joins to a primary key in one role-playing alias or the other. See"About Physical Alias Tables" for more information.

You can specify primary key and foreign keys in the Physical Diagram, or by using the Keys and Foreign Keys tabs of the Physical Table dialog. Also refer to "Defining Physical Joins with the Physical Diagram" and "Creating and Managing Columns and Keys for Physical Tables" for more information.

About Complex Joins

In the Physical layer of the repository, complex joins are joins over nonforeign key and primary key columns. In other words, physical complex joins are joins that use an expression rather than key column relationships. When you create a complex join in the Physical layer, you specify the expression for the join.

For most data sources, foreign key joins are preferred for performance reasons. Complex joins are usually not as performant because they do not use key column relationships to form the join. The exception is ADF business component data sources, which use physical complex joins exclusively to denote ViewLink instances that connect pairs of View Objects in the ADF model.

About Multi-Database Joins

A multi-database join is defined as a table under one metadata database object that joins to a table under a different metadata database object. You need to specify multi-database joins to combine the data from different databases. Use the Physical Diagram to specify multi-database joins. See "Defining Physical Joins with the Physical Diagram" for more information.

Multi-database joins can be created between tables in most types of databases and are performed within the Oracle BI Server.

While the Oracle BI Server has several strategies for optimizing the performance of multi-database joins, these joins are significantly slower than joins between tables within the same database. For this reason, you should avoid them whenever possible.

About Fragmented Data

Fragmented data is data from a single domain that is split between multiple tables. For example, a data source might store sales data for customers with last names beginning with the letter A through M in one table and last names from N through Z in another table. With fragmented tables, you need to define all of the join conditions between each fragment and all the tables to which it relates. Figure 7-5 shows the physical joins with a fragmented sales table and a fragmented customer table where they are fragmented the same way (A through M and N through Z).

Figure 7-5 Fragmented Tables Example

This image is described in the surrounding text.
Description of "Figure 7-5 Fragmented Tables Example"

In some cases, you might have a fragmented fact table and a fragmented dimension table, but the fragments might be across different values. In this case, in addition to the joins created in Figure 7-5, you need to define a one-to-many join from Customer A to F and from Customer G to Z to Sales A to M, as shown in Figure 7-6.

Figure 7-6 Joins for Fragmented Tables Example

This image is described in the surrounding text.
Description of "Figure 7-6 Joins for Fragmented Tables Example"

Note:

Avoid adding join conditions where they are not necessary (for example, between Sales A to M and Customer N to Z in Figure 7-5). Extra join conditions can cause performance degradations.

Defining Physical Joins with the Physical Diagram

You can define foreign keys and complex joins between tables, whether or not the tables are in the same data source. When you use the Physical Diagram to create joins, the Administration Tool determines what type of join to create based on the selected object types and the join expression.

If you do not want the Administration Tool to automatically determine what type of join to create, use the Joins manager to explicitly create the join. See "Defining Physical Joins with the Joins Manager" for more information.

To define a physical foreign key join or a complex join with the Physical Diagram:

  1. In the Physical layer of the Administration Tool, select one or more tables and choose one of the Physical Diagram commands from the right-click menu.

  2. Click the New Join button on the Administration Tool toolbar.

  3. In the Physical Diagram, left-click the first table in the join (the table representing one in the one-to-many join) to select it.

  4. Move the cursor to the table to which you want to join (the table representing many in the one-to-many join), and then left-click the second table to select it.

    The Physical Foreign Key dialog appears. Although physical foreign key joins are the default join type, the object type might change to a complex join after you define the join and click OK, depending on the join information.

  5. Select the joining columns from the left and the right tables.

    The SQL join conditions appear in the expression pane of the window.

    The driving table is shown on this window, but it is not available for selection because the Oracle BI Server implements driving tables only in the Business Model and Mapping layer. See "Specifying a Driving Table" for more information about driving tables.

  6. For complex joins, you can optionally set the cardinality for each side of the join (for example, N, 1, 0,1, or unknown).

    To set the cardinality to unknown, you only need to select unknown for one side of the join. For example, choosing unknown-to-1 is equivalent to unknown-to-unknown and appears as such the next time you open the dialog for this join.

  7. If appropriate, specify a database hint. See "Using Hints" for more information.

  8. If you are creating a complex join for ADF Business Component ViewObject or ViewLink instances, specify the ViewLink instance name or the ViewLink definition name in the ViewLink Name field.

  9. To open Expression Builder, click the button to the right of the Expression pane. The expression displays in the Expression pane.

    The default join expression for ViewObject or ViewLink instances is arbitrary and has no meaning.

  10. Click OK to apply the selections.

Defining Physical Joins with the Joins Manager

You can use the Joins Manager to view join relationships and to create physical foreign key joins and complex joins.

To define a physical foreign key join or complex join with the Joins Manager:

  1. In the Administration Tool toolbar, select Manage, then select Joins.

  2. In the Joins Manager dialog, perform one of the following tasks:

    • Select Action > New > Complex Join.

      The Complex Join dialog appears.

    • Select Action > New > Physical Foreign Key. Then, in the Browse dialog, double-click a table.

  3. In the Complex Join or Physical Foreign Key dialog, type a name for the join.

  4. Click the Browse button for the Table field on the left side of the dialog, and then locate the table that the foreign key references.

  5. Select the columns in the left table that the key references.

  6. Select the columns in the right table that make up the foreign key columns.

  7. For complex joins, you can optionally set the cardinality for each side of the join (for example, N, 1, 0,1, or unknown).

    To set the cardinality to unknown, you only need to select unknown for one side of the join. For example, choosing unknown-to-1 is equivalent to unknown-to-unknown and appears as such the next time you open the dialog for this join.

  8. If appropriate, specify a database hint. See "Using Hints" for more information.

  9. If you are creating a complex join for ADF Business Component ViewObject or ViewLink instances, specify the ViewLink instance name or the ViewLink definition name in the ViewLink Name field.

  10. To open Expression Builder, click the button to the right of the Expression pane. The expression displays in the Expression pane.

    The default join expression for ViewObject or ViewLink instances is arbitrary and has no meaning.

  11. Click OK to save your work.

Deploying Opaque Views

An opaque view is a Physical layer table that consists of a SELECT statement. When you need a new table, you should create a physical table or a materialized view. An opaque view should be used only if there is no other solution. See "Exchanging Metadata with Databases" in Oracle Fusion Middleware System Administrator's Guide for Oracle Business Intelligence Enterprise Edition for more information about materialized views.

This section contains the following topics:

About Deploying Opaque Views

In the repository, opaque views appear as view tables in the data source, but the view does not actually exist until you deploy it. You deploy an opaque view in the data source using the Deploy Views utility. After deploying an opaque view, it is called a deployed view. Opaque views can be used without deploying them, but the Oracle BI Server has to generate a more complex query when an opaque view is encountered.

Note:

Data sources such as XLS and nonrelational data sources do not support opaque views and cannot run the view deployment utility.

To verify that opaque views are supported by a data source, check whether the CREATE_VIEW_SUPPORTED SQL feature is selected in the Database dialog, in the Features tab. See "Specifying SQL Features Supported by a Data Source" for instructions.

Deploying Opaque View Objects

In offline mode, the Deploy Views utility is available when importing from data sources with ODBC and DB2 CLI data sources. Oracle Native (client) drivers are also supported in the offline mode for deploying views. In online mode, view deployment is available for supported data sources using Import through server (the settings on the client are ignored).

Using the Create View SELECT Statement

The SQL statement for deploying opaque views in the Physical layer of the repository is available for supported data sources. To determine which of your data sources support opaque views, contact your system administrator or consult your data source documentation.

Only repository variables can be used in the definition. An error is generated if a session variable is used in the view definition.

Syntax

CREATE VIEW view_name AS select_statement,

Where:

  • select_statement is the user-entered SQL in the opaque view object. If SQL is invalid, the create view statement fails during view deployment.

  • view_name is one of the two following formats: schema.viewname, or viewname. The connection pool settings determine if the schema name is added.

For opaque view objects, the right-click menu contains the Deploy View(s) option. When you select Deploy View(s), the Create View SQL statement executes and attempts to create the deployed view objects. The following list describes the ways you can initiate view deployment and the results of each method:

  • Right-click a single opaque view object. When you select Deploy View(s), the Create View SQL statement executes and attempts to create a deployed view for the object.

  • Right-click several objects. If at least one of the selected objects is an opaque view object, the right-click menu contains the Deploy View(s) option. When you select Deploy View(s), the Create View SQL statement executes and attempts to create the deployed views for any qualifying objects.

  • Right-click a physical schema or physical catalog. If any opaque view object exists in the schema or catalog, the right-click menu contains the Deploy View(s) option. When you select Deploy View(s), the Create View SQL statements for all qualifying objects execute and attempt to create deployed views for the qualifying objects contained in the selected schema or catalog.

During deployment, names are assigned to the views. If you change the preassigned name, the new name must be alphanumeric and no more than 18 characters. If these guidelines are not followed, the object name is automatically transformed to a valid name using the following Name Transform algorithm:

  1. All non-alphanumeric characters are removed.

  2. If there are 16 or more characters after Step 0, the first 16 characters are kept.

  3. Two digits starting from 00 to 99 are appended to the name to make the name unique in the corresponding context.

After the deployment process completes, the following occurs:

  • Views that have been successfully and unsuccessfully deployed appear in a list.

  • For unsuccessful deployments, a brief reason appears in the list.

  • If deployment is successful, the object type of the opaque view changes from Select to None and the deployed view is treated as a regular table.

    If you change the type back to Select, the associated opaque views are dropped from the data source, or an error message appears. See "When to Delete Opaque Views or Deployed Views" for information about deleting deployed views.

  • In the Administration Tool, the view icon changes to the deployed view icon for successfully deployed views.

To deploy an opaque view:

  1. In the Physical layer of the Administration Tool, right-click the opaque view that you want to deploy.

  2. In the right-click menu, select Deploy View(s).

  3. In the View Deployment - Deploy View(s) dialog, perform the following steps:

    1. In the New Table Name column, you can optionally change the new deployed view names.

      If the change does not conform to the naming rules, a new name is assigned and the dialog appears again so that you can accept or change it. This action repeats until all names pass validation.

    2. If you do not want to deploy one or more of the views, clear the appropriate rows.

  4. If there are multiple connection pools defined for the physical database, in the Select Connection Pool dialog, choose a connection pool and click Select.

    The SQL statement (CREATE VIEW) executes, and the View Deployment Messages dialog appears.

  5. In the View Deployment Messages dialog, you can search for views using Find and Find Again, or copy the contents.

  6. When you are finished, click OK.

Undeploying a Deployed View

Running the Undeploy Views utility against a deployed view deletes the views and converts the table back to an opaque view with its original SELECT statement.

To undeploy a deployed view:

  1. In the Physical layer of the Administration Tool, right-click a physical database, catalog, schema, or table.

    If a deployed view exists that is related to the selected object, the right-click menu contains the Undeploy View(s) option.

  2. Select Undeploy View(s).

    A list of views to be undeployed appears.

  3. If you do not want to undeploy one or more of the views, clear the appropriate rows.

  4. In the View Deployment - Undeploy View(s) dialog, click OK to remove the views.

    A message appears if the undeployment was successful.

  5. In the View Deployment Messages dialog, you can search for undeployed views using Find and Find Again, or you can copy the contents.

  6. When you are finished, click OK.

When to Delete Opaque Views or Deployed Views

Use the following guidelines to remove opaque or deployed view objects in the repository:

  • Removing an undeployed opaque view in the repository. If the opaque view has not been deployed, you can delete it from the repository.

  • Removing a deployed view. When you deploy an opaque view, a view table is created physically in both the data source and the repository. Therefore, you must undeploy the view before deleting it. You use the Undeploy Views utility in the Administration Tool. This removes the opaque view from the back-end data source, changes the Table Type from None to Select, and restores the SELECT statement of the object in the Physical layer of repository.

    Caution:

    Do not manually delete the view table in the data source. If deleted, the Oracle BI Server cannot query the view object. When you undeploy the view, it is removed automatically from the data source.

When to Redeploy Opaque Views

After removing an opaque view, you can choose to redeploy it. The Administration Tool does not distinguish between a first-time deployment and a redeployment. Make sure that you remove a deployed view before deploying the opaque view again. Failure to do this causes the deploy operation to fail, and an error message is returned from the data source.

Using Hints

Hints are instructions placed within a SQL statement that tell the data source query optimizer the most efficient way to execute the statement. Hints override the optimizer's execution plan, so you can use hints to improve performance by forcing the optimizer to use a more efficient plan. Hints are only supported for Oracle Database data sources.

Using the Administration Tool, you can add hints to a repository, in both online and offline modes, to optimize the performance of queries. When you add a hint to the repository, you associate it with Physical layer objects. When the object associated with the hint is queried, the Oracle BI Server inserts the hint into the SQL statement.

Table 7-6 shows the physical objects with which you can associate hints. It also shows the Administration Tool dialog that corresponds to the physical object. Each of these dialogs contains a Hint field, into which you can type a hint to add it to the repository.

Table 7-6 Physical Layer Objects That Accept Hints

Database Object Dialog

Complex join

Complex Join

Physical foreign key

Physical Foreign Key

Physical table

Physical Table - General tab


Hints are only supported when the Table Type is set to Physical Table. For other table types, the hint text is ignored. For physical tables with a table type of Select, you can provide the hint text as part of the SQL statement entered in the Default Initialization String field.

How to Use Oracle Hints

This section provides a few examples of how to use Oracle hints with the Oracle BI Server. For more information about Oracle hints, see Oracle Database SQL Language Reference for the version of the Oracle Database that you use.

About the Index Hint

The Index hint instructs the optimizer to scan a specified index rather than a table. Example 7-1 explains how you would use the Index hint.

Example 7-1 Index Hint

You find queries against the ORDER_ITEMS table to be slow. You review the execution plan of the query optimizer and find the FAST_INDEX index is not being used. You create an Index hint to force the optimizer to scan the FAST_INDEX index rather than the ORDER_ITEMS table. The syntax for the Index hint is index(table_name, index_name). To add this hint to the repository, go to the Physical Table dialog in the Administration Tool and type the following text in the Hint field:

index(ORDER_ITEMS, FAST_INDEX)

About the Leading Hint

The Leading hint forces the optimizer to build the join order of a query with a specified table. The syntax for the Leading hint is leading(table_name). If you were creating a foreign key join between the Products table and the Sales Fact table and wanted to force the optimizer to begin the join with the Products table, you would go to the Physical Foreign Key dialog in the Administration Tool and type the following text in the Hint field:

leading(Products)

About Performance Considerations for Hints

Hints that are well researched and planned can result in significantly better query performance. However, hints can also negatively affect performance if they result in a suboptimal execution plan. Follow these guidelines to create hints to optimize query performance:

  • You should only add hints to a repository after you have tried to improve performance in the following ways:

    • Added physical indexes (or other physical changes) to the Oracle Database.

    • Made modeling changes within the server.

  • Avoid creating hints for physical table and join objects that are queried often. If you drop or rename a physical object that is associated with a hint, you must also alter the hints accordingly.

Creating Hints

The following procedure explains how to add hints to the repository using the Administration Tool.

To create a hint:

  1. In the Administration Tool, go to one of the following dialogs:

    • Physical Table—General tab

    • Physical Foreign Key

    • Complex Join

  2. Type the text of the hint in the Hint field and click OK.

    For a description of available Oracle hints and hint syntax, see Oracle Database SQL Language Reference for the version of the Oracle Database that you use.

    Note:

    Although hints are identified using SQL comment markers (/* or --), do not type SQL comment markers when you type the text of the hint. The Oracle BI Server inserts the comment markers when the hint is executed.

Displaying and Updating Row Counts for Physical Tables and Columns

When you request row counts, the Administration Tool retrieves the number of rows from the data source for all or selected tables and columns (distinct values are retrieved for columns) and stores those values in the repository. The time this process takes depends upon the number of row counts retrieved.

When updating all row counts, the Updating Row Counts window appears while row counts are retrieved and stored. If you click Cancel, the retrieve process stops after the in-process table (and its columns) have been retrieved. Row counts include all tables and columns for which values were retrieved before the cancel operation.

Updating all row counts for a large repository might take a long time to complete. Therefore, you sometimes might want to update only selected table and column counts.

Row counts are not available for the following:

  • Stored Procedure object types

  • XML data sources and XML Server data sources

  • Multidimensional data sources

  • Data sources that do not support the COUNTDISTINCT function, such as Microsoft Access and Microsoft Excel, or data sources for which COUNT_STAR_SUPPORTED has been disabled in the database features table

  • In online mode, Update Row Count does not work with connection pools in which the session variables :USER and :PASSWORD are set as the user name and password.

    In offline mode, the Set values for variables dialog appears so that you can populate the session variables :USER and :PASSWORD.

  • In online mode, after importing or manually creating a physical table or column, the Oracle BI Server does not recognize the new objects until you check them in. Therefore, Update Row Count is not available in the menu until you check in these objects.

To display row counts in the Physical layer:

  1. In the Administration Tool, select Tools, then select Options.

  2. In the General tab of the Options dialog, select Show row count in physical view, and then click OK.

To update selected row counts in the Physical layer:

  1. In the Physical layer of the Administration Tool, right-click a single table or column. You can select multiple objects and then right-click.

  2. In the shortcut menu, select Update Row Count.

To update all row counts in the Physical layer:

  1. In the Administration Tool, select Tools, then select Update All Row Counts. If the repository is open in online mode, the Check Out Objects window might open.

  2. Click Yes to check out the objects.

    Any row counts that have changed since the last update are refreshed.