Summary

You now have the skills to deal with the challenges real-world data warehouses will throw at you in terms of multiple data sources. You learned about OLE DB and managed data providers that are supported by Analysis Services 2005 to retrieve data from data sources and trade offs of using one versus another. Indeed, you learned to tame the disparate data source beast by using multiple data sources. Then you learned to consolidate the tables and relationships of interest in Data Source Views (DSVs), and finally, to prune the tables and relationships in the DSVs so you only have to deal with what's relevant.

Note that when key changes are made in the DSV, that is where the changes stay — in the DSV. The changes are not written out to the underlying tables as you might expect. This is a good thing. To see why, take a look at the alternative to using the DSV capability. The alternative method is to create a view in SQL with real relationship transforms in the underlying tables. It's not that we strongly oppose this method, but if your data spans multiple databases, you have to create linked servers and that can become time consuming. Analysis Services 2005 provides an easy way to specify these cross-database relationships within a DSV without the overhead of creating linked servers. However when multiple data sources are included in a single DSV the primary data source should support the ability to send queries and retrieve results from other server. You can incur a performance degradation due to this method; however you do have the flexibility of not having to manage the data on multiple servers to create your data warehouse.

You're doing great! In fact, you're ready to tackle core business intelligence constructs like dimension design (Chapter 5) and cube design (Chapter 6). If you already know these topics from working with Analysis Services 2000, we recommend working through the chapters anyway; there have been some important changes.