Creating Data Source Views

In an ideal world, if you've followed all of our recommendations so far, then you need to do very little work in your project's Data Source View (DSV)—nothing more than selecting the views representing the dimension and fact tables and setting up any joins between the tables that weren't detected automatically. Of course, in the real world, you have to compromise your design sometimes and that's where a lot of the functionality available in Data Source Views comes in useful.

When you first create a new DSV, the easiest thing to do is to go through all of the steps of the wizard, but not to select any tables yet. You can then set some useful properties on the DSV, which will make the process of adding new tables and relationships much easier. In order to find them, right-click on some blank space in the diagram pane and click on Properties. They are:

This is extremely useful if the tables you're using don't actually contain foreign key relationships, or if you've used views on top of your dimension and fact tables in the way we suggested in the previous chapter. You'll also see an extra step in the New Data Source View wizard allowing you to set these options if no foreign keys are found in the Data Source you're using.

Now you can go ahead and right-click on the DSV design area and select the Add/Remove Tables option and select any tables or views you need to use. It might be a good idea not to select everything you need initially, but to select just one fact table and a few dimension tables so you can check the relationships and arrange the tables clearly, and then add more. It's all too easy to end up with a DSV that looks like a plate of spaghetti and is completely unreadable. Even though you don't actually need to add every single relationship at this stage in order to build a cube, we recommend that you do so, as the effort will pay off later when BIDS uses these relationships to automatically populate properties such as dimension-to-measure group relationships.

Named queries and named calculations allow you to add the equivalent of views and derived columns to your DSV, and this functionality was added to help cube developers who needed to manipulate data in the relational database, but didn't have the appropriate permissions to do so. However, if you have the choice between, say, altering a table and a SSIS package to fix a modeling problem or creating a named query, then we recommend that you always choose the former one—only do work in the DSV if you have no other choice. As we've already said several times, it makes much more sense to keep all of your ETL work in your ETL tool, and your relational modeling work in the relational database where it can be shared, managed, and tuned more effectively. Resist the temptation to be lazy and don't just hack something in the DSV! One of the reasons why we advocate the use of views on top of dimensions and fact tables is that they are as easy to alter as named queries and much easier to tune.

The SQL that Analysis Services generates during processing is influenced heavily by what goes on in the DSV and many processing performance problems are the result of cube designers taking the easy option early on in development.