Hyper­ion Fin­an­cial Man­age­ment and Azure Databricks

Oracle’s Hyper­ion Fin­an­cial Man­age­ment (HFM) is a corner­stone for global fin­an­cial report­ing. It enables con­sol­id­a­tion between entit­ies of the com­pany while main­tain­ing a reg­u­lat­ory fil­ing to Gen­eral Ledger (GL) audit trail [1]. Within insur­ance agen­cies, these state­ments are vital for reserve build­ing, price struc­tur­ing, and stra­tegic decision-making.

Pre­vi­ously, extract­ing this data for report­ing was a manual, repet­it­ive task prone to human error. Synvert’s goal was to auto­mate this trans­fer from HFM to Azure Dat­ab­ricks, trans­form­ing a bot­tle­neck into a stream­lined, scal­able data pipeline.

Under­stand­ing the HFM Data Landscape

HFM data is mul­ti­di­men­sional. For example, a single entry might rep­res­ent a post­ing on an account, for a legal entity at the start of a period. This example shows only a few of the avail­able dimen­sions within HFM. The dimen­sions – such as entit­ies and accounts – are organ­ized into hierarchies:

Unlike some sys­tems, HFM allows entries at both the leaf level and at par­ent nodes (e.g., group-level adjust­ments). Cap­tur­ing this nuance is crit­ical for accur­ate reporting.

The prob­lems of the past and future benefits

There are sev­eral use cases for export­ing HFM data into Azure Dat­ab­ricks, like auto­ma­tion of cal­cu­la­tions and report­ing. This is espe­cially rel­ev­ant for a stand­ard­iz­a­tion of the reports. E.g by migrat­ing from Microsoft Office Excel exports to a des­ig­nated report­ing soft­ware like Microsoft PowerBi for com­pre­hens­ive visu­al­isa­tions and analyses.

Pre­vi­ously the extrac­tions from Hyper­ion Fin­an­cial Man­age­ment were done either with the help of Microsoft Excel Exten­sions or dir­ectly in Hyper­ion Fin­an­cial Man­age­ment. Export­ing Data from within Hyper­ion Fin­an­cial Man­age­ment requires the manual selec­tion of the scope for each dimen­sion. This cre­ates a mono­tone repet­itiv task which is prone to human errors. 

Both pro­ced­ures require users to be setup in Hyper­ion Fin­an­cial Man­age­ment. These users need per­mis­sions and gov­ernance for their access. Within Azure Dat­ab­ricks or Mir­cosoft Power Bi one may rely on a cent­ral­ized iden­tity pro­vider like Microsoft Azure Entra ID. This ensurs a con­sist­ent gov­ernance 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 nat­ive “out-of-the-box” con­nector to Azure Dat­ab­ricks. To bridge this gap, we util­ized Mer­linXL by Amo­sca. [2]. The Mer­linXL con­nector provides an API inter­face, where we define the type and scope of the data to be extrac­ted and recieve the reques­ted data on the Dat­ab­ricks cluster. 

The Mer­linXL con­nector accepts con­fig­ur­a­tions as pay­loads in the JSON format. In short this enables to con­fig­ure a mul­ti­tude of set­tings, e.g. the out­put type of the reques­ted data and defin­i­tion of the extrac­tion scope along each dimen­sion. With this in mind we pre­pare the pay­load within a note­book in Dat­ab­ricks and per­form the API call. The con­nector returns the data accord­ing to the con­fig­ur­a­tion of the pay­load. We extract the data from the response object and use pyS­park to cast and write the data into a sta­ging table.

Con­fig­ur­a­tion and Selection

The pay­load must con­tain selec­tion defin­i­tions for each dimen­sion. There are four types of selec­tions, which can be con­cat­en­ated for a com­bined selec­tion of values:

Fur­ther options include the selec­tion of the return format. We use either the CSV or JSON format, depend­ing on the spe­cif­ics of the returned data, to min­im­ize data volume and thus net­work traffic.

Exports of addi­tional Information

Along­side the fin­an­cial state­ments, addi­tional inform­a­tion can be extrac­ted from HFM. One example are cur­rency exchange rates, as well as metadata information.

Cur­rency conversions

In a glob­al­ized world organ­iz­a­tions oper­ate in many coun­tries and pro­cess pay­ments in dif­fer­ent cur­ren­cies, here con­ver­sion rates between cur­ren­cies are cru­cial for a uni­fied report­ing. Like fin­an­cial state­ments the con­ver­sion rates can be extrac­ted from Hyper­ion Fin­an­cial Management.

Get to know the Dimensions

The extrac­tion of all des­cend­ants of a node yields many indi­vidual records. As the hier­arch­ical inform­a­tion is not part of the fin­an­cial state­ments, the inter­pret­a­tion without it is dif­fi­cult. Addi­tional to the fin­an­cial inform­a­tions the extrac­tion of the list of mem­bers cor­res­pond­ing inform­a­tion as well as the extrac­tion of entire hier­ch­ies is per­formed. This enables inde­pht ana­lyses of fin­ances through­out the organ­iz­a­tional strucutures.

Stay with the rel­ev­ant data

Fur­ther­more the inform­a­tion con­tained within Hyper­ion Fin­an­cial Man­age­ment is vast and data of older peri­ods is static. We estab­lished time win­dows, fixed rel­at­ive to the cur­rent act­ive period to retrieve rel­ev­ant data. These time win­dows may select cur­rent fin­an­cial state­ments, but also for­cas­ted val­ues or assigend bug­dets. They’ll auto­mat­ic­ally shift for each quarter without addi­tional input, while main­tain­ing the abil­ity to manu­ally trig­ger the extrac­tion for a spe­cific date.

Inges­tion into Azure Databricks

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

• bronze for basic format validations
• sil­ver for more com­plex val­id­a­tions and derivations
• gold for end­user report­ing as well as deliv­er­ies to other systems

From the response of the API request we extract the con­tent and use pyS­park to cast to the cor­rect data type per column and write to the bronze tables. Dur­ing the load from the bronze to the sil­ver layer we per­form val­id­a­tions, trans­form­a­tions and deriv­a­tions, based on cus­tom­iz­able build­ing blocks.

By join­ing the HFM secur­ity classes with metadata tables in the sil­ver layer, we dynam­ic­ally derive secur­ity roles. In the gold layer data is aggeg­ated for report­ing and secur­ity classes mapped to to actual roles. When com­bined with Dat­ab­ricks Unity Cata­log, allow­ing for Row-Level Secur­ity (RLS). This ensures that a user from the French branch only sees French data, even if the under­ly­ing table con­tains the entire global dataset. 

Con­clu­sions

By auto­mat­ing the bridge from HFM to Azure Dat­ab­ricks, we have moved bey­ond manual Excel exports to a mod­ern, secure, and “live” data land­scape. With the fin­an­cial state­ments, as well as cur­rency con­ver­sion rates and metadata inform­a­tion on dimen­sions avail­able via Dat­ab­ricks Unity Cata­log, the organ­iz­a­tion is now equipped to build state-of-the-art report­ing applic­a­tions and make data-driven decisions with confidence.

Ref­er­ences

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