DQM for Transformer 11

[raj_aries81]

Hi All,
I have configured Cognos 11.1.3 sandbox environment with DQM enabled to explore new features before we kickoff the actual upgrade.

It's a single server installation with client components such as Framework Manager & Transformer installed on the same machine. When I test the reports through report studio it seems to be working fine however it throws the below error when the same report is run through Cognos Transformer.

Please note I have added <Preference Name="AllowDQ" Value="1"/> to cogtr.xml

Any help in this regard is highly appreciated.

Thanks
Raj

[MFGF]

Hi All,
I have configured Cognos 11.1.3 sandbox environment with DQM enabled to explore new features before we kickoff the actual upgrade.

It's a single server installation with client components such as Framework Manager & Transformer installed on the same machine. When I test the reports through report studio it seems to be working fine however it throws the below error when the same report is run through Cognos Transformer.

Please note I have added <Preference Name="AllowDQ" Value="1"/> to cogtr.xml

Any help in this regard is highly appreciated.

Thanks
Raj

Hi,

The first thing to do there is to enable XQE logging and see what is being written into the log file when this error occurs.

Cheers!

MF.

[raj_aries81]

Hi,

The first thing to do there is to enable XQE logging and see what is being written into the log file when this error occurs.

Cheers!

MF.

Thanks MF.

I have enabled XQE logging and there is heaps of messages in the log file. Some of the messages from the log file are -

1)
<event component="XQE" group="Exception" level="ERROR" thread="100" timestamp="2019-09-24 17:20:54.586" contextId="238" requestId="18" sessionId="4116_d857a524-de9b-11e9-8569-ffdce18fda56"><![CDATA[Caught error in V5QueryHandler: XQE-GEN-0005 Found an internal error.
Caused by: com.cognos.xqe.ast.sql.parser.ParseException: Encountered "(" at line 13, column 27.
Was expecting one of:
    "/*+" ...
    ")" ...
    "," ...
    "absolute" ...
    "absent" ...
    "action" ...
    "after" ...
    "aggregate" ...
    "anytype" ...
    "as" ...
    "asc" ...
    "bit" ...
    "cascade" ...



2)
<event component="XQE" group="RequestAdapter.V5Response" level="ERROR" thread="100" timestamp="2019-09-24 17:20:54.618" contextId="238" requestId="18" sessionId="4116_d857a524-de9b-11e9-8569-ffdce18fda56"><![CDATA[V5QueryHandler returning SOAP Fault for request type "execute"]]>


3)
<faultstring>The server did something wrong</faultstring>
      <detail>
        <bus:exception>
          <severity>error</severity>
          <errorCode>XQE-GEN-0018 Query Service internal error has occurred, please see the log for details.</errorCode>
          <bus:message>
            <messageString>XQE-GEN-0018 Query Service internal error has occurred, please see the log for details.</messageString>
            <nestingLevel>1</nestingLevel>
          </bus:message>
        </bus:exception>
      </detail>
    </SOAP-ENV:Fault>
  </SOAP-ENV:Body>
</SOAP-ENV:Envelope>
]]></event>
<event component="XQE" group="Exception" level="ERROR" thread="82" timestamp="2019-09-24 17:34:57.001" contextId="475" requestId="40" sessionId="4116_ce9032e7-de9d-11e9-a20a-ffdce18fda56"><![CDATA[Caught error in V5QueryHandler: XQE-GEN-0005 Found an internal error




Also, Transformer version is 11.0 (other components 11.1.x). When I try to save the Configuration I get the below error which could possibly causing this error


[Save Configuration]
[ ERROR ] Unable to save the configuration file.
CAM-CRP-1064 Unable to process the PKCS #7 data because of an internal error. Reason: java.security.InvalidKeyException: Illegal key size or default parameters
The parameter named 'User ID and password' located in 'Planning Store' is currently invalid.

Thanks
Raj

[MFGF]

Thanks MF.

I have enabled XQE logging and there is heaps of messages in the log file. Some of the messages from the log file are -

1)
<event component="XQE" group="Exception" level="ERROR" thread="100" timestamp="2019-09-24 17:20:54.586" contextId="238" requestId="18" sessionId="4116_d857a524-de9b-11e9-8569-ffdce18fda56"><![CDATA[Caught error in V5QueryHandler: XQE-GEN-0005 Found an internal error.
Caused by: com.cognos.xqe.ast.sql.parser.ParseException: Encountered "(" at line 13, column 27.
Was expecting one of:
    "/*+" ...
    ")" ...
    "," ...
    "absolute" ...
    "absent" ...
    "action" ...
    "after" ...
    "aggregate" ...
    "anytype" ...
    "as" ...
    "asc" ...
    "bit" ...
    "cascade" ...



2)
<event component="XQE" group="RequestAdapter.V5Response" level="ERROR" thread="100" timestamp="2019-09-24 17:20:54.618" contextId="238" requestId="18" sessionId="4116_d857a524-de9b-11e9-8569-ffdce18fda56"><![CDATA[V5QueryHandler returning SOAP Fault for request type "execute"]]>


3)
<faultstring>The server did something wrong</faultstring>
      <detail>
        <bus:exception>
          <severity>error</severity>
          <errorCode>XQE-GEN-0018 Query Service internal error has occurred, please see the log for details.</errorCode>
          <bus:message>
            <messageString>XQE-GEN-0018 Query Service internal error has occurred, please see the log for details.</messageString>
            <nestingLevel>1</nestingLevel>
          </bus:message>
        </bus:exception>
      </detail>
    </SOAP-ENV:Fault>
  </SOAP-ENV:Body>
</SOAP-ENV:Envelope>
]]></event>
<event component="XQE" group="Exception" level="ERROR" thread="82" timestamp="2019-09-24 17:34:57.001" contextId="475" requestId="40" sessionId="4116_ce9032e7-de9d-11e9-a20a-ffdce18fda56"><![CDATA[Caught error in V5QueryHandler: XQE-GEN-0005 Found an internal error




Also, Transformer version is 11.0 (other components 11.1.x). When I try to save the Configuration I get the below error which could possibly causing this error


[Save Configuration]
[ ERROR ] Unable to save the configuration file.
CAM-CRP-1064 Unable to process the PKCS #7 data because of an internal error. Reason: java.security.InvalidKeyException: Illegal key size or default parameters
The parameter named 'User ID and password' located in 'Planning Store' is currently invalid.

Thanks
Raj

Hi,

Hmmmm. Let's take a step back and check at a wider level. Is the FM model you are using to publish your package created using Compatible or Dynamic Query? If it's Compatible, have you been through the process of creating and validating a specific DQM version of the model? This can uncover lots of issues that cause DQM to barf - Compatible Query is a lot more lax in its enforcement of legal syntax. I have found in the past that converting a model to DQM and validating everything in FM can generate lots of XQE errors like the ones you are posting, so my suspicion is that there are things in your model that DQM just won't tolerate. I'm guessing, though :)

Cheers!

MF.

[raj_aries81]

Hi,

Hmmmm. Let's take a step back and check at a wider level. Is the FM model you are using to publish your package created using Compatible or Dynamic Query? If it's Compatible, have you been through the process of creating and validating a specific DQM version of the model? This can uncover lots of issues that cause DQM to barf - Compatible Query is a lot more lax in its enforcement of legal syntax. I have found in the past that converting a model to DQM and validating everything in FM can generate lots of XQE errors like the ones you are posting, so my suspicion is that there are things in your model that DQM just won't tolerate. I'm guessing, though :)

Cheers!

MF.

Thanks MF. Yes, I did convert the package to DQM and published it. I'm able to run the reports against this DQM package from report studio. I'm running into the issue when I try to refresh a transformer cube which is using report as source..but when I use framework package as source it works.

Regards
Raghu

[MFGF]

Thanks MF. Yes, I did convert the package to DQM and published it. I'm able to run the reports against this DQM package from report studio. I'm running into the issue when I try to refresh a transformer cube which is using report as source..but when I use framework package as source it works.

Regards
Raghu

Hi,

Can you elaborate on what you mean by "I did convert the package to DQM and published it"? Are you referring to just checking the box during the package publish process that says "Use Dynamic Query", or did you go through the process of changing the FM model to DQM, validating everything in the model and resolving all issues, then publishing your package after that? There is a huge difference.

Cheers!

MF.

[raj_aries81]

Hi,

Can you elaborate on what you mean by "I did convert the package to DQM and published it"? Are you referring to just checking the box during the package publish process that says "Use Dynamic Query", or did you go through the process of changing the FM model to DQM, validating everything in the model and resolving all issues, then publishing your package after that? There is a huge difference.

Cheers!

MF.

Hi MF,

I changed the interface type to JDBC, Query Mode to Dynamic and validated all the query subjects with no error and then published the DQM package. I can successfully test the query subject and reports against this DQM package.

Thanks
Raj

[MFGF]

Hi MF,

I changed the interface type to JDBC, Query Mode to Dynamic and validated all the query subjects with no error and then published the DQM package. I can successfully test the query subject and reports against this DQM package.

Thanks
Raj

Hi,

Thanks for confirming. Looking again at the errors:

<event component="XQE" group="Exception" level="ERROR" thread="100" timestamp="2019-09-24 17:20:54.586" contextId="238" requestId="18" sessionId="4116_d857a524-de9b-11e9-8569-ffdce18fda56"><![CDATA[Caught error in V5QueryHandler: XQE-GEN-0005 Found an internal error.
Caused by: com.cognos.xqe.ast.sql.parser.ParseException: Encountered "(" at line 13, column 27.
Was expecting one of:
    "/*+" ...
    ")" ...
    "," ...
    "absolute" ...
    "absent" ...
    "action" ...
    "after" ...
    "aggregate" ...
    "anytype" ...
    "as" ...
    "asc" ...
    "bit" ...
    "cascade" ...

This looks to me like bad SQL is being formed somewhere. Are there any macros in the report or in the query items in the package? I'm wondering if something like a parameter map or a prompt macro is returning a value that is not legal? When running reports I'm guessing you do it as a logged-in user, whereas Transformer doesn't? Could there be something being set based on the user?

MF.

[raj_aries81]

Hi,

Thanks for confirming. Looking again at the errors:

<event component="XQE" group="Exception" level="ERROR" thread="100" timestamp="2019-09-24 17:20:54.586" contextId="238" requestId="18" sessionId="4116_d857a524-de9b-11e9-8569-ffdce18fda56"><![CDATA[Caught error in V5QueryHandler: XQE-GEN-0005 Found an internal error.
Caused by: com.cognos.xqe.ast.sql.parser.ParseException: Encountered "(" at line 13, column 27.
Was expecting one of:
    "/*+" ...
    ")" ...
    "," ...
    "absolute" ...
    "absent" ...
    "action" ...
    "after" ...
    "aggregate" ...
    "anytype" ...
    "as" ...
    "asc" ...
    "bit" ...
    "cascade" ...

This looks to me like bad SQL is being formed somewhere. Are there any macros in the report or in the query items in the package? I'm wondering if something like a parameter map or a prompt macro is returning a value that is not legal? When running reports I'm guessing you do it as a logged-in user, whereas Transformer doesn't? Could there be something being set based on the user?

MF.

Thanks MF,

I have been away for the last few days, sorry I havent had a chance to reply back. There is no parameter map.However, there are data security filters setup in FM.
Also, one of the cognos consultant here mentioned that I wont be able to use Cognos Report using DQM package as a source in Transformer but I should be fine using FM package as source (and really works !!).
I havent seen any mention of this in IBM documentation.

Thanks
Raj