Show Usage for a member in Calc Manager



I once received a query where customer asked whether there is an option to find out Member usage in Calc Manager.

So let’s say that member FY10 is used in ‘n’ Calculation Manager Rules, they would like to list out all the rules that has FY10 in it.

I couldn’t find out an way to do that from front end :), so ended up querying the repository

You can get the required information by running one of TWO SQL queries given below.

As you know Calculation Manager rules gets stored under Calculation Manager repository and also under respective Planning repository. (thats the reason why we have two SQL statements 🙂 )

Please use the below given SQL if running from Planning tables

SELECT location_sub_type “Database”, (select ‘SMP_PLN’ from DUAL) “Application”, (select object_name from hsp_object where object_id = hsp_calc_mgr_rules.id) “Rule name”
FROM hsp_calc_mgr_rules WHERE dbms_lob.INSTR(BODY,’FY10′) > 0;


In this table Rule is stored as CLOB in column called BODY.

Since the query is generated from the Planning application repository, I couldn’t find a table which stores Application name.  So I decided to show the application name using DUAL. (SMP_PLN should be replaced with your application name)

Please use the below given SQL if running from Calc manager tables

SELECT LOCATIONSUBTYPE “Database”, LOCATION “Application”, NAME “Rule Name” FROM calcmgrrules WHERE dbms_lob.INSTR(BODY,utl_raw.cast_to_raw(‘FY10’),1,1) > 0;


In this table Rule is stored as BLOB  in column called BODY.

HTH

Update 06/04/2012

I was getting lot of queries when I posted this information. “How can we check this if we are not using Calc Manager?”

One idea that was shared was to use LCM and then load that information to a Relational Database and then query for the members there.

If you don’t want to go with that approach then you can run the below given query 

Windows Essbase servers

  1. Open a command prompt (on Essbase Server)
  2. Navigate to the app folder (the application folder) where you want to find out the member usage. (e.g. C:OracleMiddlewareuser_projectsepmsystem1EssbaseServeressbaseserver1appSample)
  3. Run the command given below (make changes accordingly)FINDSTR /s /i <membername> *.* >  path to a filename.txt (remember not to use the same path)
Unix Essbase servers
  1. Open a command terminal (on Essbase Server)
  2. Navigate to the app folder (the application folder) where you want to find out the member usage. (e.g. /hyperion/Oracle/Middleware/user_projects/epmsystem2/EssbaseServer/essbaseserver1/app/Sample)
  3. Run the command given below (make changes accordingly)find . -iname ‘*’ |xargs grep ‘membername’ find . -iname ‘*’ |xargs grep ‘membername’ -sl (just to list the filename)
Advertisements

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