Category Archives: Data Warehouse Design

Tips & Tricks # 14: How Attribute Roles Work in MicroStrategy

Airport_Gates

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:
  1. Explicit logical table aliasing can be used to generate correct report Structured Query Language (SQL).
  2. 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).

TN5800-072-0108A.gif

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).

TN5800-072-0108B.gif

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:

TN5800-072-0108D.gif

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.

  1. In MicroStrategy Desktop Schema Objects/Tables folder, right-click on the Table and select ‘Create Table Alias’.

     

  2. 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

TN5800-072-0108J.gif

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

TN5800-072-0108K.gif

Form Definition for the DESC

TN5800-072-0108L.gif

Report SQL

TN5800-072-0108M.gif

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

TN5800-072-0108G.gif

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.
———————————————————————————
Source: Jaime Perez, TN6197: How do Attribute Roles Work in MicroStrategy 8.x?, MicroStrategy Knowledgebase, ‎08-30-2001.

Tips & Tricks #13: Star Schema in MicroStrategy

Star Schema 1

A Star Schema is a design that contains only one lookup table for each hierarchy in the data model instead of having separate lookup tables for each attribute. With only a single lookup table for each hierarchy, the IDs and descriptions of all attributes in the hierarchy are stored in the same table. This type of structure involves a great degree of redundancy. As such, star schema are always completely denormalized. Let’s review the star schema above based on the MicroStrategy Tutorial data model.

The schema contains only two lookup tables, one for each hierarchy. LU_LOCATION stores the data for all of the attributes in the Location hierarchy, while LU_CUSTOMER stores the data for all of the attributes in the Customer hierarchy. As a result, star schemas contain very few lookup tables-one for each hierarchy present in the data model. Each lookup table contains the IDs and descriptions (if they exist) for all of the attribute levels in the hierarchy.

Even though you have fewer tables in a star schema than a snowflake, the tables can be much larger because each one stores all of the information for an entire hierarchy. When you need to query information from the fact table and join it to information in the lookup tables, only a single join is necessary in the SQL to achieve the desired result.

Joins in a Star Schema

As an example, if you run the same report to display customer state sales, only one join between the lookup and fact table is required to obtain the result set as illustrated below.

Star Schema 2

To join the Customer State description (Cust_State_Desc) to the Sales metric (calculated from Sales_Amt) requires only one join between tables since the Customer State ID and description are both stored in the LU_CUSTOMER table. As a result, the query has to access only one lookup table to obtain all of the necessary information for the report.

Even though achieving this result set requires only a single join, star schemas do not necessarily equate to better performance. Depending on the volume of data in any one hierarchy, you may be joining a very large lookup table to a very large fact table. In such cases, more joins between smaller tables can yield better performance.

Characteristics of a Star Schema

The following is a list of characteristics of a star schema.

  • Contains fewer tables (one per hierarchy)
  • Contains very large tables (much larger than some forms of snowflake schemas due to storing all attribute ID and description columns)
  • Store the IDs and descriptions of all the attributes in a hierarchy in a single table
  • Requires only a single join when querying fact data regardless of the attribute level at which you are querying data

—————————————————————————

Source: MicroStrategy University, MicroStrategy Advanced Data Warehousing, Course Guide, Version: ADVDW-931-Sep13-CG.

Interview Question #8 : Many-to-Many Relationships in MicroStrategy

Question

Which of the following issues can result from many-to-many relationships?

A. Exclusion of some attribute elements when drilling

B. Multiple join paths to fact tables

C. Missing values on reports including all attributes from the hierarchy

D. Multiple counting when aggregating data from base fact tables

E. Lost analytical capability

Answer

Both

D. Multiple counting when aggregating data from base fact tables

      and

E. Lost analytical capability

 

Challenges of Many-to-Many Relationships

Because many-to-many relationships require distinct relationship tables, you have to design the logical data model and data warehouse schema in such a way that you can accurately analyze the relationship in regard to any relevant fact data.

If the structure of your logical data model and data warehouse schema does not adequately address the complexities of querying attribute data that contains many-to-many relationships, you can have problems like lost analytical capability and multiple counting.

I will be exploring both of these topics more next week as Tips and Tricks.

MicroStrategy Course Where You Will Learn About This Topic

MicroStrategy Advanced Data Warehousing Course