Process coresuite cubes with customize

Introduction

When installing coresuite cube, the load jobs will be triggered by default at midnight. What is, when the CFO makes some financial transactions and want to see the result immediately in his Excel-Report based on the cube? He would need to contact the IT guy, which needs to start the relevant jobs in the SQL server. First, he must start the load job, and then the process job.

If this process is too complicated for the CFO, it makes sense to create an SAP menu with coresuite customize. He would be able to start the jobs by himself.

Start SQL server jobs via SQL

With a simple SQL expression it is possible to start a job. For example starting the job ETL_Sales_Load_SwissDemo88 can be done as follows

EXEC [msdb].dbo.sp_start_job 'ETL_Sales_Load_SwissDemo88'

Create a customize rule which executes the queries

With the knowledge of the EXEC queries, we can do a small customize rule which executes those two queries: One for loading, and one for processing. We want to place the rule in the cube folder in SAP and create a new menu rule as follows:

The code in the rule should look as follows. Be sure, that you rename the name of your 2 jobs

 /*
***** General Information *****
Creator: coresystems ag, grg
Create Date: 2011-04-19

***** StartConfDesc *****
This rule loads the sales cube via new menu located in the cube folder.
The load job will be started directly in the second step, the process job in the first step (just to be sure, that
SQL server first loads, and the processes)
***** EndConfDesc *****

***** Updates *****
*/

string ruleName = pVal.RuleInfo.RuleName.ToString();
string errorMessage = "Error in New Menu '" + ruleName + "'";

try
{
    // Please replace the job names
    string loadJob = "ETL_Sales_Load_SwissDemo88"; // This is the load job
    string processJob = "ETL_Sales_Process_SwissDemo88"; // This is the process job

    SwissAddonFramework.Utils.SQL.ExecuteQuery("EXECUTE msdb.dbo.sp_start_job @job_name = '" + loadJob + "', @step_name= '" + loadJob + "'");    
    SwissAddonFramework.Utils.SQL.ExecuteQuery("EXECUTE msdb.dbo.sp_start_job @job_name = '" + processJob + "'");

    MessageBox.Show("Cube jobs started successfuly", "OK");
}

catch(System.Exception ex)
{
    MessageBox.Show(errorMessage + ": \n" + ex.Message, "OK");
    Debug.WriteMessage(errorMessage + ": " + ex.Message, Debug.DebugLevel.Exception);
}

Result

The CFO can now easily use the SAP menu to start the jobs. After waiting some seconds or minutes (depends on the database size and IT setup), he can refresh his Excel or whatever and see the updated figures:

HINT: It makes sense to restrict this rule only to specific users. It is suboptimal, when dozens of users start at the same time cube load jobs!

Download

You can download the rule here: NewMenuLoadCube