|
Oracle® Business Intelligence Server Administration Guide > Creating and Administering the Physical Layer in an Oracle BI Repository > Creating and Setting Up Physical Tables >
Creating and Administering Columns and Keys in a Physical Table
Each table in the Physical layer of the Administration Tool has one or more physical columns. The Columns, Keys, and Foreign Keys tabs in the Physical Table dialog box allow you 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. Opens the dialog box that corresponds to the tab.
- Edit. When you select an object and then click Edit, the dialog box that corresponds to the tab appears. You can then edit the object's properties.
- Delete. Deletes the selected object.
This section contains the following tasks:
About Measures in a Multidimensional Data Source
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 will be 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 box.
- 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 the Oracle BI Administrator assigns an aggregation rule of Aggr_External, the BI Server will bypass its internal aggregation mechanisms and use 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 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 database, 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.
- 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. The option Nullable in the Physical Columns dialog box 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.
- Data type. The data type list indicates the data type of the columns. Use caution in changing the data type values. 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 with 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 will pass these logical column data types to client applications.
NOTE: Except when stated otherwise, the characteristics and behavior of a physical cube column are the same as for other physical columns.
About Creating and Editing a Column With an Associated Column in an Alias Table
Creating and editing a column in a physical source table that has a corresponding column in an alias table, causes the following results: - Creating a Source Column. Creating a column in the physical source table, creates the same column on all its alias tables. Here are the steps involved:
- Deleting a Source Column. Deleting a column in the physical source table, deletes the same column on all its alias tables.
- Modifying a Source Column. Modifying a column in the physical source table, modifies the same column on all its alias tables.
To create or edit a physical column
- 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 choose New Object > Physical Column from the shortcut menu.
- To create a physical cube column for a multidimensional data source, right-click a physical cube table, and choose New Object > Physical Cube Column.
- To edit an existing physical column, double-click the physical column icon.
- In the Physical Column dialog box, type a name for the physical column.
For XML data sources, this field will store and display the unqualified name of a column (attribute) in an XML document.
- In the Type field, select a data type for the physical column.
- 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.
- Select the Nullable option if the column is allowed to have null values.
- 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).
- (Multidimensional data sources) When the physical cube column is a measure, in the Aggregation rule drop-down list, select the appropriate value.
NOTE: A new physical cube column is created as a measure by default. To change this, refer to Setting Up Hierarchies in the Physical Layer for a Multidimensional Data Source.
- Click OK.
Specifying a Primary Key for a Physical Table
Use the Physical Key dialog box to specify the column or columns that define the primary key of the physical table. To specify a primary key for a physical table
- In the Physical layer of the Administration Tool, right-click a physical table and choose Properties.
- In the Physical Table dialog box, click the Keys tab.
- In the Keys tab, click New.
- In the Physical Key dialog box, type a name for the key.
- Select the check box for the column that defines the primary key of the physical table.
- (Optional) In the Physical Key dialog box, type a description for the key, and then click OK.
Deleting a Physical Column For All Data Sources
You delete a physical column in the same way for all data sources. The following is a list of some results that occur: - Multidimensional data source. 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 a physical source table from which an alias table has been created, deletes the same column on all its alias tables.
To delete a physical column from the Physical layer
- In the Physical layer of the Administration Tool, locate the column that you want to delete.
- Right-click the column and choose Delete.
|
