The Building Blocks of Business Data: Facts » Fact column names and data types: Column aliases

Fact column names and data types: Column aliases

A column alias specifies both the name of the column to be used in temporary tables and the data type to be used for the fact.

By default, the data type for a fact is inherited from the data type of the column on which the fact is defined in the data warehouse. However, there are cases where you may need to change this.

For example, you can define a fact to be the difference between two dates to perform a calculation such as the average number of days between a start and an end date. You could create this fact using the following expression:

ApplySimple("DateDiff(day,#0, #1)", [Start_Date_Id], [End_Date_Id])

The expression syntax is specific to your database type. This syntax is specific to Microsoft SQL Server. The SQL you create may be different.

The data type for this fact is automatically set to a Date data type because the Start_Date_ID and End_Date_ID have Date data types. However, the result of the calculation, that is, the difference between the two dates, is an integer.

This is used when a temporary SQL table needs to be created for the calculation. If you did not change the data type of the column alias, then the system uses a Date data type and tries to insert integer data into this column. This can cause an error for some database platforms. To avoid the possibility of an error due to conflicting data types, you should modify the column alias for the fact to change the default Date data type to an Integer data type.

The procedure below describes how to use the Fact Editor to create column aliases. You can create column aliases using Architect, which is described in Creating and modifying multiple facts.

Prerequisite

This procedure assumes you have already created a fact with a valid fact expression for which to create a new column alias.

To create a column alias for a fact

1 In MicroStrategy Developer, log in to the project source that contains the fact to create a new column alias for.
2 Right-click the fact and select Edit. If a message is displayed asking if you want to use read only mode or edit mode, select Edit and click OK to open the Fact Editor in edit mode so that you can make changes to the fact. The Fact Editor opens.
If you are only given the option of opening the Fact Editor in read only mode, this means another user is modifying the project’s schema. You cannot open the Fact Editor in edit mode until the other user is finished with their changes and the schema is unlocked.
For information on how you can use read only mode and edit mode for various schema editors, see Using read only or edit mode for schema editors.
3 Select the Column Alias tab.
4 In the Column alias area, click Modify. The Column Editor - Column Selection dialog box opens.
5 Select New to create a new column alias. The Column Editor - Definition dialog box opens.
6 You can modify the following properties for the column alias:
Column name: The name for the column alias which is used in any SQL statements which include the fact column.
Data type: The data type for the fact. For a description of the different data types supported by MicroStrategy, see Data Types.
Depending on the data type selected, you can specify the byte length, bit length, precision, scale, or time scale for your column alias. For a detailed description on each of these properties, see the MicroStrategy Developer Help (formerly MicroStrategy Desktop Help).
7 Click OK to save your changes and return to the Column Editor - Column Selection dialog box.
8 Click OK to save your changes and return to the Fact Editor.
9 Select Save and Close to save your changes.