Converting OSCRE IDM to Snowflake
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. to process the OSCRE Real Estate Industry Data Model (IDM) XML.
Table of Contents
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 Flexter
Flexter exposes its functionality through a RESTful API. Converting XML/JSON to Snowflake can be performed in a few simple steps. 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.
|
1 2 3 4 |
curl --location --request POST "https://api.sonra.io/oauth/token" \ --header "Content-Type: application/x-www-form-urlencoded" \ --header "Authorization: Basic NmdORDZ0MnRwMldmazVzSk5BWWZxdVdRZXRhdWtoYWI6ZzlROFdRYm5Ic3BWUVdCYzVtZ1ZHQ0JYWjhRS1c1dUg=" \ --data "username=XXXXXXXXX&password=XXXXXXXXX&grant_type=password" |
Example of output
|
1 2 3 4 5 6 7 8 |
{ "access_token": "eyJhbG........", "token_type": "bearer", "refresh_token": "..........", "expires_in": 43199, "scope": "read write", "jti": "9f75f5ad-ba38-4baf-843a-849918427954" } |
Step 2 – Define Source Connection (Upload) for Source Data (OSCRE XML)
In a second step we will upload our OSCRE XML source data
|
1 2 3 4 5 |
curl --location --request POST "https://api.sonra.io/data_sources/oscre" \ --header "Authorization: Bearer <access_token>" \ --form "source_type=uploaded_file" \ --form "file=@<file_path>" \ --form "data_type=xml" |
Example of output
|
1 2 3 4 5 6 7 8 |
{ "name" : "oscre", "type" : "xml", "source_type" : "uploaded_file", "path" : "file-1291d2e6-a29e-4f3d-bcf4-96911d1c4124", "size" : 295402, "create_date" : "2020-04-13T12:19:08.529+0000" } |
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.
|
1 2 3 4 |
curl --location --request POST "https://api.sonra.io/schema_sources/oscre" \ --header "Authorization: Bearer <access_token>" \ --form "source_type=uploaded_file" \ --form "file=@<file_path>" |
Example of output
|
1 2 3 4 5 6 7 |
{ "name" : "oscre", "source_type" : "uploaded_file", "path" : "file-2658313f-84e4-4a78-89fa-92aed83a0b39.zip", "size" : 457953, "create_date" : "2020-04-13T12:20:24.069+0000" } |
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.
|
1 2 3 4 5 6 7 8 9 10 |
curl --location --request POST 'https://api.sonra.io/target_connections/oscre' \ --header 'Authorization: Bearer <access_token>' \ --form 'target_type=snowflake' \ --form 'host=<xxxxxx.eu-central-1.snowflakecomputing.com>' \ --form 'username=<sfuser>' \ --form 'password=<sfpass>' \ --form 'database=<sfdb>' \ --form 'schema=<sfchema>' \ --form 'role=<sfrole>' \ --form 'warehouse=<sfwh>' |
Example of output
|
1 2 3 4 5 6 |
{ "name" : "oscre", "target_type" : "snowflake", "path" : "xxxxxxxxx", "create_date" : "2020-04-13T12:21:10.584+0000" } |
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.
|
1 2 3 4 5 |
curl --location --request POST "https://api.sonra.io/conversions/oscre" \ --header "Authorization: Bearer <access_token>" \ --form "data_source=oscre" \ --form "schema_source=oscre" \ --form "target=oscre" |
Example of output
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 |
{ "name" : "oscre", "schema_source_connection" : { "name" : "oscre", "source_type" : "uploaded_file", "path" : "file-2658313f-84e4-4a78-89fa-92aed83a0b39.zip", "size" : 457953, "create_date" : "2020-04-13T12:20:24.069+0000" }, "data_source_connection" : { "name" : "oscre", "type" : "xml", "source_type" : "uploaded_file", "path" : "file-1291d2e6-a29e-4f3d-bcf4-96911d1c4124", "size" : 295402, "create_date" : "2020-04-13T12:19:08.529+0000" }, "target_connection" : { "name" : "oscre", "target_type" : "snowflake", "path" : "xxxxxxxxx", "create_date" : "2020-04-13T12:21:10.584+0000" }, "create_date" : "2020-04-13T12:22:03.666+0000", "status" : "I", "download_link" : null, "diagram_link" : null, "mapping_link" : null, "credit_usage" : null, "full_status" : "INITIALIZED" } |
Downloading ER Diagram
We can create and download an ER Diagram of the model that FaaS generated by making a GET call.
|
1 2 |
curl --location --request GET "https://api.sonra.io/conversions/oscre" \ --header "Authorization: Bearer <access_token>" |
Example of output
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 |
{ "name" : "oscre", "schema_source_connection" : { "name" : "oscre", "source_type" : "uploaded_file", "path" : "file-2658313f-84e4-4a78-89fa-92aed83a0b39.zip", "size" : 457953, "create_date" : "2020-04-13T12:20:24.069+0000" }, "data_source_connection" : { "name" : "oscre", "type" : "xml", "source_type" : "uploaded_file", "path" : "file-1291d2e6-a29e-4f3d-bcf4-96911d1c4124", "size" : 295402, "create_date" : "2020-04-13T12:19:08.529+0000" }, "target_connection" : { "name" : "oscre", "target_type" : "snowflake", "path" : "xxxxxxxxx", "create_date" : "2020-04-13T12:21:10.584+0000" }, "create_date" : "2020-04-13T12:22:03.666+0000", "status" : "C", "download_link" : null, "diagram_link" : "<ER Diagram Download Link>", "mapping_link" : "<Mapping Download Link>", "credit_usage" : 0, "full_status" : "COMPLETED" } |
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.
|
1 2 3 4 5 |
SELECT P.PROPERTYADDRESS_BUILDINGNAME, SUM(L.AMOUNTINVOICEDAMOUNT) FROM OSCRE.OSCRE.PROPERTY P Inner JOIN OSCRE.OSCRE.INTEREST I ON P.PK_PROPERTY = I.FK_PROPERTY Inner JOIN OSCRE.OSCRE.LEASECHARGES L ON I.PK_INTEREST = L.FK_INTEREST GROUP BY P.PROPERTYADDRESS_BUILDINGNAME |
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 Enterprise Edition:
Our enterprise edition can be installed on a single node or for very large volumes of XML on a cluster of servers.
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.
Enjoyed this post? Have a look at the other posts on our blog.
Contact us for Snowflake professional services.
We created the content in partnership with Snowflake.
In this video, we use Flexter to automatically convert very complex FpML XML to Snowflake tables. Book a demo to see the power of Flexter in action!
