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);
------------------------------------------------------------------------------------

Choosing the Best Survey Software For Your Business

We often need to collect data by using Survey Tools. Lot of pre-sales, post-sales, marketing data can be collected by surveys. However, it is important to choose the best survey tool before sending out the links to audience. Below article aims to help ask the right questions before making a choice of Survey software.
http://ezinearticles.com/?Choosing-the-Best-Survey-Software-For-Your-Business&id=3967858
It is written by Tim Coombe who has been an IT Professional for 20 years, specialising in Media and Business Intelligence. He has written a very nice and robust survey tool that can be accessed at: http://www.cicerosurveys.com/

A practical book on Cognos Report Development !!

Finally I got chance to write this book on Cognos 8 Report Studio to provide practical and real-life resource to my fellow report developers and super-users. While there has been some manuals and guides out there in market to tell you what each component does, this book will tell you how to use them. The main feature of this book is it will tell you how to over-come many limitations of Report Studio !!

Over 90 great recipes for taking control of Cognos 8 Report Studio

• Learn advanced techniques to produce real-life reports that meet business demands
• Tricks and hacks for speedy and effortless report development and to overcome tool-based limitations
• Peek into the best practices used in industry and discern ways to work like a pro
• Part of Packt's Cookbook series-each recipe is a carefully organized sequence of instructions to complete the task as efficiently as possible

To read more about the book and to buy it, please refer to this link:
http://www.packtpub.com/ibm-cognos-8-report-studio-cookbook/book

PDF Report Output : Blocked due to security threat

Today suddenly I started receiving complaints from Cognos report users that the drill-throughs from PDFs are not working!

We most of my business users prefer PDF output as default. This allows them to print the report right away, and also allowed them do the drill-throughs thanks to PDF JavaScript. This is quite an impressive feature of Cognos Reports (version 8.2)

However, all of sudden these drill links stopped working. I was quite surprised as I didn't install any patch on Cognos, neither did any Adobe updates. After some investigation I found that the intranet administrators had blocked Java Scripts from PDF Reader. Gosh! Now what's that for? Here is what I got to know from them..

Adobe Reader Zero-Day Exploit: The popular PDF document format has made the Adobe Reader software virtually ubiquitous. Few software products are installed so pervasively that they exist on nearly every system regardless of operating system. For malware developers, targeting flaws in Adobe Reader offers an exceptionally large potential for victims. The issue reportedly impacts Adobe Reader, and Adobe Acrobat--versions 9.2 and earlier. The actual exploit relies on JavaScript. The Shadowserver Foundation and SANS Institute both recommend that you simply disable the execution of JavaScript within the Adobe software. In your Adobe product, go to Edit--Preferences--JavaScript, and uncheck the box next to Enable Adobe JavaScript.

So, while Adobe is preparing an update to patch this vulnerability, many companies will block the JavaScript from PDF reader component. This will in-turn disable the drill-links from Cognos reports executed in PDF format.

After some persuasion, I managed to get this restriction taken off and we informed the company employees to be very wary of any e-mails they receive from an unknown sender that they aren't expecting. They should never open any attachments from any such e-mail, either.

If you are facing the same problem in your organization, it would be worth keeping yours eyes open for the Adobe patch.