Platform Deployment Guide › Data Warehouse › Advanced Configuration › Enable the Data Warehouse on SQL Server Express CMSs

Enable the Data Warehouse on SQL Server Express CMSs

If your data warehouse is hosted in a SQL Server Express database, you must manually enable and schedule the processing jobs that populate the data warehouse.

To enable the data warehouse on SQL Server Express CMSs

1. Create a SQLCMD script to run data warehousing jobs.

   Specifically, save the following SQL Server query to a batch file, such as Data_Warehouse_Job.cmd:

   sqlcmd -E -S "<host>\<instance>" -d "WGN_<server>" -Q "EXEC rut_dlp_aggregation_process"
   

   Where:

   -E

   Specifies a Windows-trusted database connection.

   Note: Verify that the scheduled task runs as a Windows user who has a SQL Server login that has been granted the db_owner privilege on the WGN_<server> database.

   -S "<host>\<instance>"

   Specifies the host server and a named (non-default) instance of SQL Server. For example:

   -S "localhost\SQLEXPRESS" 
   -S "UXW2K8DLP1\SQLEXPRESS" 
   

   If you omit the ‘-S’ parameter, the script uses the default instance of SQL Server on the localhost.

   To specify the default instance of SQL Server on a host server, you only need to specify "<host>".

   To specify the host server and the instance of SQL server using a static port number, specify the host server (without an instance name) and port number, separated with a comma:

   "<host>,CA Portal" 
   

   For example:

   -S "UXW2K8DLP1,1629"
   

   Note: You may need to specify a static port if your organization disables the SQL browser service for security reasons.

   -d "WGN_<server>"

   Defines the database name. This database name incorporates the name of your CMS host server. For example:

   -d "WGN_UXW2K8DLP1"
   

   -Q

   Specifies the database SQL command that initiates the data warehouse processing job:

   "EXEC rut_dlp_aggregation_process"
   

2. Set up a scheduled data warehousing aggregation task using the Windows Task Scheduler:
   1. Create a task to run the data warehousing batch file that you previously created.
   2. Configure the task to run as the Windows user account that corresponds to the appropriate SQL Server login.
   3. Specify a task schedule as required. We recommend that the data warehousing job runs hourly.

More information:

Change the Job Frequency