Professional SQL Server™ Analysis Services 2005 with MDX

4.4. Multiple Data Sources within a DSV

Data warehouses usually consist of several data sources. Some examples of data sources are SQL Server, Oracle, DB2, and Teradata. Traditionally, the OLTP database is transferred from the operational data store to the data warehouse — the staging area combines the data from the disparate data sources. This is not only time intensive in terms of design, maintainability, and storage, but also in terms of other considerations such as replication of data and ensuring data is in sync with the source. Analysis Services 2005 helps you avoid this and gives you better return on your investment.

The DSV designer provides you with the capability of adding tables from multiple data sources from which you can build your cubes and dimensions. You first need to define the data sources that include the tables that are part of your data warehouse design using the data source wizard. Once this has been accomplished, you create a DSV and include tables from one of the data sources. This data source is called the primary data source and needs to be a SQL Server. You can then add tables in the DSV designer by right-clicking in the diagram view and choosing Add/Remove Tables. The Add/Remove Tables dialog allows you to choose a data source as shown in Figure 4-22 so that you can add tables from that data source. You should be aware that there might be performance implications due to retrieving data from secondary data sources since all the queries are routed through ...