In order to have the latest data presented in the Dynamics AX 2009 OLAP Reports, such as the ones contained in Role Centers, the SQL Server Analysis Services cubes must be processed regularly. Typically, processing the cubes once a day is sufficient for most basic business needs. However, for companies that utilize business intelligence more than average, this may not be sufficient. The more frequently cubes are processed, the more up-to-date that data in the OLAP reports is.
Many common automated procedures for SQL Server, such as backing up a database, can be performed by using SQL Maintenance Jobs. Unfortunately, you cannot automatically process SQL Server Analysis Cubes the same way. In order to automatically process cubes, a SQL Server Integration Services package must be created and scheduled to run as an SQL job. The following steps outline the process of automating the cubes:
1. Load SQL Server Business Intelligence Developer Studio (BIDS).
2. Create a new Integration Services Project.
3. Add a new connection to the Integration Services Project by right-clicking on the Control Flow tab and click on New Connection... and select MSOLAP100 for an Analysis Services connection.
4. Provide the appropriate connection information. If the Analysis Server is on a separate server than the Database Engine and Kerberos Authentication are setup, append
;SSPI=Kerberosto the end of the connection string. To specify additional connection options and to test the connection, click on the Edit button.
5. In the Connection Manager window, specify the Initial Catalog to the Analysis Extension's OLAP database. Test the connection to verify that it works. Once complete, click on the OK button to save. Then, click on OK in the Add Analysis Services Connection Manager window to finally save and create the connection manager.
6. Now that the connection is set up, drag over an Analysis Services Processing Task from the Toolbox.
7. Right-click on the task and Edit the task.
8. In the Analysis Services Processing Task Editor, select Processing Settings and click on the Add button to select which cubes you want to have processed.
9. After you have selected the appropriate cubes to be processed and they are added to the Analysis Services Processing Task Editor, click on OK to save.
10. In Visual Studio, build the project by going to Build | Build Process Cubes.
11. Once the project builds successfully, the next step would be to create a SQL job that runs the SSIS package on a specific schedule. To create a job, open the SQL Server Management Studio and log in to the Database Engine server and select the SQL Server Agent node.
12. In the SQL Server Agent, right-click on the Jobs folder and click on New Job... to create a new job that will automatically process the cubes.
13. In the New Job window, in the General page, provide a Name for the job.
14. Select the Steps page and click on the New button to create a new job step that will run the SSIS package to process the cubes.
15. In the New Job Step window, specify a name for the step, change the type to SQL Server Integration Services Package, select Package source to be File system, and browse for the dtsx file that was built in Visual Studio from step. When the appropriate fields are specified, click on the OK button to create the step.
16. Now that we are back in the New Job screen, go to the Schedules page and click on the New button to specify a recurring date and time to process the cubes.
17. In the New Job Schedule window, provide a name, date, and time information which the job should run automatically on. Click on the OK button when complete to create the schedule.
18. There are additional pages in the New Job window, such as providing an e-mail to contact, which can be used when a job errors or completes. To finally create the job, click on the OK button.
Now that the SQL Job has been created, to process cubes, the job will run the SSIS package that was created to process cubes automatically at the specified date and time.