Converting OSCRE IDM to Snowflake

Uli Bethke Flexter, XML

In this post we will guide you through the easiest way of processing your XML data to a relational format on Snowflake . We will be using Flexter as a Service (FaaS) to process the OSCRE Real Estate Industry Data Model (IDM) XML. FaaS follows a monthly subscription model.

Flexter is available in three editions. An enterprise edition, which can be installed on-premise. A managed SaaS edition Flexter as a Service, which you can access by API. You can also try Flexter for free.

OSCRE IDM

OSCRE IDM is a revision of the OSCRE standards into one extensive resource, containing all OSCRE standards and a data governance framework in one place.

Here are some of the features of the Industry Data Model:

  • Defines over 100 use cases contained in one data model
  • 7 core real estate functions
  • Data Model readable in one of many free or low cost data model viewers such as Modelio.
  • The full real estate data model is a benefit to all types of stakeholders.

Processing XML with FaaS API

Flexter exposes its functionality through a RESTful API. Converting XML/JSON to Snowflake can be performed in a few simple steps. For more details please refer to the FaaS API documentation. We have recently also added a Command Line Interface, which you can use with Python.

Step 1 - Authenticate

Step 2 - Define Source Connection (Upload or S3) for Source Data (JSON/XML)

Step 3 - Optionally define Source Connection (Upload or S3) for Source Schema (XSD)

Step 4 - Define your Target Connection, e.g. Snowflake, Redshift, SQL Server, Oracle etc.

Step 5 - Convert your XML/JSON from Source to Target Connection

Step 1 - Authenticate

To get an access_token you need to make a call to /oauth/token with Authorization header and 3 form parameters:

  • username=YOUR_EMAIL
  • password=YOUR_PASSWORD
  • grant_type=password

You will get your username and password from Sonra when you sign up for the service.

Example of output

Step 2 - Define Source Connection (Upload) for Source Data (OSCRE XML)

In a second step we will upload our OSCRE XML source data

Example of output

Step 3 - Optionally define Source Connection (Upload or S3) for Source Schema (XSD)

In the next step we will define Source Schema by uploading OSCRE XSD files.

Example of output

Step 4 - Define Target Connection (Snowflake)

In the next step we will define the Target Connection. In this example we convert our XML data to a relational format in Snowflake.

We give the Target Connection a name and supply various connection parameters to the Snowflake.

Example of output

Step 5 - Convert XML data from Source Connection (Upload) to Target Connection (Snowflake)

In the last step we convert OSCRE XML. Data will be written to the Snowflake Target Connection.

Example of output

Downloading ER Diagram

We can create and download an ER Diagram of the model that FaaS generated by making a GET call.

Example of output

 

 

You can download the ER Diagram of our CDISC standard file here.

Querying OSCRE in Snowflake

In this step we will run an sql query which will return total amount of invoices per building.

 

Conclusion

We have converted OSCRE XML to Snowflake tables in a few simple steps. We did in a matter of minutes what would normally take a few hours or even days.

Why did we create Flexter?

Flexter was born from our own frustration of converting XML and JSON documents into a data warehouse. We saw many projects fail or run over budget. Why waste weeks of converting XML/JSON and risking failure instead of focusing on delivering real value to your business users? We think that the time spent writing manual code to convert the data to It just takes too much time of converting the data

Our customers are delighted with Flexter. Hear what they say

“This will save us weeks”

“You did in one day what we could not achieve in 3 years”

And this is what Ralph Kimball, one of the fathers of data warehousing has to say about XML conversion:

“The construction of an XML parser is a project in itself - not to be attempted by the data warehouse team”. Ralph Kimball, ETL Toolkit

We provide Flexter in three editions:

You can try out the free version of Flexter for JSON or XML.

Our enterprise edition can be installed on a single node or for very large volumes of XML on a cluster of servers.

Our SaaS edition, Flexter as a Service (FaaS), is managed by Sonra. You can use the Flexter API to convert your data.

If you have any questions please refer to the Flexter FAQ section. You can also request a demo of Flexter or reach out to us directly.

Last but not least we have open sourced Paranoid, our solution to obfuscate data in XML and JSON documents

Who uses Flexter?

Companies in travel, finance, healthcare, automotive, finance, and insurance industry use Flexter.

 

About the author

Uli Bethke LinkedIn Profile

Uli has 18 years’ hands on experience as a consultant, architect, and manager in the data industry. He frequently speaks at conferences. Uli has architected and delivered data warehouses in Europe, North America, and South East Asia. He is a traveler between the worlds of traditional data warehousing and big data technologies.

Uli is a regular contributor to blogs and books and chairs the the Hadoop User Group Ireland. He is also a co-founder and VP of the Irish chapter of DAMA, a non for profit global data management organization. He has co-founded the Irish Oracle Big Data User Group.