Attribute form expressions

Attributes act like holders of information and provide context for fact data. For example, the Customer attribute holds information about the customer such as Name and Address. These information units are called attribute forms. An attribute form expression defines what columns in the warehouse are used to represent the attribute form in SQL. Each attribute form must have at least one expression.

For example, the form expression for the Customer First Name attribute form is CUST_FIRST_NAME. The form expression for the Customer Last Name attribute form is CUST_LAST_NAME. In this instance, the CUST_FIRST_NAME and CUST_LAST_NAME columns in the warehouse provide information about first and last names respectively.

Although you can have multiple expressions in different tables, a form cannot have two different expressions in the same source table.

You can create expressions using attribute columns, constants, and/or mathematical operators, for example, +, -, /, *. Only implicit attributes do not include a column in the expression, since they only use the constants you declare.

You can also create a form expression using Apply functions. These functions are discussed in the Functions Reference.

The types of attribute form expressions are:

Simple expressions: Simple form expressions access data through columns in the data warehouse.
Derived expressions: Derived form expressions perform some type of mathematical calculation on columns in the data warehouse to create an attribute form.
Joining dissimilar column names: Heterogeneous mappings: Heterogeneous mappings allow you to use columns with different names in the data warehouse as the same attribute form.
Implicit expressions: Implicit form expressions do not relate directly to data stored in the data warehouse. These form expressions create virtual data by combining or using columns to generate the data.

Simple expressions

A simple expression is based on a single warehouse column. The definition of the simple expression includes the tables in which the column is found.

For example, Category is an attribute in the MicroStrategy Tutorial. It has two forms, ID and Description, both of which are defined by simple expressions. The expression for the ID form is the CATEGORY_ID column and the expression for the description form is the CATEGORY_DESC column. Both of these columns reside in the table LU_CATEGORY.

Example: creating an attribute form with a simple expression

A retailer begins a promotion that offers customers 25% off of their purchases if they fill out a feedback survey on the company website. The retailer intends to analyze the data gathered from the surveys to better market their products in the future.

The retailer's customers provide a variety of information on the surveys, including their dates of birth. Once gathered, the date of birth data eventually becomes part of the retailer's data warehouse and the appropriate lookup table is added to the retailer's project in MicroStrategy.

At this point, the project designer must add the column containing the customer dates of birth as an additional attribute form of the Customer attribute. This will enable report designers to display each customer's date of birth alongside each customer's name on reports.

Follow the procedure below to create Customer Birth Date as an attribute form in the Customer attribute.

You can also use Architect to create an attribute form with a simple expression, as described in Creating attributes.

To create an attribute form with a simple expression

In MicroStrategy Developer, log in to the project source that contains the MicroStrategy Tutorial project and then log in to MicroStrategy Tutorial.
Navigate to the Schema Objects folder, open the Attributes folder, and then the Customers folder.
Double-click the Customer attribute. 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.
Click New. The Create New Form Expression dialog box opens.
From the Source table drop-down list, select the LU_CUSTOMER table. This is the table that contains customers' dates of birth.
Double-click the CUST_BIRTHDATE column to add it to the Form expression pane on the right. The mapping method is selected as Automatic by default.
Click OK. The Create New Attribute Form dialog box opens.
In the Form general information area, in the Name field, type Customer Birth Date.
From the Category used drop-down list, select DATE since Customer Birth Date is neither the ID form of Customer nor the primary description form.
Click OK. The new Customer Birth Date attribute form is added to the Attribute form pane in the Attribute Editor.
Because this is only an example, close the Attribute Editor without saving your changes.

Derived expressions

Derived expressions are created using a combination of warehouse columns, mathematical operators, functions, and constants. While simple expressions have their value determined by just one column in a warehouse table, derived expressions are defined using one or more columns as well as other operators and values. Any operation on a column (such as adding a constant, adding another column, or setting the expression to be an absolute value) creates a derived expression.

For example, you can create a derived attribute to calculate age or anniversaries. By calculating the difference between the columns Date of Birth and Current Date, you can create an attribute to hold the age of a customer or an employee that has been derived from the two columns. By creating an attribute to calculate age in this manner, the attribute's value is automatically updated as the age changes. If you created an attribute for age in which you included a constant number, the attribute would need to be updated every time a customer or an employee has a birthday.

Example: creating an attribute form with a derived expression

In your database, you store Customer names in two different columns, CUST_FIRST_NAME and CUST_LAST_NAME. However, you want reports to display a customer's first name and last name together as a single entry on a report. You can achieve this using a derived form expression Name, which consists of the two strings. Using the Customer attribute, the syntax of the derived expression for Name reads:

CUST_FIRST_NAME + " " + CUST_LAST_NAME

On a report, this information is displayed as Mary Jones under the Name column. As another example, you could create a derived expression for Name in the format of Last Name, First Name using the following syntax:

CUST_LAST_NAME + ", " + CUST_FIRST_NAME

Using this expression, the information is displayed as Jones, Mary under the Name column.

Calculations and functions used in a derived expression can assist in deriving data from the database, but you must make sure you use expressions that meet the requirements of your database-specific SQL syntax. If you use syntax that is not supported by your database or other data source, the SQL query and resulting report can fail.

You can also use Architect to create an attribute form with a derived expression, as described in Creating and modifying multiple attributes.

To create an attribute form with a derived expression

In MicroStrategy Developer, log in to the project source that contains the MicroStrategy Tutorial project and then log in to MicroStrategy Tutorial.
Navigate to the Schema Objects folder, open the Attributes folder, and then the Customers folder.
Double-click the Customer attribute. 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.
Click New. The Create New Form Expression dialog box opens.
From the Source table drop-down list, select the LU_CUSTOMER table. This is the table that contains customers' first and last names.
Double-click the CUST_LAST_NAME column to add it to the Form expression pane on the right.
In the Form expression pane, place the cursor to the right of [CUST_LAST_NAME] and type + ", " +.
Double-click the CUST_FIRST_NAME column to add it to the Form expression pane on the right. Your expression should be defined as shown below.
Select Automatic as the mapping method.
Click OK. The Create New Attribute Form dialog box opens.
In the Form general information area, in the Name field, type Last Name, First Name.
From the Category used drop-down list, select None since Last Name, First Name is neither the ID form of Customer nor the primary description form.
Click OK. The new attribute form is added to the Attribute form pane in the Attribute Editor.
Because this is only an example, close the Attribute Editor without saving your changes.

Joining dissimilar column names: Heterogeneous mappings

Heterogeneous mappingallows Intelligence Server to perform joins on dissimilar column names. If you define more than one expression for a given form, heterogeneous mapping automatically occurs when tables and column names require it.

Because different source systems may store information in various contexts, your company may have multiple columns in different tables that all represent the same business concept. For example, in the MicroStrategy Tutorial, the ID form of the attribute Day contains two expressions. The Day_Date column occurs in the LU_DATE table and the Order_Date column occurs in the ORDER_DETAIL and ORDER_FACT tables.

In the above example, you can use heterogeneous mapping to map the Day attribute to all of the columns in the data warehouse that represent the same concept of Day. You can map Order_Date and Day_Date to the Day attribute. This ensures that both columns are used when information about the Day attribute is displayed on a report.

Each expression is linked to a set of source tables that contain the columns used in the expression. Of all the tables in which the columns exist, you can select as many or as few as you want to be used as part of the attribute's definition.

In the Attribute Editor, you can view the chosen tables in the source Tables area to the right of the Form Expressions area.

The data types of columns used in a heterogeneous mapping for a given attribute must be identical or similar enough for your particular RDBMS to join them properly. For example, most databases cannot join a data type of Text to a data type of Number. However, depending on your database platform, you might be able to join columns with data types of Number and Integer.

You can also use Architect to create an attribute form with a heterogeneous column mapping, as described in Creating and modifying multiple attributes.

To create an attribute form with a heterogeneous column mapping

In MicroStrategy Developer, log in to the project source that contains the MicroStrategy Tutorial project and then log in to MicroStrategy Tutorial.
Navigate to the Schema Objects folder, open the Attributes folder, and then the Time folder.
Double-click the Day attribute. 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.
Click New. The Create New Form Expression dialog box opens.
From the Source table drop-down list, select the LU_DAY table.
Double-click the DAY_DATE column to add it to the Form expression pane on the right. The mapping method is selected as Automatic by default.
Click OK. The Create New Attribute Form dialog box opens.
Click New. The Create New Form Expression dialog box opens.
From the Source table drop-down list, select the ORDER_DETAIL table.
Double-click the ORDER_DATE column to add it to the Form expression pane on the right. The mapping method is selected as Automatic by default.
Click OK. The Create New Attribute Form dialog box opens.

Notice that there are now two expressions for the attribute form definition, both of which use different tables as the source of their information. You could continue this process to add as many heterogeneous columns as part of one attribute form as necessary.
In the Form general information area, in the Name field, type Date Example.
From the Category used drop-down list, select None since this is simply an example scenario.
Click OK. The new Date Example attribute form is added to the Attribute form pane in the Attribute Editor.
Because this is only an example, close the Attribute Editor without saving your changes.

Implicit expressions

While most attributes map directly to one or more physical columns in the warehouse, an implicit attribute is a virtual or constant attribute that does not physically exist in the warehouse. Such an attribute has an implicit expression, which is a constant value, although nothing is saved in an actual column. Implicit expressions are not defined by column names; they are defined by constants that you specify. Any constant is acceptable, for example, RushOrder="Yes". Some attribute definitions can be implied by the existence of a row in a certain table, rather than being defined in terms of columns. Implicit attributes are useful in analyzing and retrieving relevant information.

Implicit attributes are not commonly used, but are useful in special cases such as the scenario described below.

For example, the Rush Order attribute in MicroStrategy Tutorial is an example of an implicit attribute. This attribute can be used to display which orders are rush orders so you can better keep track of your shipments.

The Rush Order attribute is defined by the implicit expression "Y", which stands for Yes. This implicit expression is based on an order's existence in the RUSH_ORDER table, rather than being based on a column in a table. This is shown in the image below, which shows the RUSH_ORDER table and the ORDER_FACT table, the latter being the lookup table for the Order attribute.

The ORDER_FACT table includes more schema objects than are shown below, which are hidden as they are not important to this example.

In the RUSH_ORDER table shown above, the Order attribute and the Rush Charge fact both display the table column that they use for their expressions. You can identify Rush Order as an implicit attribute because it has no associated table column to define its implicit expression.

The Tutorial project also includes a Rush Orders by Call Center report, which displays the Rush Order attribute on a report. For each order that is a rush order, a "Y" is displayed in the Rush Order column, as shown below.

In the report shown above, the Rush Charge is also displayed to show additional shipping costs that were charged to send the order as a rush order.