ETL Architecture : Metadata driven extract architecture / BI application architecture

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.

Summary:

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

Delta Detection Techniques in ETL Architecture

Log of transactions: For this approach, you need a log of all the changes to the source tables. This approach is most commonly used for transaction records, and for changes to a master table when the table is very large.

Snapshot of source system: This approach is to copy the entire content of tables to staging and then determine the changed/added records. This is a time consuming process, particularly if the source tables are huge. This is a preferred method for small tables, such as some master tables. This approach does not capture intermediate changes.

Snapshot of changed records: This approach is to capture the changed/added records directly from master or transactional tables. You do not need a separate log of changes. But this process does require the modification date and time of the changes. This approach does not capture intermediate changes.

There are various delta detection techniques to choose from based on the capabilities of the source system and business requirements. If the source system creates change logs, then detecting extracting the delta is relatively straightforward. If the source system does not help with delta detection and you cannot make changes to the source, then the extraction process has to bring in all the transactional records to staging and determine the changed records. This is time consuming and for huge OLTP data volumes it is not operationally feasible. Hence choosing appropriate delta detection technique becomes important for efficient and effective extraction process.

Reference: BI Architecture Design Whitepaper

Impact analysis of change in metadata or OLTP Schema on SSIS packages

In a large enterprise environment, you may have dozens if not hundreds of SSIS packages that run at various times to load a myriad of tables. Let’s say that you have a new DBA who decides it will be more optimal to turn a column into a small integer from a varchar field. So, what will be the impact?

The change, no matter how small it is, may create the need to change 15 packages. The metadata solution pack is one of the discovery tools you can use to explore the impact of a change like this.There are a few approaches you could take to analyze the metadata of BI systems like SSIS. One approach you could use is to look at the XML files that make up each package using XPATH queries.

In order to solve the metadata challenge a team at Microsoft has developed a free set of utilities and samples called the SQL Server BI Metadata Samples Toolkit. The kit gives you a jump start in developing your own solution for impact analysis or you can take the Microsoft solution right out of the box. SQL Server BI Metadata Samples Toolkit includes the following items to help you create your own metadata solution:

· DependencyAnalyzer.exe – Tool that evaluates and loads into a database the lineage on SSIS packages, Analysis Services and SQL Server. All the source code for this program is provided.

· DependencyViewer.exe – A tool that lets you graphically see the dependencies and lineage of objects in the lineage repository. Source code is provided for this program.

· Data Source View – A DSV that connects to the lineage repository (SSIS META database) that can be used by Reporting Services.

· Lineage Repository – A database called SSIS_META that can be used to house metadata from nearly any system.

· Reports – Some standard reports for impact analysis studies. You will find two key reports out of the box with several sub-reports.

· Report Model – A report model that you can use with Report Builder to allow end-users to create ad-hoc reports.Integration Services Samples – A few sample packages to start auditing and viewing lineage on.

Reference: BI Metadata Whitepaper

Data Source View ( DSV ) in SSIS and SSAS - Metadata management and Advantages

Definition of DSV as per MSDN goes this way: "A DSV is a metadata layer between the object that consumes / processes data and the underlying data source that allows us to build our objects without being connected to the underlying source". For reference, a DSV is similar to Business Objects (BO) universe. A data source view is the metadata definition, stored in an XML format.

A Data Source View:

• Contains the metadata that represents selected objects from one or more underlying data sources, or the metadata that will be used to generate an underlying relational data store.
• Can be built over one or more data sources, letting you define objects that integrate data from multiple sources.
• Can contain relationships, primary keys, object names, calculated columns, and queries that are not present in an underlying data source and which exist separate from the underlying data sources.
• Is not visible to or available to be queried by client applications. Data sources views are not used when you build objects programmatically.

Analysis Services Perspective for DSV: A DSV is the metadata definition of these schema elements used by the Unified Dimensional Model (UDM) and by the mining structures. A data source view contains the logical model of the schema used by Analysis Services database objects—namely cubes, dimensions, and mining structures. In Analysis Services, a developer is embedding business metadata throughout the application when they create Data Source Views (DSVs).

Analysis Services database objects are bound to the logical objects contained in the data source view, instead of being directly bound to the underlying physical objects in the underlying data sources. Therefore, you can define logical objects, such as calculated columns or named queries, that do not exist in an underlying data source and which you might not have permissions to define in an underlying data source.

Integration Services Perspective for DSV: Advantages of using data source views include the following:

• A DSV can be defined once and then used by multiple data flow components. Because a data source view is not created within the context of a package, it can be used by tasks and data flow components in multiple packages.
• A DSV can be refreshed to reflect changes in its underlying data sources.
• A DSV also allows developers to develop packages without having live connections to the source data systems by caching the metadata from the data source from which it’s built.
• A DSV can focus the lists of objects that appear in the user interface of data flow components, speeding up the design interface.

Reference: MSDN BOL

Managing Metadata in SQL Server 2005

Today I am going to open one more category of post in my blog, which I term as "Metadata Intelligence". When working on a BI project, metadata management is one of the areas that is given the least attention, which causes a havoc in a majority of cases is the later phases of the project. Project artifacts like data dictionary, mapping documents, data lineage, audit trail and logging, etc... are given attention only when a chaos gets created for a delivery date. This has been my experience and needs not be true in a generic theory.

In the database engine, SQL 2005 expands on SQL 2000 by adding support for extended properties to nearly all database objects. Extended properties can be used for rich metadata management because each table and column (as well as most other objects in the relational engine) can have multiple extended properties that can each contain up to 7500 bytes of data. Since these properties are scriptable and are part of the structure of each object, developers can make use of these properties to store business-level definitions of each object.

Extended properties can be used for the following:

• Specifying a caption for a table, view, or column. Applications can then use the same caption in a user interface that displays information from that table, view, or column.
• Specifying an input mask for a column so that applications can validate data before running a Transact-SQL statement. For example, the required format for a postal code or telephone number column can be specified in the extended property.
• Specifying formatting rules for displaying the data in a column.
• Recording a description of specific database objects that applications can display to users. For example, the descriptions may be used in a data dictionary application or report.
• Specifying the size and window location at which a column should be displayed.
  

Extended properties is one of the starting block for starting creation of metadata management within the enterprise or system, but in a relational OLTP system. There are much more resources and tools to help or cater the same in a Business Intelligence solution (which I would discuss in future posts)

Reference: MSDN BOL