Topic
5 replies Latest Post - ‏2013-05-17T17:20:54Z by Winson11
Winson11
Winson11
29 Posts
ACCEPTED ANSWER

Pinned topic Zero suppression in Anlaysis Studio

‏2013-05-10T15:49:58Z |

Hi Cognos Guru's,

Cognos 10.1.1, Relational data (DMR), Analysis Studio, Database Oracle 11g.

Here is the issue we are facing with Analysis studio.

When we nested more than 2 rows in the crosstab with the zero suppression it is kind of taking so long time to bring back the data. 

Ex:

With out zero suppression the report takes about 25 second.

With zero suppression the report takes about 15 min.

Is there anything we can do to make the crosstab run faster in Analysis studio? If so please share some idea with us.

Thanks,

Winson.

  • OpherB
    OpherB
    543 Posts
    ACCEPTED ANSWER

    Re: Zero suppression in Anlaysis Studio

    ‏2013-05-10T19:31:37Z  in response to Winson11

    You are nesting "more than 2" levels/Dimensions in a crosstab and results come back in 15 minutes with zero suppression? I know users who would think that was spectacular performance!

    Your users' expectations are out of line. Unless your cube is TINY, this should never be done and for many companies is 100% impossible to do.

    Opher

     

  • JohnWilkinson
    JohnWilkinson
    62 Posts
    ACCEPTED ANSWER

    Re: Zero suppression in Anlaysis Studio

    ‏2013-05-12T16:52:49Z  in response to Winson11

    Zero suppression in Analysis Studio always performs badly. You can improve performance in some scenarios by enabling the old suppression model from early versions of Cognos 8 - this may not provide accurate results however:

    http://publib.boulder.ibm.com/infocenter/c8bi/v8r4m0/index.jsp?topic=/com.ibm.swg.im.cognos.ug_cra.8.4.0.doc/ug_cra_id30545ChangetheSuppressionFunctionalityinAnalys.html

    Enabling DQM also improves zero suppression against DMR over SQL Server, Oracle, MSAS Cubes and TM1 sources by passing zero suppression to the database engine for processing. It has no effect against Cognos Cube sources as they are not supported by DQM.

    The other option is to think about the new Dynamic Cube functionality in 10.2. This gives vastly improved DMR performance.

    • Winson11
      Winson11
      29 Posts
      ACCEPTED ANSWER

      Re: Zero suppression in Anlaysis Studio

      ‏2013-05-13T13:26:57Z  in response to JohnWilkinson

      Thanks for the valuables input Opher/John Wilkinson.

       

    • Glen.S
      Glen.S
      20 Posts
      ACCEPTED ANSWER

      Re: Zero suppression in Anlaysis Studio

      ‏2013-05-13T15:03:12Z  in response to JohnWilkinson

      Analysis Studio can perform quite well against large cubes, even with suppression on. Things to bear in mind:

      • Suppression disables the "More" functionality that shows only the first few members in each group.
      • Zero suppression is always done locally, and can therefore be very slow if you are processing a lot of data.
      • NULL suppression is different. In Compatible Query Mode, it is done locally, and therefore can also be very slow. However, in Dynamic Query Mode, it is normally pushed to the data source, and is much faster.
      • DMR can be very slow when processing very large dimensions. If you have these, consider some alternatives:
        • Move the leaf level of the dimension to a relational package, and access then necessary using drill-through.
        • Use Dynamic Cubes.
      • Educate your users to use the guidelines here: http://www-01.ibm.com/support/docview.wss?uid=swg21495975 
      • Winson11
        Winson11
        29 Posts
        ACCEPTED ANSWER

        Re: Zero suppression in Anlaysis Studio

        ‏2013-05-17T17:20:54Z  in response to Glen.S

        Many Many Thanks Glen. Yes it makes sense. 

         

        Winson.