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: 1
      For Jakarta Tomcat – Tomcat console window
      For IBM WebSphere –Websphere_ServerStdout.log
      For BEA WebLogic – WebLogic console window
  • Edit WebAnalysis.properties 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.  Smart View 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:Windowssystem32

ADM_ESSBASE_NATIVE.log
ADM_REDIRECTOR.log
Hope it helps sometime 🙂

About Celvin Kattookaran

I’m an EPM Consultant, my primary focus is on Hyperion Planning and Essbase. Some of you from Hyperion Support team might recognize me or have seen my support articles, I was with the WebAnalysis Support Team. I'm an Independent Consultant with “Intekgrate Corporation” based out of Aurora office. I’m from God’s Own Country (Kerala, India), lived in all southern states of India, Istanbul and Johannesburg (and of course United States). I’m core gamer :) and an avid reader. I was awarded Oracle ACE Director for my contributions towards EPM community.

Leave a Reply

This site uses Akismet to reduce spam. Learn how your comment data is processed.

0 thoughts on “How to extract MDX queries fired by Reporting tools