Hyperion Essbase data Extraction Transformation and Loading

[Former Member]

Objective:
The extraction of Hyperion Essbase cube data followed by the adequate transformations, which helps in loading to SAP BW data targets, and there by achieving a method which can be applied while migrating Hyperion Applications.

Approach:
This is similar to Open Hub Service of SAP BI where the OLAP data is exported to flat structures. Similar to SAP, Hyperion also has developed many connectors to take data out of the OLAP to flat structures so that it can be distributed. We have used a similar connector provided by Hyperion to export data out of Essbase to database tables. The exported data is extracted to BI by using DBConnect.

Challenges:
The major concern is the difference of storage of data in Essbase and SAP BW Systems. The date and time formats have to be taken care by efficient transformation programs at wherever required.

There can be many ways to achieves this; like SAP has provided the BI XMLA Connector which can be used for the integration of Hyperion Applications.

Reference:   BI XMLA Connector

Extraction Steps:

Step 1: Extract data from Essbase data targets to flat database tables



We have defined Custom Functions on Essbase Applications, which export the data to SQL database. This function is capable of extracting data from any Essbase cube to any Relational database

Reference:   Custom Functions on Essbase Applications

Step 2: Transform the fields as required for SAP BW formats



The formatting of date data type is achieved by SQL Stored Procedures.

Step 3: Upload the data from database table to SAP BW data target.



Reference:   Installing DB Connect with MSSQL Server Database

Sample Custom Defined Function: JEXPORTTO

Function for Export data from Essbase Cube to SQL Server with the help of Calculation Scripts.

/** * Login */ login admin password on localhost; /** * Register function Export */ CREATE OR REPLACE FUNCTION '@JExportTo' AS 'com.hyperion.essbase.cdf.export.ExportTo.ExportToTarget(String,String,String,String,String[],double[])' SPEC '@JExportTo(targetType,targetName,delimiter,tableName,strArgs,dblArgs)' COMMENT 'Exports the data to a text file or relational database (using either JDBC or JDBC-ODBC bridge).'; /** * Logout and Exit */ logout;

Sample Calculation Scripts Used to Export data from Essbase Cube:

//ESS_LOCALE English_UnitedStates.Latin1@Binary /* Turn intelligent calc off */ set updatecalc off; /* Fix on Actual so that only one scenario is evaluated, otherwise a record for each scenario will be written and duplicated in the export */ Fix ( @LEVMBRS(Time_Periods,0) OR @LEVMBRS(Geo,0) or @LEVMBRS(Product,0) or @LEVMBRS(Customer,0) or @LEVMBRS(Scenario,0) or @LEVMBRS(OrderStatus,0) ) Sales ( /* * Export to a DSN * arg 1: specify "dsn" to export to a realtional DB * arg 2: DSN name. This DSN name must be used to close the connection after the calculation completes * arg 3: leave blank when exporting to a RDB * arg 4: table name * arg 5: an array of member names * arg 6: an array of data */ IF (SALES > 0 OR UNITS > 0) @JExportTo("dsn","EDMTEST","","SAPEDM",@List(@NAME(@Currmbr(Time_Periods)),@NAME(@Currmbr(Geo)),@NAME(@Currmbr(PRODUCT)),@NAME(@Currmbr(Customer)),@NAME(@Currmbr(Scenario)),@NAME(@Currmbr(Orderstatus))),@LIST(Sales,Units)) ; ENDIF; ) endfix; /* * * Clean up * */ /*Close the connection to DSN*/ RUNJAVA com.hyperion.essbase.cdf.export.CloseTarget "dsn" "EDMTEST" ;

Reference:   Essbase Technical Documentation

Sample MSSQL Stored Procedure Used for Date Formatting

SET QUOTED_IDENTIFIER ON GO SET ANSI_NULLS ON GO CREATE Procedure Todate as DECLARE tnames_cursor CURSOR FOR SELECT CALMONTH FROM SAPEDM OPEN tnames_cursor DECLARE @calmonth char(5) Declare @mon char(2) Declare @month char(3) Declare @year char(2) Declare @MonYear Char(6) --SET @tablename = 'authors' FETCH NEXT FROM tnames_cursor INTO @calmonth WHILE (@@FETCH_STATUS <> -1) BEGIN IF (@@FETCH_STATUS <> -2) BEGIN Set @month = Substring(@calmonth,1,3) Select @Mon = Case @Month when 'Jan' then '01' when 'Feb' then '02' when 'Mar' then '03' when 'Apr' then '04' when 'May' then '05' when 'Jun' then '06' when 'Jul' then '07' when 'Aug' then '08' when 'Sep' then '09' when 'Oct' then '10' when 'Nov' then '11' When 'Dec' then '12' end Select @year = Substring(@calmonth,4,2) Select @MonYear = '20' + @year + @Mon --Print @calmonth Update SAPEDM set CALMONTH = @MonYear where CALMONTH = @calmonth END FETCH NEXT FROM tnames_cursor INTO @calmonth END CLOSE tnames_cursor DEALLOCATE tnames_cursor GO SET QUOTED_IDENTIFIER OFF GO SET ANSI_NULLS ON GO