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, such as the auto­ma­tion of cal­cu­la­ti­ons and report­ing. This is espe­ci­ally rele­vant for stan­dar­di­zing reports, for exam­ple by migra­ting from Micro­soft Excel exports to dedi­ca­ted report­ing soft­ware like Micro­soft Power BI for com­pre­hen­sive visua­liza­ti­ons and analyses.

Pre­viously, 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­to­nous, repe­ti­tive task that is prone to human error.

Both pro­ce­du­res require users to be set up 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 Micro­soft Power BI, one can rely on a cen­tra­li­zed iden­tity pro­vi­der like Micro­soft Entra ID. This ensu­res con­sis­tent gover­nance of user accounts across sys­tems within 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 receive 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 JSON for­mat. In short, this enables us to con­fi­gure a multi­tude of set­tings, e.g. the out­put type of the reques­ted data and the 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. Examp­les include cur­rency exch­ange rates and metadata.

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; the­r­e­fore, con­ver­sion rates bet­ween cur­ren­cies are cru­cial for uni­fied report­ing. Like finan­cial state­ments, con­ver­sion rates can be extra­c­ted from Hype­rion Finan­cial Management.

Get to know the Dimensions

Extra­c­ting 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, inter­pre­ting them wit­hout it is dif­fi­cult. In addi­tion to the finan­cial infor­ma­tion, we the­r­e­fore extract the list of mem­bers with their cor­re­spon­ding attri­bu­tes as well as entire hier­ar­chies. This enables in-depth ana­ly­ses of finan­ces throug­hout the orga­niza­tio­nal structures.

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 from 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 fore­cas­ted values or assi­gned bud­gets. They auto­ma­ti­cally shift 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

Our frame­work fea­tures a medal­lion-based archi­tec­ture, recom­men­ded 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 end-user report­ing as well as deli­veries to other systems

From the response of the API request, we extract the con­tent, use PySpark to cast each column to the cor­rect data type and write it 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 aggre­ga­ted for report­ing and secu­rity clas­ses are map­ped to actual roles. Com­bi­ned with Dat­ab­ricks Unity Cata­log, this enables 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/