Add Slider or Content Filter to Workspace

If you want Cognos® Workspace users to filter content in a workspace based on a data item that is not visible in the report, you must include this data item in its own query and name it _BusinessInsight_ within the IBM Cognos Report Studio report. Therefore, the data item must exist in both the query used for the data container (such as the list, crosstab, or chart) and in this separate query.
For example, a chart shows the revenue for the product lines for each region. You want to filter the chart to show only data for the year 2008. When you author the report, in addition to including Year in the Query1 used for the chart data container, you create a new query named _BusinessInsight_ that includes Year.

Tracing user activities across AUDIT, IIS and Cognos Logs

Some times for stubborn intermittent problems like users complaining 'random' slow down of reports or errors without any description, you want to investigate multiple layers.
You can start with following:
1.       Enable Cognos Auditing and check COGPIF_USERLOGON with COGPIF_ACTION to understand what operations were exactly going on during the time of error/slow down.
2.       Tally that with IIS Logs. In case of multi-server deployment, Audit gives you the IP Address of application server that handled the request. If there are any network related issues, IIS might have more information. If IIS logs have no corresponding entries, you know that client (Analysis Studio, Workspace Advanced, etc) is not even hitting the server.
3.       If IIS logs show corresponding entries, POST and GET operation and no network related errors, then check for Cognos logs (cogserver.log)

IBM Solves problem between Cognos BI and TM1 Rules in Version 10.1.1

Our database development team shared a great news that I must post here. In my previous post, I mentioned about how Cognos BI (Report Studio and Analysis Studio) stop showing numbers for aggregated levels as soon as any rule is added to the TM1 cube.

Well, as per the documentation from IBM, the latest version of TM1 (v10.1.1) solves this problem. Please see below from the IBM documentation:

Rule-aware aggregation in Cognos TM1 and Cognos BI

IBM Cognos TM1 now allows the aggregation computation to take into account Cognos TM1 cube rule definition so that Aggregate computations are now aware of Cognos TM1 cube rules.
In previous versions of Cognos TM1 the default aggregation could not be computed by the Cognos Business Intelligence (BI) server when applied to Cognos TM1 rule-calculated cells.
With rule-aware aggregation in place, the aggregation numbers are reported based on the semantics of the Cognos TM1 rules.
We haven't yet tested this feature but we are surely looking forward to it. I will keep you all updated on progress via this blog.

Cognos 8.4 and TM1 9.5


I have been meaning to write about my experiences and learning of writing Cognos Reports (version 8.4) over TM1 cube since long. Finally got around doing it today. In coming days, I will keep on adding new points that I learn and are worth mentioning.

The most irritating and strange behaviour we saw during this development is – many standard OLAP reporting features of Cognos stop working after you put rules in the TM1 cube. These features are:

1. Automatic Aggregation: All the measure with aggregation set to automatic stop working and you have to define the aggregation function. i.e. Total in most cases.

2. Slicer and Row Member sharing: Often you put many slicers in query each based on a prompt. So you might have slicers on Dates, Products, Location, Currency, Scaling, etc. These will be standard in all reports with standard prompts and irrespective of what dimensions you have in crosstab report. However, after putting rules in TM1 cube, if we have a dimension in rows or columns (say Products) and also have a slicer on the same dimension, report stopped showing numbers!

3. Multi Select in slicers: If we choose multiple members or set in slicer, the report won’t show numbers


This phenomena is also seen in Analysis Studio and affected power user reporting!

These are very irritating issues and as we had to use the cube rules, we changed our reports to get around above limitations. These changes were:

1. Define aggregation and rollup aggregation for all measures. Anyway this is one of the best practices, so no complains.

2. Ensure that dimensions used in rows and columns don’t appear in slicer. So, if you have PRODUCTS on rows and DATES on columns, don’t use them in slicer. This was tricky as we change the row and column members dynamically based on user choice [i.e. user can choose whether they want to see the data by Dates, Area, Products, or other dimension] and it we had to dynamically change the slicers accordingly using macro etc.

3. Wherever we had to allow multi-choice for prompts, instead of put them in slicer, we define TOTAL WITHIN SET calculation and used it on rows!

While writing this post, I have no idea why Cognos reports started not supporting the above mentioned features and even the Analysis Studio! But it has been raised to IBM and we are waiting to hear back from them..

IBM Cognos TM1 Cookbook book and eBook by Ankit Garg


Congratulation to Ankit Garg on publication of his book on Cognos TM1!

Another addition to PACKT's cookbook series with this great contribution by Ankit Garg to the world of BI. Please do check out this book today.

. A comprehensive developer’s guide for planning, building, and managing practical applications with IBM TM1
. No prior knowledge of TM1 expected
. Complete coverage of all the important aspects of IBM TM1 in carefully planned step-by-step practical demos
. Practical recipes that illustrate the use of various TM1 features

http://www.packtpub.com/ibm-cognos-tm1-for-planning-budgeting-forecasting-solutions-cookbook/book

Copying multiple Report Studio reports into one


Some times we create seperate reports but later on require to copy them all into one to make a report pack. Though Report Studio is very good in allowing us copy paste individual objects across, you need to be very cautious otherwise you will end up spending hours in fixing errors.
In my understanding, if you follow below steps, you will be fine.

1. Open the first report in studio that you would like to copy in the pack.
2. First of copy all required Query Subjects across to destination by going in query explorer.
3. Then copy the conditional variables across to destination report.
4. Now we need to copy the Conditional Styles across but unfortunately it is not direct copy-paste operation. Hence, you will need to open both source and destination reports' XML specification in an editor. Then locate the Conditional Style tags from source report (they are usually towards the end of specification) and copy them to destination reports.
5. Now bring the modified specification back in report studio. Finally copy the REPORT PAGES across.
6. That's it! You are done. Now validate the destination report and it should validate without any errors.

Note: The package for destination report should either be same as source or be compatible for copying (i.e. all object names and hierarchies should be same)

All the best!

Retrieving FM package from a published package in Cognos connection

By Dilip Aghav:

It often happens that you need to work a package that was published long time back and you don't have or can't find the original Framework Model now. What to do in that? Recreate the FM model? No need... Find below the steps to recover, or we can say recreate, the FM Model using published package.

1. Create a blank file called cqeconfig.xml and save under Cognos8/bin on the server.

2. Write following code in this file:







3. Restart the Cognos 8 service for the change to take effect.
4. Stop the Cognos 8 service.
5. Rename the cognos8/data/cqe/rtmodel directory on app server to a different name.
6. Start the Cognos 8 Service
7. Create a new report using the package you wish to recover.
8. A new Cognos8/data/cqe/rtmodel directory will be created, and an XML file will be added to it. This model contains the information you need for your framework model. Take a copy of this file on your local or on the machine where you have FM installed.
9. Create empty project in FM. (Select the design language and cancel the MetaData wizard and save). Close FM.
10. Delete model.xml from FM project folder.
11. Copy xml file from point 7 to the FM project folder and rename as model.xml.
12. Open the model in Framework Manager and use it.

Congratulations! You have recreated the framework model from a published package.

To reset the server settings:
1. Rename cqconfig.xml or delete it. (on UAT the file is renamed to cqconfig1.xml currently)
2. Restart the Cognos 8 Service for change 8 to take effect.

Changing Package of multiple reports


It's a common question amongst the developers - how to change package of multiple reports in one go! Unfortunately, Cognos doesn't have any built-in facility to do this at the moment.

However, there are some work-around for this.

1. Use third party tools: Some private tools hook-up to Cognos Content Store and allow you editing multiple reports simultaneously. This includes changing the report package, access permissions, visibility, etc.

2. DIY: In this simple Do It Yourself method of doing bulk update on reports, I place all the reports in one folder. Then generate deployment archive of the folder using Export wizard. This pushes all the report definitions in one xml file. Pick up the deployment archive, unzip and examine the xmls.
Once you find the xml that contains all the reports, open it in a good xml editor. Now, search for the package name and identify the pattern you need to replace.
Now, simply replace all the instances of package name using that pattern, put the file back in zip and bring it back in Cognos using Import wizard.
Voila, here you have all the reports back with the Report Package changed to the desired one.

Note: While importing, you might want to change the 'destination' of folder so that it creates a new copy of reports. So, in case of any issue, you still have the original folder intact.

I hope this helps!

Free Chapter Download: IBM Cognos 8 Report Studio Cookbook


Now you can now download a free chapter from book 'IBM Cognos 8 Report Studio Cookbook' from this link: https://www.packtpub.com/sites/default/files/0349-chapter-2-advanced-reportauthoring.pdf

The book can be bought in PDF as well as format from the PACKT website.
If you prefer Amazon, you can buy the book from following sites:
United Kingdom Amazon: IBM Cognos 8 Report Studio Cookbook
USA Amazon: IBM Cognos 8 Report Studio Cookbook

Populating time dimension using DB2 script


Written by: Ankit G

Populating time dimension in a Data Warehouse can sometimes be a pain. While it can be done using the ETL tool, using a script can sometimes save from complexity involved in handling date time datatypes in ETL.

So here you have a sample DB2 code to create and load time dim at the hourly level. Please note that granularity can be changed while calling the stored procedure but before doing that logic needs to be changed to populate TIME_PK which act as the primary key of the table : 

CREATE TABLE ABC.TIME_DIM(
TIME_RANGE_START TIMESTAMP NOT NULL,
TIME_RANGE_END TIMESTAMP NOT NULL,
HOUR_KEY TIMESTAMP NOT NULL,
DAY_KEY TIMESTAMP NOT NULL,
WEEK_KEY TIMESTAMP NOT NULL,
MONTH_KEY TIMESTAMP NOT NULL,
QUARTER_KEY TIMESTAMP NOT NULL,
YEAR_KEY TIMESTAMP NOT NULL,
CURRENT_DATE DATE NOT NULL,
CURRENT_MINUTE SMALLINT,
CURRENT_HOUR SMALLINT,
CURRENT_DAY SMALLINT,
CURRENT_WEEK SMALLINT,
CURRENT_MONTH SMALLINT,
CURRENT_QUARTER SMALLINT,
CURRENT_YEAR SMALLINT,
DAY_OF_WEEK SMALLINT,
DAYS_IN_MONTH SMALLINT,
DAY_OF_YEAR SMALLINT,
WEEK_OF_MONTH SMALLINT,
WEEK_OF_QUARTER SMALLINT,
CANDLETIMESTAMP CHAR(16) NOT NULL,
END_CANDLETIMESTAMP CHAR(16) NOT NULL,
TIME_PK BIGINT
) ;

------------------------------------------------------------------------------------
create procedure ABC.CREATE_TIME_DIMENSION(IN startDate VARCHAR(32), IN endDate VARCHAR(32),
IN granularity SMALLINT)
LANGUAGE SQL
BEGIN
DECLARE loaddate TIMESTAMP;
DECLARE endloaddate TIMESTAMP;
DECLARE v_endDate TIMESTAMP;

DECLARE v_duplicateKey INTEGER;

-- duplicate key SQLSTATE
DECLARE duplicate_key CONDITION FOR SQLSTATE '23505';

-- Continue to the next row if we receive this error
DECLARE CONTINUE HANDLER FOR duplicate_key
-- The row insertion failed
SET v_duplicateKey = 1;

SET v_duplicateKey = 0;
SET loaddate = TIMESTAMP(startDate);
SET v_endDate = TIMESTAMP(endDate);

time_loop:
LOOP
SET loaddate = loaddate + granularity minutes;
SET endloaddate = loaddate + (granularity - 1) minutes;

INSERT into ABC.TIME_DIMENSION (
CANDLETIMESTAMP,
END_CANDLETIMESTAMP,
TIME_RANGE_START,
TIME_RANGE_END,
HOUR_KEY,
DAY_KEY,
WEEK_KEY,
MONTH_KEY,
QUARTER_KEY,
YEAR_KEY,
CURRENT_DATE,
CURRENT_MINUTE,
CURRENT_HOUR,
CURRENT_DAY,
CURRENT_WEEK,
CURRENT_MONTH,
CURRENT_QUARTER,
CURRENT_YEAR,
DAY_OF_WEEK,
DAYS_IN_MONTH,
DAY_OF_YEAR,
WEEK_OF_MONTH,
WEEK_OF_QUARTER,
TIME_PK BIGINT
)
VALUES
(
concat(concat(rtrim(char((YEAR(loaddate) - 1900)*1000000 + MONTH(loaddate)*10000 + DAY(loaddate)* 100 + HOUR(loaddate))),
rtrim(substr(char(100+ MINUTE(loaddate)),2,2))),'00000'),
concat(concat(rtrim(char((YEAR(endloaddate) - 1900)*1000000 + MONTH(endloaddate)*10000 + DAY(endloaddate)* 100 + HOUR(endloaddate))),
rtrim(substr(char(100+ MINUTE(endloaddate)),2,2))),'59999'),
loaddate - second(loaddate) seconds - microsecond (loaddate) microseconds,
endloaddate + 59 seconds + 999 microseconds,
loaddate - minute(loaddate) minutes - second(loaddate) seconds - microsecond (loaddate) microseconds,
loaddate - hour(loaddate) hours - minute(loaddate) minutes - second(loaddate) seconds - microsecond (loaddate) microseconds,
loaddate -(DAYOFWEEK(loaddate)-1) days - hour(loaddate) hours - minute(loaddate) minutes - second(loaddate) seconds - microsecond (loaddate) microseconds,
loaddate - (day(loaddate) -1 )days - hour(loaddate) hours - minute(loaddate) minutes - second(loaddate) seconds - microsecond (loaddate) microseconds,
timestamp(concat(concat(concat(rtrim(char(year(loaddate))), '-'),
rtrim(char(quarter(loaddate)*3-2))),'-1 00:00:00.0')),
loaddate - (month(loaddate) - 1) months - (day(loaddate) -1 )days - hour(loaddate) hours - minute(loaddate) minutes - second(loaddate) seconds - microsecond (loaddate) microseconds,
DATE(loaddate),
MINUTE(loaddate),
HOUR(loaddate),
DAY(loaddate),
WEEK(loaddate),
MONTH(loaddate),
QUARTER(loaddate),
YEAR(loaddate),
DAYOFWEEK(loaddate),
DAY((loaddate + 1 MONTH) - DAY(loaddate +1 MONTH) DAYS),
DAYOFYEAR(loaddate),
(DAY(loaddate)/7)+1,
WEEK(loaddate) - (QUARTER(loaddate) -1)*13,
(((((YEAR(loaddate)*100)+MONTH(loaddate))*100)+DAY(loaddate))*100)+HOUR(loaddate)
);

IF (loaddate >= v_endDate)
THEN LEAVE time_loop;
END IF;

END LOOP time_loop;

COMMIT;

END
@
-----------------------------------------------------------------------------------
call ABC.create_time_dimension('2009-01-01 00:00:00','2009-01-02 00:00:00',60);
------------------------------------------------------------------------------------