Tag: theory

Uncategorized

Why detail filters should never be used in OLAP

| cognospaul

The following post is be based on the Sales and Marketing cube on Cognos 10.1.1, but it should be true for all versions of Cognos.

One of the most common questions I am asked is why I am so intensely against detail filters on OLAP queries. Official Cognos guides show examples of detail filters on OLAP, and it’s much faster to develop when using detail filters, why shouldn’t I allow them?

To begin detail filters prevent authors from answering complex business questions. What is the profit margin per year for every product that had a profit margin exceeding 60% in the years 2006 and 2007? A filter of [Profit Margin]>=0.6 doesn’t allow us to specify which years. Let’s make the business question even easier: What is the profit margin, per year, for each project where the profit margin for all the years exceeds 60%?

These are the results I expect to receive:

The rows node has the following expression:
filter([sales_and_marketing].[Products].[Products].[Product],[Profit Margin]>=0.6)

Native MDX for the report:
WITH MEMBER [Time]..[@MEMBER].[COG_OQP_USR_Summary(Year)] AS 'IIF([MEASURES].CURRENTMEMBER IS [MEASURES]..[Profit Margin], ([Time]..[@MEMBER].[COG_OQP_INT_m1], [MEASURES]..[Profit Margin]), AGGREGATE([Time]..[Year 2].MEMBERS))', SOLVE_ORDER = 4, MEMBER_CAPTION = 'Summary(Year)' MEMBER [Time]..[@MEMBER].[COG_OQP_INT_m1] AS 'AGGREGATE([Time]..[Year 2].MEMBERS, [MEASURES]..[Profit Margin])', SOLVE_ORDER = 4, MEMBER_CAPTION = '[Time]..[@MEMBER].[COG_OQP_INT_m1]' SELECT UNION([Time]..[Year 2].MEMBERS, {[Time]..[@MEMBER].[COG_OQP_USR_Summary(Year)]}, ALL) DIMENSION PROPERTIES PARENT_LEVEL, CHILDREN_CARDINALITY ON AXIS(0), FILTER([Products]..[Product 4].MEMBERS, [MEASURES]..[Profit Margin] >= 0.6) DIMENSION PROPERTIES PARENT_LEVEL, CHILDREN_CARDINALITY ON AXIS(1), {[MEASURES]..[Profit Margin]} DIMENSION PROPERTIES PARENT_LEVEL, CHILDREN_CARDINALITY ON AXIS(2) FROM [Sales and Marketing]

The summary column makes it a bit complex, but otherwise the MDX is easy to read.

Let’s remove the filter function and apply the [Profit Margin]>=0.6 as a detail filter

MDX:
WITH MEMBER [Time]..[@MEMBER].[COG_OQP_USR_Summary(Year)] AS 'IIF([MEASURES].CURRENTMEMBER IS [MEASURES]..[@MEMBER].[COG_OQP_INT_m1], ([Time]..[@MEMBER].[COG_OQP_INT_m3], [MEASURES]..[Profit Margin]), IIF([MEASURES].CURRENTMEMBER IS [MEASURES]..[@MEMBER].[COG_OQP_USR_Profit Margin], ([Time]..[@MEMBER].[COG_OQP_INT_m3], [MEASURES]..[Profit Margin]), IIF([MEASURES].CURRENTMEMBER IS [MEASURES]..[Profit Margin], ([Time]..[@MEMBER].[COG_OQP_INT_m3], [MEASURES]..[Profit Margin]), AGGREGATE([Time]..[Year 2].MEMBERS))))', SOLVE_ORDER = 4, MEMBER_CAPTION = 'Summary(Year)' MEMBER [Time]..[@MEMBER].[COG_OQP_INT_m3] AS 'AGGREGATE(FILTER([Time]..[Year 2].MEMBERS, NOT ISEMPTY([Product brand]..[@MEMBER].[COG_OQP_INT_m2])), [MEASURES]..[Profit Margin])', SOLVE_ORDER = 4, MEMBER_CAPTION = '[Time]..[@MEMBER].[COG_OQP_INT_m3]' MEMBER [Product brand]..[@MEMBER].[COG_OQP_INT_m2] AS 'IIF(([MEASURES]..[Profit Margin], [Product brand].[].DEFAULTMEMBER) >= 0.6, 1, NULL)', SOLVE_ORDER = 2, MEMBER_CAPTION = '' MEMBER [MEASURES]..[@MEMBER].[COG_OQP_USR_Profit Margin] AS '[MEASURES]..[@MEMBER].[COG_OQP_INT_m1]', SOLVE_ORDER = 2, MEMBER_CAPTION = 'Profit Margin' MEMBER [MEASURES]..[@MEMBER].[COG_OQP_INT_m1] AS 'IIF([MEASURES]..[Profit Margin] >= 0.6, [MEASURES]..[Profit Margin], NULL)', SOLVE_ORDER = 2, MEMBER_CAPTION = 'Profit Margin' SET [COG_OQP_INT_s1] AS 'CROSSJOIN([Time]..[Year 2].MEMBERS, [Products]..[Product 4].MEMBERS)' SELECT UNION(FILTER([Time]..[Year 2].MEMBERS, COUNT(FILTER([Products]..[Product 4].MEMBERS, ([MEASURES]..[Profit Margin], [Product brand].[].DEFAULTMEMBER) >= 0.6), INCLUDEEMPTY) > 0), HEAD({[Time]..[@MEMBER].[COG_OQP_USR_Summary(Year)]}, IIF(COUNT(FILTER([COG_OQP_INT_s1], ([MEASURES]..[Profit Margin], [Product brand].[].DEFAULTMEMBER) >= 0.6), INCLUDEEMPTY) > 0, 1, 0)), ALL) DIMENSION PROPERTIES PARENT_LEVEL, CHILDREN_CARDINALITY ON AXIS(0), FILTER([Products]..[Product 4].MEMBERS, COUNT(FILTER([Time]..[Year 2].MEMBERS, ([MEASURES]..[Profit Margin], [Product brand].[].DEFAULTMEMBER) >= 0.6), INCLUDEEMPTY) > 0) DIMENSION PROPERTIES PARENT_LEVEL, CHILDREN_CARDINALITY ON AXIS(1), {[MEASURES]..[@MEMBER].[COG_OQP_USR_Profit Margin]} DIMENSION PROPERTIES PARENT_LEVEL, CHILDREN_CARDINALITY ON AXIS(2) FROM [Sales and Marketing]

Suddenly we have 16 products instead of 13. The values are also artificially inflated for some of the products that are in the correct crosstab. What’s going on? The detail filter is being applied on intersections of the year and product. Any intersection that is below 60% is being suppressed, so the summary is showing the values for the unsuppressed years for each product. Any product that had any year exceeding 60% is displayed, and the summary column would indicate that the calculated total for all the years also exceeds 60%. Bad business decisions might be made because of this faulty data.

Have a report XML:

<report xmlns="http://developer.cognos.com/schemas/report/8.0/" useStyleVersion="10" expressionLocale="en-us" interactivePageBreakByFrame="true">
				<modelPath>/content/folder[@name='Samples']/folder[@name='Cubes']/package[@name='Sales and Marketing (cube)']/model[@name='2010-06-24T18:14:00.360Z']</modelPath>
				<drillBehavior modelBasedDrillThru="true"/>
				<queries>
					<query name="Query1">
						<source>
							<model/>
						</source>
						<selection><dataItem name="Product" aggregate="none" rollupAggregate="none"><expression>filter([sales_and_marketing].[Products].[Products].[Product],[Profit Margin]&gt;=0.6)</expression></dataItem><dataItem name="Year" aggregate="none" rollupAggregate="none"><expression>[sales_and_marketing].[Time].[Time].[Year]</expression></dataItem><dataItem name="Profit Margin"><expression>[sales_and_marketing].[Measures].[Profit Margin]</expression><XMLAttributes><XMLAttribute name="RS_dataType" value="9" output="no"/></XMLAttributes></dataItem><dataItem solveOrder="1" name="Summary(Year)" aggregate="none" rollupAggregate="none"><expression>aggregate(currentMeasure within set [Year])</expression></dataItem></selection>
					</query>
				<query name="Query2">
						<source>
							<model/>
						</source>
						<selection><dataItem name="Product" aggregate="none" rollupAggregate="none"><expression>[sales_and_marketing].[Products].[Products].[Product]</expression></dataItem><dataItem name="Year" aggregate="none" rollupAggregate="none"><expression>[sales_and_marketing].[Time].[Time].[Year]</expression></dataItem><dataItem name="Profit Margin"><expression>[sales_and_marketing].[Measures].[Profit Margin]</expression><XMLAttributes><XMLAttribute name="RS_dataType" value="9" output="no"/></XMLAttributes></dataItem><dataItem solveOrder="1" name="Summary(Year)" aggregate="none" rollupAggregate="none"><expression>aggregate(currentMeasure within set [Year])</expression></dataItem></selection>
					<detailFilters><detailFilter><filterExpression>[Profit Margin]&gt;=0.6</filterExpression></detailFilter></detailFilters></query></queries>
				<layouts>
					<layout>
						<reportPages>
							<page name="Page1"><style><defaultStyles><defaultStyle refStyle="pg"/></defaultStyles></style>
								<pageBody><style><defaultStyles><defaultStyle refStyle="pb"/></defaultStyles></style>
									<contents>
										<crosstab refQuery="Query1" horizontalPagination="true" name="Crosstab1">
											<crosstabCorner><style><defaultStyles><defaultStyle refStyle="xm"/></defaultStyles></style><contents><textItem><dataSource><dataItemLabel refDataItem="Profit Margin"/></dataSource></textItem></contents></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>
												<defaultStyles>
													<defaultStyle refStyle="xt"/>
												</defaultStyles>
												<CSS value="border-collapse:collapse"/>
											</style>
										<crosstabRows><crosstabNode><crosstabNodeMembers><crosstabNodeMember refDataItem="Product" edgeLocation="e1"><style><defaultStyles><defaultStyle refStyle="ml"/></defaultStyles></style><contents><textItem><dataSource><reportExpression>RowNumber()</reportExpression></dataSource></textItem><textItem><dataSource><staticValue>. </staticValue></dataSource></textItem><textItem><dataSource><memberCaption/></dataSource></textItem></contents></crosstabNodeMember></crosstabNodeMembers></crosstabNode></crosstabRows><crosstabColumns><crosstabNode><crosstabNodeMembers><crosstabNodeMember refDataItem="Year" edgeLocation="e2"><style><defaultStyles><defaultStyle refStyle="ml"/></defaultStyles></style><contents><textItem><dataSource><memberCaption/></dataSource></textItem></contents></crosstabNodeMember></crosstabNodeMembers></crosstabNode><crosstabNode><crosstabNodeMembers><crosstabNodeMember refDataItem="Summary(Year)" edgeLocation="e3"><style><defaultStyles><defaultStyle refStyle="ol"/></defaultStyles></style><contents><textItem><dataSource><dataItemLabel refDataItem="Summary(Year)"/></dataSource></textItem></contents><factCell><style><defaultStyles><defaultStyle refStyle="ov"/></defaultStyles></style></factCell></crosstabNodeMember></crosstabNodeMembers></crosstabNode></crosstabColumns><defaultMeasure refDataItem="Profit Margin"/><crosstabFactCell><contents><textItem><dataSource><cellValue/></dataSource></textItem></contents><style><defaultStyles><defaultStyle refStyle="mv"/></defaultStyles></style></crosstabFactCell></crosstab>
									<crosstab refQuery="Query2" horizontalPagination="true" name="Crosstab2">
											<crosstabCorner><style><defaultStyles><defaultStyle refStyle="xm"/></defaultStyles></style><contents><textItem><dataSource><dataItemLabel refDataItem="Profit Margin"/></dataSource></textItem></contents></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>
												<defaultStyles>
													<defaultStyle refStyle="xt"/>
												</defaultStyles>
												<CSS value="border-collapse:collapse"/>
											</style>
										<crosstabRows><crosstabNode><crosstabNodeMembers><crosstabNodeMember refDataItem="Product" edgeLocation="e1"><style><defaultStyles><defaultStyle refStyle="ml"/></defaultStyles></style><contents><textItem><dataSource><reportExpression>RowNumber()</reportExpression></dataSource></textItem><textItem><dataSource><staticValue>. </staticValue></dataSource></textItem><textItem><dataSource><memberCaption/></dataSource></textItem></contents></crosstabNodeMember></crosstabNodeMembers></crosstabNode></crosstabRows><crosstabColumns><crosstabNode><crosstabNodeMembers><crosstabNodeMember refDataItem="Year" edgeLocation="e2"><style><defaultStyles><defaultStyle refStyle="ml"/></defaultStyles></style><contents><textItem><dataSource><memberCaption/></dataSource></textItem></contents></crosstabNodeMember></crosstabNodeMembers></crosstabNode><crosstabNode><crosstabNodeMembers><crosstabNodeMember refDataItem="Summary(Year)" edgeLocation="e3"><style><defaultStyles><defaultStyle refStyle="ol"/></defaultStyles></style><contents><textItem><dataSource><dataItemLabel refDataItem="Summary(Year)"/></dataSource></textItem></contents><factCell><style><defaultStyles><defaultStyle refStyle="ov"/></defaultStyles></style></factCell></crosstabNodeMember></crosstabNodeMembers></crosstabNode></crosstabColumns><defaultMeasure refDataItem="Profit Margin"/><crosstabFactCell><contents><textItem><dataSource><cellValue/></dataSource></textItem></contents><style><defaultStyles><defaultStyle refStyle="mv"/></defaultStyles></style></crosstabFactCell></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="2010-06-24T18:14:00.390Z" output="no"/></XMLAttributes></report>
10, Javascript

Layout Component References

| cognospaul

Zephyr, author of the blog Cognos and Me, wrote an excellent (but brief) article on what Layout Components are, and why you’d want to use them. I’m going to expand briefly on what he wrote, and give an example of my own.

In his example, he created a generic header that authors would use in their reports. This allows the author of the components report to control the header in all of the reports referencing it. Very useful for maintaining a corporate look and feel.

There is another use. While the Cognos authors that I work with excel in SQL and MDX, many of them are lacking knowledge in JavaScript. One of the most common requests for help that I receive is to code a simple container that allows users to switch between viewing a table or a graph.

In order to accomplish this, you would need to use JavaScript. So, instead of having to help each author individually, I find it easier to create a single component library report. To create the component library, simply create a new report. Since this is post is about components, and not about JavaScript I’ll give you the XML of an example components library.

<report xmlns="http://developer.cognos.com/schemas/report/3.0/" expressionLocale="en-us"><!--RS:8.2-->
	<modelPath>/content/package[@name='GO Sales']/model[@name='model']</modelPath>
	<layouts>
		<layout>
			<reportPages>
				<page class="pg" name="Page1">
					<pageBody class="pb">
						<contents><table class="tb" name="Switch Container"><tableRows><tableRow><tableCells><tableCell><contents><textItem name="SW - Object Name"><dataSource><staticValue>Object Name</staticValue></dataSource></textItem></contents><style><CSS value="padding-left:3px;padding-top:2px;padding-bottom:2px;font-weight:bold;background-color:#FAFAFA;border-top:1pt solid silver;border-bottom:1pt solid silver;border-left:1pt solid silver"/></style></tableCell><tableCell><contents><HTMLItem description="span">
														<dataSource>
															<staticValue>&lt;span onclick="
table = this.parentNode.parentNode.parentNode;
divArr = table.getElementsByTagName('Div');
for (var i = 0; i &lt; divArr.length; i++) {
  if (divArr[i].getAttribute('id') != null &amp;&amp; divArr[i].getAttribute('id') == 'First' ) {divArr[i].style.display = 'block';}
  if (divArr[i].getAttribute('id') != null &amp;&amp; divArr[i].getAttribute('id') == 'Second' ) {divArr[i].style.display = 'none';}
}
this.style.fontWeight='bold';
this.nextSibling.nextSibling.style.fontWeight='normal';"
style="font-weight:bold"
&gt;</staticValue>
														</dataSource>
													</HTMLItem>
													<textItem name="SW - First Label"><dataSource><staticValue>Graph</staticValue></dataSource></textItem><HTMLItem description="span">
														<dataSource>
															<staticValue>&lt;/span&gt; | &lt;span onClick="
table = this.parentNode.parentNode.parentNode;
divArr = table.getElementsByTagName('Div');
for (var i = 0; i &lt; divArr.length; i++) {
  if (divArr[i].getAttribute('id') != null &amp;&amp; divArr[i].getAttribute('id') == 'First' ) {divArr[i].style.display = 'none';}
  if (divArr[i].getAttribute('id') != null &amp;&amp; divArr[i].getAttribute('id') == 'Second' ) {divArr[i].style.display = 'block';}
}
this.style.fontWeight='bold';
this.previousSibling.previousSibling.style.fontWeight='normal';"
style="font-weight:normal"
&gt;</staticValue>
														</dataSource>
													</HTMLItem>
													<textItem name="SW - Second Label"><dataSource><staticValue>Table</staticValue></dataSource></textItem><HTMLItem description="/span">
														<dataSource>
															<staticValue>&lt;/span&gt;</staticValue>
														</dataSource>
													</HTMLItem>
												</contents><style><CSS value="text-align:right;padding-right:3px;padding-top:2px;padding-bottom:2px;background-color:#FAFAFA;border-top:1pt solid silver;border-bottom:1pt solid silver;border-right:1pt solid silver"/></style></tableCell></tableCells></tableRow><tableRow><tableCells><tableCell colSpan="2"><contents><block>
														<contents><HTMLItem description="div First">
														<dataSource>
															<staticValue>&lt;div id="First" &gt;</staticValue>
														</dataSource>
													</HTMLItem><textItem name="SW - First"><dataSource><staticValue>First</staticValue></dataSource></textItem><HTMLItem description="div Second">
														<dataSource>
															<staticValue>&lt;/div&gt;&lt;div id="Second" style="display:none"&gt;</staticValue>
														</dataSource>
													</HTMLItem><textItem name="SW - Second"><dataSource><staticValue>Second</staticValue></dataSource></textItem><HTMLItem description="/div">
														<dataSource>
															<staticValue>&lt;/div&gt;
</staticValue>
														</dataSource>
													</HTMLItem></contents>
														<style><CSS value="width:350px;height:350px;overflow:auto;text-align:center"/></style></block>

												</contents><style><CSS value="text-align:center;background-color:#FAFAFA;vertical-align:middle;border-bottom-style:none;border-top:1pt solid silver;border-left:1pt solid silver;border-right:1pt solid silver"/></style></tableCell></tableCells></tableRow><tableRow><tableCells><tableCell><contents><textItem name="SW - More Data Left"><dataSource><staticValue>More Data Left</staticValue></dataSource></textItem></contents><style><CSS value="padding-left:3px;background-color:#FAFAFA;border-bottom:1pt solid silver;border-left:1pt solid silver"/></style></tableCell><tableCell><contents><textItem name="SW - More Data Right"><dataSource><staticValue>More Data Right</staticValue></dataSource></textItem></contents><style><CSS value="text-align:right;padding-right:3px;background-color:#FAFAFA;border-bottom:1pt solid silver;border-right:1pt solid silver"/></style></tableCell></tableCells></tableRow></tableRows><style><CSS value="border-collapse:collapse"/></style></table></contents>
					</pageBody>
				</page>
			</reportPages>
		</layout>
	</layouts>
</report>

It was written in 8.2, but it upgrades perfectly. As the report has no queries, you can simply point it to any package you have. When the report loads, you should see the following:

It’s important to note the names. The table is named Switch Container. This is what the report author will select when he uses it from the Layout Component. Each text item is also named. The author will be able to use the component override to replace those items as needed. If the author doesn’t need links for more data below the graph/table, he simply overrides the two bottom items without replacing them. The author can also use the same component multiple times in the same report. When writing JavaScript functions, you should take this possibility into account.

The library needs to be saved in a location that both the author and users can traverse and execute. Any report will fail if the user cannot access the original components library. The components library also needs to exist in the same location in the production environment.

There are many other possible uses for layout component. Any time you need complex functionality in multiple reports components should be considered.