Working with Data Sources and Data Source Views - Professional SQL Server Analysis Services 2005 with MDX - page 161

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 ware-
house — 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 ensur-
ing 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 mul-
tiple 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 ac-
complished, 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 the primary data source.