Attribute forms - Microstrategy

Attribute forms are identifiers or descriptors of an attribute. Every attribute must have at least one form, and most have two:

  • the ID form
  • the primary description form

Every attribute must have an ID form, such as Customer_ID or City_ID. Some attributes can have additional descriptive forms that do not serve as the primary description form. For example, the Customer attribute in the MicroStrategy Tutorial has various forms. The forms include the ID and the Customer Name, which serves as the description of theattribute. Also included are address and e-mail.

One of the forms for the element City is Name. Chicago is the Name for the element of City with the ID of 1. Other forms forChicago are a URL, such as www.chicago.com, or an Abbreviation such as CH.

Each attribute form provides details that identify and describe an attribute. The Store_ID is a unique numeric identifier for each store, while Store_Name holds the actual store name. Other attribute forms for the Store attribute can include ID, numbers, descriptive names, short abbreviated names, URLs, and so on. In MicroStrategy, you can assign a maximum of 32 forms per attribute.

This example uses the attribute Store and its corresponding elements and forms:

attribute Store and its corresponding elements and forms

A simple lookup table with three columns holds the following separate forms:

separate forms

  • Store_ID: a unique, identifying number for each store (ID form)
  • Store_Name: the name of each store (Description form)
  • Store_Long_Name: the full location, including the store name and state, of each store (Long description form)

In this example, the Lookup_Store table records all of the attribute form data for the Store attribute.

Attributes must contain at least one ID form, which uniquely identifies the attribute. The forms you create must have a reference to a lookup table and can include multiple expressions. Each table must have an ID form as that is how the table will be joined. You can choose a lookup table in the Attribute Editor from a list of tables existing in the project.

For example, two tables exist, one with the forms Customer_ID, Name, and SSN. The second lookup table contains Customer _ID and E-mail. The attribute will have four forms and the tables will join together through the ID columns.

Attribute form properties

When you create forms in the Attribute Editor, you must select properties for each form. These properties affect the display of the forms and include the following:

  • Form categories help categorize the types of forms. The standard options are ID, Desc, and None. You can create new form categories in the Attribute Editor.
  • Format types control how the form is displayed. Format types also control how filters are built. For example, specifying a format type of Big Decimal allows users to preserve precision when qualifying on the form with more than 15 digits.
  • Default sort governs how the form is sorted by defaultwhen included in a report. You can choose from Ascending, Descending, or None.

Attribute form expressions

Simply put, attributes act like holders of information and provide context for facts. 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 declared. 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 create a form expression using Apply functions.These functions are discussed in Appendix C, Pass-through Expressions.

The types of attribute form expressions are

  • simple
  • implicit
  • derived
  • heterogeneous mappings

Simple form expressions access data through columns you include when creating attributes. Implicit and derived attributes do not relate directly to data stored in the datawarehouse. These attributes 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. ID is based on theCATEGORY_ID column and Description on CATEGORY_DESC, both in the table LU_CATEGORY.

Implicit expressions

An implicit attribute uses a virtual or constant attribute that does not physically exist in the database. Such an attribute has an implicit expression, which is a constant value, though nothing is saved in a column. For example, you can create “temporary columns” in the database with a value of “1” for every row, which simplifies COUNT limitations. So, in the Attribute Editor, you enter only a “1” in the expression to create a count.

Implicit attributes are useful in analyzing and retrieving information. When analyzing data, you can use constant attributes to create a COUNT to keep track of the number of rows returned. You can use constant attributes when building metrics, where you can sum the column holding the constant to create a COUNT. Any constant is acceptable, for example, RushOrder=‘Yes’.

Derived expressions

A derived expression has its value determined by an expression which contains more than just a column in a table. 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. In other words, you are making a new expression from information that is already there.

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 that has been derived from the two columns. Calculations and functions used in the expressioncan assist inderiving data from the database by producing SQL with database-specific syntax.

As another example, the derived form expression “Name” consists of the two strings “First” and “Last”:

Name -> First + “ “ + Last

On a report, this information is displayed as Mary Jones under the Name column.

Heterogeneous mappings

There are no restrictions on the names of the columns used in the expressions of a given attribute form. Heterogeneous mapping allows the Engine 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.

For example, because different source systems store Date information in various contexts, your company can have multiple columns in different tables that all represent the concept of Date. The ID form of the attribute Date may contain two expressions. The Day_Date column occurs in the LU_DATE table and the Order_Date column in the ORDER_DETAIL and ORDER_FACT tables.

Each expression is linked to a set of source tables that containthe 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.

You can view the chosen tables in the source Tablesarea to the right of the Form Expressions area in the Attribute Editor.

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, youmight be able to join between data types of Number and Integer.

Attributes and SQL

Reports are made possible by SQL. The user creates a report and then the Intelligence Server, using this report definition, instructs the engine how to build the SQL for that report. The expressions defined in an attribute or fact define the SELECT clause of a SQL command.

For example, consider the following:

You have specified that you are looking for sales information by store and date. The attributes and metrics you havedefined tell the Intelligence Server where to look in the data warehouse for the information and how to create the SQL that will retrieve it. Because of this process, you do not haveto know SQL to extract information from your data warehouse.

Column alias

For attributes, a column alias performs the same function as it does for facts. By default, the data type for an attribute form is inherited from the data type of the column on which the form is defined. However, there are cases where you may need to change the data type. Following are some examples of such cases:

For example, in your warehouse you have a lookup table for Accounts where the ID is Account Number and the ID is stored in the database as DECIMAL(18, 0). Because this column stores high-precision values, you must modify the column alias for the attribute form and map it to a special data type, Big Decimal, so that precision can be preserved when performing filtering, drilling, or page by on the Account attribute.

Another example could be a case in which your warehouse does not have a lookup table for year information, but you would like to create a Year attribute. Many database platforms have functions that can extract parts of a date from a Date data type. For example, SQL Server has a Year function that extracts just the year from a date. In such a case, you can create a Year attribute using the following form expression:

ApplySimple("Year(#0)",[Date_Id])

The data type for this attribute is automatically set to a Date data type. This is because Date_ID is a Date data type. However, the result of the calculation is a year, such as 2002, and it is an integer.

When a temporary SQL table is created, if you do not change the data type of the column alias, the system uses a Date data type and tries to insert integer data into this column. While this does not create a problem in all database platforms, some databases will return an error. To avoid the possibility of an error due to conflicting data types, modify the column alias for the attribute form and change the default Date data type to an integer data type.

In addition to specifying the data type to be used for an attribute form, the column alias also lets you specify the actual column alias name to be used in the SQL generated by MicroStrategy. When you create a form expression using a custom expression as discussed above or using multiple columns, the column alias for the attribute form defaults to CustCol_1 (or CustCol_2, CustCol_3, and so on). The following piece of SQL shows, in bold, where the column alias name is used:

While the column alias name does not affect the actual results or your report, you can change the column alias name to be more meaningful. The above example is a simple one, but this can be useful for troubleshooting the SQL for a particularly complex report.

All rights reserved © 2018 Wisdom IT Services India Pvt. Ltd DMCA.com Protection Status

Microstrategy Topics