Creating and modifying multiple facts

With Architect, you can create and modify multiple facts in your project quickly from an integrated interface. Architect allows you to create and modify facts in most of the same ways as the Fact Editor.

You cannot create fact level extensions using Architect. For information on how to create fact level extensions, see Modifying the levels at which facts are reported: Level extensions.

For conceptual information on facts as well as detailed examples, see The Building Blocks of Business Data: Facts. Refer to the list below for steps to perform various fact definitions using Architect:

• Modifying facts with the Properties pane

• Creating fact expressions

• Creating and modifying fact column names and data types: Column aliases

Modifying facts with the Properties pane

Once facts are created, you can modify and view fact definitions using the Properties pane in Architect. To view the various properties of a fact in Architect, from the Facts tab of the Properties pane, select the fact from the drop-down list. The Cost fact of the MicroStrategy Tutorial project shown below is used as an example of how you can modify and view facts using Architect.

When selecting a fact in Architect, the Properties pane allows you to modify and view facts as described below.

You can select a property in the Properties pane to view a description of the property. The description is displayed at the bottom of the Properties pane.

• Creating and modifying multiple attributes

• Creating and modifying multiple attributes

Defining and viewing fact definitions: Definition section

When you select a fact in Architect, the Definition section of the Properties pane displays the various properties for the fact. These properties and how to use them are described below:

• ID: The identifier of the fact. You cannot modify this value.

• Name: The name of the fact in the MicroStrategy project.

• Description: The description of the fact. A description can help explain the purpose of a fact in a project.

• Hidden: Specifies whether the fact is defined as hidden. Select the check box to set the value to True, which defines the table as hidden.

  Objects that are hidden are not displayed to a user unless the user has changed his or her Developer Preferences and selected the Display hidden objects check box. Therefore, defining an object as hidden does not necessarily prevent users from viewing or accessing an object. The best way to prevent users from viewing or accessing an object is to restrict the user permissions for it.

• Location: The location of the fact in a project.

Modifying fact expressions: Fact Expressions section

When you select a fact in Architect, the Fact Expressions section of the Properties pane displays the tables that the fact is included in, as well as the fact expression used for the fact in that table.

In the Cost fact example shown in Modifying facts with the Properties pane, TOT_COST is displayed as the fact expression for most of the tables. However, the Cost fact uses a derived fact expression in the ORDER_DETAIL table (derived fact expressions are described in Creating derived facts and fact expressions). The Cost fact also uses a different column name in the ORDER_FACT table (heterogeneous column naming is described in Creating facts with varying column names: Heterogeneous column names).

From the Properties pane, you can select a column mapped to a fact and click the ... (browse) button to open the Modify Fact Expression dialog box. From this dialog box, you can modify the fact expression.

Creating fact expressions

A fact expression represents a mapping to specific fact information in the data source. For conceptual information on fact expressions, see Mapping physical columns to facts: Fact expressions.

Fact expressions are commonly created by mapping a column to a fact, as described in Creating facts. With Architect, you can also create and define facts as listed below:

• Creating derived facts and fact expressions

• Creating facts with varying column names: Heterogeneous column names

Creating derived facts and fact expressions

A derived fact has its value determined by an expression that contains more than just a column in a table. Any operation on a column such as adding a constant, adding another column's values, or setting the expression to be an absolute value creates a derived fact. In other words, you are creating a fact from information that is available in the data warehouse.

For more information on derived facts and derived fact expressions, see Mapping physical columns to facts: Fact expressions. The procedure below describes how to create a derived fact using Architect, and follows the example scenario provided in Mapping physical columns to facts: Fact expressions.

To create a derived fact using Architect

1. In MicroStrategy Developer, log in to a project.

   For the example scenario, log in to the MicroStrategy Tutorial project.

2. From the Schema menu, select Architect.

3. If a message is displayed asking if you want to open Architect in read only mode or edit mode, select Edit and click OK to open Architect in edit mode so that you can make changes to the project. MicroStrategy Architect opens.

   If you are only given the option of opening Architect in read only mode, this means another user is modifying the project's schema. You cannot open Architect 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.

4. From the Project Tables View, locate and select a table that includes a column or columns to use in a fact definition.

   For the example scenario, select the ORDER_DETAIL table.

5. Right-click the table and select Create Fact. The Create New Form Expression dialog box opens.

6. From the Available columns pane, drag and drop a column into the Form expressionpane.

   For the example scenario, drag and drop the QTY_SOLD column to add it to the Form expression pane.

To complete the derived fact expression

A derived fact expression includes a combination of columns, numerical constants, and mathematical operators. The steps below continue the example scenario to provide a guideline of how to create derived fact expressions.

1. With the cursor in the Form expression pane, click * (the multiplication operator) to add it to the expression.

2. From the Available columns pane, double-click the UNIT_PRICE column to add it to the end of the fact expression.

3. Under Mapping method, select Automatic.

4. Click Validate to check whether the syntax of the expression is correct. The expression should appear as shown below:

   

5. Click OK. You are returned to Architect. The derived fact is given a default name and is displayed in the ORDER_DETAIL table.

   To change the default name of the fact, right-click the fact in the table and select Rename. Type the new name in the dialog box that appears and click OK to save and return to Architect.

6. From the Home tab, in the Save area, click Save and Update Schema to save your changes.

Creating facts with varying column names: Heterogeneous column names

In your warehouse, the same fact can access columns with different column names. MicroStrategy allows you to identify heterogeneous fact column names for each fact. With heterogeneous column names, you can refer the same fact to multiple columns with different column names and from different tables that identify the same quantitative value.

For more information on heterogeneous column names, see Mapping physical columns to facts: Fact expressions. The procedure below describes how to use Architect to create a fact with heterogeneous column names, and follows the example scenario provided in Mapping physical columns to facts: Fact expressions.

To create a fact with heterogeneous column names using Architect

1. In MicroStrategy Developer, log in to a project.

   For the example scenario, log in to the MicroStrategy Tutorial project.

2. From the Schema menu, select Architect.

3. If a message is displayed asking if you want to open Architect in read only mode or edit mode, select Edit and click OK to open Architect in edit mode so that you can make changes to the project. MicroStrategy Architect opens.

   • If you are only given the option of opening Architect in read only mode, this means another user is modifying the project's schema. You cannot open Architect 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.

4. From the Project Tables View, locate and select a table that includes a column or columns to use in a fact definition.

   For the example scenario, select the ORDER_FACT table. This is one of the tables in which a heterogeneous fact column for the Units Sold fact exists.

5. Right-click the table and select Create Fact. The Create New Form Expression dialog box opens.

6. From the Available columns pane, drag and drop a column into the Form expressionpane.

   For the example scenario, drag and drop the QTY_SOLD column to add it to the Form expression pane.

7. In the Mapping method area, select Automatic.

8. Click OK. You are returned to Architect and the derived fact appears in the ORDER_FACT table.

9. Right-click the new fact and select Edit. The Fact Editor opens and the fact expression you just created appears in the Expressions pane.

To add additional columns for heterogeneous facts

Now you must add the other heterogeneous fact column as separate expression for the fact.

1. Click New. The Create New Fact Expression dialog box opens.

2. From the Source table drop-down list, select the CITY_CTR_SALES table. This is the other table that contains a heterogeneous fact column for the Units Sold fact.

3. From the Available columns pane, double-click the TOT_UNIT_SALES column to add it to the Fact expression pane on the right.

4. In the Mapping method area, select Automatic.

5. Click OK. You are returned to the Fact Editor and the fact expression that you just created appears in the Expressions pane. Now the fact that you are creating maps correctly to its heterogeneous fact columns.

6. Click OK. You are returned to Architect. The fact is given a default name and is now displayed in the table.

   To change the default name of the fact, right-click the fact in the table and select Rename. Type the new name in the dialog box that appears and click OK to save and return to Architect.

7. From the Home tab, in the Save area, click Save and Update Schema to save your changes.

Creating and modifying 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.

For information on column aliases, see Fact column names and data types: Column aliases. The procedure below describes how to create a column alias using Architect.

Prerequisites

• This procedure assumes you have already created a fact with a valid fact expression.

To create a column alias for a fact using Architect

1. In MicroStrategy Developer, log in to the project source that contains the fact to create a new column alias for.

2. From the Schema menu, select Architect.

3. If a message is displayed asking if you want to open Architect in read only mode or edit mode, select Edit and click OK to open Architect in edit mode so that you can make changes to the project. MicroStrategy Architect opens.

   • If you are only given the option of opening Architect in read only mode, this means another user is modifying the project's schema. You cannot open Architect 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.

4. Right-click the fact and select Edit. The Fact Editor opens.

5. Select the Column Alias tab.

6. In the Column alias area, click Select. The Column Editor - Column Selection dialog box opens.

7. Select New to create a new column alias. The Column Editor - Definition dialog box opens.

8. You can modify the following properties for the column alias:

   • Column name: The name for the column alias. This name 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 the MicroStrategy Desktop Help).

9. Click OK to save your changes and return to the Column Editor - Column Selection dialog box.

10. Click OK. You are returned to the Fact Editor.

11. Click OK. You are returned to Architect.

12. From the Home tab, in the Save area, click Save and Update Schema to save your changes.