Interview Questions and Answers -1

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.

Filters

What is filter?

Filter is used to restrict data in a report

 What is report as filter?

In the MicroStrategy when the same filter conditions must be applied to multiple passes, the same where clause appears in each of those passes. This redundant where clause can be expensive if the filter conditions are complicated and thus involve many tables and joins. Ideally, an intermediate table populated with entries could be created to satisfy the complicated filter conditions so that the rest of the SQL statements can use that intermediate table. In that case, the where clause would be executed only once instead of multiple times and SQL performance would be improved. In this case to populate the temporary table we can use report as a filter

What is view filter?

View Filters: View Filters are the conditions that come into play before a specific result is presented to the user. Thus the view filters are not part of the SQL statements like report definitions, rather they are the filters applied after the execution of the SQL statements, after the data is retrieved from the data warehouse.

 What is filtered prompt?

We can restrict the number of elements in a prompt using a filter.

Difference between report and view filter?

Report filters: report filters are the conditions that accompany the report generating SQL statements. The report definitions have the filters as part of their definition.

View Filters: View Filters are the conditions that come into play before a specific result is presented to the user. Thus the view filters are not part of the SQL statements like report definitions, rather they are the filters applied after the execution of the SQL statements, after the data is retrieved from the data warehouse.

Difference between Report Limit and Report filter?

A report limit specifies a set of criteria used to restrict the data returned in the report data set after the report metrics are calculated.

Report Filter applies the where condition to the query sent to warehouse to extract the results.

What is the difference between Absolute filtering and standard filtering?

When we use the absolute filtering in definition of level metric  whatever data we obtain from the filter is goingto be reported as such and the the report filter will be overridden by the absolute filter settings. The standard filtering the report filter interacts with the metric filter in the normal way and what we obtain will be formatted according to the report filter settings.

What is a Joint Element List Filter?

Helps to choose combination of attribute elements from different attributes to filter a report

Enables to create filters with attribute pairs as well as triplets, quadruplets and so on.

This is available in the advanced qualification section of the filters

What is a Security filter?

Security filter is used to apply security at the database data level.Whenevera users associated with security filter runs a report, a WHERE clause is always included in the report sql with the condition defined in the Security Filter.

What is a Attribute to Attribute Filtering?

This is used to compare values of 2 attributes using their forms.

Eg: Ship Date<Day(ID)+2