Creating an Essbase Report Script

Creating a Essbase Report script is one of the ways used to export data out from the Essbase Database. Other ways being

1. Using a Calculation Script (Starting version 9.3.1)
2. Using the database export option and
3. Using a MDX (Multi Dimensional Expressions) query.

The report scripts are the simplest of all, to create an export file (either a .txt or .rep o/p file).

While creating the Report Script, first, we define some properties for the output file, like

1. Output File Properties
TABDELIMIT – to specify TAB as a delimiter for the output file
ROWREPEAT – To repeat the rows (just like we would use it in reports)
NOINDENTGEN – To indent or avoid indentation based on Generation
Suppression Options :
SUPEMPTYROWS, SUPMISSINGROWS, SUPCOMMAS (for thousand separators), SUPHEADING (if you don’t want to print the headings in the report output file

2. Page, Row and Column Dimension Definitions.
Having been specified the output file properties, the next step is to specify the ROW, COLUMN and PAGE dimension definitions.
Remember, the report script will process the output for the same member combinations that have been specified in this section.
An example of the ROW, COLUMN and PAGE listings is as follows (For ease, we have used the Sample:Basic DB)

<PAGE(“Measures”)
“Sales”
<COLUMN(“Scenario”, “Year”)
“Scenario”
“Jan” “Feb” “Mar” “Apr”
<ROW(“Market”, “Product”)
“New York”
“Product” “100” “100-10” “200-10″”300-10”
!

The output text file will look as follows:


The report script output can be formatted, arranged, sorted and modified to match the output requirements.

Lets see a simple scenario, where, we want to Reverse the sign of the output.

For achieving the sign reversal, we can add a column to the end of the Report Output file, like in the example below:

<COLUMN (“Scenario”)
“Actual”
{CALCULATE COLUMN “Inverse” = 5 * -1.}
{ORDER 0 1 2 3 4 5 6}

The output file has a newly added column (specified as column 6 in the ORDER command), as follows:

The most commonly used Report Script Commands can be found in the Administration Services Online Help, Here.

Advertisements

Essbase Automation using windows Batch files.

Lets discuss a scenario, where:
At every interval, we have to Export the data from the source Essbase database, and load it to some target Essbase Database. The entire task of Export and Import is to be automated so that there are minimal administration tasks.
The data flow is as follows:
1. Create the Report Scripts, to export Data from source Essbase Database.
2. Create the Rules files to map the source data with Target Essbase Database.
3. Load the Data file to the Target database.
The requirement is to create automation, where the user (or Administrator) will run a single file, which will accomplish above tasks.

Solution: Depending upon the level of automation, there are multiple options to achieve this automation. But in most cases, a combination of MaxL scripts and Windows Batch files should achieve this.

Steps for automating the above tasks,
1. Create a Report Script to Export Data from the source DB.

2. Create a MaxL script to Execute the Report Script, and load the data file generated by this export to the Target Database.

Sample MaxL

3. Create a Windows Batch File, which will invoke the MaxL She’ll, and execute the MaxL commands to achieve the required result. If the users are going to use a client machine for executing this batch file, then make sure that the Essbase Client (with Essmsk.exe) is installed into their machine. We will then be connecting to a Server machine, whose IP address or machine name will be specified in the batch file.
A sample batch file can be seen as follows:

The last line in the batch file is to delete the Essbase data export file, after the data is loaded into the target database.
Additionally, if the ID and Password of the Admin user is to be hidden from the user executing the batch, then we can also convert the Batch file into an executable file (.exe) using the freeware utility Batch to Exe Converter which can be downloaded from Here.

Thus a simple automation is set for the periodic tasks, which need to be executed without any administrative supervision.

Professional Services

Professional Services

In today’s dynamic world the executives and management in organizations are tested every moment by external factors like slashed prices, competition, innovation etc. which in turn leads to changes in strategies and policies. We help organizations extract valuable information from various

data sources. Our main area of focus is implementing business intelligence (BI) and enterprise performance management (EPM) solutions to enhance the decision making processes across the organizations. With a proven methodology Stractive has helped organizations across the world to solve th

eir information delivery problems help them evolve as market leaders.

iNITIATION, EVALUATION, REALIZ

ATION, MONITORING (i-ERM) methodology is broken into four high-level phases. The phases are designed to be run in sequence, as the outputs from one phase are required inputs for the next phase. Each phase focuses on systematic and proven approach to undertake BI and EPM implementations.
Implementation Phases
iNITIATION
This phase is a first step towards building enterprise solution to improve business performance. As a part of initiation process it enables Stractive to understand and define customer’s business needs at high level. Various pre-execution tasks are carried out with the help of customer’s participation to keep check on expectation from proposed business solution(s).
EVALUATION
The first activity in any project is to evaluate the existing environment and understand the design goals of the proposed solution within the customer enterprise. This phase is also focused on defining and validating the project objectives and key success criteria. It also includes the development of a comprehensive project plan that includes all major project milestones, identification of project team and project responsibilities.
REALIZATION
This phase examines three key areas: people, process and technology. The project requirements and knowledge of the organization are used to determine the required architecture. A solution design specification is developed that includes the physical, logical and security design for the proposed architecture, integration and monitoring requirements.

It also includes all key activities involved in moving into full production deployment. It involves building the environment, installation of core software, initial conference room pilot, and deployment of monitoring policies, reporting and alerting.

MONITORING
This phase of the project should be repeated regularly to ensure optimization and alignment with best practices. Along with ensuring undisrupted business (by maintenance program), it involves a review of new technical, business and functional requirements to determine required system changes.

It also includes all key activities involved in moving into full production deployment. It involves building the environment, installation of core software, initial conference room pilot, and deployment of monitoring policies, reporting and alerting.

i-ERM Benefits
This phase of the project should be repeated regularly to ensure optimization and alignment with best practices. Along with ensuring undisrupted business (by maintenance program), it involves a review of new technical, business and functional requirements to determine required system changes.

  • On time, in budget project delivery
  • A high degree of project visibility and documentation
  • Manage risk and change request with shorter iterations
  • Project Stakeholder’s involvement and governance to avoid unnecessary surprises
  • Scalable System architecture to accommodate future business requirements
  • Education programs for users adoption and their increased productivity.
%d bloggers like this: