In part one and part two of this series I addressed two of the three methods I identified that can be used on PBCS to extract data from the command line and scheduled to run without user interaction. In this third and final post of the series, I will explain how to extract data using a Business Rule created on the Calculation Management Module of PBCS.
This option is the most flexible of all three. It allows for more control on what data can be exported; it allows exporting multiple periods on one single file.
It relies on the flexibility of the Calculation Manager Module. Like other EPM components, the Calculation Manager Module usage is very similar as it is on the on-premises version.
The following script can be used as an example to extract data from the PBCS application:
/* Created: Marco Rossodivita – Perficient EPM Practice. */
/* Date: 10/14/16 */
/* Purpose: Export level zero data with a specific format to be use in BICS. */
/* Set Export parameters */
SET DATAEXPORTOPTIONS {
DataExportLevel ALL;
DataExportDynamicCalc ON;
DataExportNonExistingBlocks OFF;
DataExportDecimal 3;
DataExportPrecision 3;
DataExportOverwriteFile ON;
DataExportColHeader “Period”;
DataExportDryRun OFF;
DataExportDimHeader ON;
DataExportRelationalFile OFF;
};
FIX (
/*”Actual”,*/
@RELATIVE(“Scenario”,0),
@LIST(&CurrYear),
@RELATIVE(“Western Alliance Bank Corp.”,0), /* Company: L0 Members */
@RELATIVE(“Western Alliance Bancorp”,0), /* CostCenter: L0 Members */
“Ending Balance”, /* Versions (Measures */
“Average Balance”,
“PL”,
“Interest Inc”,
“Interest Exp”,
“Int Inc Tax Eq Basis”,
“Funds Credit”,
“Funds Charge”,
“FTP Net Interest Income”,
“FTP Net Interest Income Tax Equivalent”,
“Total Accounts”,
“Number of Days (30/360)”,
“Number of Days (Act/360)”,
“Number of Days (Act/Act)”
)
FIX (
@LIST(&CurrMnth,&QTDMnth,&YTDMnth), /* Current Periods: */
@RELATIVE(“Net Income to Common Shares Profitability”,0), /* Accounts: NI */
@RELATIVE(“Balance Sheet”,0), /* Accounts: BS */
@RELATIVE(“Statistical”,0) /* Accounts: ST */
)
DATAEXPORT “File” “,” “/u03/inbox/inbox/ZWabPlanBICSPer.csv” “NULL”;
ENDFIX
ENDFIX
Once the calculation script is validated and deployed, it can be executed from inside PBCS:
To execute this calculation script using the epmautomate tool, the following command can be issued:
epmautomate.bat runbusinessrule zPlan1_Export_for_BICS_Periodic
The data file will be formatted with two lines on the header:
“Company”,”Version”,”CostCenter”,”Scenario”,”Years”,”Account”,”Period”
“Sep”,”Sep (Alt) YTD”,”Sep (Alt) QTD”
“02”,”Ending Balance”,”1000″,”Actual”,”FY16″,”170030″,2200275.100,2200275.100,2200275.100
“02”,”Ending Balance”,”1000″,”Actual”,”FY16″,”170031″,-1091042.770,-1091042.770,-1091042.770
“02”,”Ending Balance”,”1000″,”Actual”,”FY16″,”170040″,1402108.010,1402108.010,1402108.010
“02”,”Ending Balance”,”1000″,”Actual”,”FY16″,”170041″,-796508.840,-796508.840,-796508.840
“02”,”Ending Balance”,”1000″,”Actual”,”FY16″,”170050″,1228800.220,1228800.220,1228800.220
“02”,”Ending Balance”,”1000″,”Actual”,”FY16″,”170051″,-970010.790,-970010.790,-970010.790
“02”,”Ending Balance”,”1000″,”Actual”,”FY16″,”170070″,216609.080,216609.080,216609.080
“02”,”Ending Balance”,”1000″,”Actual”,”FY16″,”170071″,-201849.660,-201849.660,-201849.660
I hope you found these methods of data extraction useful and look forward to your feedback.
