2.0 Understanding Data Connections and Flow
The various SQL Server and Analysis Services components make data connections. The SQL Server Agent on the Data Mart server reads from the source database, and writes to the Data Mart and Data Warehouse relational databases. The SQL Server agent owns the ETL job. The ETL job is the process by which data is extracted from an AppManager repository, transformed to a format appropriate to the Data Warehouse, and then loaded into the Data Warehouse.
The SQL Server Agent on the Data Warehouse server creates and reads from the Data Mart database, reads from and writes to the Data Warehouse relational database, and invokes the SSIS packages that process the cubes in the Data Warehouse multidimensional database (the OLAP processing job).
The Analysis Services on the multidimensional database server read data in the Data Warehouse relational database, as well as views in that database that are based on fact data in the Data Marts.
A legend describing the connections, and how Analysis Center gets its data follows the illustration.
Connection 1: Data Mart SQL Server Agent to source SQL Server: This connection is defined when you create a Data Source from the Analysis Center Console. This connection can use either Windows or SQL Server Authentication, depending on the security configuration of the source SQL Server and the choices you make during configuration of the Data Source.
This connection is used to copy data from the source database.
Connection 2: Data Mart SQL Server Agent to Data Mart SQL Server database . This connection is defined when you install the Analysis Center Data Warehouse. This connection can use either Windows or SQL Server Authentication, depending on the security configuration of the Data Mart SQL Server and the choices you make during the Data Warehouse installation.
This connection is used to write source data to the Data Mart database and to prepare that data for the Data Warehouse.
Connection 3: Data Mart SQL Server Agent to Data Warehouse SQL Server . This connection is defined when you install the Analysis Center Data Warehouse. This connection can use either Windows or SQL Server Authentication, depending on the security configuration of the Data Mart SQL Server and the choices you make during the Data Warehouse installation.
This connection is used to copy data from the Data Mart to the Data Warehouse relational database.
Connection 4: Data Warehouse SQL Server Agent to Data Mart SQL Server . This connection is defined when you install the Analysis Center Data Warehouse. This connection can use either Windows or SQL Server Authentication, depending on the security configuration of the Data Mart SQL Server and the choices you make during the Data Warehouse installation.
This connection is used to create the Data Mart databases.
Connection 5: Data Warehouse SQL Server Agent to Data Warehouse relational database . This connection is defined when you install the Analysis Center Data Warehouse. This connection can use either Windows or SQL Server Authentication, depending on the security configuration of the Data Warehouse SQL Server and the choices you make during the Data Warehouse installation.
This connection is used to further process data.
Connection 6: Data Warehouse SQL Server Agent to Analysis Services . This connection is defined when you install the Analysis Center Data Warehouse. This connection must use Windows authentication.
This connection is used to initiate cube and dimension processing.
Connection 7/8: Analysis Services to Data Warehouse SQL Server to Data Mart SQL Server . This connection is defined when you install the Analysis Center Data Warehouse. This connection can use either Windows or SQL Server Authentication, depending on the security configuration of the Data Warehouse and Data Mart SQL Servers and the choices you make during the Data Warehouse installation.
This connection is used to get data for cube and dimension processing from the Data Warehouse relational database and from the Data Mart database. The Data Warehouse relational database contains dimensional data and views to fact data in the Data Mart database; the Data Mart database contains fact data.
For more information about Analysis Center components, and how Analysis Center works, see the NetIQ Analysis Center User Guide.