Expert SQL Server™ 2005 Integration Services

6.1. SSAS ETL Processing and Management Overview

Enterprise BI systems often have recurring ETL schedules that are predicated on the availability of source data. Often, these schedules are set to run nightly when either a source system is idle or has downtime, or when some prior batch process or precedence is complete. However, there are times when scheduling is required more or less frequently, such as when an inventory system sends data updates weekly or monthly, or on the flip-side, when data is made available several times a day. You may even have a situation that contains a diversity of processing schedule requirements, from monthly to near real-time.

The common thread in most of the enterprise ETL processes is that the load times are known. The ETL schedules kick off at either pre-defined times, or by scheduling tools that work in conjunction with other environment precedence. This can be for ETL jobs that run every five minutes to once a month. And, when a job finishes, the scheduling tool or package that coordinates execution can spawn off another process.

ETL scheduling is important in a discussion on SSAS processing because, in the majority of cases, when an Enterprise BI system involves SSAS, the processing of the SSAS objects is the responsibility of the ETL subsystem. Therefore, you must take into consideration the aspects and methods involved in it.

To be sure, a handful of BI solutions are real-time or very near real-time, meaning the transactions that are happening ...