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

In Analysis Services 2000, certain limitations were associated with the use of
data sources. Analysis Services 2000 only supported one fact table per cube.
Therefore, only one data source could be used for specifying the fact table of
a cube. You could still specify multiple data sources within Analysis Services
2000, because dimensions referenced did not have to be in the same data
source as the fact table. A workaround addressing the single fact table con-
straint was to create a SQL view on the multiple fact tables to create what ap-
peared to Analysis Services 2000 as a single fact table. A more common and
straightforward solution adopted by many users was to have multiple cubes
based on disparate data sources and combine them into a single cube that
was called a virtual cube.
Analysis Services 2005 natively supports the capability of specifying multiple
fact tables within a single cube. Each of these fact tables can be from a differ-
ent data source. Analysis Services 2005 still provides you with the capability
of creating what are essentially virtual cubes; this is accomplished using
linked objects (discussed in Chapter 9 ) . Since Analysis Services 2005
provides you with the capability of creating cubes from various data sources
you need to be extremely careful about how you model your cube — that is,
you must specify the right relationships (primary key and foreign key map-
pings) between tables from various data sources. In this way you can make
sure your cube is designed to provide you the results you want.
Using the Analysis Services 2000 data source methods is like carving on a
bar of soap with a butter knife: you could create a statue, but it might not win
any awards for beauty. Conversely, the kind of power and flexibility in Analys-
is Services 2005 puts you in a position similar to that of carving a bar of soap
with a razor blade. Carving with a razor blade, you can make a gorgeous and
intricate statue, but if you're not careful, you could cut the heck out of your fin-
gers. So, be careful and craft some beautiful dimensional schemas! To do so,
keep your schemas as simple as possible relative to the flexibility require-
ments imposed by the application specification you're working with.
Data Sources Supported by Analysis Services
Strictly speaking, Analysis Services 2005 supports all data sources that ex-
pose a connectivity interface through OLE DB or .Net Managed Provider. The
data sources need to support certain basic methods based on the interface
that conform to the standard since Analysis Services 2005 uses those meth-