2 Replies Latest reply: May 14, 2013 2:04 AM by Schattenzeit RSS

    AWM    OLAP-DML Expression to large?

    Schattenzeit
      Hello all!

      I have a problem with my AW or with my AW -Manager. I used OLAP-DML Expressions ,and when I write longer Expressions, I got:

      oracle.express.idl.util.OlapiException: java.sql.SQLException: Keine weiteren Daten aus Socket zu lesen.

      So no more data from socket. I have to restart AW Manager. I can compile the Expressio nwithout error, but when I save the Expression I get the error.
      I use AWM 11.2.0.3.0A

      Her an example:
      This function very well:

      if time_utility eq 'CURRENT_DAY' then CUB_MEMBER_TRANSACTION_F_TVON / CUB_MEMBER_TRANSACTION_F_DURATION else -
      if time_utility eq '12MONTHS' then lag(CUB_MEMBER_TRANSACTION_F_TVON / CUB_MEMBER_TRANSACTION_F_DURATION, 12, zeit, RELATION ZEIT_LEVELREL) else -
      if time_utility eq 'CURRENT_DAY28' then lag(CUB_MEMBER_TRANSACTION_F_TVON / CUB_MEMBER_TRANSACTION_F_DURATION, 28, zeit, RELATION ZEIT_LEVELREL) else -
      if time_utility eq 'CURRENT_DAY7' then lag(CUB_MEMBER_TRANSACTION_F_TVON / CUB_MEMBER_TRANSACTION_F_DURATION, 7, zeit, RELATION ZEIT_LEVELREL) else -
      if time_utility eq 'CURRENT_MONTH' then CUB_MEMBER_TRANSACTION_F_TVON / CUB_MEMBER_TRANSACTION_F_DURATION else -
      if time_utility eq 'CURRENT_MONTH12' then lag(CUB_MEMBER_TRANSACTION_F_TVON / CUB_MEMBER_TRANSACTION_F_DURATION, 12, zeit, RELATION ZEIT_LEVELREL) else -
      if time_utility eq 'DELTA_CD7' then LAGPCT(CUB_MEMBER_TRANSACTION_F_TVON / CUB_MEMBER_TRANSACTION_F_DURATION, 7, zeit, RELATION ZEIT_LEVELREL) else -
      if time_utility eq 'DELTA_CD28' then LAGPCT(CUB_MEMBER_TRANSACTION_F_TVON / CUB_MEMBER_TRANSACTION_F_DURATION, 28, zeit, RELATION ZEIT_LEVELREL) else -
      if time_utility eq 'DELTA_CM12' then LAGPCT(CUB_MEMBER_TRANSACTION_F_TVON / CUB_MEMBER_TRANSACTION_F_DURATION, 12, zeit, RELATION ZEIT_LEVELREL) else -
      if time_utility eq 'MTD' then if extchars(zeit,1,3) eq 'DAY' then mvtot(CUB_MEMBER_TRANSACTION_F_TVON / CUB_MEMBER_TRANSACTION_F_DURATION,1-convert(joinchars(extchars(zeit,5,2)) int),0,1,zeit) else CUB_MEMBER_TRANSACTION_F_TVON / CUB_MEMBER_TRANSACTION_F_DURATION else -
      if time_utility eq 'MTD1' then if extchars(zeit,1,3) eq 'DAY' then  mvtot(CUB_MEMBER_TRANSACTION_F_TVON / CUB_MEMBER_TRANSACTION_F_DURATION,convert (ADD_MONTHS(convert( joinchars( '01' extchars(zeit,7,8)), date  'DMY'), -1) - convert( joinchars(  extchars(zeit,5,8)), date  'DMY'),int)  ,convert(ADD_MONTHS(convert(extchars(zeit,5,8), date  'DMY'), -1)  -  convert(extchars(zeit,5,8), date  'DMY'),int),1,zeit) else CUB_MEMBER_TRANSACTION_F_TVON / CUB_MEMBER_TRANSACTION_F_DURATION else -
      if time_utility eq 'MTD12' then if extchars(zeit,1,3) eq 'DAY' then mvtot(CUB_MEMBER_TRANSACTION_F_TVON / CUB_MEMBER_TRANSACTION_F_DURATION,convert (ADD_MONTHS(convert( joinchars( '01' extchars(zeit,7,8)), date  'DMY'), -12) - convert( joinchars(  extchars(zeit,5,8)), date  'DMY'),int)  ,convert(ADD_MONTHS(convert(extchars(zeit,5,8), date  'DMY'), -12)  -  convert(extchars(zeit,5,8), date  'DMY'),int),1,zeit) else CUB_MEMBER_TRANSACTION_F_TVON / CUB_MEMBER_TRANSACTION_F_DURATION else -
      if time_utility eq 'YTD' then if extchars(zeit,1,3) eq 'DAY' then if convert(extchars(zeit,8,2), int) GE 10  AND  convert(extchars(zeit,8,2), int) LE 12 THEN   mvtot(CUB_MEMBER_TRANSACTION_F_TVON / CUB_MEMBER_TRANSACTION_F_DURATION, convert(convert(joinchars( '01.10.'  extchars(zeit,11,2)), date  'DMY')   - convert( joinchars(  extchars(zeit,5,8)), date  'DMY'),int)  , 0,1,zeit)   ELSE   mvtot(CUB_MEMBER_TRANSACTION_F_TVON / CUB_MEMBER_TRANSACTION_F_DURATION, convert( ADD_MONTHS( convert(joinchars( '01.10.'  extchars(zeit,11,2)), date  'DMY'),-12)   - convert( joinchars(  extchars(zeit,5,8)), date  'DMY'),int)  , 0,1,zeit) else CUB_MEMBER_TRANSACTION_F_TVON / CUB_MEMBER_TRANSACTION_F_DURATION else -
      if time_utility eq 'YTD1' then if extchars(zeit,1,3) eq 'DAY' then if convert(extchars(zeit,8,2), int) GE 10  AND  convert(extchars(zeit,8,2), int) LE 12 THEN   mvtot(CUB_MEMBER_TRANSACTION_F_TVON / CUB_MEMBER_TRANSACTION_F_DURATION, convert( ADD_MONTHS (convert(joinchars( '01.10.'  extchars(zeit,11,2)), date  'DMY'),-12)   -convert( joinchars(  extchars(zeit,5,8)), date  'DMY'),int), convert(ADD_MONTHS(convert(extchars(zeit,5,8), date  'DMY'), -12)  -  convert(extchars(zeit,5,8), date  'DMY'),int),1,zeit)  ELSE mvtot(CUB_MEMBER_TRANSACTION_F_TVON / CUB_MEMBER_TRANSACTION_F_DURATION, convert( ADD_MONTHS (convert(joinchars( '01.10.'  extchars(zeit,11,2)), date  'DMY'),- 2 * 12 )   -convert( joinchars(  extchars(zeit,5,8)), date  'DMY'),int), convert(ADD_MONTHS(convert(extchars(zeit,5,8), date  'DMY'), - 12)  -  convert(extchars(zeit,5,8), date  'DMY'),int),1,zeit) else CUB_MEMBER_TRANSACTION_F_TVON / CUB_MEMBER_TRANSACTION_F_DURATION else -
      CUB_MEMBER_TRANSACTION_F_TVON / CUB_MEMBER_TRANSACTION_F_DURATION

      but this is too much:

      if prod_type_no eq  'PROD_TYPE_L1_Call' then ( -
      if time_utility eq 'CURRENT_DAY' then CUB_MEMBER_TRANSACTION_F_TVON / CUB_MEMBER_TRANSACTION_F_DURATION else -
      if time_utility eq '12MONTHS' then lag(CUB_MEMBER_TRANSACTION_F_TVON / CUB_MEMBER_TRANSACTION_F_DURATION, 12, zeit, RELATION ZEIT_LEVELREL) else -
      if time_utility eq 'CURRENT_DAY28' then lag(CUB_MEMBER_TRANSACTION_F_TVON / CUB_MEMBER_TRANSACTION_F_DURATION, 28, zeit, RELATION ZEIT_LEVELREL) else -
      if time_utility eq 'CURRENT_DAY7' then lag(CUB_MEMBER_TRANSACTION_F_TVON / CUB_MEMBER_TRANSACTION_F_DURATION, 7, zeit, RELATION ZEIT_LEVELREL) else -
      if time_utility eq 'CURRENT_MONTH' then CUB_MEMBER_TRANSACTION_F_TVON / CUB_MEMBER_TRANSACTION_F_DURATION else -
      if time_utility eq 'CURRENT_MONTH12' then lag(CUB_MEMBER_TRANSACTION_F_TVON / CUB_MEMBER_TRANSACTION_F_DURATION, 12, zeit, RELATION ZEIT_LEVELREL) else -
      if time_utility eq 'DELTA_CD7' then LAGPCT(CUB_MEMBER_TRANSACTION_F_TVON / CUB_MEMBER_TRANSACTION_F_DURATION, 7, zeit, RELATION ZEIT_LEVELREL) else -
      if time_utility eq 'DELTA_CD28' then LAGPCT(CUB_MEMBER_TRANSACTION_F_TVON / CUB_MEMBER_TRANSACTION_F_DURATION, 28, zeit, RELATION ZEIT_LEVELREL) else -
      if time_utility eq 'DELTA_CM12' then LAGPCT(CUB_MEMBER_TRANSACTION_F_TVON / CUB_MEMBER_TRANSACTION_F_DURATION, 12, zeit, RELATION ZEIT_LEVELREL) else -
      if time_utility eq 'MTD' then if extchars(zeit,1,3) eq 'DAY' then mvtot(CUB_MEMBER_TRANSACTION_F_TVON / CUB_MEMBER_TRANSACTION_F_DURATION,1-convert(joinchars(extchars(zeit,5,2)) int),0,1,zeit) else CUB_MEMBER_TRANSACTION_F_TVON / CUB_MEMBER_TRANSACTION_F_DURATION else -
      if time_utility eq 'MTD1' then if extchars(zeit,1,3) eq 'DAY' then  mvtot(CUB_MEMBER_TRANSACTION_F_TVON / CUB_MEMBER_TRANSACTION_F_DURATION,convert (ADD_MONTHS(convert( joinchars( '01' extchars(zeit,7,8)), date  'DMY'), -1) - convert( joinchars(  extchars(zeit,5,8)), date  'DMY'),int)  ,convert(ADD_MONTHS(convert(extchars(zeit,5,8), date  'DMY'), -1)  -  convert(extchars(zeit,5,8), date  'DMY'),int),1,zeit) else CUB_MEMBER_TRANSACTION_F_TVON / CUB_MEMBER_TRANSACTION_F_DURATION else -
      if time_utility eq 'MTD12' then if extchars(zeit,1,3) eq 'DAY' then mvtot(CUB_MEMBER_TRANSACTION_F_TVON / CUB_MEMBER_TRANSACTION_F_DURATION,convert (ADD_MONTHS(convert( joinchars( '01' extchars(zeit,7,8)), date  'DMY'), -12) - convert( joinchars(  extchars(zeit,5,8)), date  'DMY'),int)  ,convert(ADD_MONTHS(convert(extchars(zeit,5,8), date  'DMY'), -12)  -  convert(extchars(zeit,5,8), date  'DMY'),int),1,zeit) else CUB_MEMBER_TRANSACTION_F_TVON / CUB_MEMBER_TRANSACTION_F_DURATION else -
      if time_utility eq 'YTD' then if extchars(zeit,1,3) eq 'DAY' then if convert(extchars(zeit,8,2), int) GE 10  AND  convert(extchars(zeit,8,2), int) LE 12 THEN   mvtot(CUB_MEMBER_TRANSACTION_F_TVON / CUB_MEMBER_TRANSACTION_F_DURATION, convert(convert(joinchars( '01.10.'  extchars(zeit,11,2)), date  'DMY')   - convert( joinchars(  extchars(zeit,5,8)), date  'DMY'),int)  , 0,1,zeit)   ELSE   mvtot(CUB_MEMBER_TRANSACTION_F_TVON / CUB_MEMBER_TRANSACTION_F_DURATION, convert( ADD_MONTHS( convert(joinchars( '01.10.'  extchars(zeit,11,2)), date  'DMY'),-12)   - convert( joinchars(  extchars(zeit,5,8)), date  'DMY'),int)  , 0,1,zeit) else CUB_MEMBER_TRANSACTION_F_TVON / CUB_MEMBER_TRANSACTION_F_DURATION else -
      if time_utility eq 'YTD1' then if extchars(zeit,1,3) eq 'DAY' then if convert(extchars(zeit,8,2), int) GE 10  AND  convert(extchars(zeit,8,2), int) LE 12 THEN   mvtot(CUB_MEMBER_TRANSACTION_F_TVON / CUB_MEMBER_TRANSACTION_F_DURATION, convert( ADD_MONTHS (convert(joinchars( '01.10.'  extchars(zeit,11,2)), date  'DMY'),-12)   -convert( joinchars(  extchars(zeit,5,8)), date  'DMY'),int), convert(ADD_MONTHS(convert(extchars(zeit,5,8), date  'DMY'), -12)  -  convert(extchars(zeit,5,8), date  'DMY'),int),1,zeit)  ELSE mvtot(CUB_MEMBER_TRANSACTION_F_TVON / CUB_MEMBER_TRANSACTION_F_DURATION, convert( ADD_MONTHS (convert(joinchars( '01.10.'  extchars(zeit,11,2)), date  'DMY'),- 2 * 12 )   -convert( joinchars(  extchars(zeit,5,8)), date  'DMY'),int), convert(ADD_MONTHS(convert(extchars(zeit,5,8), date  'DMY'), - 12)  -  convert(extchars(zeit,5,8), date  'DMY'),int),1,zeit) else CUB_MEMBER_TRANSACTION_F_TVON / CUB_MEMBER_TRANSACTION_F_DURATION else -
      CUB_MEMBER_TRANSACTION_F_TVON / CUB_MEMBER_TRANSACTION_F_DURATION)  ELSE -
      +0+

      Is there a limit for the length of Expressions? Or is there a problem with AW -Manager ??

      Thanks a lot

      Greetings from Berlin

      Michael

      Edited by: Schattenzeit on 13.05.2013 14:54
        • 1. Re: AWM    OLAP-DML Expression to large?
          Nasar-Oracle
          Not sure why you are getting error.

          You can put this in an OLAP dml program and call that program in your formula (i.e., calculated measure).

          Some other suggestions:

          (1). Define multiple formulas (or calculated measures) in your AW. For example you can define a separate calc measure for moving total function.

          (2). Try to use OLAP Expression Syntax, instead of OLAP dml.
          IF...THEN..ELSE logic is done using CASE statements in OLAP Expression Syntax. Moving Total and other time-series functions can be done easily also in OLAP Expression Syntax.

          (3). Looks like you probably have a dimension whose members are time-series measures. You can define all the time series measures separately also. Its easier to manage each measure and you can do LOOP_VAR and LOOP_DENSE settings also for better performance.
          • 2. Re: AWM    OLAP-DML Expression to large?
            Schattenzeit
            Hello Nasar,
            yes you are right, I have to put it into an OLAP DML program. I read, the expression can hold max 4000 charakters. I change it today :)
            It is my first try, so I didn't know that there is al imit.
            Thanks a lot!