Author Topic: moving reports from 10.2 to Cognos analytics 11  (Read 2674 times)

Offline sdf

  • Statesman
  • ******
  • Join Date: Feb 2014
  • Posts: 476
  • Forum Citizenship: +3/-0
Re: moving reports from 10.2 to Cognos analytics 11
« Reply #30 on: 01 Aug 2018 03:48:34 pm »
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?

Offline CognosPaul

  • Global Moderator
  • Statesman
  • *****
  • Join Date: Jan 2009
  • Posts: 1,704
  • Forum Citizenship: +252/-1
    • Paul's Cognos Blog
Re: moving reports from 10.2 to Cognos analytics 11
« Reply #31 on: 01 Aug 2018 04:28:45 pm »
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.

Offline MFGF

  • Never knowingly correct
  • Super Moderator
  • Statesman
  • ******
  • Join Date: Jul 2005
  • Posts: 10,411
  • Forum Citizenship: +627/-10
  • Cognos Software Muppet
Re: moving reports from 10.2 to Cognos analytics 11
« Reply #32 on: 02 Aug 2018 02:50:19 am »
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.
« Last Edit: 02 Aug 2018 02:51:57 am by MFGF »
Meep!

Offline CognosPaul

  • Global Moderator
  • Statesman
  • *****
  • Join Date: Jan 2009
  • Posts: 1,704
  • Forum Citizenship: +252/-1
    • Paul's Cognos Blog
Re: moving reports from 10.2 to Cognos analytics 11
« Reply #33 on: 02 Aug 2018 11:21:54 am »
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!

Offline sdf

  • Statesman
  • ******
  • Join Date: Feb 2014
  • Posts: 476
  • Forum Citizenship: +3/-0
Re: moving reports from 10.2 to Cognos analytics 11
« Reply #34 on: 02 Aug 2018 11:50:07 am »
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


Offline CognosPaul

  • Global Moderator
  • Statesman
  • *****
  • Join Date: Jan 2009
  • Posts: 1,704
  • Forum Citizenship: +252/-1
    • Paul's Cognos Blog
Re: moving reports from 10.2 to Cognos analytics 11
« Reply #35 on: 02 Aug 2018 04:12:35 pm »
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.

Offline sdf

  • Statesman
  • ******
  • Join Date: Feb 2014
  • Posts: 476
  • Forum Citizenship: +3/-0
Re: moving reports from 10.2 to Cognos analytics 11
« Reply #36 on: 02 Aug 2018 04:31:35 pm »
Good stuff!

And I totally get this :
Quote
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!

 


       
Twittear