Professional SQL Server™ Analysis Services 2005 with MDX

4.2. Data Source Views

Data Source Views (DSV) enable you to create a logical view of only the tables involved in your data warehouse design; in this way system tables and other tables not pertinent to your efforts are excluded from the virtual workspace. In other words, you don't have to look at what you're never going to use directly anyway. DSVs are a powerful tool; in fact, you have the power to create DSVs that contain tables from multiple data sources which you learn later in this chapter. You need to create a DSV in your Analysis Services data since cubes and dimensions are created from the DSV rather than directly from the data source object. The DSV wizard retrieves the schema information including relationships so that joins between tables are stored in the DSV. These relationships help cube and dimension wizards to identify fact and dimension tables as well as hierarchies. If the relationships do not exist in the data source we recommend you to create the right relationships within the DSV. Defining the relationships between the tables in the DSV helps you to get a better overview of your data warehouse in the DSV. When you take the time to create a DSV it ultimately pays for itself in terms of speeding up the design of your data warehouse.

Back in Chapter 2 you used the DSV wizard to create a view on the Sales fact table in Adventure Works DW. The DSV wizard is a great way to get a jump-start on DSV creation. Then, once the DSV is created you can perform several operations ...