Converting LEI XML Data to Power BI

The Legal Entity Identifier (LEI) connects key reference information that enables clear and unique identification of legal entities participating in financial transactions. The International Organization for Standardization (ISO) 17442 standard defines a set of attributes, or legal entity reference data, that comprises the most essential elements of identification.
Daily reporting of LEI and legal entity reference data is conducted by the LEI issuing organizations using the Common Data File (CDF) formats. The CDF formats provide the specificity needed for the operational implementation of the ISO standard. External standards have been included in the CDF formats, where appropriate, to promote data quality.

LEI Format Guide

The Global Legal Entity Identifier Foundation (GLEIF) publishes four separate ‘Concatenated Files’ daily. The Concatenated Files include Legal Entity Identifiers (LEIs) and related LEI reference data published by the LEI issuing organizations. GLEIF does not modify any of the original source files and performs no data checks.
The GLEIF Concatenated Files, which are available for download, include specific information on LEI records

Downloading LEI XML file

You can find 4 types of XML files on the LEI website. For the purpose of this presentation we have chosen the LEI-CDF v2.1 Concatenated file .

Uploading LEI XML to Power BI

Lets first try to upload LEI Xml file to Power BI and show you why Flexter is so powerful and superior.
We first click Get Data

• Choose XML and click on Connect

• You now pick the XML file which you want to upload and click Open

• Now choose which tables you want to use and click Load

• Power BI has two tabs on the left side. The first one is called Reports and we use it for creating powerful Dashboards
• The second one is called Data, in which we can see our files as Tables

• When we go to Data Tab, we can see that we don’t get any useful data which we can analyze. The PowerBI XML import only works for the most simple and basic XML files.

• Now lets show you how Flexter does it.

Converting LEI XML file to Text (CSV/TSV)

Now that we have downloaded the XML we can convert the data to a format that can be easily loaded into Power BI. We will use the Flexter. You can compare the features of the various Flexter editions on the Flexter product pages.
We first upload our XML data

We skip uploading the XSD Schema. Medline only provides schemas in DTD format.

Next we submit our e-mail address. You will get an e-mail with a download link to your converted data once Flexter has finished parsing the LEI XML.

That’s it.

Once we have downloaded our tab separated output (TSV) files we can extract them.
With Flexter, we have achieved in minutes what normally would take days or weeks of manual coding (or would fail as is the case with the Power BI XML import tool)

Analysing LEI XML Data in Power BI
Now we can load output from Flexter into Power BI. Microsoft provides various editions of their powerful data analysis tool.

Now we can start loading all tsv files we got from Flexter

• We do this by clicking on “Get Data”

• Picking Text/CSV option

• And click Connect
• Then we will pick the files we want to upload. Make sure that you chose an option to show all files

• Lets upload LEIData_LEIRecord.tsv first. We will get a new window.

• We then need to open it as CSV Document

• We will get a table, which we will have to modify

• First we will have to right click on the “ Source “ in APPLIED STEPS field and open Edit Settings

• We will get a new window in which we will have to choese TAB under Delimiter dropdown

• Next we need to put first rows as header. We do that by clicking on “ Use First Rows as Headers “

• Now the file is prepared and we can Apply it. We do that by clicking on Close & Apply option

• For some files all this steps Power BI will do, so it will be easy as clicking just on Load

• And when we are done with uploading all the files, we can start doing some analysis of the uploaded data.
• With Power BI it is easy to Analyze data, you just need to pick an option of how you will show your data and assign Data to it.
• First we will make an World Map with Data we want to Analyze. We choose ArcGis Maps for Power BI and assign data to it

• Next we can make an easy chart with choosing Stacked Bar Chart and assigning data to it

• And in just couple of minutes we were able to Analyze Data and get a nice looking Dashboard

Conclusion: XML Conversion and Analysis with Power BI

We have shown you how easy it is to convert and analyse XML using two powerful tools such as Flexter and Power BI. This only took us 10 minutes from downloading data from LEI website to converting XML into TSV. You can achieve what normally takes weeks, in just 10 minutes with Flexter.
Which data formats apart from XML also give you the heebie jeebies and need to be liberated? Please leave a comment below or reach out to us.