Micro-Strategy Interview Questions : Part 3

Facts

What is  fact extension?

You can use level extensions to change a fact level, which is a set of attributes that represent the lowest level of detail at which the fact exists in the warehouse.

 Level extensions define how facts can be extended, lowered, or disallowed to other facts across the schema.

What is fact degradation?

When facts exist at a higher level than the report display level, you must specify how the Engine degrades the data to the lower level. When you lower the level at which a fact is reported, you are using degradation.

Types of facts

Simple facts

A simple fact is made up of one or more fact expressions. With a simple fact definition, you can define a fact as a column, constant, or simple expression.

Implicit facts 

An implicit fact is a virtual or constant fact that does not physically exist in the database because it is created at the application level.

Derived facts

A derived fact has its value determined by an expression that combines two or more columns in a database to create a new column.

Metrics

What are different types of metrics?

Simple : Simple metrics combine aggregate operators with fact columns or attributes.

Nested: Metrics that perform multiple aggregations by placing one calculation formula inside another

Compound : A compound metric is a combination of expressions that, through the use of functions, are themselves metrics.

Derived

What is Base Formula?

 Use a simple expression as a base formula to facilitate the creation of more complex metrics.

 What is smart metrics?

Compound metrics are the ones that are derived by some specific expression involving the different simple metrics. Eg, Total( profit/units Sold). Smart metrics is when the compound metric is calculated with the help of subtotal calculations for every element inside the compound metric. For the above example the smart metric computation can be Total(profit)/Total(Sold).

What is level metric?

Level metrics are advanced metrics which are set to be evaluated at a specified attribute level. These are required when in the same report you need to roll up a metric at two different levels side by side. Example is comparison of “Revenue from a Region” to “Revenue from a Country”. Here Region and Country are the two different levels.

The level of a metric, also referred to as dimensionality, allows you to determine the attribute level at which the metric is calculated.

Default – Report Level

The elements needed to specify  a level  for a metric

Target       - Attribute level at which the metric

Grouping  - Determines the metric aggregation.

Filtering    - governs the relationship between the

                        report filter and the calculation of the metric.

 What is purpose of having conditionality in metrics?

Conditionality associates a filter to the metric calculation. This is an optional component.

 What are the different components of metrics?

The formula defines the data to be used and the calculations to be performed on the data. The outermost formula must be a group function.

The level, or dimensionality, determines the level at which to perform the metric calculation. For example, you can choose to calculate at the month level or year level.

Conditionality associates a filter to the metric calculation. This is an optional component.

The transformation applies offset values, such as “four months ago,” to the selected attributes. This is also an optional component.

 What is the purpose of transformation in Metrics? Types of transformation.

It encapsulates a business rule used to compare results of different time periods.Transformations are used in the definition of a metric to alter the behavior of the metrics.

Expression - based  transformations  –  You implement  these  transformations using   a mathematical   formula   in    Microstrategy Architect.

Table - based  transformations   –      These transformations are based on a transformation – or relate –table in the warehouse.

What is dynamic Aggregation?

Rollup metric values that occurs when an attribute is moved from the report grid to the report objects.

For Eg: The report grid has Quarter and Customer City, Revenue. If we remove Quarter into the report objects then revenue should automatically roll up to Customer City.

How to ensure that a particular fact table will be chosen for a metric

The MSTR operates in such a way that the incoming queries and data retrievals are done from the table which has the least logical size. Thus if we prefer a specific table to be the center of activity then we should try to reduce the logical size of that specific table so that it becomes considerably lesser than the other tables.

In Microstrategy, how can you direct the sql generated to use a specifc table?

Using the Level parameter in the Metric level options
Assuming you have OLAP licence,the easiest way to direct to a particular table is to create a dummy fact on the table,include the fact in metric and put the metric in report objects.

How to hide a particular metric in a report for a specific user?

Using Object level security

What is Metric Formula Join Type? How it is different with Metric Join Type?

Metric Formula Join Type is used for Compound Metrics and determines how the different tables used in metric formula are joined.

Whereas the Metric Join Type determines how the metrics are joined to other metrics.