Business Intelligence, Analytics and Cognos

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 re...

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...

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 fil...

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

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, D...

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/

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-...

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 Re...

Finally I managed to install and start Cognos 8.3 BI server on my laptop with Vista.. You can to choose the default installation for BI server. Also run the BI Modelling installer to install the Framework Manager (choose different installation folder than default).. Go to Cognos Configuration and start the service. Open services.msc and you can see Cognos 8 is running. Also start Cognos Content service if it is not already running. Up to here is cake!! If you have IIS7 and Web Services skills, you can refer to the installation guide and setup the virtual directory for your Cognos installation. I personally couldn't get IIS7 working with Cognos 8.3. It was giving me all kinds of errors around CGI. So I installed APACHE (freely available) and configured it. Woolllaa.. It started working .... Please note: 1. Report Studio doesn't open in current version of Firefox. I need to use IE. 2. The GOSales samples come as separate installation and you need to install them using Cognos Samp...

Written by: Ankit G Some days ago I came across a DW query posted on a technical discussion: “Based on prior experience, can someone please comment on which is better: small dimensions versus a large dimension. I am asking in terms of performance and reporting?” Now this is a common dilemma which DW designers face while designing Star Schemas. My thoughts: If we want to split a dimension (along width) we can do that by snowflaking it (normalizing the dimensions) or by creating mini-dimensions. Snowflake schema will reduce data redundancy and duplication, but it will increase the number of joins required to fetch all the dimensional information. The decision needs to be made based on two major factors: Performance and Usability Dimension tables that contain customer or product information might easily have 50 to 100 attributes and many millions of rows. However, dimension tables with too many attributes can lead to excessively wide rows and poor performance . For this reason, you might...

Written by: Ankit G Cognos planning (EPS) was the flagship planning and budgeting solution from Cognos until Cognos took over Applix and hence TM1. Now they are promoting both the products as planning and budgeting solution. I have installed and evaluated both but did not find much differences, as long as application is concerned (in order to recommend one over the other). Some points I found worth noting are: 1) EPS has got a better workflow management and matured front end, hence if you have large number of users spread across various locations who want to collaborate and enter planning data, EPS would be a better solution than TM1. 2) EPS works on a 32 bit engine. Analyst has difficulty while loading large slices of data in contiguous RAM. Contributor also moves large blocks of data in and out of datastore in the form of xml. Hence scalability and performance is a problem in case the data is in very large volumes. TM1 uses 64 bit architecture and hence faster while dealing large amo...

Okay... So last time we discussed a model with WEIGHT or WEIGHING FACTOR, which helps in distributing the facts (Sales) across multiple Product Groups. It still had limitation on the maximum number of Groups, a Product can be assigned to. So, now consider this scenario. What if we bring the grain down to PRODUCT-PRODUCT GROUP combinations? So, in our example, we treat Umbrella-Fashion as one entity, and Umbrella-Seasonal as another entity..?? This will raise a question - When actual Sales happens, what we know is only that a product called 'Umbrella' is sold!! Here, comes in the Intelligent Modelling. So, we can weave some kind of 'intelligence' at the Source or in ETL to classify the Product, or split the facts across multiple entities. Imagine the whole new world this idea opens! If business agrees, the PRODUCT-PRODUCT GROUP combinations can be exposed to the source system. So the fancy, colourful umbrellas can be connected to Umbrella-Fashion, whereas the basic umbre...

As discussed in previous post, the idea of attaching one product to many product group is fundamentally flawed. In previous example, we have seen that every time there is jump in Umbrella sales, they will be jump in Fashion sales and Seasonal sales. So, this is clearly not going help the trend analysis or intelligent decision making. Also, the schema proposed in previous example has an upper limit on the number of groups a product can belong to. If the business is happy with above limitations, you don't need to read further. However, business also agrees that it is a flaw and they would like to see what options are available - we can a step further. Depending on the nature of the business, we will assign a weighing factor to each product group for each item. This weighing factor (now on referred to as WF) will be less than 1 for each entry, such that the sum of WF for a product equals ONE . In our example, Umbrella is more of a seasonal product. It is also a fashion (because there...