Professional SQL Server™ Analysis Services 2005 with MDX

4.1. Data Source

In order to retrieve data from the source you need information about the source such as name of the source, type of method to retrieve the data, security permissions needed to retrieve the data etc. All this information is encapsulated into an object called Data Source in Analysis Services 2005. An Analysis Services database contains a collection object called Data Sources which stores all the data sources needed to build cubes and dimensions within that database. Analysis Services will be able to retrieve source data from data sources via the OLEDB interface or using the managed .Net provider interface.

In the simplest case you will have one data source that contains one fact table with some number of dimensions linked to it by joins; that data source is populated by data from an OLTP database and is called an Operational Data Store (ODS). Figure 4-1 shows a graphical representation of this data source usage. The ODS is a single entity storing data from various sources so that it can feed as a source of data for your data warehouse.

Figure 4.1. Figure 4-1

A variant on the data source usage, which is enabled by the UDM in Analysis Services 2005, is the ability to take data directly from the OLTP system as input to the decision support software. This is shown in Figure 4-2.

Figure 4.2. Figure 4-2

In Analysis Services 2000, certain limitations were associated ...