Introduction

How many times have you wished you could do a table rebuild during the day…. create a new calculated column (in seconds)…alphabetized the column names in a table (yes I really had requests to do this for an external tool)…. hide “unnecessary” columns from users such as checksums, audit_keys etc… code a particular table with (NOLOCK)…. security for specific objects…. etc…??

If you have wanted to do any of those, you should be using views to expose the data in the warehouse.  Because you have built a schema that contains the actual tables, your primary user views can be the default dbo owned objects, and you select the schema that the users utimatly use through the view.

An example:

Recently, due to reporting requirements, it was necessary to alter the description of a group of products, however, the source could not be changed to reflect the change in names.  Rather than build a lookup table, and translation process in the ETL load, I let the undesirable descriptions load, and built an override in the view.  In this case I did need to alter the dimension to add a column (preferred_product_type_desc) that allows nulls. The dimension load process does nothing with the new column, it is not included as comparison for type-1 changes.  Notice that the table is created in a schema.

CREATE TABLE [dw_dbo].[all_products](
   [product_type_key] [int] IDENTITY(1000,1) NOT NULL,
   [product_type_desc] [varchar](50) NOT NULL,
   [preferred_product_type_desc] [varchar](50) NULL,
   [product_category] [varchar](50) NULL,
   [product_type_id] [int] NOT NULL,
   [audit_key] [int] NOT NULL,
   [rec_mod_dt] [datetime] NULL,
   [rec_create_dt] [datetime] NOT NULL
) ON [Primary_DIM_IDX]

Next, create the dbo view (so when the users select product_type_key… from all_products they are actually selecting from the underlying schema object).You will also see a few key things in this view:

1. There is a case statement that exposes the altered description (if there is one) for this particular field.
2. The load audit information (that the users don’t need) is not exposed.
3. The nolock hint is applied in the select.  You don’t want a long-running user query to prevent the load from processing data, and you certainly should not hope (or require) the user to code the hint into the query.

CREATE VIEW [dbo].[all_products]
AS
   SELECT product_type_key,
          CASE
             WHEN preferred_product_type_desc IS NOT NULL
                THEN preferred_product_type_desc
             ELSE product_type_desc
          END AS product_type_desc,
          product_category,
          product_type_id
   FROM   dw_dbo.all_products WITH(nolock)

The schema approach will also allow you to make a temporary copy of the existing table, point the view to the temporary copy while the existing table is altered, repartitioned, moved whatever the maintenance is…. once the maintenance work is complete, the view is pointed back to the permanent table.

Conclusion

Do yourself a favor, and build a layer of insulation between your data  and the outside world. User  and tools have unique requirements, and creating this separations will allow for flexibility, customization, and tool-specific needs that you will be thankful you can implement with such ease!