October 26, 2014 — Michael
Tips & Tricks # 14: How Attribute Roles Work in MicroStrategy
Attribute Roles
The support of attribute roles is among the most requested features in the schema/modeling area. It is the result of a common modeling practice where two or more attributes are defined using the same lookup table and column. When these attributes are placed on the same report, the result may return no data. To address this issue, a unique table name alias is required.
This Tips & Tricks post demonstrates a business scenario where two possible solutions are available in MicroStrategy:
- Explicit logical table aliasing can be used to generate correct report Structured Query Language (SQL).
- MicroStrategy SQL Generation Engine automatically detects tables where the same column is used to create two different attributes.
Business Case Scenario
The Airport Example
A user defines two attributes that have the same definition but play different roles in the business model. In this example, attribute Origin Airport and Destination Airport are defined using the same Lookup Table and Column (Airport_ID).
Both attributes share the same forms, or information about them (Description, Location, etc.). In the fact table, however, a separate column exists for each of their roles (Origin_Airport_ID and Destination_Airport_ID).
If the user places both attributes on a report to obtain the number of flights originated from ‘MIA’ and arrived at ‘LGA’, an empty result set is returned. The SQL statement tries to obtain the description of an airport that is both ‘MIA’ and ‘LGA’ at the same time (Airport_ID = “MIA” AND Airport_ID = “LGA”), thus generating an empty result set as shown below:
Example
NOTE: The SQL for this template is similar to the following:
Returning no data as explained previously:
SOLUTIONS
1. Explicit Table Alias Definition
The recommended way to model attribute roles in MicroStrategy is using explicit table aliases. This approach gives project architects direct control over the specific attributes to be treated as different roles, as well as their mappings to logical tables. This level of control can ensure consistent Engine behavior.
- In MicroStrategy Desktop Schema Objects/Tables folder, right-click on the Table and select ‘Create Table Alias’.
- A logical table alias is created for LU_AIRPORT as LU_AIRPORT (1). The alias name can be modified. In this example, the alias table is named ‘LU_AIRPORT_ALIAS’.
The two attributes, Origin Airport and Destination Airport, can each have its own lookup table.
In this example, the Destination Airport attribute uses LU_AIRPORT_ALIAS as the primary lookup table. Be sure to uncheck LU_AIRPORT as a source table so no join will be made.
Form Definition for the ID
Form Definition for the DESC
The Origin Airport attribute’s definition remains the same, using LU_AIRPORT as the source table for both of its forms. LU_AIRPORT_ALIAS is not checked as a source table.
Form Definition for the ID
Form Definition for the DESC
Report SQL
2. Automatic Attribute Role Recognition
A VLDB property is available at database instance level in the Query Optimizations folder, called “Engine Attribute Role Options.” The option is disabled by default. When enabled, the engine detects columns that support more than one attribute in the same table and automatically creates a separate alias in memory only for each attribute. These automatic aliases are not saved into the metadata and cannot be edited in MicroStrategy Desktop. Thus, while the engine attribute role option makes it more convenient to use attribute roles, it also represents a loss of control over the schema representation that is ultimately used for SQL generation. As a result, some logical schema designs may function incorrectly when using automatic role recognition.
Example
REPORT SQL
Enabling/Disabling ‘Automatic Attribute Role Recognition’
Attributes are considered candidates for the unique table name alias feature if their attribute forms are defined off the same column of a lookup table. Due to the specific nature of the modeling design described above, and that users may want to create two attributes of the same definition, automatic attribute role recognition is an optional setting.
The Very Large Database (VLDB) ‘Engine Attribute Role Options’ property can be found under Project Configuration/VLDB Properties/Query Optimizations folder in the VLDB Settings.
IMPORTANT NOTES:
1. Attribute roles are intended for lookup tables. Since automatic role recognition effectively splits a logical table into several virtual logical tables, the option should not be applied when a fact table supports more than one attribute on the same column. In that case, some levels of analysis will not be available (that is, reports may return the “Fact does not exist at a level that can support the requested analysis” error). In general there should be no need to have attribute roles on fact tables. However, if it is necessary in a specific scenario, explicit table aliases should be used.
2. The automatic detection option will not work if the attributes in the roles situation are in the same hierarchy, meaning that a child attribute is shared. In the example above, the two airport attributes do not have a common child attribute.
———————————————————————————
Excelent trick……