Oracle Essbase Starter Series (ii)

This article begins with an example of a family financial database of three families, which describes the function and development of Essbase. To illustrate the management and development process of the EPM application, it is possible to use EPMA instead of EAS, bypassing some detours.

Create an application

First, log in to workspace, and in the top left menu, choose Navigation > Administration > Application Gallery.

The application library is part of the EPMA that allows you to manage EPM and Essbase applications. After opening the application gallery, choose File > New > Applications in the Workspace menu, and the Application Creation Wizard will appear.

In the first step of the Application Creation Wizard, select the Application type Essbase (BSO), select Unicode to support Chinese, and then name the application and the database.

Create an application named "Homefinancial", the annual household financial data as a database, the 2013 database named y2013.

The second step is to select the dimension types contained in the database, create 4 dimensions according to the following table, and the name of the 1-generation member, and in Essbase, time and account are two more special dimensions. Time dimensions can use temporal-related functions and dynamic time series, and account can use time-balance and expense report properties. Only one time and one account dimension can be in each database.

The next step is to define metadata, including dimension structures, calculation methods, member properties, and so on. After deployment, the metadata defined in EPMA becomes an outline of Essbase. The interface of this step is divided into three parts, the left list is the tree dimension structure, the right side of the dimension member's properties, the following shows the system message.

In the tree dimension Structure section, the top-level node represents the database, and the next level is the 1-generation member of each dimension, representing the entire dimension, and the member of the dimension under the 1-generation member. Select the nodes in the tree structure, the right side will show the relevant properties, modify the properties and don't forget to click on the top of the save key.

In the tree structure, select the 1-generation members of each dimension, as the following table modifies the dimension's dimension Storage type and dimension Sort order two properties. Dimension Storage type is defined as whether the storage type is sparse (Sparse) or dense (dense); Dimension sort order defines the order in which dimensions are sorted in the outline after deployment. These two properties determine the physical structure and calculation order of the database, which is discussed later.

Then, in the tree structure, right-click the dimension members Display menu, where you can delete, rename, add members, sort, and so on.

Drag a member of the mouse to change its position in the dimension.

Add members to the dimension model structure to create a dimension structure such as.

G1. Account    G2. Expenditure        G3-commodity        G3-traffic        G3-Electrical        G3-education        G3-food G3-        public operating expenses            G4. Electrical unit Price            G4. Electricity consumption            G4. Water price            G4. Water usage        G3. Medical    G2. Income        G3. Wages        G3. Other income    G2. Cash Flow        G3. Cash G3.        net income        G3. C20/>g3. End-of-term cash G1. Home    G2. Zhang Daming    G2. Wang Tila    G2. Zhang Xiaoming G1. Scenario    G2. Budget    G2. Actual    G2. Difference G1. Year    G2. Q1        G3. January            G4. January 1            G4. January 2

After you finish editing the data model, click "Validate" and the message box below will display the verification information. If no error or warning message appears, click "Finish" to finish editing the application.

You can then see an application icon named "Homefinancial" in the application library.

At this point, the use of the EPMA application library and the dimension library has been described. EPMA is a module introduced from the EPM version 9.3, which includes applications libraries, vecuronium, data synchronization, library control jobs, and more to enable centralized management of EPM product applications. Applications created through EPMA are called EPMA applications, whereas applications created using traditional methods are called standard applications. EPMA provides tools for converting standard applications to EPMA applications, which can be found in the workspace menu, and the conversion process is irreversible. EPMA the tools used during the deployment of the application, such as the.

load Meta Data

We have now created an Essbase application through the EPMA application library. Unlike the EAS console, which creates a standard application directly in Essbase server, the EPMA application is simply metadata that is stored in EPMA and is not deployed to Essbase server. Here are some important tools for EPMA by refining and deploying this application.

Looking back at the homefinancial data model created in the previous section, the time dimension does not contain all the date items, it will be a lot of work to manually add 365 days a year, and we need to import the dimension members in batches from the relational database. In real-world applications, importing dimensions and data from an external system is often the case, such as importing a ledger account from an accounting system. To this end, EPMA provides an interface table that can be easily imported with ETL tools.

The interface table is a set of data tables that are automatically created by EPMA in a relational database, supporting mainstream databases such as Oracle database, Microsoft SQL Server, and IBM DB2. First, connect to the Oracle database that is used to install EPM, and execute the following statement to create a new account to store the interface table.

CREATE USER  by  password; GRANT  to EPMA_INTFC; GRANT  to EPMA_INTFC; GRANT  to EPMA_INTFC;

Then choose Navigation > Administration > Configure interface Data Sources from the workspace menu. After you open the interface data source, in the menu, choose File > New > Interface Data source, and in the Interface Data Source Creation dialog box, enter the interface table account connection information.

In the next step, name the interface data source and select "Create Tables" and click "Finish". The EPMA will establish an interface table in the given database account.

Connected to the database, you will find a number of data tables created under the EPMA_INTFC account. Looking closely at the naming conventions of these tables, the tables beginning with HS are divided into 4 classes, which are required to write dimension information and then imported into EPMA Vicoury, whereas a data table starting with IM is a system table. The data table uses the following table, specific table structure, and the definition of each field, referring to the EPMA documentation.

Table name	Use
Hs_Dimension_member	Dimension members and member properties
Hs_Dimension_hierarchy	Dimension Parent-Child hierarchy
Hs_Dimension_propertyarray	Dimension member properties
Hs_Dimension_property	Dimension attributes
Im_dimension	Register the interface table, the self-built interface table must be registered in this table
Im_load_info	To filter the imported data batches with the load ID
Im_dimension_association	Defining association relationships between dimension tables

in the interface table, not every table and every field is required, such as available Hs_ Dimension_hierarchy and Hs_ Dimension_propertyarray replaces Hs_ Dimension_member defines dimension members and attributes. For most properties that use default values, the corresponding field can be a null value. So, in a homefinancial application, to import a Time dimension member, you simply write the member to the Hs_time_hierarchy table.

Execute the following SQL statement in the database to write the dimension member data to the interface table Hs_time_hierarchy.

Declarev_date Date:=To_date ('130101','RRMMDD'); V_order Number:= 0; V_month_strvarchar(3 Char); V_date_strvarchar(6 Char);begin  Delete  fromEPMA_INTFC.    Hs_time_hierarchy;  forIndxinch 1..4Loop V_order:=V_order+ 1; Insert  intoEPMA_INTFC. Hs_time_hierarchy (Parent,child,isprimary,sortorder)Values(' Year','Q' ||To_char (indx),1, V_order); EndLoop;  forIndxinch 1.. ALoop V_order:=V_order+ 1; Insert  intoEPMA_INTFC. Hs_time_hierarchy (Parent,child,isprimary,sortorder)Values('Q' ||To_char (Ceil (indx/ 3)), To_char (indx)|| 'Month',1, V_order); EndLoop;  forIndxinch 1..365Loop V_order:=V_order+ 1; V_MONTH_STR:= LTrim(To_char (V_date,'MM'),'0')|| 'Month'; V_DATE_STR:=V_month_str|| LTrim(To_char (V_date,'DD'),'0')|| 'Day'; Insert  intoEPMA_INTFC. Hs_time_hierarchy (Parent,child,isprimary,sortorder)Values(V_month_str, V_date_str,1, V_order); V_date:=V_date+ 1; EndLoop; Commit;End;

After PL/SQL is executed, the Hs_time_hierarchy table should have the following data.

When the interface table data is ready, open EPMA vecuronium. There are two ways to open, one is to double-click the application icon in the application library, and the other is to select the application in the menu file > Edit Application by Workspace the menu "navigation" > "Manage" > "Vecuronium". Vecuronium is divided from left to right into three columns, the leftmost is the shared dimension library, the middle is the application dimension structure, and the right side is the attribute of the selected member.

The Vecuronium interface is similar to the dimension editing interface in the Application Creation Wizard, except that the shared dimension library is the only difference. Shared dimensions, as the name implies, can be shared by multiple applications, and some common dimensions in the enterprise, such as organizational structure, ledger accounts, and so on, are used by multiple applications, and using shared dimensions first avoids the duplication of effort to create dimensions, and secondly avoids data inconsistency issues between applications. This concept is similar to the master Data management system, in fact Hyperion acquired a master data relationship Management (DRM) in 2005 through acquisition of Razza, is now part of Oracle's Master Data management solution. EPMA also supports the import of dimensions from DRM.

The local dimension, which corresponds to the share, is a dimension that belongs to only one application. The local dimension of the application can be copied to the shared dimension library, or the shared dimension can be copied to the application. Shared dimension libraries are one of the main advantages of EPMA applications relative to standard applications.

In the Gallery, click Workspace Menu "File" > "Import" > "Create Profile", select Import from the local interface library to the Homefinancial application, and give the configuration file a name. From the Import type option, the import from flat files and DRM is supported in addition to the interface table. EPMA also provides a desktop client tool for generating flat files, file Generator, which can be found in the Start menu after the EPM is installed.

In the dimension mapping configuration, select Import only Time dimension, process type Select Merge as Primary member, rearrange type select "Merge to Bottom", and select rearrange existing member selection boxes.

After saving the import profile, select File > Import > Import dimension in the menu, and check the import profile you just created. Here, if the load ID is defined in the Im_load_info interface table, you can select several interface load IDs to filter the imported dimension members, and not select the load ID to import all the members in the interface table.

After clicking the "Import" button, EPMA will create a job to perform import work in the background, and the interface will show a dialog box prompting the job to be submitted.

A job link is given on the dialog box, click to open the job console to display the job execution status. The job console is one of the EPMA tools that you can use to view the history of EPMA various jobs. The job console can be opened through the Workspace menu Navigation > Administration > Job Console.

After the job executes successfully, refresh the application structure in the dimension library to see if the time dimension members are imported correctly. By the way, in addition to the EPMA interface table, Oracle Data Integrator, Essbase Studio, and Essbase SQL interface can all directly load to Essbase.

deploying applications

Before deploying the Homefinancial application, open the Essbase Administration Services Console (EAS console) and confirm that Essbase Sever is enabled for Unicode. The EAS console can be opened via the link Http://<epm_server>:9000/easconsole or installed from the downloaded EPM Client compression package. Open EAS Console first display login information, enter the EAS address and account after landing.

After landing in the enterprise view on the left, right click on "Essbase Server" and select "Add Essbase Server" in the menu. In the dialog box, add the EAS local Essbase server join.

In Enterprise view, expand the Essbase server node that you added, and you can see that there are several sample applications that are included with your installation. Each application contains one or more multidimensional databases, which are outlines, rules files, partitions, report scripts, and so on. The EAS console is the most versatile and classic Essbase management and development tool for application management, outline editing, dimension and data import, and computational scripting. You can also use EAS console to edit a database created with EPMA after you deploy it to Essbase server. However, the EAS console modifies the database in the Essbase server directly, and all modifications are not synchronized to EPMA, so the EAS console is for editing standard applications and it is best not to modify the EPMA application.

Right-click on Essbase Server, choose Edit > Properties in the menu, and in the security properties, make sure the "Permissions required to create Unicode-mode applications" is selected, then click "Apply".

Back in EPMA, open the application library. Right-click the homefinancial application icon, select Validate in the menu, and then view the validation job execution status in the job console. If validation is not a problem, right-click the application and select Deploy. The deployment process can also be monitored by the job console.

After the deployment is complete, you can see in the EAS console that homefinancial is joined to Essbase server.

Oracle Essbase Starter Series (ii)