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
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..
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
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!
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.
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.
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
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,
CANDLETIMESTAMP CHAR(16) NOT NULL,
END_CANDLETIMESTAMP CHAR(16) NOT NULL,
create procedure ABC.CREATE_TIME_DIMENSION(IN startDate VARCHAR(32), IN endDate VARCHAR(32),
IN granularity SMALLINT)
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);
SET loaddate = loaddate + granularity minutes;
SET endloaddate = loaddate + (granularity - 1) minutes;
INSERT into ABC.TIME_DIMENSION (
concat(concat(rtrim(char((YEAR(loaddate) - 1900)*1000000 + MONTH(loaddate)*10000 + DAY(loaddate)* 100 + HOUR(loaddate))),
concat(concat(rtrim(char((YEAR(endloaddate) - 1900)*1000000 + MONTH(endloaddate)*10000 + DAY(endloaddate)* 100 + HOUR(endloaddate))),
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,
loaddate - (month(loaddate) - 1) months - (day(loaddate) -1 )days - hour(loaddate) hours - minute(loaddate) minutes - second(loaddate) seconds - microsecond (loaddate) microseconds,
DAY((loaddate + 1 MONTH) - DAY(loaddate +1 MONTH) DAYS),
WEEK(loaddate) - (QUARTER(loaddate) -1)*13,
IF (loaddate >= v_endDate)
THEN LEAVE time_loop;
END LOOP time_loop;
call ABC.create_time_dimension('2009-01-01 00:00:00','2009-01-02 00:00:00',60);