Hype­rion Finan­cial Manage­ment and Azure Databricks

Oracle’s Hype­rion Finan­cial Manage­ment (HFM) is a cor­ner­stone for glo­bal finan­cial report­ing. It enables con­so­li­da­tion bet­ween enti­ties of the com­pany while main­tai­ning a regu­la­tory fil­ing to Gene­ral Led­ger (GL) audit trail [1]. Within insu­rance agen­cies, these state­ments are vital for reserve buil­ding, price struc­tu­ring, and stra­te­gic decision-making.

Pre­viously, extra­c­ting this data for report­ing was a manual, repe­ti­tive task prone to human error. Synvert’s goal was to auto­mate this trans­fer from HFM to Azure Dat­ab­ricks, trans­forming a bot­t­len­eck into a stream­li­ned, sca­lable data pipeline.

Under­stan­ding the HFM Data Landscape

HFM data is mul­ti­di­men­sio­nal. For exam­ple, a sin­gle entry might repre­sent a pos­ting on an account, for a legal entity at the start of a period. This exam­ple shows only a few of the available dimen­si­ons within HFM. The dimen­si­ons – such as enti­ties and accounts – are orga­ni­zed into hierarchies:

Unlike some sys­tems, HFM allows ent­ries at both the leaf level and at parent nodes (e.g., group-level adjus­t­ments). Cap­tu­ring this nuance is cri­ti­cal for accu­rate reporting.

The pro­blems of the past and future benefits

There are seve­ral use cases for export­ing HFM data into Azure Dat­ab­ricks, like auto­ma­tion of cal­cu­la­ti­ons and report­ing. This is espe­ci­ally rele­vant for a stan­dar­diza­tion of the reports. E.g by migra­ting from Micro­soft Office Excel exports to a desi­gna­ted report­ing soft­ware like Micro­soft PowerBi for com­pre­hen­sive visua­li­sa­ti­ons and analyses.

Pre­viously the extra­c­tions from Hype­rion Finan­cial Manage­ment were done eit­her with the help of Micro­soft Excel Exten­si­ons or directly in Hype­rion Finan­cial Manage­ment. Export­ing Data from within Hype­rion Finan­cial Manage­ment requi­res the manual sel­ec­tion of the scope for each dimen­sion. This crea­tes a mono­tone repe­ti­tiv task which is prone to human errors. 

Both pro­ce­du­res require users to be setup in Hype­rion Finan­cial Manage­ment. These users need per­mis­si­ons and gover­nance for their access. Within Azure Dat­ab­ricks or Mir­co­soft Power Bi one may rely on a cen­tra­li­zed iden­tity pro­vi­der like Micro­soft Azure Entra ID. This ensurs a con­sis­tent gover­nance of user accounts across sys­tems with in the organization.

The Path to Auto­ma­tion: Mer­linXL and API Integration

HFM does not offer a native «out-of-the-box» con­nec­tor to Azure Dat­ab­ricks. To bridge this gap, we uti­li­zed Mer­linXL by Amosca. [2]. The Mer­linXL con­nec­tor pro­vi­des an API inter­face, where we define the type and scope of the data to be extra­c­ted and recieve the reques­ted data on the Dat­ab­ricks cluster. 

The Mer­linXL con­nec­tor accepts con­fi­gu­ra­ti­ons as pay­loads in the JSON for­mat. In short this enables to con­fi­gure a multi­tude of set­tings, e.g. the out­put type of the reques­ted data and defi­ni­tion of the extra­c­tion scope along each dimen­sion. With this in mind we prepare the pay­load within a note­book in Dat­ab­ricks and per­form the API call. The con­nec­tor returns the data accor­ding to the con­fi­gu­ra­tion of the pay­load. We extract the data from the response object and use pySpark to cast and write the data into a sta­ging table.

Con­fi­gu­ra­tion and Selection

The pay­load must con­tain sel­ec­tion defi­ni­ti­ons for each dimen­sion. There are four types of sel­ec­tions, which can be con­ca­te­n­a­ted for a com­bi­ned sel­ec­tion of values:

Fur­ther opti­ons include the sel­ec­tion of the return for­mat. We use eit­her the CSV or JSON for­mat, depen­ding on the spe­ci­fics of the retur­ned data, to mini­mize data volume and thus net­work traffic.

Exports of addi­tio­nal Information

Along­side the finan­cial state­ments, addi­tio­nal infor­ma­tion can be extra­c­ted from HFM. One exam­ple are cur­rency exch­ange rates, as well as meta­data information.

Cur­rency conversions

In a glo­ba­li­zed world orga­niza­ti­ons ope­rate in many count­ries and pro­cess pay­ments in dif­fe­rent cur­ren­cies, here con­ver­sion rates bet­ween cur­ren­cies are cru­cial for a uni­fied report­ing. Like finan­cial state­ments the con­ver­sion rates can be extra­c­ted from Hype­rion Finan­cial Management.

Get to know the Dimensions

The extra­c­tion of all des­cen­dants of a node yields many indi­vi­dual records. As the hier­ar­chi­cal infor­ma­tion is not part of the finan­cial state­ments, the inter­pre­ta­tion wit­hout it is dif­fi­cult. Addi­tio­nal to the finan­cial infor­ma­ti­ons the extra­c­tion of the list of mem­bers cor­re­spon­ding infor­ma­tion as well as the extra­c­tion of entire hier­chies is per­for­med. This enables inde­pht ana­ly­ses of finan­ces throug­hout the orga­niza­tio­nal strucutures.

Stay with the rele­vant data

Fur­ther­more the infor­ma­tion con­tai­ned within Hype­rion Finan­cial Manage­ment is vast and data of older peri­ods is sta­tic. We estab­lished time win­dows, fixed rela­tive to the cur­rent active period to retrieve rele­vant data. These time win­dows may sel­ect cur­rent finan­cial state­ments, but also for­cas­ted values or assi­gend bug­dets. They’ll auto­ma­ti­cally shift for each quar­ter wit­hout addi­tio­nal input, while main­tai­ning the ability to manu­ally trig­ger the extra­c­tion for a spe­ci­fic date.

Inges­tion into Azure Databricks

In our frame­work we fea­ture a medal­lion based archi­tec­ture as descri­bed by Dat­ab­ricks as best-practice. 

• bronze for basic for­mat validations
• sil­ver for more com­plex vali­da­ti­ons and derivations
• gold for endu­ser report­ing as well as deli­veries to other systems

From the response of the API request we extract the con­tent and use pySpark to cast to the cor­rect data type per column and write to the bronze tables. During the load from the bronze to the sil­ver layer we per­form vali­da­ti­ons, trans­for­ma­ti­ons and deri­va­tions, based on cus­to­mizable buil­ding blocks.

By joi­ning the HFM secu­rity clas­ses with meta­data tables in the sil­ver layer, we dyna­mi­cally derive secu­rity roles. In the gold layer data is agge­ga­ted for report­ing and secu­rity clas­ses map­ped to to actual roles. When com­bi­ned with Dat­ab­ricks Unity Cata­log, allo­wing for Row-Level Secu­rity (RLS). This ensu­res that a user from the French branch only sees French data, even if the under­ly­ing table con­ta­ins the entire glo­bal dataset. 

Con­clu­si­ons

By auto­ma­ting the bridge from HFM to Azure Dat­ab­ricks, we have moved bey­ond manual Excel exports to a modern, secure, and «live» data land­scape. With the finan­cial state­ments, as well as cur­rency con­ver­sion rates and meta­data infor­ma­tion on dimen­si­ons available via Dat­ab­ricks Unity Cata­log, the orga­niza­tion is now equip­ped to build state-of-the-art report­ing appli­ca­ti­ons and make data-dri­ven decis­i­ons with confidence.

Refe­ren­ces

• [1] https://www.oracle.com/performance-management/hyperion-financial-management/
• [2] https://www.merlinxl.com/