How to extract MDX queries fired by Reporting tools

How to extract MDX queries fired by Reporting tools

I was doing a reporting project and OBIEE team had to generate an MDX query for a complex report.

It was hard in FR to get the logic correct, so I was looking for an option how to capture the MDX script that is fired by Hyperion Financial Reporting to Essbase.

I know some of you may have heard ADM…..and wondered why we have this thing with Hyperion. (I’ve done that in the beginning :))

To communicate with Essbase most of the reporting tools make use of the ADM (Analytic Data Module) drivers. These drivers are located in the Hyperion_Home%/common/ADM/lib folder.

Follow the below given steps to extract the MDX queries

Enable ADM trace for different components

1.  WebAnalysis

Setting up Environment variable
  • Temporarily set up two Environment Variables, one to set the trace messagelevel and one to enable output to the console window:
    • Variable: ADM_TRACE_LEVEL

      Value: 0

      Note, the message levels are :8 (only fatal messages)4 (all error messages)1 (debug messages)0 (verbose debug messages)

    • Variable: ADM_CONSOLE

      Value: 1For Jakarta Tomcat – Tomcat console window

      For IBM WebSphere –Websphere_ServerStdout.log

      For BEA WebLogic – WebLogic console window

  • Edit file and add “LogQueries=true”.
  • If you are using in v11 code line then you may need to launch the cmc console from Workspace by following the steps given below
    • Start Workspace Agent UI from “Start”–>”Oracle EPM System”–>”Workspace”–>”Utilities and Administration”–>”Start Workspace Agent UI”
    • To launch CMC login to workspace and go to Navigate–>Administer–>Configuration Console
2.  SmartView and Excel Addin
  • Use SSAudit option of Essbase to get the information SSAudit Appname Dbname logfilepath
E.g. SSAudit Sample Basic:\loginfo.log

3.  Financial Reporting
  • Add 2 environment variables
    • Variable: ADM_TRACE_LEVEL

      Value: 0

    • Variable: REDIRECTOR_TRACE_LEVELValue: 0
    • Variable: ADM_LOG_TO_FILE

      Value: 1

The log files will be generated in C:\Windows\system32

Hope it helps sometime 🙂