Modifying attribute data types: Column aliases

A column alias is a new data type that you can specify in place of the default data type for a given attribute form. Column aliases allow you to specify a more appropriate data type that can help avoid errors in your SQL.

They can also help you take more advantage of the data in your data warehouse. For attributes, a column alias performs the same function as it does for facts. By default, the data type for an attribute form is inherited from the data type of the column on which the form is defined. This inheritance is governed by MicroStrategy, which attempts to use a data type as similar as possible to the data type in your database or other data source (see Data Types for more information on how MicroStrategy selects a matching data type). However, there are cases where you may need to change the data type. The following are some examples of such cases.

In your data warehouse you have a lookup table for an Accounts attribute where the ID is Account Number and the ID is stored in the database as DECIMAL(18, 0). Because this column stores high-precision values, you must modify the column alias for the attribute form and map it to a special data type, Big Decimal. By doing so, the precision can be preserved when performing filtering, drilling, or page-by on the Account attribute.

Another example could be a case in which your warehouse does not have a lookup table for year information, but you would like to create a Year attribute. Many database platforms have functions that can extract parts of a date from a Date data type. For example, SQL Server has a Year function that extracts just the year from a date. In such a case, you can create a Year attribute using the following form expression:

ApplySimple("Year(#0)",[Date_Id])

The ApplySimple expression above is syntactically correct for SQL Server. However, depending on your database or data source type, you may need to use a different syntax.

The data type for this attribute is automatically set to a Date data type. This is because Date_ID is a Date data type. However, the result of the ApplySimple calculation is an integer value that represents a given year, such as 2011.

When a temporary SQL table is created, if you do not change the data type of the column alias, the system uses a Date data type and tries to insert integer data into this column. While this does not create a problem in all database platforms, some databases will return an error. To avoid the possibility of an error due to conflicting data types, modify the column alias for the attribute form and change the default Date data type to an Integer data type.

In addition to specifying the data type to be used for an attribute form, the column alias also lets you specify the column alias name to be used in the SQL generated by MicroStrategy. When you create a form expression using a custom expression or multiple columns (as discussed in Attribute form expressions), the column alias for the attribute form defaults to CustCol (or CustCol_1, CustCol_2, and so on). The following piece of SQL shows, in bold, where the column alias name is used:

SELECT Year(a12.Date_Id) CustCol_1,
			sum(a11.Tot_Dollar_Sales) WJXBFS1
FROM YR_CATEGORY_SLS a11
			cross join TRANS_DATE_LW_LY a12
GROUP BY Year(a12.Date_Id)

While the column alias name does not affect the actual results or your report, you can change the column alias name to be more meaningful. The above example is a simple one, but this can be useful for troubleshooting the SQL for a particularly complex report.

You can use the Attribute Editor to create column aliases. You can also use Architect to create column aliases, as described in Creating and modifying multiple attributes.

Prerequisite

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

To create a column alias for an attribute

1 In MicroStrategy Developer, log in to the project source that contains the attribute to create a new column alias for.
2 Right-click the attribute 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 Attribute Editor in edit mode so that you can make changes to the attribute. The Attribute Editor opens.
If you are only given the option of opening the Attribute Editor in read only mode, this means another user is modifying the project’s schema. You cannot open the Attribute 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 an attribute form and click Modify. The Modify Attribute Form dialog box opens.
4 Select the Column Alias tab.
5 In the Column alias area, click Modify. The Column Editor - Column Selection dialog box opens.
6 Select New to create a new column alias. The Column Editor - Definition dialog box opens.
7 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 attribute form column.
Data type: The data type for the attribute form. 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.
8 Click OK to save your changes and return to the Column Editor - Column Selection dialog box.
9 Click OK to save your changes and return to the Attribute Editor.
10 Select Save and Close to save your changes.