Defining project creation and display options

Architect provides various options that determine how you can create and modify projects. Reviewing and defining these options before using Architect can save you time when creating and modifying projects.

The options you can define determine how Architect displays data, automatically creates and maps schema objects, loads the Warehouse Catalog, and updates the project's schema. Some of these options are available in the MicroStrategy Architect Settings dialog box. For steps to access this dialog box, see Accessing the Architect options. The available options are described in the sections listed below:

• Controlling the view that is displayed when starting Architect

• Disabling the ability to add tables

• Automatically updating the project schema

• Creating metrics based on the facts of a project

You can also access various Architect options from the Architect toolbar, as described below:

• From the Home tab:

  • In the View area, click the arrow icon () to access the following configurations:

    • Displaying columns and attribute forms in tables

  • In the Auto Arrange area, click the arrow icon () to access the following configurations:

    • Defining the layout of the Architect working area

• From the Design tab:

  • In the Auto Recognize area, click the arrow icon () to access the following configurations:

    • Automating the creation of facts and attributes

    • Automatically mapping columns to existing attribute forms and facts

    • Automatically defining attribute relationships

  • In the Editors area, click the Edit logical size of tables icon (shown below) to access the Logical Size Editor, which provides access to the following configurations:

    

    • Defining logical sizes for tables

Accessing the Architect options

You can access the MicroStrategy Architect Settings dialog box from Architect. In Architect, from the Architect Button, select Settings. The MicroStrategy Architect Settings dialog box opens.

Controlling the view that is displayed when starting Architect

Architect allows you to choose whether the Project Tables View or the Hierarchy View is displayed at startup. You can configure this behavior using the Choose the default open view drop-down list. This drop-down list is available in the Configuration tab of the MicroStrategy Architect Settings dialog box. You can choose from the options listed below to determine what view is displayed when Architect opens:

• Last Used: Select this option to display the view that was used last during the most recent use of Architect.

• Project Tables View: Select this option to display the Project Tables View.

• Hierarchy View: Select this option to display the Hierarchy View.

Automating the creation of facts and attributes

You can save time during the schema creation process of designing a project by allowing Architect to automatically create attributes and facts. Architect can create attributes and facts automatically when you add tables to your project. The attributes and facts are created based on data types, database column names, primary and foreign keys, and other schema creation heuristics.

You can define how attributes and facts are created when tables are added to your project by defining the automatic column recognition rules. To access the options listed below, from the Design tab, in the Auto Recognize area, click the arrow icon ():

• Do not auto recognize: Select this option to disable the automatic creation of attributes and facts when tables are added to your project using Architect.

  This can be a good option to use if you are updating a project in which you have already defined the bulk of the project schema. In this scenario, it prevents Architect from automatically defining attributes and facts that might not be needed in the project. After adding extra tables to your project you can create any required attributes and facts in a way that fits your current project schema.

• Auto recognize: Select this option to enable the automatic creation of attributes and facts when tables are added to your project using Architect.

  This option can save time during the schema creation process of designing a project by allowing Architect to automatically create attributes and facts.

  When selecting this option, facts are created for database columns that use numeric data types and are not used for attribute forms. Attributes and attribute forms are created based on various schema creation heuristics and the rules that you define with the Advanced Options listed below:

  • Separator: Type the character used as a separator in your database column names. For example, a database column name such as USER_ID uses the underscore character (_) as a separator.

  • Attribute naming rule: Type database column name suffixes that identify that the column should be mapped to a new attribute as the identity form. For example, the suffix ID is commonly used for database columns that are mapped to attributes as an identity form.

    Use a semicolon (;) to separate suffixes that are to be mapped to new attributes.

    You can also define how the attribute name is created. Use the vertical bar (|) to define what the suffix is replaced with in the resulting attribute name. The text to the left of the | character is the suffix, and the text to the right of the | character is what replaces the suffix in the attribute name that is created.

    For example, including ID|; creates new attributes for any database columns that use the suffix ID, and removes the ID suffix from the attribute name. When a table that uses a column such as USER_ID is imported into the project, a new attribute named User is created. Including DT|DATE; creates new attributes for any database columns that use the suffix DT, and replaces the DT suffix with DATE when creating an attribute name. When a table that uses a column such as YEAR_DT is imported into a project, a new attribute named Year Date is created.

  • Attribute form naming rule: Type database column name suffixes that identify that the column should be mapped to a new attribute form. For example, the suffix DESC is commonly used for database columns that are mapped to description attribute forms.

    Use a semicolon (;) to separate suffixes that are to be mapped to new attribute forms.

    You can also define how the attribute form name is created. Use the vertical bar (|) to define what the suffix is replaced with in the resulting attribute form name. The text to the left of the | character is the suffix, and the text to the right of the | character is what replaces the suffix in the attribute form name that is created.

    For example, including DSC|DESC; creates new attribute forms for any database columns that use the suffix DSC, and replaces the DSC suffix with DESC when creating an attribute form name. When a table that uses a column such as PRODUCT_DSC is imported into a project, a new attribute form named Product Desc is created.

  • In addition to using these rules to define attributes and attribute forms, selecting the Auto recognize option also employs other schema creation heuristics:

    • The automatic column mapping rules described in Automatically mapping columns to existing attribute forms and facts, are employed to map columns to existing attribute forms that use the columns in their definitions.

    • An attribute is created for any column defined as a primary or foreign key, and the column name for the primary key is used to define the attribute name. The column name for the primary key is used to define the attribute name even if the primary key column is not included in the project.

    • Every column must be mapped to either a fact or an attribute. If none of the schema creation heuristics or the rules you define can determine whether to create a fact or attribute for the column, an attribute is created for the column.

  • Auto recognize form format: Select this check box to define when attribute form formats are applied to newly created attribute forms. Attribute form formats control how the form is displayed and how filters are defined. For example, specifying a format type of Big Decimal allows users to preserve precision when qualifying on a form with more than 15 digits. For more detailed information on each form format type, see Format types.

    You can define rules on when to apply attribute form formats by using the Options listed below:

    • Default form format: Select the form format to use for forms that do not fit any of the other form format rules. The default selection is Text. You cannot define the rules for when to use the default form format, as it is implicitly defined to be used when all other form format rules are not met.

    • Form format: Select a form format, and define the rules for applying the form format to newly created attribute forms. The rules for when to apply a certain form format are defined by the options listed below. If you supply neither a column data type nor a column naming rule for a form format, the form format is never automatically applied to a newly created attribute form.

      • Column data type: Select the column data types relevant to a form format. Only columns with one of the selected data types are considered to be defined as the associated form format. For example, the Number form format is defined to be used for columns with decimal, double, float, integer, numeric, and real data types by default. If you do not select relevant data types for a form format, only the column naming rules described below are used to determine when to apply the associated form format.

      • Naming rules: Type the column names relevant to a form format, separated by a semicolon (;). Only columns with one of the provided column names are considered to be defined as the associated form format. For example, the Picture form format is defined to be used for columns with Picture, Image, Icon, Drawing, Figure, Photo, and Print column names by default. If you do not type relevant column names for a form format, only the column data type rules described above are used to determine when to apply the associated form format.

        An attribute form can only have one form format. The rules for each form format should be defined so that they are all mutually exclusive. If an attribute form meets the rule requirements of more than one form format, the first form format in the Form format list is applied to the form.

• Display message if objects are not recognized: If this check box is selected, which it is selected by default, a message is displayed during the automatic creation of schema objects if some of these objects are not created. By clearing this check box, this message is never displayed.

Automatically mapping columns to existing attribute forms and facts

You can save time during the schema creation process of designing a project by allowing Architect to automatically map columns to attribute forms and facts already defined in your project. Architect can map columns to existing attribute forms and facts automatically when you add tables to your project.

You can enable the automatic mapping of columns to attribute forms and facts in your project when tables are added to your project by selecting the Use automatic column mapping check box. To access this option, from the Design tab, in the Auto Recognize area, click the arrow icon ().

When this option is enabled and tables are added to your project, the column expressions included in the table are compared to the column expressions used in attribute forms and facts. If an attribute form or fact is found that matches the column expression, then the column is mapped to that attribute form or fact. For example, the MicroStrategy Tutorial project maps the Revenue fact to the column TOT_DOLLAR_SALES. If automatic column mapping is enabled and you use Architect to add a table that includes the TOT_DOLLAR_SALES column to the project, the TOT_DOLLAR_SALES column for the table is automatically mapped to the Revenue fact.

The Use automatic column mapping option is particularly helpful to automatically map columns in newly added tables to existing facts and attributes, without creating any new facts or attributes. To map columns in newly added tables to existing facts and attributes, as well as create new facts and attributes based on various rules, you should enable the Auto recognize option, as described in Automating the creation of facts and attributes.

While enabling or disabling this option, you can also select or clear the Display message if objects are not recognized check box. If this check box is selected, which it is selected by default, a message is displayed during the automatic creation of schema objects if some of these objects are not created. By clearing this check box, this message is never displayed.

Displaying columns and attribute forms in tables

When you add tables to your project using Architect, you can view the various schema objects included in the table as well as the columns that are used to define the schema objects.

The display of data available in the tables included in your project can be defined using the options listed below.

To access the options listed below, from the Home tab, in the View area, click the arrow icon ().

• Physical Tables View area

• Visible Links area

Physical Tables View area

Prior to selecting options in the Project Tables view area, you must choose to display the data available in tables in either physical view or logical view. This can be defined using the following Architect toolbar options:

• Display table physical view: On the toolbar, select this option to display the columns that are available in the table. Columns are displayed in the form Column_Name : Column_Data_Type. For example, the YR_CATEGORY_SLS table from the MicroStrategy Tutorial project shown below is displayed in physical view:

  

  If you display tables in physical view, the options in the Project Tables View area have no affect on the display of data within the tables in Architect.

• Display table logical view: On the toolbar, select this option to display the columns that are available in the table and how they relate to MicroStrategy schema objects. The LU_YEAR and LU_REGION tables from the MicroStrategy Tutorial project shown below are used to illustrate the various logical view options available for displaying columns and attribute forms in tables.

  If you display tables in logical view, the options in the Project Tables View area can be used to modify the display of data within tables in Architect. These options are described below:

  • Display available columns on logical tables: Select this logical view option to display columns that are available in the table but are not used in any schema objects in the project. Columns are displayed in the form Column_Name : Column_Data_Type.

    In the LU_YEAR table shown above, selecting this option displays the PREV_YEAR_ID : INTEGER column which has not been mapped to a schema object.

    You can also display this information for an individual table by right-clicking a table, pointing to Properties, then pointing to Logical View, and selecting Display Available Columns.

    Selecting this option also allows you to select the option described below:

    • Display columns used for data internationalization: Select this option to display columns that are available in the table that used for data internationalization. Columns are displayed in the form Column_Name : Column_Data_Type.

      In the LU_REGION table shown above, selecting this option displays the various REGION_NAME columns that are used to translate the name of a region into various languages.

      For information on supporting internationalized data in a data source, see Supporting data internationalization.

  • Display attribute forms on logical tables: Select this option to display attribute forms that are mapped to columns of the table. Attribute forms are displayed in the form Attribute_Form_Name : Attribute_Form_Category (Column_Name).

    In the LU_YEAR table shown above, selecting this option displays the ID form and the Date form for the attribute Year.

    You can also display this information for an individual table by right-clicking a table, pointing to Properties, then pointing to Logical View, and selecting Display Attribute Forms.

Visible Links area

Maximum number of visible links per table row: Define the number of link lines that are displayed when you select a column, fact, attribute, or attribute form in a table. When selecting one of these objects in a table, a line is drawn to each occurrence of this object in other tables included in the project. For example, selecting the Year attribute in the LU_YEAR table displays a line that connects to every other occurrence of the Year attribute in other tables.

Disabling the ability to add tables

By default, Architect allows you the flexibility to browse the Warehouse Catalog to add new tables to your project. However, it can be beneficial to disable the ability to add tables to your project if your project includes all the required tables. This prevents any unnecessary tables from being added to the project, which can trigger the creation of unnecessary schema objects. It also provides better performance while using Architect since all the tables in the Warehouse Catalog do not have to be made available.

You can disable the ability to add new tables to your project using Architect by selecting the Disable loading warehouse catalog check box. This option is available in the Configuration tab of the MicroStrategy Architect Settings dialog box. Any tables not included in the project are hidden from view in Architect.

Automatically updating the project schema

Changes made in Architect affect the schema of your project. By default, the schema of your project is updated when you save your changes and exit Architect. This ensures that your project is updated to reflect your modifications.

Updating your project schema every time that you exit Architect can be disabled. The schema update process can require a substantial load on your Intelligence Server and require a considerable amount of time to complete. You may also have other project updates that you plan to perform after using Architect. In these scenarios, you can disable the project schema update process, and instead execute a schema update manually at the desired time. You can disable the project schema update process from occurring when closing Architect by clearing the Update schema after closing Architect check box. This option is available in the Configuration tab of the MicroStrategy Architect Settings dialog box.

Creating metrics based on the facts of a project

Architect allows you to create metrics based on the facts created for a project. This can reduce the time it takes to create the basic metrics for your project.

The options to create metrics based on the facts of your project are available in the Metric Creation tab of the MicroStrategy Architect Settings dialog box. On this tab, you can allow the automatic creation of metrics using the aggregation functions listed below:

• Avg: To create metrics that perform an average calculation on the fact expression.

• Sum: To create metrics that perform a summation calculation on the fact expression.

• Count: To create metrics that perform a count calculation on the fact expression.

• Min: To create metrics that perform a minimum calculation on the fact expression.

• Max: To create metrics that perform a maximum calculation on the fact expression.

• Var: To create metrics that perform a variance calculation on the fact expression.

• Advanced Options: Click Advanced Options to open the Advanced Options dialog box, which lets you define metric naming conventions. A metric naming convention can be defined for each aggregation function listed above. When a metric is created for an aggregation function, the metric naming convention for that aggregation function is used to define the name for the metric. You can use the characters %1 to insert the name of the fact into the metric name. For example, you can create the following metric naming convention for metrics created based on the Avg aggregation function.

  Average %1

  If you create a fact named Cost and select to create metrics with the Avg aggregation function, a metric with the name Average Cost is created by substituting the fact name for the %1 characters in the metric naming convention.

When a fact is created for a project, metrics are created for the fact using the selected aggregation functions. A separate metric is created to support each aggregation of a fact. The metrics are created in the Public Objects/Metrics folder of a MicroStrategy project.

Automatically defining attribute relationships

Architect allows you to create attribute relationships based on the design of the data in your data source. This can help reduce the time required to create the relationships between the attributes within a project.

To access the options to automatically create attribute relationships between the attributes within a project option, from the Design tab, in the Auto Recognize area, click the arrow icon (). You can select from the following options to automatically create attribute relationships:

• Do not automatically create relations: Attribute relationships are not automatically created based on the design of the data in your data source. For information on manually defining attribute relationships with Architect, see Defining attribute relationships.

• Automatically create relations in System Hierarchy: Attribute relationships are automatically created based on the design of the data in your data source as you add tables to your project. These attribute relationships are created automatically the first time you switch to Hierarchy View after an applicable attribute has been added to the project. To manually execute the action of automatically defining attribute relationships you can use the System Hierarchy dialog box, as described in Automatically defining attribute relationships.

  Attribute relationships are created based on the rules that you select in the Advanced Options, as described below:

  • Based on Primary Keys/Foreign Keys: Creates attribute relationships based on the primary keys and foreign keys defined on your tables. Each attribute that acts as a foreign key of a table is defined as a parent attribute of each attribute that acts as a primary key of the same table. The attribute relationship is defined as a one-to-many relationship from the foreign key attribute (parent attribute) to the primary key attribute (child attribute).

  • Based on lookup tables: Creates attribute relationships based on lookup tables that do not include primary key or foreign key information. To define a table as a lookup table for an attribute, see Creating attributes. Each attribute that defines a table as its lookup table is defined as a child attribute of all other attributes in the same table, that do not define the table as its lookup table. Each attribute relationship is defined as a one-to-many relationship from the parent attribute to the child attribute.

  • Based on sample data from the table: Creates attribute relationships for attributes that share the same lookup table. To define a table as a lookup table for an attribute, see Creating attributes.

    Architect analyzes sample data for the table. The attributes with fewer distinct values are defined as parents of the attributes with more distinct values, using a one-to-many relationship from the parent attribute to the child attribute. For example, a lookup table includes four rows of data, which include data related to year and quarter. Each row includes the same year (for example, 2009), but the quarter changes for each row (Q1, Q2, Q3, Q4). In this case, the Year attribute is created as a parent of the Quarter attribute.

  • Show preview result: Displays the attribute relationships that can be automatically created in a Results Preview dialog box. From this dialog box, you can select which attribute relationships should be created, and which attribute relationships should be excluded. This is the default behavior. If you clear the Show preview result check box, all of the potential attribute relationships are created automatically without first displaying them in the Results Preview dialog box.

    After all relationships are determined by the rules that you selected, Architect performs a final analysis on the attribute relationships that are to be created. Any attribute relationships that are found to be redundant are not created. This ensures that attribute relationships are created that properly reflect the design of the data in your data source. For information on modifying the attribute relationships that are created, see Defining attribute relationships.

• Display message if objects are not recognized: If this check box is selected, which it is selected by default, a message is displayed during the automatic creation of attribute relationships if some of these attribute relationships are not created. By clearing this check box, this message is never displayed.

Defining the layout of the Architect working area

Architect allows you to define the layout of the working area for the Project Tables View and the Hierarchy View in Architect using the auto arrange options on the Architect toolbar. You can also define the margins of this working area using the options listed below:

To access the options listed below, from the Home tab, in the Auto Arrange area, click the arrow icon ().

• Margin to the left: Type the number of pixels to use as a margin for the left side of the Architect working area. This provides white space at the left margin of the working area, which can provide visual separation between the working area and the other panes and options available on the Architect interface.

• Margin to the right: Type the number of pixels to use as a margin for the right side of the Architect working area. This provides white space at the right margin of the working area, which can provide visual separation between the working area and the other panes and options available on the Architect interface.

Defining logical sizes for tables

Architect allows you to view and define the logical sizes for the tables in a project using the Logical Size Editor. The logical table size determines which logical table to use to answer a query when multiple logical tables would provide the same data. The table with the lowest logical table size is used, as a lower logical table size means the table has a higher level of aggregation. The performance of accessing a table with a higher level of aggregation is usually better than accessing a table with a lower level of aggregation.

To access the options listed below, in the Editors area, click the Edit logical size of tables icon (shown below) to access the Logical Size Editor.

The Logical Size Editor displays several columns, as follows:

• Size locked: Displays whether a table's size is locked (selected) or unlocked (cleared). All tables have their size unlocked by default. When a table's logical size is locked the table is excluded from the logical table size calculation when a schema update is performed. This helps to retain any table sizes that are manually defined.

• Table name: Displays the name of each table in the warehouse.

• Size value: Displays the current size value for each table. Architect initially assigns logical table sizes based on an algorithm that takes into account the number of attribute columns and the various levels at which they exist in their respective hierarchies. See Defining logical table sizes for details on how table sizes are calculated.

  You can click in the Size value cell for a table to manually define the logical size for the table. You can then lock the table size for the table to ensure that this manual value is retained.

• Row count (optional): This column displays the number of rows in each table. Click the Display each table row count in the editor icon () to display the Row count column.