MSTR Inventory metrics or No Aggregate metrics

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_ID	Fact
20061	200601	10
20061	200602	20
20061	200603	30
20062	200604	40
20062	200605	50

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:

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.
For each "parent" element, find the first or last "child" element that exists in step 1.
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.

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.)
Calculate the metric, grouping by the dimensional level attribute ("parent") but filtering according to the child elements from step 1.

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

Microstrategy Report Services documents and dashboards

Microstrategy Report Services documents vs Dashboards A MicroStrategy Report Services document displays data coming from multiple reports, with the data laid out and designed in presentation-quality format. Most data on a document is from one or more underlying datasets. A dataset is a standard MicroStrategy report. Other document components that do not originate from the dataset, such as static text used for a title or heading, page numbers, and images, are added by the document's designer and are stored in the document's definition. A Report Services (RS) dashboard is a special type of document. An RS dashboard is commonly only one page long, is intended to be viewed online, and usually provides interactive features that let analysts change how they view the dashboard’s data, as well as what data they are viewing. A broad selection of widgets and a wide variety of formatting options allow you to design a customized, interactive dashboard. Both documents and RS dashb...

MicroStrategy URL API Parameters

MicroStrategy URL Structure The following table summarizes the root URL structure used for every request to MicroStrategy Web. Environment Main Application URL Administration URL J2EE http://webserver/MicroStrategy/servlet/mstrWeb http://webserver/MicroStrategy/servlet/mstrWebAdmin .NET http://webserver/MicroStrategy/asp/Main.aspx http://webserver/MicroStrategy/asp/Admin.aspx Every request sent to MicroStrategy Web calls a central controller. Parameters are appended to Main.aspx or mstrWeb (in a .NET and J2EE environment, respectively) to indicate to the controller how the request should be internally forwarded and handled. The following examples show a URL for accessing a MicroStrategy folder when the user does not have an existing session. The URL contains not only the parameters needed to connect to MicroStrategy Web, but also the parameters needed to log on and create a session. J2EE environment: <a href="http:...

mstrio – Python and R wrappers for the MicroStrategy

mstrio – Python and R wrappers for the MicroStrategy REST APIs Connecting to MicroStrategy Create a connection to the Intelligence Server using Connection() and  connect() in Python and R, respectively. Required arguments for the   Connection()  function are the URL for the MicroStrategy REST API server, MicroStrategy Intelligence Server username and password, as well as the MicroStrategy project name. By default, the  connect()  function anticipates your MicroStrategy Intelligence Server username and password. LDAP authentication is also supported. Use the optional argument  login_mode=16  in the  connect()  function for LDAP authentication. Extract data from cubes and reports To extract data from MicroStrategy cubes and reports, use the get_cube()  and  get_report() functions. Use...

Metric values are repeated across rows when a report is executed in MicroStrategy

Metric values are repeated across rows when a report is executed in MicroStrategy When comparing report results between DB Query Tool and MicroStrategy, some reports show repeated metric values in MicroStrategy where there were none in DB Query Tool. To illustrate the issue, a fact table CAT_ITEM_SLS has been added into the MicroStrategy Tutorial project and populated with a small set of three rows. CAT_ID ITEM_ID REVENUE 1 1 10 1 2 20 2 2 30 Report results in DB Query Tool: Report results in MicroStrategy: In MicroStrategy, the row for "Art As Experience" in the Spring 2007 catalog repeats the $20 value from the Winter 2007 catalog, where DB Query Tool shows the $30 value from the fact table. CAUSE The discrepancy occurs because the attribute elements for Catalog and Item are in a many-to-many relationship, but the attribute relationship in the MicroStrategy schema is defined incorrectly w...

Microstrategy Dossiers explained

Microstrategy Dossiers With the release of MicroStrategy 10.9, we’ve taken a leap forward in our dashboarding capabilities by simplifying the user experience, adding storytelling, and collaboration.MSTR has evolved dashboards to the point that they are more than dashboards - they are interactive, collaborative analytic stories . Ultimately, it was time to go beyond dashboards, both in concept and in name, and so the've renamed VI dashboards to ‘ dossiers ’. Dossiers can be created by using the new Desktop product or Workstation or simply from the Web interface which replaces Visual Insights. All the existing visual Insights dashboards will be converted to Dossiers With MicroStrategy 10.9, there was an active focus on making it easier to build dashboards for the widest audience of end users. To achieve this, some key new capabilities were added that make it easier to author, read, interact and collaborate on dashboards ...

Types of filters in Microstrategy

Types of filters in Microstrategy Below are the types of filters: 1. Attribute qualification filter These types of qualifications restrict data related to attributes on the report. a) Attribute form qualification Filters data related to a business attribute’s form(s), such as ID or description. • For example, the attribute Customer has the forms ID, First Name, Last Name, Address, and Birth Date. An attribute form qualification might filter on the form Last Name, the operator Begins With, and the letter H. The results show a list of customers whose last names start with the letter H. b) Attribute element list qualification Filters data related to a business attribute’s elements, such as New York, Washington, and San Francisco, which are elements of the attribute City. • For example, the attribute Customer has the elements John Smith, Jane Doe, William Hill, and so on. An attribute element list qualification can filter data to display only those customer...

Custom Tooltips in Microstrategy developer and Web

Custom Tooltips in Microstrategy developer and Web The following table describes the macros you can use to customize graph tooltips in both MicroStrategy Developer and MicroStrategy Web: Macro Information Displayed {&TOOLTIP} All relevant labels and values associated with a graph item. {&GROUPLABEL} Name of the graph item's category. This value is often the graph item's attribute element information, as attributes are commonly used as the categories of graph reports. {&SERIESLABEL} Name of the graph item’s series. This value is often the graph item's metric name information, as metrics are commonly used as the series of graph reports. {&VALUE} The value of a given data point. {&XVALUE} The X-value of a data point. Only applicable to Bubble charts and Scatter plots. {&YVALUE} The Y-value of a data point. Only applicable to Bubble charts and Scatter plots. {&ZVALUE} The Z-value of a data point. Only applicable to Bubble charts and Scatter plots. {...

Prompt-in-prompt(Nested Prompts) in Microstrategy

Prompt-in-prompt(Nested Prompts) in Microstrategy Nested prompts allows you to create one prompt based on the other and other bases on another, nested prompts allows us to prompt the highest level(Like year) to middle level(like Quarter, then to the low level(like Month). Here you can see how to create a 3-level deep nested prompt that will prompt the user to select a year, then a quarter within that year, then a month within that quarter. Prompt-in-prompt is a feature in which the answer to one prompt is used to define another prompt. This feature is only implemented for element list prompts . The following procedure describes how to achieve this: Create the highest level filter. This is a filter which contains a prompt on an attribute element list. Create a filter on the attribute "Year." Click "prompt on attribute element list" and click "Next" through the rest of the screens to accept the default values. Do not set any additio...

Custom formatting Microstrategy metric format into 1M 2M etc

Custom formatting metric values of 1,100,000 into a 1.1 million 1.1M type format In addition to the the pre-defined options for metric formatting, MicroStrategy supports custom formatting. The MicroStrategy Tutorial project is used to explain how users can customize numbers from "1,000,000" to a "1.00" format. Consider a report containing row data values greater than a million, as illustrated below: To format these metric values to use a decimal (i.e., 1.1) instead of showing all the numerals, right-click on the metric (' Revenue ' , for this example) and select Formatting > Revenue Values , as shown below: Select " Custom " as a category in the Number tab and enter ' 0,,.## ' (without quotation marks), as shown below: Confirm that the format applied properly:

Purge Web Caches in MicroStrategy Web

Purge Web Caches in MicroStrategy Web By executing the mstr server by embedding the blue text below with admin privileges purges web server cache. https:// mstrserver.com /MicroStrategy/asp/Admin.aspx//mstrWebAdmin/?pg=purgecaches MicroStrategy Web and Web Universal caches various properties that are related to the user, project, or the MicroStrategy Intelligence Server. This helps in reducing the response time for every request by delivering the properties from a closer location than the original MicroStrategy Intelligence Server. In majority of the cases, the default caching properties should be applicable to the business intelligence infrastructure. There are 3 types of caches used in MicroStrategy Web. These are described below: Session Cache - Specific to a user and exists only while the user is logged in. This cache is deleted when the user logs out or if there's no activity for a specified timeout interval. The default value for a timeout is 1200 seco...

Microstrategy Analytics

Search This Blog