Macro Prompts in FM Model for Adhoc Packages

Working on creating an Adhoc Package for use in Query Studio. The package needs to include base metrics and compound metrics and users need to be prompted for Date range and other Dimensions on pulling any of the metrics.

For the prompts, I included filters in the model query subject and built of the metrics. But what I noticed was that, only the base metrics prompt the users and if you drag a compound metric you are not prompted the first time in Query Studio until you hit the re-run button.

Work-around is to use macro filters rather than ?parameter? filters.
.

FM and Teradata Stored Procedures

I have been working on using Stored Procedures to update tables through Event Studio based on a condition. The back end DB is Teradata.

With Teradata if you have a separate schema to create Stored Procedures, the schema will not show up in FM when you use the run meta data wizard unless it has at least 1 table or view. To overcome this issue, a dummy table was created in the schema after which the schema showed up in FM. I then imported the SP. The SP is a data modification SP. Teradata SPs cannot be run through FM or through Event Studio because Cognos fires a "CALL SPName" statement rather than an "EXEC SPName" statement.

To work around the above issue create macros and use them through FM or Event Studio. This is only for Teradata. Oracle Stored Procedures do not have any issues.
 

Creating a "Top 10 + Others" dimensional report

Couldn't figure out a better title :-( .

The requirement is to display Top x Products and group all the other products into the "Others" group. If you were using a relational model, you could rank the Products based on Revenue and then use a case statement to identify the Products that need to go into the Others group. But with a dimensional model you would run into issues using a case statement or Rank function.

Steps:

Step 1: Create a Top Prod data item to order the Products based on Revenue and to retrieve the Top 2 Products.

head(order([Sales and Marketing].[Products].[Products].[Product line],[Revenue],desc ),2)

Step 2: Create a Bottom Prod data item to retrieve all the other Products.

except([Sales and Marketing].[Products].[Products].[Product line],[Top Prod])

Step 3: Drag the Revenue data item. Create a Bottom Value data item to calculate the Revenue for "Others" category.

total(currentMeasure within set [Bottom Prod])

Step 4: Create a member for Others that has the Bottom value associated with the Products Dimension.

member([Bottom Value],'Others','Others',[Sales and Marketing].[Products].[Products])

Step 5: Create a Products data item that now includes Top 2 Products and Others.

union([Top Prod],[Others])



Report XML 10.1.1 against Go Sales:

<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 Sales (analysis)']/model[@name='model']</modelPath> <drillBehavior modelBasedDrillThru="true"/> <queries> <query name="Query1"> <source> <model/> </source> <selection><dataItem name="Top Prods"><expression>head(order([Sales (analysis)].[Products].[Products].[Product line],[Revenue],desc),2)</expression></dataItem><dataItem name="Bottom Prod"><expression>except([Sales (analysis)].[Products].[Products].[Product line],[Top Prods])</expression></dataItem><dataItem name="Bottom Prod value"><expression>total(currentMeasure within set [Bottom Prod]) </expression></dataItem><dataItem name="Others"><expression>member([Bottom Prod value],'Others','Others',[Sales (analysis)].[Products].[Products]) </expression></dataItem><dataItem name="Products"><expression>union([Top Prods],[Others])</expression></dataItem><dataItemMeasure name="Revenue"><dmMember><MUN>[Sales (analysis)].[Sales].[Revenue]</MUN><itemCaption>Revenue</itemCaption></dmMember><dmDimension><DUN>[Sales (analysis)].[Sales]</DUN><itemCaption>Sales</itemCaption></dmDimension><XMLAttributes><XMLAttribute name="RS_dataType" value="2" output="no"/></XMLAttributes></dataItemMeasure></selection> </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"> <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> <crosstabFactCell><contents><textItem><dataSource><cellValue/></dataSource></textItem></contents><style><defaultStyles><defaultStyle refStyle="mv"/></defaultStyles></style></crosstabFactCell><crosstabRows><crosstabNode><crosstabNodeMembers><crosstabNodeMember refDataItem="Products" edgeLocation="e1"><style><defaultStyles><defaultStyle refStyle="ml"/></defaultStyles></style><contents><textItem><dataSource><memberCaption/></dataSource></textItem></contents></crosstabNodeMember></crosstabNodeMembers></crosstabNode></crosstabRows><crosstabColumns><crosstabNode><crosstabNodeMembers><crosstabNodeMember refDataItem="Revenue" edgeLocation="e2"><style><defaultStyles><defaultStyle refStyle="ml"/></defaultStyles></style><contents><textItem><dataSource><memberCaption/></dataSource></textItem></contents></crosstabNodeMember></crosstabNodeMembers></crosstabNode></crosstabColumns></crosstab> </contents> </pageBody> <pageHeader> <contents> <block><style><defaultStyles><defaultStyle refStyle="ta"/></defaultStyles></style> <contents> <textItem><style><defaultStyles><defaultStyle refStyle="tt"/></defaultStyles></style> <dataSource> <staticValue/> </dataSource> </textItem> </contents> </block> </contents> <style> <defaultStyles> <defaultStyle refStyle="ph"/> </defaultStyles> <CSS value="padding-bottom:10px"/> </style> </pageHeader> <pageFooter> <contents> <table> <tableRows> <tableRow> <tableCells> <tableCell> <contents> <date> <style> <dataFormat> <dateFormat/> </dataFormat> </style> </date> </contents> <style> <CSS value="vertical-align:top;text-align:left;width:25%"/> </style> </tableCell> <tableCell> <contents> <pageNumber/> </contents> <style> <CSS value="vertical-align:top;text-align:center;width:50%"/> </style> </tableCell> <tableCell> <contents> <time> <style> <dataFormat> <timeFormat/> </dataFormat> </style> </time> </contents> <style> <CSS value="vertical-align:top;text-align:right;width:25%"/> </style> </tableCell> </tableCells> </tableRow> </tableRows> <style> <defaultStyles> <defaultStyle refStyle="tb"/> </defaultStyles> <CSS value="border-collapse:collapse;width:100%"/> </style> </table> </contents> <style> <defaultStyles> <defaultStyle refStyle="pf"/> </defaultStyles> <CSS value="padding-top:10px"/> </style> </pageFooter> </page> </reportPages> </layout> </layouts> <XMLAttributes><XMLAttribute name="RS_CreateExtendedDataItems" value="true" output="no"/><XMLAttribute name="listSeparator" value="," output="no"/><XMLAttribute name="RS_modelModificationTime" value="2011-06-09T13:51:23.483Z" output="no"/></XMLAttributes></report>

Drill Through Pointers for Deployment

Recently, I updated and moved a drill through target report. Soon after, the Source reports started throwing drill-through links error. It was later that I figured out that the target report was moved to another folder prior to import for back-up reasons. This caused the drill through in the source reports to break.

A drill through source and target report are linked to each other through their search path and ID. When a target report is moved around the source report specification gets updated with the new path without having to make any modifications.

This can prove to be a boon to some and a head-ache to others while doing deployment. The administrator doing deployments should keep the above point in mind while doing deployments involving drill through reports else it will result in broken links.

Let us consider a scenario where we have a Source Report and a Target report in Folder 1.

Now the Target Report is moved to My Folders and a newer version of the Target Report is imported into Folder 1. The Source report link to the target report breaks.

The reason is when the Target report was moved to My Folders the Source report re-pointed to this new path. Even when a newer version of the report with the same name is pasted in the same path the Source report's spec points to the new path and the Source Report ID is linked to the older Target report ID. Hence the links from the Source report break.

Until the ID connection between the Source and Target Report is lost, deployments need to be done carefully. An ID association is lost when Target report is deleted or the Target report is imported separate from the source report.
 

Multiple Queries for different data containers

Why does Cognos generate Multiple Queries even when you have the different objects pointed to the same Query?

Now before I answer that, here's a tip to all the newbies on seeing the SQLs generated by Cognos. To identify the SQLs that Cognos fires against the DB, you would need to use the Generated SQL/MDX option from the Tools Menu and not the one available for each individual Query Object in Cognos. This will display the various SQLs that Cognos would fire against the DB and here's where you would notice that even if you have multiple objects pointing to the same Query Object, Cognos would still fire multiple queries.

Now if you have say multiple lists all pointing to the same Query Object, then unless the columns pulled in, the sort order, group by clauses match, Cognos would fire multiple queries. To make the columns pulled in to match you could use the Properties property of the lists and select all those columns that are missing from each list to match the other lists based out of the same Query Subject.

In case you have different data containers say like a list and crosstab or a bar graph and crosstab pointing to the same query subject Cognos would fire multiple queries as that's how its designed. So no luck over there.
  

New Dynamic Sorting Controls from IBM Cognos

Cognos seems to have come up with new controls / method for dynamic column sorting that doesn't involve the drill through methods.

Here's the link to the article.

http://www.ibm.com/developerworks/data/library/cognos/reporting/scripting_techniques/page515.html?ca=drs-

Sounds cool!! Hope this ends the report re-run issues we have while using the drill-through method for sorting.

I will check this method out and post my comments later.

Let me know your thoughts on this method though.

Here's an update from PaulM about applying this method on Cognos 10 environment:

[User comments: PaulM]

There are some differences between the XML files from Cognos 8 to Cognos 10. The general structure of the files are the same, so making the correct updates is not difficult.

First you need to use a decent text editor, one with comparison capabilities. Notepad++ is excellent for this.

Open both the C10 version and the version from the download. They should be in different tabs in Notepad++. Click on the plugins menu and select Compare --> Compare.

Find and copy the differences related to this technique only. Don't try to copy deprecated objects over. All of the related fragments have "orderCol" somewhere in the name.

 

Using CAMID for Bursting

To burst reports to Cognos Directories that need to be visible to all users with access to the respective folder / report, you would need to burst to the Everyone group.

Retrieve the CAMID of everyone group and create a data item with the CAMID included as an expression. Set this as the recipient data item.

'CAMID("::Everyone")'

Likewise you can burst to various groups of users by retrieving their CAMIDs and setting them up as recipients in your database recipient table.
 

Master Query Cannot Be a Nested Query - Error Message

When you try bursting a report that has multiple pages with page breaks set on different queries, the report could throw errors "Master Query Cannot Be a Nested Query" unless you have master detail relationships set up between the Burst query and the Page Set Queries.


Master Detail relationships can be set up using the "Master Detail Relationships" property of the Page Set query.
 

Single Select Tree Prompt on a Relational Model

You can base Tree prompts not just on Dimensional Model but on relational Model as well. This involves using macros and decoding MUNs. This has been covered in article on IBM's support site but I am covering the same here for the benefit of those who use this blog as a one-stop-site for all Cognos needs.

The below solution works for single select Tree Prompt. I am working on multi-select Tree Prompts and would update this article once I have the solution.

Requirement: Create a Date Dimension Tree Prompt based on a Relational Date Dimension Table and filter the report to retrieve data for the selected dates.

Solution:

Step 1: In the FM Model, create a stand alone Date Regular Dimension based on the existing Date Dimension query subject in the Presentation Layer View.

This Dimension would be used for creating the Tree Prompt.

Step 2: In the report create a tree prompt based on Date Dimension.

Step 3: Create a filter as below:

[Date] = #csv(substitute(']','',grep(']',split('].', grep('all',split('->',prompt('dte','memberuniquename','[Presentation Layer].[Date Dimension].[YEAR].[DATE]->[all].[2008].[Q1 2008].[Jan 2008].
[Wk 1].[2008-01-01]')))))))#

The above filter works as explained:

• First the MUN for the selected Tree Prompt value is retrieved.
  In the above Example: [Presentation Layer].[Date Dimension].[YEAR].[DATE]->[all].[2008].[Q1 2008].[Jan 2008].[Wk 1].[2008-01-01]



• Then we split the retrieved MUN into 2 parts based on the symbol "->".
  In the above Example: The MUN is split into [Presentation Layer].[Date Dimension].[YEAR].[DATE], [all].[2008].[Q1 2008].[Jan 2008].[Wk 1].[2008-01-01]



• We then select the part containing "all".
  In the above Example: [all].[2008].[Q1 2008].[Jan 2008].[Wk 1].[2008-01-01] is selected.



• Then split the part again into multiple parts based on the symbol "].[".
  In the above Example: [all].[2008].[Q1 2008].[Jan 2008].[Wk 1].[2008-01-01] is split into [all, 2008, Q1 2008, Jan 2008, Wk 1, 2008-01-01]



• Retrieve the part containing "]".
  In the above Example: 2008-01-01] is selected.



• Replace "]" with "".
  In the above Example: 2008-01-01] is converted to 2008-01-01.

Thus [Date] is compared with 2008-01-01 and the filter works as expected.
 

User Query - Display Totals for Top 2 Ranks while displaying data for all other ranks in a crosstab?

This is in response to a user query.

Requirement: Display Ranks across Columns in a Crosstab and the Total column needs to include only the Top 2 ranks along with Overall Total column for all Ranks.

Solution:

Step 1: Create the base query Query 1 with the following data items: Product, Year, Sales, Rnk, Top 2 Sales Total.

Rnk - Rank([Sales] for [Product])
Top 3 Sales Total - case when [Rnk] < 3 then [Sales] end

Step 2: Create the outer query Query 2 by referencing the base query Query 1. Drag the data items - Product, Year, Sales, Rnk, Top 2 Sales Total from the referenced query.

Step 3: Set the aggregate function of Rnk to None.

Step 4: Create the crosstab by dragging in the data items Product, Sales, Top 2 Sales Total from Query 2.

Step 5: Create the Overall Total column by clicking on the Summary button for Rank column.




 

User Query - Creating Overall Percentage Summary Values in Reports

This requirement is in response to a user query:

Requirement: Display Dates, Sales, Percentage Sales along with summary total.

Solution:

Step 1: Create a crosstab report with Year, Month, Sales data items.

Step 2: Create a data item Total Sales - total(...[Sales] for report).

Step 3: Set the Aggregate and Rollup Aggregate functions to calculated.

Step 4: Create a data item Percentage - [Sales] / [Total Sales] and set aggregate and rollup to calculated. Drag this query item into the crosstab.



Here are a couple of other ways to format the above information:





 

Cognos 8.4 Query Studio Bug - Including Model and Query Filter

Recently I ran into an issue with Cognos 8.4 Query Studio. When I try to include both a model filter and a query studio filter the query studio report just hangs forever and then it throws an "object not found" error on the Model filter even though the object exists in the model.

This has been identified as a bug in Cognos 8.4 and seems to have been corrected in version 8.4.1.

So here's one more reason to move to 8.4.1.
 

Tree Prompts Explained

A Tree Prompt provides the user with ability to navigate levels in a hierarchy easily and select members across Levels or even select multiple levels within a hierarchy.

I have covered below a few points on Tree Prompts

• To set up a tree prompt filter, in your report embed a prompt function inside a data item
  
  Example: To display only a selected Product Hierarchy members / levels and associated revenue:
  
  [Sales and Marketing].[Products].[Products]->?Products?







• To create a multiselect tree prompt, change the expression to -
  
  set([Sales and Marketing].[Products].[Products]->?Products?)







• To display members of levels starting from a particular level -
  
  Example: Display a Tree Prompt that allows users to select members or levels starting from Product Type.
  
  In the query associated with your tree prompt, drag the level from which you wish to be able to display members and descendants and set this as the use value for your tree prompt.
  
  
  
  



• Default values can only be hard-coded selections.


• To use a Tree Prompt in a Macro -
  
  set(#promptmany('Products','memberuniquename','[Sales and Marketing].[Products].[Products].[Product type]->:[PC].[@MEMBER].[951]')#)



• Tree prompts are based on Dimensional model, but you can still create Tree Prompts using a Relational Model which I shall explain in a later article.
  

 

Identifying nth Weekday of a month in reports

Requirement: Identify the 2nd Thursday of the current month.

Solution:

Create data items ToDate, 1stDateofMonth, 1stDayofMonth, Nth, Day , AddDays, NthDay

ToDate - current_date
 
1stDateofMonth - _first_of_month(ToDate)
 
1stDayofMonth - _day_of_week(1stDateofMonth ,1) // Assuming Monday is Day 1
 
Nth - 2 // The nth value of the weekday required, in our case we require 2nd Thursday
 
Day - 4 // Assuming Monday is Day 1, then Thursday is Day 4
 
AddDays - case when [Day] >= [1stDayofMonth]
then [Day] - [1stDayofMonth]
else
([Day] + [1stDayofMonth]) -1
end
 
NthDay - _add_days([1stDateofMonth],(([Nth]-1) * 7 ) + [AddDay])
 
NthDay returns the 2nd Thursday of the month.
 

Required a Property for Relational querying !!!

I so wish there was a Property or setting for Relational Querying vs Dimensional Querying. We are dealing with a model that is ER modeled and is based on an OLTP system. We need to build an ad-hoc model based on this. And as you know this model is not supported by Cognos. So don't you think if there was a setting in FM that instructed Cognos to generate ER based queries rather than identifying facts and dimensions based on cardinalities that would make life so much simpler.

I mean, just think if there are 3 tables A, B, C and they are joined to each other, you would expect a query that relates the 3 tables based on the joins. I do not want Cognos to identify if A/B/C is a fact / dimensions and break-up my query.

I know you would ask me to move to a dimensional model but in our case it doesn't even make sense. The OLTP system doesn't even deal with millions of rows nor do we have an ETL system. SO moving to a dimensional model would mean a lot of expenditure in terms of architecting, getting an ETL tool, designing and developing ETL workflows, time, resources involved and so on...Just wish there was a setting..
 

Practical Usage for Sectioning

Most often you would have used Sectioning for formatting reasons. Here's how you could use Sectioning, Page-break concepts to make a single list report work like 2 lists.


Requirement: Display Data for Current Year and Previous Year as 2 separate lists on 2 separate pages.

Normally you would have used 2 separate queries for the 2 list reports. Using Sectioning and Page-Break you can use a single query to achieve this. You can use this method when you need to display multiple lists that differ in terms of filters alone.

Solution:

Step 1: Drag Product, Sales, Year in to the report

Step 2: Create a filter to filter the data for current and previous year.

Step 3: Create a data item called Section Title.

case when [YEAR] = extract(year from current_date)
then 'Current Year'
else 'Previous Year'
end

Step 4: Section on the Section Title data item



Step 5: Create a page break on Section Title data item.






 

Limited Cache, Query Re-Use Capabilities

I have always had a major complaint with Cognos and that is with regards to caching and query re-use. I am not sure if this is the case with other BI tools as well. And it is really frustrating when you have to explain this to the users.

For instance you are asked to implement dynamic sorting on your report that includes a number of columns and retrieves a huge amount of data. Now with Dynamic sorting each sort fires a query against the DB which is performance impacting. And when you explain this to the users their query is "Why doesn't Cognos just retrieve the data and push it to the client side, so that each sort would not hit the DB?" Good question, but that is not for me to answer.

Likewise imagine a scenario where you have to display 3 Lists in your report one each for YTD, QTD and WTD. Ideally what I would like to do is to get the YTD data and QTD, WTD would all be a subset of the YTD data. So if the YTD data can be retrieved and cached and this data re-used for the other queries that would be great. But unfortunately that is not the case with Cognos. When I create a query object and point other query objects to this the base query still gets executed multiple times.

These are all required features in Cognos.

The only cache and query re-use capabilities Cognos offers is when you have multiple lists all pointing to the same query and having the same order by, group by and columns which is ever rarely the case.
 

Bursting Explained - Part III

Links to Part I, Part II.

Scenario 3: Separate Burst Group, Burst Recipient and Report Queries.

In this case all 3 queries are separate.

Example: Burst a Sales report based on Sales Regions that has 2 lists in them and make all the sales reports available to all users through Cognos Connection Directories.

BurstGroupQuery (Master Burst Group Query) - Sales Region

ListQuery1 (Detail List Query 1) - Year, Sales Region, Revenue

ListQuery2 (Detail List Query 2) - Year, Sales Region, Sales Target




RecipientQuery (Detail Recipient Query) - Sales Region, Recipient

Create Master-Detail Relationships between Master Burst Group query and Detail List Queries. Create Master-Detail Relationship between Master Burst Group query and Detail Recipient Query.



In this example the master burst group query is run once, the data set is retrieved, for each record retrieved the recipient query is run once and the data set is retrieved. Again, for the master query the detail list queries are run once each for each record and data is retrieved.



In the above case:

Master Query - Run Once, 100 records retrieved.

Recipient Query - Run 100 times and 1 record retrieved each time.

Report Queries - Run 100 times * 2 report queries
 

Bursting Explained - Part II

Link to Part I, Part III.

Scenario 2: A single Burst Group and Burst Recipient Query but multiple report queries.

In this case the report has multiple queries. The burst group and burst recipient information is fed by a single query.

Example: Burst a Sales report that has 2 lists based on Sales Regions and make the report available to users through Cognos Connection Directories.




RecipientQuery (Master Query) - Sales Region, Recipient

ListQuery1 (List Query 1) - Year, Sales Region, Revenue

ListQuery2 (List Query 2) - Year, Sales Region, Sales Target

You would need to create master-detail relationships between the Master Query and the 2 List queries. The Burst Group and Recipient query would be the master query.





In the above example, since the burst group and burst recipient is sourced from a single query, this query is run once before the start of bursting process. Then for each record retrieved by the burst query the detail queries are fired once each.

Say we have 100 sales regions. The master query is fired once and the 100 records are retrieved. Then for each Sales region the 2 detail queries are fired. So you would see a total of 2 * 100 queries fired against the DB in sequence.

It is in the above scenario that you would see the entire burst process taking time to complete. Assume each report output is generated in 1 minute. The entire burst process in the above example is completed in 100 minutes and that is like more than an hour.


 

Bursting Explained - Part I

I have been asked by so many users what happens behind the scenes when Cognos bursts a report? Why do burst reports take longer to complete? etc. And I have explained about master-detail relationships and its impact on Burst reports in an earlier post. In this post and the next couple of posts I am going to try and cover the various burst scenarios and how many SQLs Cognos fires against the backend in each scenario.

Bursting is made up of 3 components:

The Burst Query or Report Query: The report query that needs to be burst. The report could be a single query report or multi-query report. In case of a multi-query report, each data container in the report needs to have a master-detail relationship set up with the burst group query.



The Burst Group Query: The query that decides the burst group. For each record in the burst group query the report is sliced.

Example: A report needs to be burst for each Sales Regions. In this case the burst group would be the Sales Region. The report is sliced for each Sales Region record generated by the burst group query.

The Burst Recipient Query: The query that provides the recipient information.



Scenario 1: A single Burst Query, Burst Group Query, Burst Recipient Query.

In this case the list in the report, the burst group and the burst recipient information is fed by a single query.

Example: Burst a Sales report based on Sales Regions to be made available to users through Cognos Connection Directory.

Query 1 Data Items: Year, Sales Region, Sales, Recipient.

Recipient - CAMID('Everyone')

In the above Example, since burst group, burst recipient and list report are sourced from a single query, Cognos runs this query once, retrieves the entire data set and then slices the report output for the various sales regions. Hence only 1 query is fired by Cognos in this case.



Links to Part II, Part III