Tag Archives: Attribute Mapping

Automatic Column Recognition in MicroStrategy 9

Architect,

Architect 9.x can automatically create attributes and facts in order to save time during the schema creation process. The facts and attributes are created based on data types, database column names, primary and foreign keys, and other schema creation heuristics. The user can define how attributes and facts are created when tables are added to the project by defining the automatic column recognition rules available in the MicroStrategy Architect Settings menu, by following the steps.

  1. Open the Setting menu, located under Options.
    Step 1
  2. Go to the Column Recognition tab.
    Step 2
  3. Do not use auto recognize option is used to disable the automatic creation of attributes and facts when tables are added to the project using Architect. Uncheck this radio button.
  4. Use auto recognize option is used to enable the automatic creation of attributes and facts when tables are added to the project using Architect. These are created based on the following schema creation heuristics and rules:
    • Separator: The character used as a separator in the database column names. For instance, a database column name such as USER_ID uses the undersocre character (_) as a separator.
    • Attribute naming rule: Database column name suffixes that identify that the columns should be mapped to a new attribute as the identity form. A semicolon (;) can be used to separate suffixes that are to be mapped to new attributes. It is also possible to define how the attribute name is created by using a vertical bar () to define what the suffix is replaced with in the resulting attribute name.
    • Attribute form naming rule: Database column name suffixes that identify that the columns should be mapped to a new attribute form.
  5. In addition to using these rules to define attributes and attribute forms, selecting the Use auto recognize option also employs other schema creation heuristics:
    • The automatic column mapping rules described in Automatically column recognition 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.
  6. Even when the option to not use auto recognize is selected, it is still possible to take advantage of the auto recognize feature on a table on table basis. Right click on the table in the Architect Project Tables View. Select Recognize > Attributes, Facts, or Both.
    Additional Step

Manual Mapping

Architect

In a previous post, I’ve warned about the not so automatically behavior of the automatic mapping. However, I can’t blame the manual mapping for the same degree of inconsistency. I mean, boy, that’s as manual as it gets!

Take a manually mapped attribute and have a look at its source tables. Now drop the attribute from one of these tables (on database and Catalog level) and try to run a report which used that table.

On the one hand, MicroStrategy knows from the attribute definition that it can include that table into the SQL. On the other hand, it should know from the table definition, as updated in the Warehouse Catalog, that the column is no longer there, but such a small detail doesn’t have enough force to deter the SQL Engine. So it simply writes the SQL as if nothing had happened, and the only error you get is from the database (i.e. a11.attribute_id does not exist).

So, if you aren’t doing this already, don’t forget to inform the manually mapped attribute that one of its source tables decided to discontinue their relationship, next time you indulge in such data model changes.

Automatic mapping

MicroStrategy

Things aren’t always what they seem…

Automatic mapping is a great thing. It enables us to stop worrying about updating an attribute or a fact whenever we bring modifications to the Warehouse Catalog.

Well, my advice is to don’t rely too much on it. The following pictures show two instances of the “Customer” attribute and both of them appear to have CUSTOMER_ID automatically mapped.

However, one of them lacks the ORDER_FACT table, and that is because I mischievously removed it by clicking Modify and then unchecking it from the source tables list.
This ID’s mapping is still automatic, in that that it will use any new table that contains CUSTOMER_ID and it will remove from its definition any table that cease to contain this column. But as far as the table ORDER_FACT is concerned, it will not be used by the attribute until it is checked again in the source table list.

So, when debugging reports which don’t run because a fact appears not to be available at a certain attribute level, do yourself a favor and check if that particular attribute suffers from what I have described above.