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

Naming conventions
A clear and consistent naming convention is good practice for any kind of relational
database and a data mart is no different. As well as making the structure more
readable, it will help you when you come to build your cube because SQL Server
Data Tools will be able to work out automatically which columns in your dimension
and fact tables should join to each other if they have the same names.

Views versus the Data Source View
The Data Source View (DSV, from now on) is one of the places where we can create
an interface between Analysis Services and the underlying relational model. In
the DSV, we can specify joins between tables and we can create named queries
and calculations to provide the equivalent of views and derived columns. It's very
convenient for the cube developer to open up the DSV in SQL Server Data Tools and
make these kind of changes.

This is all well and good, but nevertheless our opinion about the DSV is clear: it is
almost too powerful and, using its features, we risk turning a clean, elegant structure
into a mess. It is certainly true that there is the need for an interface between the
relational model of the database and the final star schema, but we don't think it's a
good idea to use the DSV for this purpose.

SQL Server gives us a much more powerful and easy-to-use tool to use instead: SQL
Views . The following is a list of some of the reasons why we prefer to use views
instead of the DSV:

• Views are stored where we need them : When we need to read the

specification of an interface, we want to be able to do it quickly. Views
are stored in the database, exactly where we want them to be. If we need
to modify the database, we want to be able to find all of the dependencies
easily, and using views, we have a very easy way of tracking dependencies.
If we use the DSV, we are hiding these dependencies from the database
administrator, the person who needs to be able to update and optimize
the data mart. In addition, there are tools on the market that can analyze
dependencies between table and views. It is not easy to do this if information
on the joins between tables is stored outside the database.

• We can easily change column names in views : In the database, we might

have SoldQty as a field in a table. This is good because it is concise and does
not contain useless spaces. In the cube, we want to show it as Quantity Sold
simply because our user wants a more descriptive name.