In the previous post I went through a possible solution for producing audit reports through the planning UI with the help from business rules and Groovy. I mainly covered the background to planning audit reports and went through a demo of the solution, in this post I am going to break down how it was all put together.
Let us start with running the audit report business rule which can be accessed through the standard or simplified user interface. I am going to stick again with the simplified interface as it provides the ability to download the audit report once it has been generated.
Once launched this will present the user with runtime prompts to define how the audit report will be generated.
The audit type and date range are built from Smart Lists within the planning application.
As I explained in the previous post I have limited the list of options for demo purposes but the final solution has all the different audit types available.
The above list is built from the following Smart List.
In Calculation Manager a variable has been created as a runtime prompt with the type set to integer and the planning Smart List selected.
This means when the rule is launched the audit type drop down will be displayed as shown above, the selection that is passed into the business rule is the integer value that matches the Smart List ID.
It is the same concept for the date range, there is a planning Smart List.
The reason why the IDs have been created this way is due to the way the SQL will be generated in the Groovy script, beside the “All” option the IDs match the number of days.
Just like with the audit type, a variable has been created to display the Smart List when launching the rule.
The option to select a select a delimiter for the audit report is handled using a Calculation Manager runtime prompt string variable with the default value set.
There is also a hidden variable which passes the name of the planning application into the business rule.
On to the business rule which basically just calls a Groovy script and passes in the variable names and the values from the runtime prompts.
If you want more detail about getting up and running with Groovy then please refer to one of my previous posts which you can read about here or here.
Before I get on to the Groovy script there are a few points to mention.
I have disabled static compile and sandbox in groovycdf.properties in order to relax the static type checking and access to Java packages, no additional Java files are required other than the Groovy jar.
The SQL in the script is based on Oracle but wouldn’t require much updating for it to run against SQL Server.
The script does not contain any hardcoding of database connection information including passwords as they are generated at runtime.
I will break down the script into chunks, the variables would have all been defined at the beginning of the script but I have moved them about to make more sense.
The beginning section of the script generates the connection information to the planning application database, a method is called to return the planning system database connection details from the EPM registry.
A connection is made to the planning system database and a SQL query executed to return the planning application database connection information, the planning application name used in the SQL query was passed in from the business rule, the value had been defined in a Calculation Manager variable.
Once the planning application connection details have been returned a connection is then made to it.
If you are not interested in dynamically generating the database connection details then the above can be simply replaced with something like:
sql=Sql.newInstance("jdbc:oracle:thin:@dbserver:port/servicename", "username", "password","oracle.jdbc.OracleDriver")
The next section manages the variables, which like I said would usually be at the start of the script.
The values selected from the drop downs for the list of audit types and date ranges are passed into the script as strings so they are converted to integers.
A date is generated that is then used to form the names for the exported audit text file and zip.
A temporary directory is defined which will be the location where the audit file will be generated before it is compressed and then moved to the planning inbox/outbox explorer location.
The Groovy script is executed by the Essbase process so the temporary directory is located on the Essbase server.
Next a lookup is made to the EPM Registry to retrieve the planning/inbox explorer location, which if you are not aware, is the same location in the LCM import/export directory.
If I ran the audit business rule and with the following runtime prompt selections:
The variables in the Groovy script would be assigned with the following:
auditList=2
dateRange=30
delimiter=|
auditFile=audit110320181534.txt
zipFileName= audit110320181534.zip
planInbox=\\FILESERVER\epmshare\import_export\
The next section builds a SQL statement to retrieve the records from the planning audit table based on the values selected from the business rule runtime prompts.
There are two SQL statements built, they are practically the same, except one of them returns a count of the number of audit records based on the selected criteria, this is because we don’t want to generate an audit file if there are no records returned.
Based on the same selection from the previous example the SQL generated for the count would be:
SELECT count(*) as numrecords FROM hsp_audit_records WHERE 1=1 AND type = 'Data' AND TRUNC(time_posted) >= TRUNC(sysdate) -30
The query is returning a count of the number of audit records where the audit type is data for the last 30 days.
The SQL is then executed and if the number of records returned equals zero then the business rule will terminate with an error and the error message “No records returned” will be available in the job console.
The SQL is then generated to return the audit records which is based on the same criteria and would produce:
SELECT * FROM hsp_audit_records WHERE 1=1 AND type = 'Data' AND TRUNC(time_posted) >= TRUNC(sysdate) -30 order by time_posted desc
If the above SQL query is run using a tool like SQL developer it would produce the following results:
The Groovy script executes the SQL and the column names and rows returned are split by the delimiter and written to a text file
The file is temporarily created in the directory defined in the “tmpFileDir” variable.
The contents of the file will be similar to the results shown in the earlier query.
The audit text file is then compressed into a zip file.
The zip file is created in the same temporary directory.
Finally, the text file is deleted.
The zip file is moved to the planning inbox/outbox location.
The file is then accessible from the planning simplified interface where it can be downloaded or deleted.
As shown in the last post the solution does also have the ability to archive the records in the audit table and includes a planning form to show the number of records in the audit table and when it was last archived, if you would like to find out more information then feel free to get in touch.
Let us start with running the audit report business rule which can be accessed through the standard or simplified user interface. I am going to stick again with the simplified interface as it provides the ability to download the audit report once it has been generated.
Once launched this will present the user with runtime prompts to define how the audit report will be generated.
The audit type and date range are built from Smart Lists within the planning application.
As I explained in the previous post I have limited the list of options for demo purposes but the final solution has all the different audit types available.
The above list is built from the following Smart List.
In Calculation Manager a variable has been created as a runtime prompt with the type set to integer and the planning Smart List selected.
This means when the rule is launched the audit type drop down will be displayed as shown above, the selection that is passed into the business rule is the integer value that matches the Smart List ID.
It is the same concept for the date range, there is a planning Smart List.
The reason why the IDs have been created this way is due to the way the SQL will be generated in the Groovy script, beside the “All” option the IDs match the number of days.
Just like with the audit type, a variable has been created to display the Smart List when launching the rule.
The option to select a select a delimiter for the audit report is handled using a Calculation Manager runtime prompt string variable with the default value set.
There is also a hidden variable which passes the name of the planning application into the business rule.
On to the business rule which basically just calls a Groovy script and passes in the variable names and the values from the runtime prompts.
If you want more detail about getting up and running with Groovy then please refer to one of my previous posts which you can read about here or here.
Before I get on to the Groovy script there are a few points to mention.
I have disabled static compile and sandbox in groovycdf.properties in order to relax the static type checking and access to Java packages, no additional Java files are required other than the Groovy jar.
The SQL in the script is based on Oracle but wouldn’t require much updating for it to run against SQL Server.
The script does not contain any hardcoding of database connection information including passwords as they are generated at runtime.
I will break down the script into chunks, the variables would have all been defined at the beginning of the script but I have moved them about to make more sense.
The beginning section of the script generates the connection information to the planning application database, a method is called to return the planning system database connection details from the EPM registry.
A connection is made to the planning system database and a SQL query executed to return the planning application database connection information, the planning application name used in the SQL query was passed in from the business rule, the value had been defined in a Calculation Manager variable.
Once the planning application connection details have been returned a connection is then made to it.
If you are not interested in dynamically generating the database connection details then the above can be simply replaced with something like:
sql=Sql.newInstance("jdbc:oracle:thin:@dbserver:port/servicename", "username", "password","oracle.jdbc.OracleDriver")
The next section manages the variables, which like I said would usually be at the start of the script.
The values selected from the drop downs for the list of audit types and date ranges are passed into the script as strings so they are converted to integers.
A date is generated that is then used to form the names for the exported audit text file and zip.
A temporary directory is defined which will be the location where the audit file will be generated before it is compressed and then moved to the planning inbox/outbox explorer location.
The Groovy script is executed by the Essbase process so the temporary directory is located on the Essbase server.
Next a lookup is made to the EPM Registry to retrieve the planning/inbox explorer location, which if you are not aware, is the same location in the LCM import/export directory.
If I ran the audit business rule and with the following runtime prompt selections:
The variables in the Groovy script would be assigned with the following:
auditList=2
dateRange=30
delimiter=|
auditFile=audit110320181534.txt
zipFileName= audit110320181534.zip
planInbox=\\FILESERVER\epmshare\import_export\
The next section builds a SQL statement to retrieve the records from the planning audit table based on the values selected from the business rule runtime prompts.
There are two SQL statements built, they are practically the same, except one of them returns a count of the number of audit records based on the selected criteria, this is because we don’t want to generate an audit file if there are no records returned.
Based on the same selection from the previous example the SQL generated for the count would be:
SELECT count(*) as numrecords FROM hsp_audit_records WHERE 1=1 AND type = 'Data' AND TRUNC(time_posted) >= TRUNC(sysdate) -30
The query is returning a count of the number of audit records where the audit type is data for the last 30 days.
The SQL is then executed and if the number of records returned equals zero then the business rule will terminate with an error and the error message “No records returned” will be available in the job console.
The SQL is then generated to return the audit records which is based on the same criteria and would produce:
SELECT * FROM hsp_audit_records WHERE 1=1 AND type = 'Data' AND TRUNC(time_posted) >= TRUNC(sysdate) -30 order by time_posted desc
If the above SQL query is run using a tool like SQL developer it would produce the following results:
The Groovy script executes the SQL and the column names and rows returned are split by the delimiter and written to a text file
The file is temporarily created in the directory defined in the “tmpFileDir” variable.
The contents of the file will be similar to the results shown in the earlier query.
The audit text file is then compressed into a zip file.
The zip file is created in the same temporary directory.
Finally, the text file is deleted.
The zip file is moved to the planning inbox/outbox location.
The file is then accessible from the planning simplified interface where it can be downloaded or deleted.
No comments:
Post a Comment
Note: only a member of this blog may post a comment.