MicroStrategy ONE

Using OLAP Functions

You can better understand how Intelligence Server computes OLAP functions by observing several examples based on the following properties:

  • Window size
  • BreakBy
  • SortBy
  • NULL handling
  • Tie handling

There are two examples for each subsection, comparing the SQL syntax when the database performs the OLAP function calculations with the one when the Intelligence Server performs the computation.

Window Size in Moving Functions

For this example, define metric M1A as Sum(Revenue){~+}. Then create an OLAP metric called OM1, that is defined as

MovingAvg <BreakBy = {Category}, SortBy = (Value)>([M1A],5).

OM1computes the moving average of M1A with a window size of 5. The OLAP metric is computed after it is sorted by the value of M1A in ascending order. Moreover, the computation restarts for every category.

Now, put the attributes Category and Item on the Row axis and the above metrics on the Column axis. Run this report with an empty filter against a database that does not support OLAP functions (in this case Microsoft Access), the following SQL is generated:

Report 5A (MicroStrategy OLAP function - window size) - Microsoft Access

Pass0 - Duration: 0:00:00.60
select a13.[CATEGORY_ID] AS CATEGORY_ID,
	max(a14.[CATEGORY_DESC]) AS CATEGORY_DESC,
	a11.[ITEM_ID] AS ITEM_ID,
	max(a12.[ITEM_NAME]) AS ITEM_NAME,
	sum(a11.[TOT_DOLLAR_SALES]) AS WJXBFS1
from [ITEM_MNTH_SLS] a11, 
	[LU_ITEM] a12, 
	[LU_SUBCATEG] a13, 
	[LU_CATEGORY] a14
where a11.[ITEM_ID] = a12.[ITEM_ID] and 
	a12.[SUBCAT_ID] = a13.[SUBCAT_ID] and 
	a13.[CATEGORY_ID] = a14.[CATEGORY_ID]
group by a13.[CATEGORY_ID],
	a11.[ITEM_ID]
Pass1 - Duration: 0:00:00.01
[An Analytical SQL]

Alternatively, if you execute the above report against a database that does support OLAP computations (in this case Oracle 9i), then the following SQL is generated:

Report 5B (DB OLAP Function - Window Size) - Oracle 9i

Pass0 - Duration: 0:00:10.15
select a13.CATEGORY_ID  CATEGORY_ID,
	a14.CATEGORY_DESC  CATEGORY_DESC,
	a11.ITEM_ID  ITEM_ID,
	a12.ITEM_NAME  ITEM_NAME,
	sum((a11.QTY_SOLD * (a11.UNIT_PRICE - a11.DISCOUNT)))
	WJXBFS1,
	avg(sum((a11.QTY_SOLD * (a11.UNIT_PRICE -
	a11.DISCOUNT)))) over(partition by a13.CATEGORY_ID
	order by sum((a11.QTY_SOLD * (a11.UNIT_PRICE -
	a11.DISCOUNT))) asc rows 4 preceding)  WJXBFS2
from ORDER_DETAIL a11, 
	LU_ITEM a12, 
	LU_SUBCATEG a13, 
	LU_CATEGORY a14
where a11.ITEM_ID = a12.ITEM_ID and 
	a12.SUBCAT_ID = a13.SUBCAT_ID and 
	a13.CATEGORY_ID = a14.CATEGORY_ID
group by a13.CATEGORY_ID,
	a14.CATEGORY_DESC,
	a11.ITEM_ID,
	a12.ITEM_NAME

The following example shows how the Intelligence Server computes OLAP functions when the database does not support OLAP functions. The Intelligence Server retrieves all components: the input metric, BreakBy parameter, and SortBy parameter. In the above examples, since the SortBy parameter setting is by Value, it is sufficient to bring back just the input metric (WJXBFS1).

BreakBy in OLAP Functions

In the Intelligence Server, the BreakBy parameter is only available at the attribute level. In other words, you can start over certain computations of OLAP functions when a part of the metric belongs to a different attribute element.

In the previous example (window size), there is an OLAP function with Category in the BreakBy parameter. If the Analytical Engine computes the OLAP function, Intelligence Server must select this attribute in the select clause. If the database computes the OLAP function, then this attribute must also be in the partition by clause.

Attributes in the BreakBy parameter of an OLAP metric are always applied, meaning that they are always in the Select clause, and if the database computes it, they are also in the Partition by clause. This is true whether the attribute is on the template or not.

Run a report similar to the one for window size, deleting the attributes Category and Item from the template and adding Subcategory. The results for metric M1A are completely different from the previous report because if Category and Item are not on the template, then the level of aggregation for metric M1A is replaced by Subcategory, which is on the template. On the other hand, OLAP metric OM1 must still restart the calculation (break by) for each Category and therefore remains in the appropriate Select and Partition by clauses. Notice that in both the SQLs below, the Intelligence Server always selects Category even though Category is not on the template.

For a database that does not support OLAP functions (in this case Microsoft Access), the following SQL is generated:

Report 6A (MicroStrategy OLAP Function <BreakBy>) - Microsoft Access

Pass0 - Duration: 0:00:00.63
select a11.[SUBCAT_ID] AS SUBCAT_ID,
	max(a12.[SUBCAT_DESC]) AS SUBCAT_DESC,
	a12.[CATEGORY_ID] AS CATEGORY_ID,
	sum(a11.[TOT_DOLLAR_SALES]) AS WJXBFS1
from [CITY_SUBCATEG_SLS] a11, 
	[LU_SUBCATEG] a12
where a11.[SUBCAT_ID] = a12.[SUBCAT_ID]
group by a11.[SUBCAT_ID],
	a12.[CATEGORY_ID]
Pass1 - Duration: 0:00:00.00
[An Analytical SQL]

If the database supports computation of OLAP functions (in this example Oracle), then the following SQL is generated:

Report 6B (DB OLAP Function) - Oracle 

Pass0 - Duration: 0:00:09.39
select a12.SUBCAT_ID  SUBCAT_ID,
	a13.SUBCAT_DESC  SUBCAT_DESC,
	a13.CATEGORY_ID  CATEGORY_ID,
	sum((a11.QTY_SOLD * (a11.UNIT_PRICE - a11.DISCOUNT)))
	WJXBFS1,
	avg(sum((a11.QTY_SOLD * (a11.UNIT_PRICE -
	a11.DISCOUNT)))) over(partition by a13.CATEGORY_ID
	order by sum((a11.QTY_SOLD * (a11.UNIT_PRICE -
	a11.DISCOUNT))) asc rows 4 preceding)  WJXBFS2
from ORDER_DETAIL a11, 
	LU_ITEM a12, 
	LU_SUBCATEG a13
where a11.ITEM_ID = a12.ITEM_ID and 
	a12.SUBCAT_ID = a13.SUBCAT_ID
group by a12.SUBCAT_ID,
	a13.SUBCAT_DESC,
	a13.CATEGORY_ID

Sorting in OLAP Functions

For OLAP functions, sorting is done before performing computations. Basically, there are two choices for the SortBy parameter setting:

  • Sort by Value in Subexpression: See Reports 5B (in Report 5B (DB OLAP Function - Window Size) - Oracle 9i) and 6B (in Report 6B (DB OLAP Function) - Oracle ) for examples. Note that if the database can perform computation of OLAP functions, then the definition of the subexpression M1A displays in the order by clause.

    For example, the above reports contain: "order by sum((a11.QTY_SOLD * (a11.UNIT_PRICE - a11.DISCOUNT))) asc" to represent the <SortBy = Value ascending> parameter setting.

  • Sort by Objects: This type of sorting can use either attributes or metrics. The following examples demonstrate the differences in processing when an attribute or metric is used.

In this example, the OLAP metric is sorted by a normal attribute (either by ID or Desc).

Create an OLAP metric defined as:

OM2 = RunningSum<BreakBy = {[Customer Region]}, SortBy = ([Customer City]@ID asc, Customer@Name dsc)>([M1A])

Then add the attributes Customer Region and Customer, and the metric OM2 to the template.

The metric OM2 is sorted by Customer City@ID in ascending order, then by Customer@Name in descending order, though the attribute Customer City is not on the template. Assume that attribute Customer is a child of the attribute Customer City, and Customer City is a child of Customer Region.

For a database that does not support OLAP functions (in this case Microsoft Access), the following SQL is generated:

Report 7A (MicroStrategy OLAP Function <SortBy>) - Microsoft Access

select a14.[CUST_REGION_ID] AS CUST_REGION_ID,
	a15.[CUST_REGION_NAME] AS CUST_REGION_NAME,
	a11.[CUSTOMER_ID] AS CUSTOMER_ID,
	a12.[CUST_LAST_NAME] AS CUST_LAST_NAME,
	a12.[CUST_FIRST_NAME] AS CUST_FIRST_NAME,
	a12.[CUST_CITY_ID] AS CUST_CITY_ID,
	a11.[TOT_DOLLAR_SALES] AS WJXBFS1
from [CUSTOMER_SLS] a11, 
	[LU_CUSTOMER] a12, 
	[LU_CUST_CITY] a13, 
	[LU_CUST_STATE] a14, 
	[LU_CUST_REGION] a15
where a11.[CUSTOMER_ID] = a12.[CUSTOMER_ID] and 
	a12.[CUST_CITY_ID] = a13.[CUST_CITY_ID] and 
	a13.[CUST_STATE_ID] = a14.[CUST_STATE_ID] and 
	a14.[CUST_REGION_ID] = a15.[CUST_REGION_ID]
[An Analytical SQL]

If the database supports computation of OLAP functions (in this example Oracle 9i), the following SQL is generated:

Report 7B (DB OLAP Function <SortBy>) - Oracle

Pass0 - Duration: 0:00:15.00
select a15.CUST_REGION_ID  CUST_REGION_ID,
	a16.CUST_REGION_NAME  CUST_REGION_NAME,
	a12.CUSTOMER_ID  CUSTOMER_ID,
	a13.CUST_LAST_NAME  CUST_LAST_NAME,
	a13.CUST_FIRST_NAME  CUST_FIRST_NAME,
	a13.CUST_CITY_ID  CUST_CITY_ID,
	sum(sum(a11.ORDER_AMT)) over(partition by
	a15.CUST_REGION_ID order by a13.CUST_CITY_ID asc,
	a13.CUST_LAST_NAME desc, a13.CUST_FIRST_NAME desc rows
	unbounded preceding)  WJXBFS1
from ORDER_FACT a11, 
	LU_ORDER a12, 
	LU_CUSTOMER a13, 
	LU_CUST_CITY a14, 
	LU_CUST_STATE a15, 
	LU_CUST_REGION a16
where a11.ORDER_ID = a12.ORDER_ID and 
	a12.CUSTOMER_ID = a13.CUSTOMER_ID and 
	a13.CUST_CITY_ID = a14.CUST_CITY_ID and 
	a14.CUST_STATE_ID = a15.CUST_STATE_ID and 
	a15.CUST_REGION_ID = a16.CUST_REGION_ID
group by a15.CUST_REGION_ID,
	a16.CUST_REGION_NAME,
	a12.CUSTOMER_ID,
	a13.CUST_LAST_NAME,
	a13.CUST_FIRST_NAME,
	a13.CUST_CITY_ID

In both SQLs, the Intelligence Server selects a13.CUST_CITY_ID, even though attribute Customer City is not on the report. In Report 7B, this attribute is also in the group by clause. Sort by attribute Customer City@ID is done because of attribute Customer that is a child of Customer City.

In Report 7B, the SQL has to issue group by CUST_LAST_NAME and CUST_FIRST_NAME due to the sort by Customer@Name. For optimization purposes, the Intelligence Server only groups by the ID column.

There is a VLDB setting that allows you to group by non-ID columns. This VLDB setting can be set for any report that uses an OLAP metric with a sort by attribute description.

In this example, an OLAP metric is sorted by an attribute and a metric.

Create an OLAP metric defined as:

OM3 = RunningSum<BreakBy = {[Customer Region]}, SortBy = (Customer@[Last Name] desc, [M1A] asc)>([M1A])

Then add attributes Customer Region and Customer, and metric OM3 to the template. The OLAP metric OM3 is sorted by Customer Last Name and then by the metric [M1A].

For databases that do not support OLAP functions (in this case Microsoft Access), the following SQL is generated:

Report 8A (MicroStrategy OLAP Function <SortBy>) - Microsoft Access 

Pass0 - Duration: 0:00:00.47
select a14.[CUST_REGION_ID] AS CUST_REGION_ID,
	a15.[CUST_REGION_NAME] AS CUST_REGION_NAME,
	a11.[CUSTOMER_ID] AS CUSTOMER_ID,
	a12.[CUST_LAST_NAME] AS CUST_LAST_NAME,
	a12.[CUST_FIRST_NAME] AS CUST_FIRST_NAME,
	a11.[TOT_DOLLAR_SALES] AS WJXBFS1
from [CUSTOMER_SLS] a11, 
	[LU_CUSTOMER] a12, 
	[LU_CUST_CITY] a13, 
	[LU_CUST_STATE] a14, 
	[LU_CUST_REGION] a15
where a11.[CUSTOMER_ID] = a12.[CUSTOMER_ID] and 
	a12.[CUST_CITY_ID] = a13.[CUST_CITY_ID] and 
	a13.[CUST_STATE_ID] = a14.[CUST_STATE_ID] and 
	a14.[CUST_REGION_ID] = a15.[CUST_REGION_ID] and
	a14.[CUST_REGION_ID] in (3, 7)
Pass1 - Duration: 0:00:00.06
[An Analytical SQL]

If the database supports computation of OLAP functions (in this example Oracle), the following SQL is generated:

Report 8B (DB OLAP Function <SortBy>) - Oracle

select a15.CUST_REGION_ID  CUST_REGION_ID,
	a16.CUST_REGION_NAME  CUST_REGION_NAME,
	a12.CUSTOMER_ID  CUSTOMER_ID,
	a13.CUST_LAST_NAME  CUST_LAST_NAME,
	a13.CUST_FIRST_NAME  CUST_FIRST_NAME,
	sum(a11.ORDER_AMT)  WJXBFS1,
	sum(sum(a11.ORDER_AMT)) over(partition by
	a15.CUST_REGION_ID order by a13.CUST_LAST_NAME desc,
	sum(a11.ORDER_AMT) desc rows unbounded preceding) 
	WJXBFS2
from ORDER_FACT a11, 
	LU_ORDER a12, 
	LU_CUSTOMER a13, 
	LU_CUST_CITY a14, 
	LU_CUST_STATE a15, 
	LU_CUST_REGION a16
where a11.ORDER_ID = a12.ORDER_ID and 
	a12.CUSTOMER_ID = a13.CUSTOMER_ID and 
	a13.CUST_CITY_ID = a14.CUST_CITY_ID and 
	a14.CUST_STATE_ID = a15.CUST_STATE_ID and 
	a15.CUST_REGION_ID = a16.CUST_REGION_ID
 and a15.CUST_REGION_ID in (7, 3)
group by a15.CUST_REGION_ID,
	a16.CUST_REGION_NAME,
	a12.CUSTOMER_ID,
	a13.CUST_LAST_NAME,
	a13.CUST_FIRST_NAME

NULL Handling in OLAP Functions

When sorted by Intelligence Server, NULL is placed at the end of a list. Databases also put NULL at the end, as required by the ANSI standard.

For RunningCount or MovingCount functions, NULL is always ignored. For other computations, such as RunningSum or MovingAvg, NULL is treated as zero for the sum of values across the function parameters. This behavior is consistent for Intelligence Server and ANSI-compliant database OLAP functions.

Tie Handling in OLAP Functions

Tie Handling is related to the SortBy parameter when you sort a tie in a metric. Intelligence Server uses a Merge-Sort algorithm, which always preserves the original order retrieved from the database (via ODBC) to the Intelligence Server.

It is not known whether databases use the same sort algorithm; therefore, the Intelligence Server and database computation of OLAP functions can produce different results whenever there is a tie on the data.