Run the Initial ETL Job

In the following steps you will connect to the Data Warehouse SQL Server instance and run the initial Extraction, Transformation & Loading (ETL) SQL Server job “StudentAnalytics <data warehouse database name> Initial Load” to move data from the source database to the warehouse and process the Semantic Model databases.

Student Analytics 5.3.0 provides an option to run the initial load process in two steps:

  • First, the “StudentAnalytics (Data warehouse database name) Initial Load (Process Model Excluded)” job can be run to load data up to data warehouse.

  • Later. the “StudentAnalytics (Data warehouse database name) Process Model” job can be run to process the analysis model database.

This will reduce the application downtime during the upgrade process.

  1. Launch Microsoft SQL Server Management Studio on the server where the SSIS Catalog is installed.

  2. Navigate to SQL Server Agent > Jobs > StudentAnalytics (<Data warehouse database name>) Initial Load.

    Server job for ETL

    The "Age Refresh" job is scheduled to run at 12.00.00 am on the first day of every month. This job updates all Student, Prospect, CRM Contact, and Lead age data.

    Analytics 4.1 adds the “Student Account Aging Snapshot” job, which is scheduled to run every day at 12.00.00 a.m. This job creates monthly Student Account Aging Snapshots for current and past 1 year and a snapshot for the current month.

  3. Right-click and select Start Job at step, select Step 1 in the Start Jobs window, and click Start.

    Depending on the size of the database, this job may take minutes or hours. Observe the Status value while the process is running. Upon completion of the job, the Start Jobs window displays a Success message.

    You can also view reports on the execution of the ETL packages by navigating to Integration Services Catalogs > SSISDB > <Catalog Folder Name>. Right-click <Catalog Folder Name>, then navigate to Reports > Standard Reports > All Executions. Here ‘StudentAnalytics’ is the SSIS Catalog folder name specified during installation. The reports provide error messages and associated details that are useful to troubleshoot any issues with the ETL packages. There are packages for each of the tables that are synchronized between the source and the warehouse.

  4. After the initial ETL job completes successfully, disable the initial Load job and make sure the following jobs are enabled:

    • StudentAnalytics <data warehouse database name> Age Refresh
    • StudentAnalytics <data warehouse database name> Cleanup
    • StudentAnalytics <data warehouse database name> Incremental Load
    • StudentAnalytics <data warehouse database name> Student Account Aging Snapshot

    Leave the StudentAnalytics <data warehouse database name> Process Model job in disabled mode, because processing Semantic Model databases is now part of the Incremental Load job. The Process Model job can be used to manually process Semantic Model databases when needed.

Incremental updates from the source database to the warehouse are performed automatically based on the schedule on these jobs. The default schedule is daily once at 1.00.00 a.m.