Using OLAP functions without a DMR model

This is on Cognos 10.1.1 with the GO Data Warehouse (query) package.

I was asked a somewhat difficult question. On a relational model the report shows a crosstab years with another attribute nested in rows and measures in columns. How can the growth % of the years from the previous year to the current year be displayed?

Normally I would create data items for each measure for each year that needs to be included in the expression. Something along the lines of
case when [Year] = extract(‘year’,current_date) then [Measure] else 0 end
The issue with this is that this is a crosstab – how can you make it accommodate different measures on different edges?

A better solution would be one that mimics dimensional functionality. I want to divide 2011 by 2010, with all of the measures acting accordingly.

In a dimensional query I would simply do [Current Year]/[Previous Year]. If I didn’t have any calculated members in the cube, I might try something like this:

item(filter([Year Level],[Year Caption] = #sq(timestampMask(‘yyyy’,$current_timestamp))#),0)

The filter returns a set of all of the years that match the current year, and the item takes that set and returns the first member in it.

Because this is a relational model, OLAP functions shouldn’t work.

(item(filter([Year],[Year]=2007),0)/item(filter([Year],[Year]=2006),0))-1

But they do.

My guess is that Cognos is implicitly converting the [Year] field into a level, like it would be in a DMR model. The odd thing is, that while it works, I don’t see it reflected in the SQL of the xtab.

Cognos SQL:

select 
       SLS_ORDER_METHOD_DIM.ORDER_METHOD_EN  as  Order_method_typekey,
       GO_TIME_DIM.CURRENT_YEAR  as  Yearkey,
       XSUM(SLS_SALES_FACT.QUANTITY  for SLS_ORDER_METHOD_DIM.ORDER_METHOD_EN,GO_TIME_DIM.CURRENT_YEAR )  as  Quantity,
       XSUM(SLS_SALES_FACT.SALE_TOTAL  for SLS_ORDER_METHOD_DIM.ORDER_METHOD_EN,GO_TIME_DIM.CURRENT_YEAR )  as  Revenue,
       XSUM(SLS_SALES_FACT.GROSS_PROFIT  for SLS_ORDER_METHOD_DIM.ORDER_METHOD_EN,GO_TIME_DIM.CURRENT_YEAR )  as  Gross_profit
 from 
       great_outdoors_warehouse..GOSALESDW.SLS_ORDER_METHOD_DIM SLS_ORDER_METHOD_DIM,
       great_outdoors_warehouse..GOSALESDW.GO_TIME_DIM GO_TIME_DIM,
       great_outdoors_warehouse..GOSALESDW.SLS_SALES_FACT SLS_SALES_FACT
 where 
       (GO_TIME_DIM.CURRENT_YEAR in (2006,2007)) and 
       (SLS_SALES_FACT.ORDER_METHOD_KEY = SLS_ORDER_METHOD_DIM.ORDER_METHOD_KEY) and 
       (SLS_SALES_FACT.ORDER_DAY_KEY = GO_TIME_DIM.DAY_KEY)
 group by 
       SLS_ORDER_METHOD_DIM.ORDER_METHOD_EN,
       GO_TIME_DIM.CURRENT_YEAR

Running a trace in SQL Server shows that it’s not doing anything different:

declare @p1 int
set @p1=1
exec sp_prepare @p1 output,NULL,N'select "SLS_ORDER_METHOD_DIM"."ORDER_METHOD_EN" AS "Order_method_typekey", "GO_TIME_DIM"."CURRENT_YEAR" AS "Yearkey", sum("SLS_SALES_FACT"."QUANTITY") AS "Quantity", sum("SLS_SALES_FACT"."SALE_TOTAL") AS "Revenue", sum("SLS_SALES_FACT"."GROSS_PROFIT") AS "Gross_profit" from "GOSALESDW"."SLS_ORDER_METHOD_DIM" "SLS_ORDER_METHOD_DIM", "GOSALESDW"."GO_TIME_DIM" "GO_TIME_DIM", "GOSALESDW"."SLS_SALES_FACT" "SLS_SALES_FACT" where "GO_TIME_DIM"."CURRENT_YEAR" in (2006, 2007) and "SLS_SALES_FACT"."ORDER_METHOD_KEY" = "SLS_ORDER_METHOD_DIM"."ORDER_METHOD_KEY" and "SLS_SALES_FACT"."ORDER_DAY_KEY" = "GO_TIME_DIM"."DAY_KEY" group by "SLS_ORDER_METHOD_DIM"."ORDER_METHOD_EN", "GO_TIME_DIM"."CURRENT_YEAR"',1
select @p1

So it must be doing the calculation locally. Local processing is frowned upon, so you should try to find another way to handle this. Ideally building a real cube and using real OLAP functionality.

Report XML:

<report xmlns="http://developer.cognos.com/schemas/report/8.0/" useStyleVersion="10" expressionLocale="en-us">
				<modelPath>/content/folder[@name='Samples']/folder[@name='Models']/package[@name='GO Data Warehouse (query)']/model[@name='model']</modelPath>
				<drillBehavior modelBasedDrillThru="true"/>
				<layouts>
					<layout>
						<reportPages>
							<page name="Page1">
								<style>
									<defaultStyles>
										<defaultStyle refStyle="pg"/>
									</defaultStyles>
								</style>
								<pageBody>
									<style>
										<defaultStyles>
											<defaultStyle refStyle="pb"/>
										</defaultStyles>
									</style>
									<contents><crosstab horizontalPagination="true" name="Crosstab1" refQuery="Query1" rowsPerPage="999999">
			<crosstabCorner>
				<contents/>
				<style>
					<defaultStyles>
						<defaultStyle refStyle="xm"/>
					</defaultStyles>
				</style>
			</crosstabCorner>
			
			
			<noDataHandler>
				<contents>
					<block>
						<contents>
							<textItem>
								<dataSource>
									<staticValue>No Data Available</staticValue>
								</dataSource>
								<style>
									<CSS value="padding:10px 18px;"/>
								</style>
							</textItem>
						</contents>
					</block>
				</contents>
			</noDataHandler>
			<style>
				<CSS value="border-collapse:collapse"/>
				<defaultStyles>
					<defaultStyle refStyle="xt"/>
				</defaultStyles>
			</style>
		<crosstabRows><crosstabNode><crosstabNestedNodes><crosstabNode><crosstabNodeMembers><crosstabNodeMember refDataItem="Order method type" edgeLocation="e7"><style><defaultStyles><defaultStyle refStyle="ml"/></defaultStyles></style><contents><textItem><dataSource><memberCaption/></dataSource></textItem></contents></crosstabNodeMember></crosstabNodeMembers></crosstabNode></crosstabNestedNodes><crosstabNodeMembers><crosstabNodeMember refDataItem="2007 %" edgeLocation="e6"><style><defaultStyles><defaultStyle refStyle="ml"/></defaultStyles></style><contents><textItem><dataSource><dataItemValue refDataItem="2007 %"/></dataSource></textItem></contents><factCell><style><defaultStyles><defaultStyle refStyle="mv"/></defaultStyles><dataFormat><percentFormat/></dataFormat></style></factCell></crosstabNodeMember></crosstabNodeMembers></crosstabNode><crosstabNode><crosstabNestedNodes><crosstabNode><crosstabNodeMembers><crosstabNodeMember refDataItem="Order method type" edgeLocation="e2"><style><defaultStyles><defaultStyle refStyle="ml"/></defaultStyles></style><contents><textItem><dataSource><memberCaption/></dataSource></textItem></contents></crosstabNodeMember></crosstabNodeMembers></crosstabNode></crosstabNestedNodes><crosstabNodeMembers><crosstabNodeMember refDataItem="Year" edgeLocation="e1"><style><defaultStyles><defaultStyle refStyle="ml"/></defaultStyles></style><contents><textItem><dataSource><memberCaption/></dataSource></textItem></contents></crosstabNodeMember></crosstabNodeMembers></crosstabNode></crosstabRows><crosstabFactCell><contents><textItem><dataSource><cellValue/></dataSource></textItem></contents><style><defaultStyles><defaultStyle refStyle="mv"/></defaultStyles></style></crosstabFactCell><crosstabColumns><crosstabNode><crosstabNodeMembers><crosstabNodeMember refDataItem="Quantity" edgeLocation="e3"><style><defaultStyles><defaultStyle refStyle="ml"/></defaultStyles></style><contents><textItem><dataSource><memberCaption/></dataSource></textItem></contents></crosstabNodeMember></crosstabNodeMembers></crosstabNode><crosstabNode><crosstabNodeMembers><crosstabNodeMember refDataItem="Revenue" edgeLocation="e4"><style><defaultStyles><defaultStyle refStyle="ml"/></defaultStyles></style><contents><textItem><dataSource><memberCaption/></dataSource></textItem></contents></crosstabNodeMember></crosstabNodeMembers></crosstabNode><crosstabNode><crosstabNodeMembers><crosstabNodeMember refDataItem="Gross profit" edgeLocation="e5"><style><defaultStyles><defaultStyle refStyle="ml"/></defaultStyles></style><contents><textItem><dataSource><memberCaption/></dataSource></textItem></contents></crosstabNodeMember></crosstabNodeMembers></crosstabNode></crosstabColumns></crosstab></contents>
								</pageBody>
							</page>
						</reportPages>
					</layout>
				</layouts>
			<XMLAttributes><XMLAttribute name="RS_CreateExtendedDataItems" value="false" output="no"/><XMLAttribute name="listSeparator" value="," output="no"/><XMLAttribute name="RS_modelModificationTime" value="2011-06-09T13:50:33.233Z" output="no"/></XMLAttributes><queries><query name="Query1"><source><model/></source><selection><dataItem name="Year" aggregate="none" rollupAggregate="none"><expression>[Sales (query)].[Time].[Year]</expression><XMLAttributes><XMLAttribute name="RS_dataType" value="1" output="no"/><XMLAttribute name="RS_dataUsage" value="attribute" output="no"/></XMLAttributes></dataItem><dataItem name="Order method type" aggregate="none" rollupAggregate="none"><expression>[Sales (query)].[Order method].[Order method type]</expression><XMLAttributes><XMLAttribute name="RS_dataType" value="3" output="no"/><XMLAttribute name="RS_dataUsage" value="attribute" output="no"/></XMLAttributes></dataItem><dataItem name="Quantity" aggregate="total"><expression>[Sales (query)].[Sales fact].[Quantity]</expression><XMLAttributes><XMLAttribute name="RS_dataType" value="1" output="no"/><XMLAttribute name="RS_dataUsage" value="fact" output="no"/></XMLAttributes></dataItem><dataItem name="Revenue" aggregate="total"><expression>[Sales (query)].[Sales fact].[Revenue]</expression><XMLAttributes><XMLAttribute name="RS_dataType" value="2" output="no"/><XMLAttribute name="RS_dataUsage" value="fact" output="no"/></XMLAttributes></dataItem><dataItem name="Gross profit" aggregate="total"><expression>[Sales (query)].[Sales fact].[Gross profit]</expression><XMLAttributes><XMLAttribute name="RS_dataType" value="2" output="no"/><XMLAttribute name="RS_dataUsage" value="fact" output="no"/></XMLAttributes></dataItem><dataItem name="2007 %"><expression>(item(filter([Year],[Year]=2007),0)/item(filter([Year],[Year]=2006),0))-1</expression><XMLAttributes><XMLAttribute name="RS_dataType" value="1" output="no"/><XMLAttribute name="RS_relationalExpression" value="true" output="no"/></XMLAttributes></dataItem></selection><detailFilters><detailFilter><filterExpression>[Year] in (2006,2007,2008)</filterExpression></detailFilter></detailFilters></query></queries><reportName>test</reportName></report>
Advertisements

4 Responses to Using OLAP functions without a DMR model

  1. charon says:

    cognos…thy mistery of life in a nutshell!

  2. Anil says:

    Hi Paul

    I have a requirment where I need to highlight difference in data between “one” Cognos report run at different dates in a month. My question is — Can this be achieved only within Cognos? Is there a way we can highlight what data has been changed between the report outputs generated with run on the first of the month and then on the 15th of the same month?

    Please let me know if this is possible and kindly let me know if you need more clarity on the requirement which I have.

    Thank you for the assistance.

    Regards
    Anil

    • CognosPaul says:

      I think both BSP and Motio have tools that can do this. If you don’t want to go through them, there are a number of tools available online for comparing PDF and Excel documents. You’ll need to have the reports saved in the content store then compare them manually.

  3. Nimrod Avissar says:

    Hi,
    Cognos treats any crosstab as if it was built on dimensional data. That’s why you can also use the “measure within set” syntax in crab (and in fact, Cognos produces this syntax when adding aggregates automatically). This is indeed done locally (which is why master detail relationships are so slow with crosstabs). In 10.1.1 it would be wise to use the DQM engine for such reports, to get faster results using the Hybrid Query.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: