Designing the Data Warehouse for Analysis Services - Expert Cube Development with SSAS Multidimensional Models - page 30

• Views can provide default values when needed : When we have a

NULLable column that contains NULL values, we can easily assign a default
value to it using views. We shouldn't really have a NULLable column in a
data mart, but sometimes it happens.

• Views can expose a star schema even if the relational model is more

complex : As we've already mentioned, sometimes we end up with a
relational design that is not a perfectly designed star schema. By removing
unused columns, by creating joins when necessary, and in general by
designing the appropriate queries, we can expose Analysis Services to a
star schema, even when the relational model has a more complex structure.

• Views are database objects : As views are database objects, they inherit two

important properties:
° We can configure security for views, and so stop unauthorized access
to data very easily.
° Views can belong to a schema. If we are using schemas for the
definition of subject areas, we can assign views to subject areas. This
will lead to a very clean project, where each object belongs to the
subject area that is relevant to it.

• Views can be optimized : With views we can use hints to improve

performance. For example, we can use the NOLOCK hint to avoid locking
while reading from tables; although, of course, removing locking leads to
the possibility of dirty reads, and it is up to the developer to decide whether
doing this is a good idea or not. Moreover, we can analyze the execution path
of a view in order to fine tune it. All this can be done without affecting in any
way the Analysis Services project.

One very important point needs to be stressed: views should not be
used as a substitute for proper ETL. Whenever views are used to
feed Analysis Services, they should not contain complex calculations
or WHERE clauses as this can lead to serious processing performance
and maintenance problems. We can use a view instead of ETL
code for prototyping purposes, but this is very bad practice in a
production system.