As we’ve seen in the last post, we have a list of users and their filter names as they exist in Planning. Continuing on, let’s take a look at what the documentation tells us about filters.
It’s not evident at first glance, the importance of the section, “as FILTER-NAME”. If you dig one step deeper, we find that the filter-name object needs to be follow the syntax, “application_name.database_name.filter_name”.
The part above, is what helps us in copying filters from one application to another. The pseudo-code MaxL required to do this might look like:
|
1 |
create or replace filter TargetApplication.TargetDatabase.FilterName as PlanningApplication.PlanningDatabase.FilterName; |
The next step is to extract the list above to a file by calling SQLPlus (yes, this one’s on an Oracle database, not SQL Server).
|
1 2 |
:: Connect to the Planning repository to get filters CMD /C sqlplus -S planapp/password@UNLKORA:1531/UNLK @"%HOMEDIR%\Definitions\Ext_Filters.sql" > "%OUTPUTDIR%\FilterList.txt" |
The contents of “Ext_Filters.sql” looks like:
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 |
set heading off; -- turn off headings set feedback off; -- turns off items like “no of records” from the file set pages 0; -- suppresses page breaks, titles etc. select chr(39)|| n.object_name || chr(39) || ',''f' || n.object_name || chr(39) filterName from hsp_users u, hsp_object n where u.user_id = n.object_id and u.role in (1,2,4) order by 1; exit; |
The code above extracts the filters into a usable format.
Now, let’s set up some MaxL scripts to use the output above in an efficient manner. I chose to go with 2 individual scripts, one for copying filters and one for granting the user with the filter. The former might look like (ignore the fact that I am using encryption, this will work either way):
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 |
/* Purpose:Copy filters from Planning to Essbase. Variables: $1=Essbase Server $2=Reporting Essbase Application Name $3=Reporting Essbase DB Name $4=Log Directory */ /* spool on to "$4\\Extract_Filter.log"; */ set timestamp on; login $key 4511023520776288950029499341801077203000 $key 7604639300400586272058221904603835319641613487411156721167402358850821 on $1; create or replace filter $2.$3.$5 as ScPLN.FINPLN.$5; spool off; Logout; Exit; |
And the latter might look like:
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 |
/* Purpose:Grant filter to user. Variables: $1=Essbase Server $2=Reporting Essbase Application Name $3=Reporting Essbase DB Name $4=Log Directory $5=Filter Name $6=User Name */ /* spool on to "$4\\Grant_Filter.log"; */ set timestamp on; login $key 4511023520776288950029499341801077203000 $key 7604639300400586272058221904603835319641613487411156721167402358850821 on $1; grant filter $2.$3.$6 to $5; spool off; Logout; Exit; |
Finally, all we need is a little batch script to put all the pieces together.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 |
:: ******************************************************* :: ** Name: Copy_Filters.bat ** :: ** Purpose: Copies filters from FINPLN to RPTG ** :: ** Created By: Unlocked Cube Associates ** :: ** Created On: ** :: ******************************************************* :: Set up variables SET HOMEDIR=D:\Admin SET LOGDIR=%HOMEDIR%\Logs SET ERRORDIR=%HOMEDIR%\Errors SET MAXLDIR=%HOMEDIR%\MAXL SET MAXL_UTILITY=D:\Oracle\Middleware\user_projects\epmsystem1\EssbaseServer\essbaseserver1\bin SET OUTPUTDIR=%HOMEDIR%\Export\Filters SET ESS_SERVER=UNLKESS :: Start Filter extract process ECHO ******************************************************* > %LOGDIR%\RPTG_Filter.log ECHO Filter extract process started at %TIME% >> %LOGDIR%\RPTG_Filter.log ECHO ******************************************************* >> %LOGDIR%\RPTG_Filter.log ECHO. >> %LOGDIR%\RPTG_Filter.log :: Connect to the Planning repository to get filters CMD /C sqlplus -S planapp/password@UNLKORA:1531/UNLK @"%HOMEDIR%\Definitions\Ext_Filters.sql" > "%OUTPUTDIR%\FilterList.txt" :: Parse through each item on the list, create filters and assign them to the right users FOR /f "tokens=1,2 delims=," %%a in (%OUTPUTDIR%\FilterList.txt) DO ( CALL %MAXL_UTILITY%\startMaxL.bat -D %MAXLDIR%\Import_Filter.maxls 1248210469,1513054427 %ESS_SERVER% RPTG RPTG %LOGDIR% %%b >> %LOGDIR%\RPTG_Filter.log CALL %MAXL_UTILITY%\startMaxL.bat -D %MAXLDIR%\Grant_Filter.maxls 1248210469,1513054427 %ESS_SERVER% RPTG RPTG %LOGDIR% %%a %%b >> %LOGDIR%\RPTG_Filter.log ) :: Filter extract process ends ECHO. >> %LOGDIR%\RPTG_Filter.log ECHO ******************************************************* >> %LOGDIR%\RPTG_Filter.log ECHO Filter extract process ended at %TIME% >> %LOGDIR%\RPTG_Filter.log ECHO ******************************************************* >> %LOGDIR%\RPTG_Filter.log |
The “FOR” loop above will go through each item on the “FilterList.txt” file and process them individually. And that’s it, all I had to do was run the script and my filters were copied over. Though this does provide a quick way to copy filters from 1 app to another, you do still lose the controls you might get if these users were in groups. As I mentioned earlier, this is just a little POC.
Merci pour le partage.