THE SQL Server Blog Spot on the Web

Welcome to SQLblog.com - The SQL Server blog spot on the web Sign in | |
in Search

SQLBI - Marco Russo

SQLBI is a blog dedicated to building Business Intelligence solutions with SQL Server.
You can follow me on Twitter: @marcorus

DSV and Views: how to decouple the physical tables from UDM

I just answered to a newsgroup question about the use of views as fact tables to populate cubes.

I am a strong supporter of the need to build a star schema before to build a UDM, while I understand that in some circumstances (few data, fast prototypes) you can think to build a similar model through views on the source database, without an ETL in the middle.

That said, when you have a regular and well defined star schema, you still may have doubts about the use of views (in the database) and named queries (in the DSV) to decouple the UDM model from physical tables. The reason for doing that is to simplify the maintainance of the solution. After years of experience (AS2000 had similar scenario) and tests in many combinations of the factors, I have these guidelines.

  1. Unless you have only a few Mb of database, it is ALWAYS BETTER to create your data mart to feed dimensions and cubes
  2. When you present dimension and fact tables to SSAS, it is better to create a VIEW on the database that is easy to maintain if you have to implement some logic on the query to present data to SSAS (for example, a join to decode some fields - it should not be the standard case, but it happens in the lifetime of a project when you need to add some attribute and temporarily want to show data that are still not implemented well in the denormalized star schema) - the ideal case is that all these views are SELECT * FROM table, because you only decouple the physical fact table from the logical view for SSAS
  3. Use named query on DSV only to apply some cosmetic change to data just to correctly populate dimensions and cubes. For example, a lastname + ' ' + firstname expression is right to use here, while I can't see many reasons to use a WHERE or a JOIN, because it imply some transformation logic that is better to place into the VIEW (that could be used by  other SQL queries, for example for Reporting Services).

What is your opinion? Feedback and comments would be very appreciated.

Published Tuesday, October 03, 2006 3:41 AM by Marco Russo (SQLBI)

Comment Notification

If you would like to receive an email when updates are made to this post, please register here

Subscribe to this post's comments using RSS

Comments

No Comments

Leave a Comment

(required) 
(required) 
Submit

About Marco Russo (SQLBI)

Marco Russo is a consultant, writer and trainer specialized in Business Intelligence with Microsoft technologies. He runs the SQLBI.COM website, which is dedicated to distribute resources useful for BI developers, like Integration Services components, Analysis Services models, tools, technical information and so on. Marco is certified as MCT, MCDBA, MCSD.NET, MCSA, MCSE+I.

This Blog

Syndication

Archives

Powered by Community Server (Commercial Edition), by Telligent Systems
  Privacy Statement