moving reports from 10.2 to Cognos analytics 11

[CognosPaul]

MF,

This has not been our experience. We are upgrading from 10.2.2 to 11.05 and have many reports that ran just fine in 10.2.2 and now either error or take hours to run in 11.0.5 (when they took minutes to run in 10.2.2). In fact there is a scheduled report running now that is at almost 6 hours when it took less than 20 minutes on our 10.2.2 server. My IT services group tells me that the configuration and resource are the same and should not be causing this restriction.

My fellow report developers are at our wits end trying to figure out why these reports don't run in the same time or at all.

CC-GW

Did you migrate from CQM to DQM? I'm doing an upgrade project now, and it's been a nightmare. Pretty much every report, over 1400, needs a rewrite to work in DQM. Even if the reports validate, some expressions just killed performance.

[ccgwalters]

Did you migrate from CQM to DQM? I'm doing an upgrade project now, and it's been a nightmare. Pretty much every report, over 1400, needs a rewrite to work in DQM. Even if the reports validate, some expressions just killed performance.

CognosPaul,

The packages we used in 10.2.2 for reports were all CQM and still are in 11. I do not know how Cognos is installed or configured, as that is the realm of our ITS department. I'll check and see if DQM is the default now.

Thanks for the info.

[MFGF]

MF,

This has not been our experience. We are upgrading from 10.2.2 to 11.05 and have many reports that ran just fine in 10.2.2 and now either error or take hours to run in 11.0.5 (when they took minutes to run in 10.2.2). In fact there is a scheduled report running now that is at almost 6 hours when it took less than 20 minutes on our 10.2.2 server. My IT services group tells me that the configuration and resource are the same and should not be causing this restriction.

My fellow report developers are at our wits end trying to figure out why these reports don't run in the same time or at all.

CC-GW

It's also pertinent to understand that Cognos Analytics has quite a bit more functionality (and therefore more java services running) than Cognos 10. Just because Cognos 10 runs quickly on a specific server configuration, it doesn't necessarily follow that Cognos Analytics will run as quickly using the same server spec - you will quite likely find that the server requires more memory for CA. In 11.0.8 IBM added a setting to the Run Options dialog to "Show performance details" - you can switch this on to see where the time is being taken when running reports.

Cheers!

MF.

[sdf]

Hi Paul,

How exactly are you doing this?

Did you migrate from CQM to DQM? I'm doing an upgrade project now, and it's been a nightmare. Pretty much every report, over 1400, needs a rewrite to work in DQM. Even if the reports validate, some expressions just killed performance.

[CognosPaul]

We used Motio to generate a list of every malfunctioning report, and went through them one by one. The total number of reports ended up being over 3k, and took several months of effort with multiple consultants.

Hi Paul,

How exactly are you doing this?

[sdf]

Did you set up a parallel environment just so the CQM reports are still in use while you rewrite them moving to the new version?

[CognosPaul]

The end users were still on the C10 environment until their reports were completed. The client organization was set up in divisions, each with their own suite of reports. As each suite was migrated, we had the division move over and disabled the C10 area. The transition wasn't as smooth as I'd have liked, divisions often made copies/report views/shortcuts of reports from other division folders.

An easier solution would have been to perform the CQM to DQM conversion in C10 before migrating everyone over at once.

[MFGF]

The end users were still on the C10 environment until their reports were completed. The client organization was set up in divisions, each with their own suite of reports. As each suite was migrated, we had the division move over and disabled the C10 area. The transition wasn't as smooth as I'd have liked, divisions often made copies/report views/shortcuts of reports from other division folders.

An easier solution would have been to perform the CQM to DQM conversion in C10 before migrating everyone over at once.

My experience of CQM to DQM migrations is that they can often be quite difficult. This isn't because there are problems with DQM, per se, but often because of some of the outrageous stuff modellers and authors have "got away with" in their CQM models and reports. One example that comes to mind is where in a CQM model there was some hard-coded SQL that used the same table alias (a) for two different things at different nested levels within the same query. DQM barfed at this, and quite rightly too - it was never legal syntax. There were also ORDER BY clauses in some of the SQL - we have never supported this in query subjects in a model. There were stored procedure calls defined without any default testing values for the arguments, calls to stored procedures in regular SQL query subjects rather than in Stored Procedure query subjects, data type mismatches between values passed as arguments to stored procedures and the defined types for the arguments, and many, many more awful things. CQM would permit this nonsense, whereas DQM is more exacting. If you can find a CQM model and reports that are built without these kinds of issues (aka a Unicorn project :) ) the migration will be much easier.

Just my tuppence...

MF.

[CognosPaul]

This is exactly my experience. The vast majority of the reports used essentially the same anti-patterns, over and over.

The big ones:

1. Tuples work only with members or measures. CQM would allow tuples or value expressions, DQM does not. tuple(tuple(<measure>,h1.member),h2.member) would fail.
2. Case/when or if/then in OLAP is frowned upon, but CQM would let you get away with it.
3. A few functions were being used incorrectly. bottomCount was being used to reverse the natural order of a hierarchy, bottomCount(years,12) in CQM would reverse the years set and return the last 12 years. In DQM that expression fails as invalid because it's missing the value parameter.
4. Non-assigned members. DQM isn't that clever when it tries to assign calculated members to the correct hierarchy.
5. Using member summaries with an incorrect number of parameters. total(<value expression> within set h1.member1,h1.member2,h2.member3) I'm actually surprised that worked in CQM. This actually worked in DQM, but killed the runtime, sometimes taking up to an hour to process. Fixing that to something like total(tuple(measure, h2.member3) within set set(h1.member1, h1.member2)). It's hard to find because DQM doesn't actually flag it as invalid!

[sdf]

Wow! what a pain!  :(

Those items are commonly/widely used, at least from my development experience.  :'(

@paul, this would be a big favor.
But it would be a great help if you can list the techniques or work around you've used to overcome all this  ;)

cheers!

sdf

[CognosPaul]

It really was just a lot of elbow grease. I found just running the report was much faster than validating.

So my run through for fixing the next report on the list was basically.

• Go through each data item, manually, in every query looking for the following.
   
  
  
  • tuples referencing data items
  • member summaries with multiple items instead of a single set expression
  • any instance of case/when
   
• fix the issues as I find them
• run and rerun the report, fixing the errors that are thrown

The tuple issue might look like this:

tuple([Asia Region],[Data item1])
Data Item1: tuple([Revenue],[Previous Year])

in that case fixing the tuple would mean going to data item1 and changing the expression to something like: member(tuple([Revenue],[Previous Year]),'Data Item1','Data Item1',[Cube].[Measures])

That way if there were any other data items tupling on Data Item1 those would be fixed as well.


The member summary issue is just a matter of fixing the set expression to actually be a set expression.

The case/when is more difficult. Fixing that is entirely dependent on the expression. Usually I had to redesign the logic to get it to work.

At the end of the day there's no quick and easy fix. It took me and a team of colleagues several months to fix the reports. If you're really stuck, you can email me through my blog, and I'll put you in touch with the guy in charge of upgrade projects at my firm.

[sdf]

Good stuff!

And I totally get this :

tuple([Asia Region],[Data item1])
Data Item1: tuple([Revenue],[Previous Year])

in that case fixing the tuple would mean going to data item1 and changing the expression to something like: member(tuple([Revenue],[Previous Year]),'Data Item1','Data Item1',[Cube].[Measures])

since it would require a member for the tuple to work.

Thanks Paul!