COGNOS for Excel

[matrixfree]

Hello,

Not sure how to name this or where to post it.

But, we want to explore the Cognos for Excel capabilities. We never used it and don't even know if we have this option (NOT talking about running report in Cognos then Exporting it to Excel)

Any advices ? Advantages and Disadvantages please ?

Thanks !

[Lynn]

My understanding is that you have two areas to explore so I'm not sure it is clear which one you are interested in.

Cognos for Microsoft Office is installed locally on an individual's laptop. After installing, you open MS Word or Excel or PowerPoint and find a new tab for IBM Cognos to expose a menu ribbon allowing you to connect to your content store and view a source pane from which you can select pre-authored content to include in your document or spreadsheet or presentation. I've used this and it is a very nice supplement to the concept of author once, consume anywhere. It offers a great productivity enhancement for situations where content is routinely copy/pasted into presentation decks, proposals, and such. Personally I find Word and PowerPoint make the most sense but perhaps I've just not come across a compelling use case for Excel.

Cognos Analysis for Excel is a different product that allows you to actually author content within the Excel environment. I've never used this but I think I'm correct in saying it does not offer as robust an authoring environment when compared with Report Studio or Workspace Advanced. I think it is aimed mainly at those spread-heads who simply insist on living in a green world of numbered rows and lettered columns with the word Excel somewhere in constant view. Sort of like a two-year-old who insists on having a particular blanket or favorite stuffed toy always in sight :D

[matrixfree]

Thank you Lynn,

mainly at those spread-heads who simply insist on living in a green world of numbered rows and lettered columns with the word Excel
==> I agree with you.

p.s by the way are they very expensive tools ?

[MFGF]

Thank you Lynn,

mainly at those spread-heads who simply insist on living in a green world of numbered rows and lettered columns with the word Excel
==> I agree with you.

p.s by the way are they very expensive tools ?

Hi,

IBM Cognos for MS Office is included at no extra charge for all Analytics User (and above) licenses.
IBM Cognos Analysis for Microsoft Excel (CAFE) is included at no extra charge for all Analytics Explorer (and above) licenses.

The license types for Cognos BI from least to most expensive (and from least to most capabilities) are:

- IBM Cognos Analytics Information Distribution
- IBM Cognos Analytics User
- IBM Cognos Analytics Explorer
- IBM Cognos Analytics Administrator
- IBM Cognos Forward Looking Analytics Architect

Cheers!

MF.

[matrixfree]

Thanks MF. This I a GOOOOOOD Cognos forum by the way ! 8)

[hespora]

I think it is aimed mainly at those spread-heads who simply insist on living in a green world of numbered rows and lettered columns with the word Excel somewhere in constant view. Sort of like a two-year-old who insists on having a particular blanket or favorite stuffed toy always in sight :D

I gotta say, I actually take offence to that. ;)

One of the main differences of Cafe over RS is the fact that all except the most basic calculations are done completely client side in Excel, rather than by the query service. For a certain group of my org's users, I've developed a report in RS that would allow them to analyze historic pricing. The whole thing is laid out as an interactive report in html. Now, thanks to cognos' piss-poor performance when it comes to master-detail relationships, this thing takes 6-8 minutes to run (3 repeater tables containing a list and a chart object, to each of which the detail query is a staggered join), which of course renders it completely unusable for ad-hoc analysis. Built the whole thing in Excel with Cafe, runs in 40sec.

Knowing which tool to use when is key. Don't bash a tool just because you haven't found a use for it yet. ;)

[Lynn]

I gotta say, I actually take offence to that. ;)

One of the main differences of Cafe over RS is the fact that all except the most basic calculations are done completely client side in Excel, rather than by the query service. For a certain group of my org's users, I've developed a report in RS that would allow them to analyze historic pricing. The whole thing is laid out as an interactive report in html. Now, thanks to cognos' piss-poor performance when it comes to master-detail relationships, this thing takes 6-8 minutes to run (3 repeater tables containing a list and a chart object, to each of which the detail query is a staggered join), which of course renders it completely unusable for ad-hoc analysis. Built the whole thing in Excel with Cafe, runs in 40sec.

Knowing which tool to use when is key. Don't bash a tool just because you haven't found a use for it yet. ;)

Apologies for causing offence.

[cognostechie]

I gotta say, I actually take offence to that. ;)

One of the main differences of Cafe over RS is the fact that all except the most basic calculations are done completely client side in Excel, rather than by the query service. For a certain group of my org's users, I've developed a report in RS that would allow them to analyze historic pricing. The whole thing is laid out as an interactive report in html. Now, thanks to cognos' piss-poor performance when it comes to master-detail relationships, this thing takes 6-8 minutes to run (3 repeater tables containing a list and a chart object, to each of which the detail query is a staggered join), which of course renders it completely unusable for ad-hoc analysis. Built the whole thing in Excel with Cafe, runs in 40sec.

Knowing which tool to use when is key. Don't bash a tool just because you haven't found a use for it yet. ;)

You used the wrong tool too  ;D Instead of using an OLAP package which should have used a physical cube, you used a relational package for ad-hoc analysis !!
It is actually you who is bashing the tool  ;) There is a big difference between ad-hoc reporting and ad-hoc analysis (analytics). That does not mean that CAFE is not a good tool. It was introduced with the idea to let the users pull data into EXCEL to give them the ability to merge that data with other data already in EXCEL (ex: get the expense details from Cognos and use that to refresh the PL of the company which is already in EXCEL)

[hespora]

The point is using or not using the tools *available*. I am business, I am not IT. As in, I am a report designer slash analyst with access to packages published by IT. If my org's IT only publishes certain data in a relational package rather than dimensional, there's nothing I can do about it except make do with what I got. And thus, from the limited resources I have available, I need to choose the best tool available for the job.

/Edit: Also, wha makes you think you know that an OLAP cube would have performed any better in this particular task? The report encompasses a dynamic calculation actually on row level of the DB which, as both user inputs via prompts and Facts from row level go into it, kills each and any performance advantage a precalculated and -aggregated cube would have.

[cognostechie]

The point is using or not using the tools *available*. I am business, I am not IT. As in, I am a report designer slash analyst with access to packages published by IT. If my org's IT only publishes certain data in a relational package rather than dimensional, there's nothing I can do about it except make do with what I got. And thus, from the limited resources I have available, I need to choose the best tool available for the job.

/Edit: Also, wha makes you think you know that an OLAP cube would have performed any better in this particular task? The report encompasses a dynamic calculation actually on row level of the DB which, as both user inputs via prompts and Facts from row level go into it, kills each and any performance advantage a precalculated and -aggregated cube would have.

This might be a 'point' for you but to me , it sounds like a limitation and that being the case, your judgement of tools is limited by your exposure and hence likely to be wrong. I have worked for companies where the reports had lot of queries with joins and unions taking 4-5 minutes to run and in some cases just timing out. I re-wrote those reports with much less queries and it ran in less than 30 seconds. In all those cases, they were blaming Report Studio until they got the new report. Applying a calculation to each row of data does not cause any performance issues and you can test it by making a simple report with one query and two columns. You won't see any performance issues. The problem arises because of too many queries made poorly. Another reason for making reports like that is the poor designing of the package. When the package supports only simple reporting then complexities have to embedded in the report.

I do understand your problem that you have to live with what has been given to you but for the sake of enlightenment , there are tools to do exactly what you need. TM1 is designed to take inputs from the user (what-if analysis). If you do not have licenses for that, you can use Dynamic Cubes which would also do this. Cubes do have pre-aggregated measures but also allow calculations on the fly over the entire data set.   

Your issue is a very standard issue, dealt by many people many times but you think it is a huge challenge simply because of the exposure. I will give you an example. I have a package which allows the users to select the currency of their choice and it converts the value of all revenue metrics to that currency meaning it applies calculations to all of them, not just one, at the row level ! We haven't seen any degradation in performance since implementing this and we do  have operational reports as well as summarized reports which allow drill thru to detailed data.

By the way, your problem is not just yours. Lot of other companies have the same problem and that's exactly why Gartner has suggested that the BI team should be working for the business instead of IT  ;)  It's the 'non-technical' issues which causes the problem.