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)
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
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
- Open a command prompt (on Essbase Server)
- Navigate to the app folder (the application folder) where you want to find out the member usage. (e.g. C:OracleMiddlewareuser_projectsepmsystem1EssbaseServeressbaseserver1appSample)
- 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
- Open a command terminal (on Essbase Server)
- 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)
- Run the command given below (make changes accordingly)find . -iname ‘*’ |xargs grep ‘membername’ find . -iname ‘*’ |xargs grep ‘membername’ -sl (just to list the filename)
