ETL using SSDB · Microsoft Data Technology · Microsoft SSDB

SSDB – Part 14. ETL – Implementing ETL with SQL Objects (VIEWS)

Working with ETL Objects

It has long been a best practice to build software using multiple layers of abstraction. By creating an abstraction layer, the underlying objects are always used indirectly.

Abstraction can give you greater flexibility and lower maintenance costs in situations where database tables are only accessed using abstraction objects.
In SQL programming, abstraction layers are typically used with views and stored procedures. Adding this layer of objects can have the following benefits:

  • Mask complexity of their programming statements
  • Facilitate changes to underlying tables while hiding these changes
  • Allow for security settings that protect the tables from misuse

Views

A SQL view is a single select statement that is saved internally in the database under a specified name. The view is then used as if it were a table. The biggest differences between a table and a view are that a view is simply a saved select statement, while a table stores the actual data.

Views are useful for many ETL processing tasks such as the following:

  • Transforming column names
  • Using column aliases
  • Combining data from multiple tables
  • Processing nulls
  • Performing data conversions

Stored Procedures

Stored procedures are similar to views. Both are named sets of SQL code. Unlike views, however, stored procedures can contain multiple statements, work with variables, and process transaction statements.

A combination of both Views and Stored procedures can be used to create an effect ETL Process:

Creating ETL views:

 

Note: Microsoft is the owner of the content and video in this blog post.

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s