Logical Columns

Many logical columns are automatically created by dragging tables from the Physical layer to the Business Model and Mapping layer. Other logical columns, especially ones that involve calculations based on other logical columns, can be created later.

Logical columns are displayed in a tree structure expanded out from the logical table to which they belong. If the column is a primary key column or participates in a primary key, the column is displayed with the key icon. If the column has an aggregation rule, it is displayed with a sigma icon. You can reorder logical columns in the Business Model and Mapping layer.

This section includes the following topics:

• Creating and Moving a Logical Column
• Setting Default Levels of Aggregation for Measure Columns
• Associating an Attribute with a Logical Level in Dimension Tables

• Creating and Moving a Logical Column

  ---

  Use the General tab to create or edit the general properties of a logical column. You can create a logical column object in the Business Model and Mapping layer, and then drag and drop it to the Presentation layer.

  About Sorting on a Logical Column

  For a logical column, you can specify a different column on which to base the sort. This changes the sort order of a column when you do not want to order the values lexicographically. Lexicographical sort arranges the results in alphabetic order such as in a dictionary. In this type of sort, numbers are ordered by their alphabetic spelling and not divided into a separate group.
  For example, if you sorted on month (using a column such as MONTH_NAME), the results would be returned as February, January, March, and so on, in lexicographical sort order. However, you might want months to be sorted in chronological order. Therefore, your table should have a month key (such as MONTH_KEY) with values of 1 (January), 2 (February), 3 (March), and so on. To achieve the desired sort, you set the Sort order column field for the MONTH_NAME column to be MONTH_KEY. Then a request to order by MONTH_NAME would return January, February, March, and so on.
  To create a logical column
  1. In the Business Model and Mapping layer, right-click a logical table.
  2. From the shortcut menu, select New Object > Logical Column.
  3. In the Logical Column dialog box, select the General tab.
  4. In the General tab, type a name for the logical column.
     The name of the business model and the associated logical table appear in the Belongs to Table field.
  5. (Optional) If you want to assign a different column on which to base the sort order for a column, perform the following steps:
     1. Next to the Sort order column field, click Set.
     2. In the Browse dialog box, select a column
     3. To view the column details, click View to open the Logical Column dialog box for that column, and then click Cancel.
        NOTE:  You can make some changes in this dialog box. If you make changes, click OK to accept the changes instead of Cancel.
     4. In the Browse dialog box, Click OK.
  6. (Optional) To remove the Sort order column value, click Clear.
  7. (Optional) If you want the logical column to be derived from other logical columns, perform the following steps:
     1. Select the check box for Use existing logical columns as source.
     2. Click the ellipsis button next to the text box to open the Expression Builder.
     3. In the Expression Builder - Derived logical column dialog box, specify the expression from which the logical column should be derived.
     4. Click OK.
  8. (Optional) In the Logical Column dialog box, type a description of the logical column.
     NOTE:  The type and length fields are populated automatically based upon the column's source.
  9. Click OK.
  To move or copy logical columns
  1. In the Business Model and Mapping layer, drag and drop a logical column to a different logical table.
     NOTE:  You can select multiple columns to move.
  2. In the Sources for moved columns dialog box, in the Action area, select an action.
  3. If you select Ignore, no logical source will be added in the Sources folder of the destination table.
  4. If you select Create new, a copy of the logical source associated with the logical column will be created in the Sources folder of the destination table.
  5. If you select Use existing, in the Use existing drop-down list, you must select a logical source from the Sources folder of the destination table.
     The column that you moved or copied will be associated with this logical source.
     
     

     Create Logical Columns

     To create a logical column, perform the following steps:

     1.	In the Physical layer, expand the Sales physical table.
     2.	Select the column AMOUNT_SOLD.
     3.	Drag the AMOUNT_SOLD column from the Physical layer to the Sales Facts logical table in the Business Model and Mapping layer. A new AMOUNT_SOLD logical column is added to the Sales Facts logical table.
     4.	Expand the Sources to see the logical table source that was created automatically when you dragged the AMOUNT_SOLD column from the Physical layer. Logical table sources define the mappings from a logical table to a physical table. A logical table’s Sources folder contains the logical table sources. Because you dragged a column from the Physical layer, the logical table source name, SALES, is the same name as the physical table. However, it is possible to change names in the Business Model and Mapping layer without impacting the mapping.
     5.	In the Business Model and Mapping layer, double-click the AMOUNT_SOLD logical column to open the Logical Column dialog box.
     6.	Click the Aggregation tab.
     7.	In the Default aggregation rule drop down list, select SUM.
     8.	Click OK to close the Logical Column dialog box. Notice that the logical column icon is changed to indicate an aggregation rule is applied.