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.