Create views for all your tables in one step

I have to admit that I am a little lazy. So if there is something I have to do more than once I’ll try and find a quicker way.

One of these tasks in any DW build is creating views for all your dimension and fact tables to create an abstraction layer between the relational tables and the client tool or SSAS Data Source Views. These views can be useful for all sorts of functionality such simplifying currency conversion or creating calculated fields.

What really becomes a nuisance is when using a RAD approach you can find yourself re-creating these views several times over.

The below script automates this process. For it to work properly you must set SSMS to “Results to Text”:

image

This could be wrapped in a stored proc if you were super keen.

DECLARE @exclCols TABLE (COLUMN_NAME varchar(255))
DECLARE @crlf char(2)
DECLARE @viewPrefix varchar(10)
 
INSERT INTO @exclCols (COLUMN_NAME)
VALUES
-- Metadata columns you want excluded from the views
     ('source_item_id')
    ,('source_line_no')
    ,('load_date')
    ,('update_date')
 
SET @crlf = CHAR(13) + CHAR(10)
-- Set view prefix to 'uvw' or uvw_'
SET @viewPrefix = 'uvw'
 
SELECT 
    CASE 
    -- Set first line of create view statement
    WHEN c0.ORDINAL_POSITION = 
            (SELECT 
                MIN(c1.ORDINAL_POSITION) 
             FROM 
                INFORMATION_SCHEMA.COLUMNS c1 
             WHERE 
                c1.TABLE_NAME = c0.TABLE_NAME 
                    AND 
                c1.COLUMN_NAME NOT IN (SELECT COLUMN_NAME FROM @exclCols)
            ) 
        THEN 
        'CREATE VIEW ' + @viewPrefix + c0.TABLE_NAME + ' AS ' + @crlf + 
        -- Add comments to view
        '/* CREATED BY:   ' + Suser_Sname() + ' */ ' + @crlf + 
        '/* CREATED DATE: ' + CONVERT(varchar(255), GETDATE(), 127) + ' */ ' + @crlf + 
        ' SELECT ' + @crlf + 
        '   ' + COLUMN_NAME
    -- Set last line of create view statement
    WHEN c0.ORDINAL_POSITION = 
            (SELECT 
                MAX(c1.ORDINAL_POSITION) 
             FROM 
                INFORMATION_SCHEMA.COLUMNS c1
             WHERE 
                c1.TABLE_NAME = c0.TABLE_NAME 
                    AND 
                c1.COLUMN_NAME NOT IN (SELECT COLUMN_NAME FROM @exclCols)
            )
        THEN
        '  ,' + c0.COLUMN_NAME + @crlf +
        ' FROM ' + @crlf +
        '  ' + c0.TABLE_NAME + @crlf + 
        'GO;' + @crlf + @crlf 
    -- Set other lines of create view statement
    ELSE
    '  ,' + c0.COLUMN_NAME 
    END 
FROM
    INFORMATION_SCHEMA.COLUMNS c0 
INNER JOIN INFORMATION_SCHEMA.TABLES t0 ON 
    c0.TABLE_CATALOG = t0.TABLE_CATALOG 
        AND 
    c0.TABLE_SCHEMA = t0.TABLE_SCHEMA 
        AND
    c0.TABLE_NAME = t0.TABLE_NAME 
WHERE 
    c0.COLUMN_NAME NOT IN (SELECT COLUMN_NAME FROM @exclCols)
        AND 
    -- Prevents creating views of views
    t0.TABLE_TYPE = 'BASE TABLE'

Running this against AdventureworksDW will result in an output something like:

CREATE VIEW uvwDimAccount AS 
/* CREATED BY:   MYDOMAIN\jsmith */ 
/* CREATED DATE: 2010-08-17T16:47:31.780 */ 
 SELECT 
   AccountKey
  ,ParentAccountKey
  ,AccountCodeAlternateKey
  ,ParentAccountCodeAlternateKey
  ,AccountDescription
  ,AccountType
  ,Operator
  ,CustomMembers
  ,ValueType
  ,CustomMemberOptions
 FROM 
  DimAccount
GO;
 
CREATE VIEW uvwDimCurrency AS 
/* CREATED BY:   MYDOMAIN\jsmith */ 
/* CREATED DATE: 2010-08-17T16:47:31.780 */ 
 SELECT 
   CurrencyKey
  ,CurrencyAlternateKey
  ,CurrencyName
 FROM 
  DimCurrency
GO;

etc…

Advertisements

About Karl Beran

I take interest and satisfaction from enabling people within an organisation to have an accurate, concise and flexible representation of the right information they need, when (or before) they need it. I believe this allows the business to make both operational and strategic decisions quickly and with more confidence, thus improving the agility and efficiency of the company. Having worked with BI stacks from the major vendors, the focus of my blog is on BI principals and strategy with examples from the Microsoft stack as I believe it has the most dynamic and engaged user community with the knowledge base and components fairly readily available to new developers.

Posted on August 17, 2010, in SQL. Bookmark the permalink. 5 Comments.

  1. Karl,
    given that the DSV is already a data abstraction layer, what’s your take on the value of creating another layer between the tables and the DSV? Surely this is just adding to the amount of ‘state’ the poor maintenance programmer has to keep in her head when trying to understand the ETTL?
    Put it another way . . . what do you think of this:
    If you are going to use a DSV, don’t use SQL views; alternately, if you’re going to use SQL views, don’t bother with intermediation through a DSV as well.
    Having said that, I personally use SQL views to incorporate calculations, and use the DSV to just show the relevant tables/views to the ETTL. I only put calcs in the DSV for prototyping and move them to SQL Server when I’m happy with them.
    Cheers, Donna Kelly

    Like

    • Hi Donna,
      I agree with you in that creating another abstraction layer with views increases the number of objects (“amount of ‘state'”) a maintenance programmer has to be aware of in understanding data the data transformation process/flow within the ETL/ETTL of the DW. However in some cases this may be a price worth paying (and if your source to target maps are all up to date it should be less of an issue).

      I both agree and disagree with your suggestion of using only one of SQL views or DSV, my opinion would depend on the use case.

      Consistency:
      The main advantage of applying arithmetic indiviudal row level or table join level logic (ie UnitsSold*UnitPrice = TotalPrice or currency rate triangulation), is that you can expose the exact same calculations to both relational database reporting tools as you do to the SSAS cube, keeping any user exposed objects consistent. SSRS isn’t the most elegant tool to report against SSAS, and for transaction line level reporting, it can often be the case that the relational (SQL DB) storage and query engine performs better than the OLAP (SSAS) one.

      Another scenario where this consistency is becoming much more prevalent is use of the SSAS Tabular. There are valid scenarios for using SSAS Tabular and SSAS Multidimensional alongside each other and even with different subject areas/facts/measure groups there will inevitably be shared dimensions. In this case it makes sense to have the logic stored in one place rather than trying to maintain two sets of named queries. With users creating powerpivot models using a subset of data from your DW it really makes sense that they are using logic from the same objects as your SSAS cube.

      Obviously if you are mixing several data sources in the DSV (ie Oracle & MS SQL) keeping logic in the DSV may be your only option. So in this case, if your logic spans both data sources, your only option is to use the DSV (however if you are doing this there may be a bigger architecture question to be asked about why this isn’t being combined in the ETL.

      Performance:
      There can be some performance advantages around using views driven by query plan caching and normalisation of snowflake dimensions, however this will only become a concern on fairly large cubes or near realtime cube processing.

      Hopefully that explains a bit. Thanks for your thoughts.

      Like

      • You write:
        “I both agree and disagree with your suggestion of using only one of SQL views or DSV, my opinion would depend on the use case. Consistency: The main advantage of applying arithmetic indiviudal row level or table join level logic (ie UnitsSold*UnitPrice = TotalPrice or currency rate triangulation), is that you can expose the exact same calculations to both relational database reporting tools as you do to the SSAS cube”

        Well, I both agree and disagree with myself, too! As I went on to say later in my comment, I use both.

        But not for the purpose you describe. I refuse to expose anything from the relational layer at all. End users get a single view of the enterprise warehouse – the Presentation Layer – and that’s from the hypercube, where all the calcs, metrics, KPIs, perspectives and actions are stored. No-one is allowed to see past the restaurant into the kitchen, to use the Kimball metaphor.

        You also say “SSRS isn’t the most elegant tool to report against SSAS”. No argument (from a technical viewpoint), but I do not want end users to see how the sausage is made. I’m more concerned about the how the end result is presented to the business.

        You make a valid point regarding SSAS Tabular, though, and I haven’t yet got a really good architectural pattern on how best to incorporate the two models.

        Cheers, Donna

        Like

  2. So it’s always worth getting someone else to test you code…

    The above code seems to work against “cleanly” created databases -i.e. those where all objects are created at once via a single DDL script.

    It appears in that scenario that INFORMATION_SCHEMA.COLUMNS will return rows in ascending order of ORDINAL_POSITION.
    For tables that have had columns added or modified, or under other scenarios it can return rows in a random order which will cause query to produce a ‘CREATE VIEW’ DDL script that won’t run.

    The fix is quite simple and involves adding the following to the end of the query:

    ORDER BY
    c0.TABLE_CATALOG
    ,c0.TABLE_SCHEMA
    ,c0.TABLE_NAME
    ,c0.ORDINAL_POSITION

    Like

    • and a second bug…
      I’m creating some views against a source database (not a dimensional model) for a quick virtual DW and found some of the tables I was creating views for contain which cause the ‘CREATE VIEW…’ statements to fail.
      So the three instances of
      c0.ColumnName
      within the CASE statement need to be replaced with
      ‘[‘ + c0.COLUMN_NAME + ‘]’

      I will post an updated query soon.

      Like

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 )

Twitter picture

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

Facebook photo

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

Google+ photo

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

Connecting to %s

%d bloggers like this: