Siebel Analytics Server Administration Guide > Creating and Administering the Physical Layer in a 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:

• Creating and Editing a Column in a Physical Table
• Specifying a Primary Key for a Physical Table
• Deleting a Physical Column For All Data Sources

Creating and Editing a Column in a Physical Table

If the column is imported, the properties of the column are set automatically.

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. If a measure is a calculated measure in Analysis Services, the aggregation rule will be reported as None. Therefore, it is recommended that you 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 aggregation rule value of None, contact the multidimensional data source administrator to verify that the value of the aggregation rule (Sum, Min, Max, and so on) 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 coincide with its definition in the multidimensional data source.

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

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 Physical Column from the shortcut menu.
   • To create a physical cube column for a multidimensional data source, right-click a physical cube table, select New Object, Physical Cube Column.
   • To edit an existing physical column, double-click the icon for the physical column that you want to edit.
2. 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.

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.

   The option Nullable in the Physical Columns dialog box indicates whether null values are allowed for the column. The data type list indicates the data type of the columns. This information is imported from the database when the column is imported to the Physical layer. It is generally safe to change a non-nullable value to a nullable value in a physical column. Making the value nullable allows null values to be returned to the user, which is expected with certain functions and with outer joins. Use caution in changing the data type values, however; 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 types of logical columns depend on the data types of physical columns which are their sources. The Siebel Analytics Server will furnish these logical column data types to client applications.

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 and multidimensional data sources. The External Name field stores and displays the fully qualified name of a column (attribute).
7. For 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, see Setting Up Hierarchies in the Physical Layer for a Multidimensional Data Source.

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

1. In the Physical layer of the Administration Tool, right-click a physical table and choose Properties.
2. In the Physical Table dialog box, click the Keys tab.
3. In the Keys tab, click New.
4. In the Physical Key dialog box, type a name for the key.
5. Select the check box for the column that defines the primary key of the physical table.
6. To add a column to the Columns list:
   1. Click Add.
   2. In the Browse dialog box, select the column, and then click OK.

      The column you selected appears in the Columns list of the Physical Key dialog box.

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

Deleting a Physical Column For All Data Sources

You delete a physical column in the same way for all data sources.

To delete a physical column from the Physical layer

1. In the Physical layer of the Administration Tool, locate the column that you want to delete.
2. Right-click the column and select Delete.