Skip to main content

MSTR Inventory metrics or No Aggregate metrics

No Aggregate metrics

 Edit 0 1…

TN20363: How do non-aggregatable metrics work in MicroStrategy SQL Generation Engine 9.x?


https://success.microstrategy.com/t5/Architect/TN20363-How-do-non-aggregatable-metrics-work-in-MicroStrategy/ta-p/180533

Some kinds of fact data are not valid for aggregation with respect to one or more dimensions. The classic example is inventory with respect to time. The fact that there were 300 units in inventory yesterday is not relevant to the 200 units in inventory today. Adding those values together results in an inflated total, 500, that does not reflect the actual size of the inventory. What is relevant, rather than the sum, is the last atomic inventory value in a given period of time.

In MicroStrategy SQL Generation Engine 9.x, non-aggregatable metrics are used for this kind of calculation. Non-aggregatable metrics are defined using the dimensionality section of the metric editor. The "Grouping" property has six possible settings, of which four specify non-aggregating behavior:

  • Beginning (fact)
  • Ending (fact)
  • Beginning (lookup)
  • Ending (lookup)
"Fact" and "lookup" define how the first or last atomic value is identified within the period. "Fact" means to use the first/last existing value in the fact table, while "lookup" goes to the first or last child element of the element to be displayed on the report. For example, a fact table might contain data such as the following:

|| Quarter_ID
Month_IDFact
2006120060110
2006120060220
2006120060330
2006220060440
2006220060550

No data exist for June 2006. An ending fact metric would return 30 for Q1 2006 and 50 for Q2 2006; the corresponding ending lookup metric would return 30 for Q1 2006, but the value for Q2 2006 would be null. This is because the lookup table identifies June 2006 as the last month in the second quarter, but the fact table does not have any data for that month.

Dimensionality vs. fact table attributes
The actual calculation level of a non-aggretable metric will be the lowest level attribute from the non-aggregatable hierarchy that is present on the fact table. If the fact table includes the Day attribute, day-level values would be the result. If, instead, Month is the lowest fact table level, then the metric would produce month-level values.The level of the first or last "x" within "y" depends on the granularity of the fact table.

If fact tables exist at multiple levels within a non-aggregatable hierarchy, the attribute chosen as the dimensionality target determines the calculation level by influencing the selection of the fact table. If Month is the target attribute, the Engine will never choose a fact table at a higher level than Month (never Quarter or Year). Thus the first or last month-level value will be presented in the report.

Note: The final determination of the calculation level is based on the fact table. The Engine uses normal fact table selection logic for non-aggregatable metrics, taking into account dimensionality and filtering attributes. The Engine will revert to a lower-level fact table if that is the only way to support all of the grouping and filtering attributes. For example, if a metric indicates non-aggregatable dimensionality at the level of Month, but a report includes attributes that require a day-level fact table, the day-level table will be used. Even though the metric specified Month level, the report will display the first or last day's value within the time period because the fact table demands it.

Beginning/ending (fact) dimensionality
Beginning or ending (fact) metrics calculate according to the following procedure:

  1. Calculate the metric, including both the dimensional level attribute ("parent") and the fact table level attribute ("child"). Including the fact table level attribute at this stage ensures that the non-aggregatable hierarchy will not be subject to aggregation.
  2. For each "parent" element, find the first or last "child" element that exists in step 1.
  3. The metric's final result is the intersection of steps 1 and 2.
For example, an "End on Hand" metric may be defined with the following dimensionality. Month is used as the dimensional attribute because the fact exists at the level of Month.

external image TN5200-8X-2457_1.gif

A report calculating this metric over Quarters generates the following SQL. (Note that a filter restricts the results to one quarter. This is because the MicroStrategy Tutorial project uses a partitioned fact table for the End on Hand fact. The filter reduces the size of the SQL and makes a better illustration.)

Partitioning pre-query -- this is independent of non-aggregatable metrics

select distinct a11.PBTNAME PBTNAME

from PMT_INVENTORY a11

where a11.QUARTER_ID in (20074)


Month-level values, with Quarter ID included

select a12.QUARTER_ID QUARTER_ID,

a11.MONTH_ID MONTH_ID,

sum(a11.EOH_QTY) WJXBFS1

into #ZZTTS0200F6PO000

from INVENTORY_Q4_2007 a11

join LU_MONTH a12

on (a11.MONTH_ID = a12.MONTH_ID)

where a12.QUARTER_ID in (20074)

group by a12.QUARTER_ID,

a11.MONTH_ID


What is the last Month found in the metric table for each Quarter?

select pc11.QUARTER_ID QUARTER_ID,

max(pc11.MONTH_ID) WJXBFS1

into #ZZTTS0200F6MB001

from #ZZTTS0200F6PO000 pc11

group by pc11.QUARTER_ID


Intersect (inner join) the tables to preserve only the last Month's value from each Quarter

select distinct pa11.QUARTER_ID QUARTER_ID,

a13.QUARTER_DESC QUARTER_DESC,

pa11.WJXBFS1 WJXBFS1

from #ZZTTS0200F6PO000 pa11

join #ZZTTS0200F6MB001 pa12

on (pa11.MONTH_ID = pa12.WJXBFS1 and 

pa11.QUARTER_ID = pa12.QUARTER_ID)

join LU_QUARTER a13

on (pa11.QUARTER_ID = a13.QUARTER_ID)


drop table #ZZTTS0200F6PO000

drop table #ZZTTS0200F6MB001
Beginning/ending (lookup) dimensionality
Beginning or ending (lookup) metrics follow a slightly different procedure.

  1. Create a table containing the first or last child elements (fact table level) corresponding on the parent elements to be displayed (dimensional level). Note that there is no need to include the parent attribute in the SELECT clause of this pass, because the pass will be used only for filtering the fact table. (The parent attribute is in the GROUP BY clause.)
  2. Calculate the metric, grouping by the dimensional level attribute ("parent") but filtering according to the child elements from step 1.
external image TN5200-8X-2457_2.gif

Determine the latest Months in each Quarter, according to lookup table

select max(c11.MONTH_ID) MONTH_ID

into #ZZTTS0200GZOP000

from LU_MONTH c11

where c11.QUARTER_ID in (20074)

group by c11.QUARTER_ID


Partitioning pre-query -- this is independent of non-aggregatable metrics

select distinct a11.PBTNAME PBTNAME

from PMT_INVENTORY a11

where a11.QUARTER_ID in (20074)


// Calculate the metric, filtering on the set of last months in their quarters

select a13.QUARTER_ID QUARTER_ID,

max(a14.QUARTER_DESC) QUARTER_DESC,

sum(a11.EOH_QTY) WJXBFS1

from INVENTORY_Q4_2007 a11

join #ZZTTS0200GZOP000 pa12

on (a11.MONTH_ID = pa12.MONTH_ID)

join LU_MONTH a13

on (a11.MONTH_ID = a13.MONTH_ID)

join LU_QUARTER a14

on (a13.QUARTER_ID = a14.QUARTER_ID)

where a13.QUARTER_ID in (20074)

group by a13.QUARTER_ID


drop table #ZZTTS0200GZOP000

Comments

Post a Comment

Popular posts from this blog

Microstrategy Custom number formatting symbols

Custom number formatting symbols If none of the built-in number formats meet your needs, you can create your own custom format in the Number tab of the Format Cells dialog box. Select  Custom  as the Category and create the format using the number format symbols listed in the table below. Each custom format can have up to four optional sections, one each for: Positive numbers Negative numbers Zeros Text Each section is optional. Separate the sections by semicolons, as shown in the example below: #,###;(#,###);0;"Error: Entry must be numeric" For more examples, see  Custom number formatting examples . To jump to a section of the formatting symbol table, click one of the following: Numeric symbols Character/text symbols Date and time symbols Text color symbols Currency symbols Conditional symbols Numeric symbols For details on how numeric symbols apply to the Big Decimal data type, refer to the  Project Design Guide . ...
4 comments
Read more

Control the display of null and zero metric values

Show   Control the display of null and zero metric values in a grid report You can determine how to display or hide rows and columns in a grid report that consist only of null or zero metric values. You can have MicroStrategy hide the rows and columns in the following ways: Hide rows and columns that consist only of null metric values Hide rows and columns that consist only of zero metric values Hide rows and columns that consist only of null or zero metric values (default) Once you have defined how MicroStrategy hides null and zero metric values in the grid, you can quickly show or hide the grid using the Hide Nulls/Zeros option in the Data menu, as described below, or by clicking the  Hide Nulls/Zeros  icon  in the Data toolbar. To determine how null and zero metric values are displayed or hidden in a grid report Open the report in Edit mode. From the  Tools  menu, select  Report Options . The Report Options...
Post a Comment
Read more

Apply or Pass-through functions in Microstrategy

Ap ply (Pass-Through) functions MSTR Apply functions provide access to functions or syntactic constructs that are not standard in MicroStrategy but are provided by various RDBMS systems.. Syntax common to Apply functions Apply Function Name   ("expression with placeholders", Arg1, Arg2, Arg3, …ArgN) where: Apply Function Name  – is a generic name used for the predefined pass-through functions described above expression with placeholders  – is the string describing the actual expression or syntax that the engine uses while generating the SQL and which is sent to the RDBMS. The placeholders are represented by #0, #1, and so on. "#" is a reserved character for MicroStrategy. Arg  – is an argument that replaces the parameter markers in the pattern. Arg1 replaces #0, Arg2 replaces #1, and so on. There are   five  pre-defined Apply functions to replace regular, predefined functions of the same type. For more details, cli...
6 comments
Read more

Case functions Microstrategy

Ca se functions Microstrategy Case functions return specified data in a SQL query based on the evaluation of user-defined conditions. In general, a user specifies a list of conditions and corresponding return values. Case This function evaluates multiple expressions until a condition is determined to be true, then returns a corresponding value. If all conditions are false, a default value is returned.  Case  can be used for categorizing data based on multiple conditions. This is a single-value function. Syntax Case ( Condition1 ,  ReturnValue1 ,  Condition2 , ReturnValue2 ,...,  DefaultValue ) Example Case(([Total Revenue] < 300000), 0, ([Total Revenue] < 600000), 1, 2) sum(Case (Day@DESC in (“Sat”,”Sun”), Sales, 0) {~+} Sum(Case(Category@DESC In("Books","Electronics"),Revenue,0)){~+} CaseV (case vector) CaseV  evaluates a single metric and returns different values according to the results. It can be used to perfo...
3 comments
Read more

Types of prompts in Microstrategy

Types of prompts in Microstrategy The different types of prompts allow you to create a  prompt  for nearly every part of a report. Prompts can be used in many objects including reports, filters, metrics, and custom groups, but all prompts require user interaction when the report is executed. The correct prompt type to create depends on what report objects you want users to be able to base a filter on to filter data, as described in the list below. Filter definition prompts   allow users to determine how the report's data is filtered, based on one of the following objects: Attributes in a hierarchy : Users can select prompt answers from one or more attribute elements from one or more attributes. The attribute elements that they select are used to filter data displayed on the report. This prompt lets you give users the largest number of attribute elements to choose from when they answer the prompt to define their filtering criteria. For example, on a repor...
1 comment
Read more

Settings for Outer Join between metrics in MicroStrategy

Settings for Outer Join between metrics in MicroStrategy MicroStrategy adopts multi-pass logic to determine the execution plan for a report. This means that every metric is evaluated in separate SQL passes. Outer Joins come into play when MicroStrategy Engine merges the results from all SQL passes into one report. For a multi-pass report, different Outer Join behaviors can give the user completely different results. In addition, report metrics can be of different types which can, in some cases, influence the result of the outer join. In MicroStrategy, there are two settings that users can access to control Outer Join behavior : Formula Join Type and Metric Join Type . Metric Join Type: VLDB Setting at Database Instance Level Report and Template Levels Report Editor > Data > Report Data Options Metric Level   Metric editor > Tools > Metric Join Type Control Join between Metrics Formula Join Type: Only at Compound/Split...
Post a Comment
Read more

Prompt-in-prompt (Nested prompt) feature in a FreeForm SQL Report

Prompt-in-prompt (Nested prompt) feature in a FreeForm SQL Report In some business scenarios, it is required to implement prompt-in-prompt (nested prompt) feature to use the answer of one prompt to qualify on the elements of another prompt in Freefrom SQL reports in MicroStrategy Developer 9.4.x-10.x. The following procedure describes how to achieve prompt-in-prompt in a Freeform SQL report in MicroStrategy Tutorial project: Create a new filter, select Add an Attribute qualification and choose the highest level attribute Country. Make sure to Qualify On: Elements. Click Prompt as the image shown below. Accept all the default values without any changes in the popped up window after clicking on "Prompt" as shown below. Save the filter as "Country Filter". Click "Prompt", choose "Use a filter to reduce the number of elements" option and select Country Filter created in last step, as shown below. Save the filter as "Region Filter...
Post a Comment
Read more

System Manager workflow to execute on a schedule

Creating a System Manager workflow to execute on a schedule System Manager workflow can execute on a schedule or after an event has been triggered. This can be accomplished by creating a simple batch file, and scheduling that batch file to execute with a third-party tool like Microsoft Task Scheduler.   Note : To avoid user permission conflicts, the following steps must be performed with highest privileges.   In the below example, the workflow makes the i-server restarts every day.   1. The user must first have a valid workflow. This particular workflow is a template that is delivered out-of-the-box with System Manager.   2. Save the workflow in  .smw  format.   3. In a text editor (such as Notepad), enter the command line statement that the task scheduler should execute.     MASysMgr.exe -w C:\filename.smw” “UserName=User1 “Password=1234”   4. Save the file in  .bat  ...
1 comment
Read more

Bursting file subscriptions Microstartegy

Bursting file subscriptions: Delivering  parts of reports across multiple files: Large MicroStrategy reports and documents are often broken up into separate pages by attributes. In a similar way, with Distribution Services, you can split up, or burst, a report or document into multiple files. When the subscription is executed, a separate file is created for each element of each attribute selected for bursting. Each file has a portion of data according to the attributes used to group data in the report (page-by axis) or document (group-by axis). Ex:, you may have a report with information for all regions. You could place Region in the page-by axis and burst the file subscription into the separate regions. This creates one report file for each region. As a second ex:, if you choose to burst your report using the Region and Category attributes, a separate file is created for each combination of Region and Category, such as Central and Books as a report, Central and Ele...
1 comment
Read more

Create an alert-based subscription in MicroStrategy Distribution Services

Create an alert-based subscription in MicroStrategy Distribution Services on Web Subscription to a report or Report Services document which will be executed when a certain conditional threshold is met based on another executing report. For example, a scheduled report executes which shows the Revenue by day for the past week. If the Revenue on any one day falls below a certain value, a subscription to another report or Report Services document can be triggered and delivered to a recipient. An alert based subscription can only be created directly on a report; however, another report or Report Services document can be delivered when the alert based subscription is triggered. Note: you need a grid report to create an alert and you cannot create if you want to create on a document with text boxes. The following example will walk through the basic steps on how to setup a subscription based on an alert like this: Follow the brief  steps bel...
2 comments
Read more