Wednesday, June 03, 2009

ETL Architecture : Metadata driven extract architecture / BI application architecture

I'm reading: ETL Architecture : Metadata driven extract architecture / BI application architectureTweet this !
For a requirement where, real time (or near-real time) movement of data from their operational logistics application to an analytics/reporting visualization environment where presentation tools such as Microsoft Reporting Services, Business Objects or QlikView consume the data is required, there are two approaches: Operational Analysis & Traditional Business Intelligence.

Operational Analysis is based on loading the source data into compressed proprietary data stores without transformations (Cleansing, Normalization, Hierarchies). This will provide analytical capabilities including metrics relationships and hierarchies that exist in the data model being sourced.

The limitations here are no trending, history or complex business rules, metrics that require corporate reference data such as product or organization hierarchies. A benefit in relation to the extraction process is this approach does not require any staging area.

Traditional Business Intelligence is based on a metadata driven approach were the source data is transformed to properly analyze a specific set of business metrics and their associated business process hierarchies, including trending and history.

A limitation of approach will require a more complex extraction and loading process and a staging area.

A major benefits is this approach will "insulate" the reporting or analytical layer from any changes or additions to the source. This is accomplished through using a data driven approach and creating a business dimensional oriented semantic layer. In most cases the metric and dimensions math the business processes and do not change over time, where as the source data and nomenclature is volatile.


A best practices Extraction Architecture requires a metadata driven application architecture that would be data aware of structure or content (domain) changes and automatically adjust load processes accordingly.

Reference: Article by Ira Warren Whiteside on SQLServerCentral

No comments:

Related Posts with Thumbnails