Tag Archives: Do

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.

Stephen Few: Why Do We Visualize Quantitative Data?

Readers:

Stephen_FewIt has been a while since I have discussed some of the latest creative thoughts on data visualization from Stephen Few. I have read all of Steve’s books, attended several classes from him, and religiously follow his blog and newsletter on his website, Perceptual Edge.

For those of you who don’t know, Stephen Few is the Founder & Principal of Perceptual Edge. Perceptual Edge, founded in 2003, is a consultancy that was established to help organizations learn to design simple information displays for effective analysis and communication.

Steve has stated that his company will probably always be a company of one or two people, which is the perfect size for him. With 25 years of experience as an innovator, consultant, and educator in the fields of business intelligence and information design, he is now considered the leading expert in data visualization for data sense-making and communication.

Steve writes a quarterly Visual Business Intelligence Newsletter, speaks and teaches internationally, and provides design consulting. In 2004, he wrote the first comprehensive and practical guide to business graphics entitled Show Me the Numbers, now in its second edition. In 2006, he wrote the first and only guide to the visual design of dashboards, entitled Information Dashboard Design, also now in its second edition. In 2009, he wrote the first introduction for non-statisticians to visual data analysis, entitled Now You See It.

Here is his latest thoughts from his newsletter.

Best regards,

Michael

 

Why Do We Visualize Quantitative Data?

Per Stephen Few, we visualize quantitative data to perform three fundamental tasks in an effort to achieve three essential goals:

Web

These three tasks are so fundamental to data visualization, Steve used them to define the term, as follows:

Data visualization is the use of visual representations to explore, make sense of, and communicate data.

Steve poses the question of why is it that we must sometimes use graphical displays to perform these tasks rather than other forms of representation? Why not always express values as numbers in tables? Why express them visually rather than audibly?

Essentially, there is only one good reason to express quantitative data visually: some features of quantitative data can be best perceived and understood, and some quantitative tasks can be best performed, when values are displayed graphically. This is so because of the ways our brains work. Vision is by far our dominant sense. We have evolved to perform many data sensing and processing tasks visually. This has been so since the days of our earliest ancestors who survived and learned to thrive on the African savannah. What visual perception evolved to do especially well, it can do faster and better than the conscious thinking parts of our brains. Data exploration, sensemaking, and communication should always involve an intimate collaboration between seeing and thinking (i.e., visual thinking).

Despite this essential reason for visualizing data, people often do it for reasons that are misguided. Steve dispels a few common myths about data visualization.

Myth #1: We visualize data because some people are visual learners.

While it is true that some people have greater visual thinking abilities than others and that some people have a greater interest in images than others, all people with normal perceptual abilities are predominantly visual. Everyone benefits from data visualization, whether they consider themselves visual learners or not, including those who prefer numbers.

Myth #2: We visualize data for people who have difficulty understanding numbers.

While it is true that some people are more comfortable with quantitative concepts and mathematics than others, even the brightest mathematicians benefit from seeing quantitative information displayed visually. Data visualization is not a dumbed-down expression of quantitative concepts.

Myth #3: We visualize data to grab people’s attention with eye-catching but inevitably less informative displays.

Visualizations don’t need to be dumbed down to be engaging. It isn’t necessary to sacrifice content in lieu of appearance. Data can always be displayed in ways that are optimally informative, pleasing to the eye, and engaging. To engage with a data display without being well-informed of something useful is a waste.

Myth #4: The best data visualizers are those who have been trained in graphic arts.

While training in graphic arts can be useful, it is much more important to understand the data and be trained in visual thinking and communication. Graphic arts training that focuses on marketing (i.e., persuading people to buy or do something through manipulation) and artistry rather than communication can actually get in the way of effective data visualization.

Myth #5: Graphics provide the best means of telling stories contained in data.

While it is true that graphics are often useful and sometimes even essential for data-based storytelling, it isn’t storytelling itself that demands graphics. Much of storytelling is best expressed in words and numbers rather than images. Graphics are useful for storytelling because some features of data are best understood by our brains when they’re presented visually.

We visualize data because the human brain can perceive particular quantitative features and perform particular quantitative tasks most effectively when the data is expressed graphically. Visual data processing provides optimal support for the following:

1. Seeing the big picture

Graphs reveal the big picture: an overview of a data set. An overview summarizes the data’s essential characteristics, from which we can discern what’s routine vs. exceptional.

The series of three bar graphs below provides an overview of the opinions that 15 countries had about America in 2004, not long after the events of 9/11 and the military campaigns that followed.

graph-of-country-opinions

Steve first discovered this information in the following form on the website of PBS:

table-of-country-opinions

Based on this table of numbers, he had to read each value one at a time and, because working memory is limited to three or four simultaneous chunks of information at a time, he couldn’t use this display to construct and hold an overview of these countries’ opinions in his head. To solve this problem, he redisplayed this information as the three bar graphs shown above, which provided the overview that he wanted. Steve was able to use it to quickly get a sense of these countries’ opinions overall and in comparison to one another.

Bonus: Here is a link to where Steve discusses the example above on his website.

2. Easily and rapidly comparing values

Try to quickly compare the magnitudes of values using a table of numbers, such as the one shown above. You can’t, because numbers must be read one at a time and only two numbers can be compared at a time. Graphs, however, such as the bar graphs above, make it possible to see all of the values at once and to easily and rapidly compare them.

3. Seeing patterns among values

Many quantitative messages are revealed in patterns formed by sets of values. These patterns describe the nature of change through time, how values are distributed, and correlations, to name a few.

Try to construct the pattern of monthly change in either domestic or international sales for the entire year using the table below.

table-of-sales-data

Difficult, isn’t it? The line graph below, however, presents the patterns of change in a way that can be perceived immediately, without conscious effort.

graph-of-sales-data

You can thank processes that take place in your visual cortex for this. The visual cortex perceives patterns and then the conscious thinking parts of our brains make sense of them.

4. Comparing patterns

Visual representations of patterns are easy to compare. Not only can the independent patterns of domestic and international sales be easily perceived by viewing the graph above, but they can also be compared to one another to determine how they are similar and different.

In Summary

These four quantitative features and activities require visual displays. This is why we visualize quantitative data.

Tips & Tricks #9: How Do Changes on the Source Report (Dataset) Get Reflected in MicroStrategy Report Services 9.x Documents

In MicroStrategy Report Services Documents, document datasets and their original source reports  (such as a grid report being used as a dataset) are not completely connected to each other. Depending on the changes made on the source report, it can be reflected differently on the document. Basically, the change can be divided into two types.

Type 1 – Formatting Changes

Formatting changes, for example, changing autostyles, thresholds, subtotals.

If a user chooses the option Add to Section without Formatting, the grid/graph showing on the document will not use the report’s stored formatting. Any formatting changes on the source report will not be reflected on the document.

Tip 9-1If a user chooses the option Add to Section with Formatting, the grid will be added with the current format of the report.  However, any formatting changes made to the source report AFTER the dataset has been included in the document will NOT be reflected on the document.

For example, a user disabled the subtotal (see screenshot below) for the original report after the report has been included as a dataset in a document, the document will still show the subtotals.

Tip 9-2

To force the document to recognize the report’s formatting changes, the user needs to delete the grid/graph from document section and add it again using the With Formatting option. By doing this, the latest formatting properties of the grid/graph on the source report are retrieved.

Type 2 – Adding/Removing Objects and Modifying Report Filters

Another type of changes made on the report involving adding/removing objects and modifying report filters.

Unlike the formatting change, this type of change does carry over from the source report to the document datasets.

For example, if users add/remove/modify report filters (see screenshot below), the change will be reflected on the data when running the document.

Tip 9-3

If an object is removed from the source report (see screenshot below), the user can see the change in the document’s Dataset Objects window. After the user runs the document, the object will be removed from grid/graph on the document.

Tip 9-4

If an object is added to the report (see screenshot below), the change will show in the document’s dataset objects window. However, the object will not be automatically added to the grid/graph. The user has to manually add the object to the grid/graph or add the dataset to the section again to make it show up.

Tip 9-5

NOTE:   As of MicroStrategy 9.0, a new feature was introduced where a user can add a dataset report to a Report Services document as a shortcut by selecting the Add to Section As a shortcut option, as shown below:

Tip 9-6

If the grid/graph is added to the document using this option, then the document would be updated automatically if ANY type of change is made on the source report.