Oracle XDB and Data Vault Staging

It’s been around for quite a while now, and it’s been improving in features and functionality. I’ve blogged over the years about using XML inside the database engines – and the need to embed this functionality directly inside the core. Oracle, I am happy to say, has a wonderful solution to the problem.

Oracle has got a piece called XDB, which appears to use some of the “file attach” functionality that has been available for a very long time. XDB has several features which make it a prime target / easy mechanism for configuring it as the Data Vault staging area – particularly if you are dealing with XML data sets, and you have Oracle on-site as a database.

XML is inherantly thorny (as are XSD’s, DTD’s, the like). It is riddled with hierarchies, empty data sets, text-based tagging systems, recursive relationships, forward/backward references, optional structures, and so on. It’s sort of like the old hierarchical file systems (HFS) defined by cobol records, the only difference is that it is much more fluid. Yes, as you know – it can change at any time. Volatility makes dealing or ingesting XML into your data warehouse a tall order.

So, what’s the best way to deal with it?

I don’t know about the best way, but I do know about leveraging native database technology rather than using a 3rd party ETL / ELT tool to accomplish the tasks. I would strongly suggest that any time your data warehouse has to ingest XML, that you establish an agreement with the customer: they *MUST* provide an XSD, (xml schema document definition). Once you have the XSD (even if it changes over time), it makes it that much easier to process and validate. Without the XSD you are left to archane hand-coding to decipher the XML into something usable, very costly and very time consuming; or you use an ETL tool and hard-code the structure to the tool making it difficult (if not impossible) to adapt to future changes.

So, the recommendation?

Find yourself a good database engine like Microsoft SQLServer 2008, Oracle with XDB, DB2 with XML capabilities, what ever the engine – with the ability to 1) absorb XSD’s for definitions, 2) attach an XML file directly to the XSD (tie it to the structure), and 3) provide SQL/XQUERY interface so you can actually use a standard/hybrid SQL query to get the records out of the XML document directly inside the database engine.

Well, Oracle’s XDB is impressive. It has the features needed, and is efficient enough to handle most batch refresh windows that we deal with. We are using the structured schema definition to make this work, and I am pleased as punch in how the database absorbs XSD changes and XML files. The good part about this, is we receive hundreds of XML files in the batch cycle, but none of them are very large (by themselves). This allows us to process/tie/attach these files in parallel processes – and they each process very quickly.

Voila, our staging area is loaded and ready to go, simply by dropping an XML file into a specific directory. Then, once we’ve moved the data from the XDB staging to the Data Vault, we simply move the XML files from the “staging directory” to the “archive directory”, and voila – they are automatically picked up for backup purposes, leaving the “staging structures” in the database as “empty.” No need to manage indexes, partitioning, or even truncation.

I Applaud Oracle for such an elegant solution, if you have Oracle licensing in house and you are dealing with XML/XSD, then I suggest you check out their XDB options. Here’s a link to some cool information.

Dan L

Tags: , , , , , ,

No comments yet.

Leave a Reply