SYSPRO Analytics > SYSPRO Analytics Administration

SYSPRO Analytics Administration

You use this program to:

  • Create the SSIS packages in the MSDB database

  • Create and maintain the SQL Server Agent jobs

  • Run diagnostics to assist in troubleshooting

This program can be run standalone, or from the Administration tab in the SYSPRO Analytics Setup program.

Once you have successfully configured your Analytics Servers and Sites, as well as enabled your companies and operators, you are then ready configure your jobs.

Analytics toolbar and menu

Field Description
Tools  
Logging
Option Description
Client side Create logging files for troubleshooting on the SYSPRO Analytics client workstation.

This is the same as entering the system diagnostics code SAN001 in the Admin System Setup program.
Server side Create logging files for troubleshooting on the SYSPRO Analytics server.

This is the same as entering the system diagnostics code SAN002 in the Admin System Setup program.
Save additional logging data Save the return values from business object calls and Analytics server side calls as XML.

This is the same as entering the system diagnostics code SAN004 in the Admin System Setup program.

[Note]

This has been separated from the standard logging options, as it creates a lot of extra data.

Save T-SQL scripts Save the executed SQL scripts to the temp folder.
Options Change any of the SYSPRO Analytics options.
Help  
Contents Open the SYSPRO Help window.
About SYSPRO Analytics View information regarding the version of SYSPRO Analytics installed on the client workstation.
Main Menu Show the Menu pane (left pane) if this pane was closed.
Summary List Show the Task pane (right pane) if this pane was closed.

This only applies to Setup and Administration.
Master Database Settings Select this to:

  • View the details of the created master database (including the server name, authentication details and the database details).

  • Recreate the master database, if required.

  • Load the standard Analytics scripts into the master database.

Job Maintenance

A SYSPRO Analytics job refers to the process of extracting data from the SYSPRO database and transferring that data to the SYSPRO Analytics data warehouse.

The job also transforms and cleans some of the data, ensuring that there are valid links between the Dimension and Transaction tables.

The final step of the job is to load the data into the Analytics cubes and process it so that the data becomes available for analysis (the ETL process).

The more regularly the job is executed, the more recent data is available. Therefore it is good practice to execute this job automatically using the scheduler, once a day during non-productive hours.

Toolbar

Field Description
New Add a new SYSPRO Analytics job.

When you select this, the SYSPRO Analytics Job Wizard is displayed.
Delete Delete a job.
[Note]

When you delete a job, the job as well as it's history and schedule are deleted from the SQL Server Agent in SQL Server Management Studio.
Save Save a job.
Refresh Refresh the list of existing jobs as well as the history displayed in the History pane.

You can use this to monitor the progress of the job in the History pane as it is executed.
Start Start the job.

If the job immediately fails on the first step:

  • check if the SQL Server Agent is running on the Analytics SQL Server.

  • ensure that the Distributed Transaction Coordinator service (which is not started by default) is running on the Analytics SQL Server, if you selected the Run ETL with transaction option.
Stop Stop the job.

SYSPRO Analytics Job Wizard

When you select New, the SYSPRO Analytics Job Wizard is displayed.

This wizard helps you to create a job whereby you select the following:

  • site against which the job will run

  • job details (i.e. the build type and dimension process type)

  • job step options

  • the companies to which the job applies

  • the cubes for which you want data transferred

  • the job schedule

Field Description
Site Selection  
Analytics site name Select the name of the previously created SYSPRO Analytics site that you want to create the job for.
Job Details  
Job name Enter a name for the job.
Description Enter a description for the job (optional).
SQL category Enter a SQL category (optional).
Enable job Enable the job as soon as it is created.

If a job is disabled, then the SQL Server Agent will not execute it.
Build type Select the type of objects that you want to process.

You can select either Cubes or Analysis Objects.

By default you should select Cubes as this will build the cubes and ensure that the data is available in the SYSPRO Analytics Viewer.
Dimension Process type Select how you would like to process the dimensions:
[Note]

If you are running the job for the first time, you need to select Process Full so that all cubes/analysis objects are fully processed, otherwise the job will fail.

OptionDescription
Process DefaultDetects the process state of the dimension and performs processing necessary to deliver unprocessed or partially processed dimensions to a fully processed state.
Process FullProcesses the OLAP database and all the objects that it contains. When Process Full is executed against an object that has already been processed, Analysis Services drops all data in the object, and then processes the object.
Process DataProcesses data only without building aggregations or indexes. If there is data in the partitions, it will be dropped before re-populating the partition with source data.
Recreate SSIS packages Enable this option to recreate all the SSIS packages.

If this is the first time that the job is going to be executed, you need to select this to ensure that all the required SSIS packages are created.
Run ETL within transaction Enable this option to run the ETL process within a SQL transaction.
Job Step Options You need to select at least one of the job step options, otherwise there will be no steps to add to the job and you cannot add a job without steps.
[Note]

We recommend that you select all three of these options, unless you are an advanced Analysis Services user.
Process ETL packages Add steps to the job to execute the ETL packages in order to extract, transform and load data from the SYSPRO database to the Analytics database.
[Note]

This is selected by default.

If you deselect this, then the option Run ETL within transaction cannot be selected and the ETL packages are not added as job steps to the job.
Process Dimensions Add steps to process the dimensions that affect the cubes selected.
[Note]

This is selected by default.

If you deselect this, then the steps to process the dimensions are not added as job steps to the job.
Process Cubes Add steps to process the cubes.
[Note]

This is selected by default.

If you deselect this, then the steps to process the cubes are not added as job steps and the steps to process the stored procedures are not added.
Companies A list of SYSPRO companies that you enabled (in the Company Details pane in the SYSPRO Analytics Setup window) is displayed.

Select the SYSPRO companies that you require the job to extract data from.

A set of ETL packages for each company will be created.

The Dimensions and Cubes packages are not company-specific.
Cubes A list of all the available cubes is displayed.

Select the cubes you require the job to update.

If you are executing the job for the first time, Select All to ensure that all the cubes are updated.
Schedules A list of available schedules is displayed.

Select one of the applicable existing schedules.

If none of the existing schedules are appropriate, then you can create your own.

This is an optional step, and if you do not select or create any schedules, then the job will be run manually.
New Add a new schedule.

When you select this, the Add New Schedule window is displayed.
Change Change an existing schedule.

When you select this, the Maintain Schedule: SYSPRO Analytics window is displayed, which is similar to the Add New Schedule window.
Delete Delete an existing schedule.

Details tab

If you select an existing job, then the job's details are displayed in the Details pane.

Field Description
Job Details  
Name This indicates the name of the job.
Description This indicates the description of the job.
Enabled Enable or disable the job.
[Note]

If a job is disabled, then the job will not be executed by the SQL Server Agent.

SQL Schedules tab

If you select an existing job, then a list of all the existing schedules is displayed in this pane. You can select or deselect the job schedules that you want to maintain.

Field Description
Add Add a job schedule.
Change Maintain the selected job schedule.
Delete Delete the selected job schedule from the SQL Server Agent in SQL Server Management Studio.

History tab

If you select an existing job which has been executed before, then a list of the job history is displayed in this pane.

A line entry is created every time a job is executed. If you collapse the line entry, you can view the details per step executed.

When a job is busy executing, then In-progress is displayed in the Status column against the job.

Field Description
Delete Delete the history entry of a job from the SQL Server Agent in SQL Server Management Studio.

Add New Schedule window

When you select New in the Schedules pane, then the Add New Schedule window is displayed.

Field Description
Schedule name Enter a unique name for the schedule.
Schedule type Select the schedule type.

You can select either Recurring or One time.
Enabled Enable or disable the job.
[Note]

If a job is disabled, then the job will not be executed by the SQL Server Agent.
One-time occurrence If you selected One time as the schedule type, indicate the Date and Time that the job must be executed.
Frequency  
Occurs Select how often the job must be executed.
Recurs every Select how often the job execution must recur.

Depending on your selection at the previous option, different options are available.
Daily Frequency  
Occurs once at If the job must be executed at a specific time, indicate the time that it must be executed.
Occurs every Select the job execution interval (e.g. every 4 hours).
FieldDescription
Starting atSelect the start time of the job execution interval.
Ending atSelect the end time of the job execution interval.
Duration Select the Start and End date of the date period that the job schedule is valid for, or if the job schedule is valid indefinitely.
Summary  
Description Description of all the scheduling options you have selected.
Create/Update Create the new job or update the existing job that you have changed.
Cancel Cancel out of the program.

Once the Add New Schedule wizard is complete, it will do the following:

  1. Create the SSIS packages on the SQL server defined as the Integration Server against the SYSPRO Analytics site configuration. These SSIS packages are created within the MSDB database.

    You can view these packages in SQL Server Management Studio within the Integration Services SQL Server (\Server\Stored Packages\MSDB\SYSPRO Analytics\SYSPRO Analytics Site 1\Company 1).

    These packages are created per company selected.

  2. Create a job, including:

    • job steps to run the SSIS packages per company

    • job steps to run the stored procedures and SQL views

    • job steps to process the dimensions (one step per dimension). These steps are configured as SQL Server Analysis Services XML commands

    • job steps to process the cubes (one step per cube). These steps are configured as SQL Server Analysis Services XML commands.

The data is therefore extracted from the data source (normally the SYSPRO SQL database), transformed and loaded into the SYSPRO Analytics OLAP database.

You can view the job, job steps and details within the SQL Server Agent in SQL Server Management Studio.

Notes and warnings

SYSPRO Analytics Job Wizard considerations

  • If you select Run ETL with transaction within Job Details, then you must ensure that the Distributed Transaction Coordinator service (which is not started by default) has been started on the Analytics server.

    If this service is not running, the job will fail on the first step.

  • If the company you are looking for is not displayed in the Companies window, then:

    • check if the company is included in the SYSPRO Analytics site

    • check if the company is enabled for analysis.

Hints and tips

Adding a new job

  • Ensure you have enabled the job, otherwise it will not be processed.

  • If you select the Recreate SSIS packages option, then all SSIS packages will be overwritten, including customized packages. If there are customized SSIS packages it is good practice to rename the packages so that they are not overwritten.

  • If you select the Run ETL with transaction option, then you must ensure that the Distributed Transaction Coordinator service (which is not started by default) has been started. This allows for roll back if there is an interruption when transferring data (i.e server crash).

    SQL Server will roll back the transaction including updating the sysaf_SourceFilters table. This ensures that when you next transfer data there will be no duplicates.