Tag Archives: #8

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

Data Blending: Why are Some Metric Values Blank in Documents Using Multiple Datasets in MicroStrategy Analytics Enterprise 9.4.1 (Part 8)

MicroStrategy Analytics Enterprise

Introduction

Starting with MicroStrategy Analytics Enterprise 9.4.1, Report Services documents can contain grids with objects coming from more than one dataset.

Multiple Datasets in a Single Grid/Graph/Widget Object in MicroStrategy Web 9.4 [2]

Users now have the ability to add attributes and/or metrics from multiple datasets to a single grid, graph, or widget. For example, if Dataset #1 contains Category and Revenue and Dataset #2 contains Category and Profit, a grid can be created which contains Category, Revenue, and Profit.

Part 8 - 1

Administrators can control the use of multiple datasets in a single grid, graph, or widget through the Analytical Engine VLDB properties window at the project level.

  1. Right mouse click (RMC) on the project name.
  2. Select Project Configuration.
  3. Click on Project Definition.
  4. Select ‘Advanced’.
  5. Click “Configure” under the Analytical engine VLDB.

Part 8 - 2

NOTE:

The default value is set to: “Objects in document grids must come from the grid’s source dataset only”.

Users can set the set the source of the grid to a particular dataset or choose no dataset (in which case, the MicroStrategy engine will determine the best suited dataset). [1]

The MicroStrategy Analytical Engine displays no data for metrics in ambiguous cases or when there is a conflict. Ambiguous cases can arise in cases where multiple datasets contain the same objects.  Examples based on the MicroStrategy Tutorial project have been provided to explain this information.

Note: When the MicroStrategy Analytical Engine cannot resolve the correct datatset as explained in the cases below, the data displayed for these will correspond to the value chosen for the missing object display under Project Configuration > Report definition > Null values > Missing Object Display. The default value for this blank.

Case1:

Multiple datasets have the same metric. Only one dataset does not contain this metric and this dataset is set as the source of the grid.

This case is explained with an example based on the MicroStrategy Tutorial project.

1. Create the following objects:

a. Dataset DS1 with the attribute ‘Year’ and metric ‘Profit’.

b. Dataset DS2 with the attribute ‘Year’ and metrics ‘Profit’, ‘Revenue’.

c. Dataset DS3 with the attribute ‘Quarter’ and metric ‘Cost’.

2.  Create a document based on the above datasets and create a grid object on the document with the following objects: ‘Year’, ‘Quarter’, ‘Profit’. Set the source of this grid to be the dataset ‘DS3’.

3. In the executed document, no data is displayed for the metric ‘Profit’ as shown below.

Part 8 - 3

In the above example, the metric ‘Profit’ does not exist in the source dataset ‘DS3’ and exists in more than dataset which are in the document i.e., it exists in both ‘DS1’ and ‘DS2’. Since the engine cannot just randomly pick one of the two available datasets, it chooses not to display any data for this metric. If users do not want such blank columns to be displayed, set the source dataset so that such ambiguity does not arise.

Case 2:

The same metric exists multiple times on the grid. For example, users can have a smart compound metric and a component metric of this compound smart metric on the grid in the document. The smart metric and the component metric are from different datasets.

This case is also explained with an example based on the MicroStrategy Tutorial project.

1. Create the following objects:

a. Dataset DS1 with attribute ‘Year’ and metric ‘Profit’.

b. Dataset DS2 with attribute ‘Year’ and metrics ‘Revenue’, ‘Profit’, ‘Profit Margin’ (this is a compound smart metric calculated from metrics Revenue and Profit).

2. Create a document based on the above datasets and create a grid object on the document with the following objects: ‘Year’, ‘Revenue’, ‘Profit’ and ‘Profit Margin’. The source of this grid object is set to DS1.

3. In the executed document, no data is displayed for the metric ‘Profit Margin’, as shown below.

Part 8 - 4

In the above example, since the source of the dataset is set to ‘DS1’, the ‘Profit’ metric is sourced from this dataset and the metric ‘Revenue’ is sourced from the dataset ‘DS2’ (as this is the ONLY datatset with this metric). However, for the metric ‘Profit Margin’, the component metric ‘Profit’ exists on dataset ‘DS1’, so this becomes a conflict metric and is not displayed. If the source of the grid is changed to ‘DS2’, the data is displayed correctly as shown below.

Part 8 - 5

References:

[1] MicroStrategy Knowledgebase, Why are some metric values blank in documents using multiple datasets in MicroStrategy Analytics Enterprise 9.4.1, TN Key: 44517, 12/16/2013, https://resource.microstrategy.com/support/mainsearch.aspx.

[2] MicroStrategy Knowledgebase, Multiple datasets in a single grid/graph/widget object in MicroStrategy Web 9.4, TN Key: 44944, 09/30/2013, https://resource.microstrategy.com/support/mainsearch.aspx.

NOTE: You may need to register to view MicroStrategy’s Knowledgebase.

Tips & Tricks #8: MicroStrategy VLDB Properties Hierarchy

Very Large DataBase

VLDB is an acronym for Very Large DataBase. VLDB properties enable MicroStrategy products to take advantage of the unique optimizations that different databases offer. There are over 100 settings that affect how the MicroStrategy Engine handles functions such as:

  • Join options (star join, full outer join, and so forth)
  • Metric calculation options (null and zero check during division operations)
  • Query optimizations (subqueries and driving tables)
  • Table types (temporary or derived tables)

These settings, along with other settings, directly modify how the SQL Engine writes SQL against the warehouse.VLDB Hierarchy

VLDB Properties

The diagram above illustrates how VLDB properties that are set for one level will take precedence over those set for another level.

DBMS Level

The default value for every VLDB property is based on the database type of the warehouse and is written into the metadata at the time that you configure the project. MicroStrategy supplies the default values for the DBMS level, which are optimized for the database type and version.

The VLDB properties values are inherited from the DBMS object when you have not changed any VLDB properties (and all the Use default inherited value check boxes are selected for every VLDB property in the VLDB Properties Editor).

Database Instance Level/Project Level

In the MicroStrategy platform, the Database Instance level and Project level settings are merged together (or considered to be one overall level), because only one database instance can be applied for each project. Also, the Project level settings contain the Analytical Engine-related VLDB properties and the MDX VLDB properties.

Yield Red SignWhen you change a VLDB property setting at the database instance or project level, you must restart the Intelligence Server for the MicroStrategy Engine to read the latest schema information from the metadata.

Report Level

Properties you set at the report level override properties at every other level. For example, if a VLDB property is set to one particular value for a report and the same VLDB property is set to a different value for a metric on that report, the report-level setting takes precedence over the metric-level setting for that VLDB property.

Template Level

Values set at the template level override those at the metric, database instance, and DBMS levels.

Attribute/Transformation Level

There are only a select number of VLDB properties available within the Attribute Editor and the Transformation Editor. If you alter these properties, they automatically override the values for the same properties at the database instance and DBMS levels.

Use default inherited value Check Box 

By default, all objects in a project, including the project itself, have the Use default inherited value check box enabled for all of the VLDB properties (see screenshot below). As mentioned earlier, when you have not changed any VLDB properties and all the Use default inherited value check boxes are selected for every VLDB property in the VLDB Properties Editor, the values are inherited from the DBMS level. If you customize the settings for a VLDB property at  particular object’s level (whether it is a metric, template, project, etc.), any objects above it (in the VLDB Hierarchy) automatically accept the custom setting as the inherited value.

Tip 8-2

Example: Sub Query Type VLDB Property

As an example, let’s review the Sub Query Type VLDB property.

Suppose you define the Sub Query Type VLDB property at the template level to use temporary tables instead of sub queries. You create a new report using the template. When you view the Sub Query Type VLDB property at the report level, you notice that the same value (use Temporary Table) is automatically inherited at the report level. The report inherits the template’s VLDB value because the Use default inherited value check box is enabled (and the check box even lists “Template level” as the level from which the report inherited the custom value). This behavior of inheriting the custom value (as long as long as the Use default inherited value check box is enabled) occurs in a recursive fashion all the way to the DBMS object.

Tip8-3