SYSPRO Analytics > SYSPRO Analytics Setup

SYSPRO Analytics Setup

You use this program to configure the following required components for SYSPRO Analytics:

  • Master database

  • Analytics servers

  • Analytics sites

  • Analytics companies

  • Analytics operators

You can also use this program to perform various database diagnostic tasks, as well as maintain your global variables, analysis objects and templates.

Summary implementation

Once you have prepared SYSPRO to be able to accommodate Analytics, you need to start with the actual Analytics setup.

These setup steps are critical to the successful use of Analytics and should be completed in the following sequence:

  1. Create a master database

  2. Connect to a local or remote Analytics server

  3. Create an Analytics site

  4. Enable a company for analysis

  5. Configure operator securities and permissions

  6. Apply Global Variables (optional)

  7. Define Analysis Objects (optional)

  8. Create or modify templates (optional)

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.

Options

Field Description
Tools  
Reset Settings Reset the settings to their default values.
Save Save the selected settings.
Apply Settings Apply the selected settings.
General Settings  
Save settings on Close Save the configured settings when you close the Options window.
Flash Validation Icons Select this to cause the validation icons displayed on dialog boxes to flash when the error message changes.
Use Separate Tab for each Menu Item Select this to cause the items selected in the treeview menu to be loaded into individual tab panes.

If you do not select this, then the items are loaded into the same tab page.
Display Settings  
Overall visual theme Change the overall visual theme used in SYSPRO Analytics.

If you select this, then you cannot select the individual theme settings below.
Color theme Select the overall background color to be used in the SYSPRO Analytics programs.
Shortcut Bar Settings  
Shortcut Bar Theme Change the shortcut bar theme.

This is only available if you select {none} as the overall visual theme.
Listview Settings  
Column style Change the column style.

This is only available if you select {none} as the overall visual theme.
Property Grid Settings  
Property Grid Style Change the style of the property grid.

This is only available if you select {none} as the overall visual theme.
Docking Pane Settings  
Splitter Style Change the appearance of the splitter bar between windows and panes.
Splitter Size Change the size of the splitter bar.
Tab Appearance Change the appearance of tab bars.

This is only available if you select {none} as the overall visual theme.
Docking Theme Apply a theme to the docking panes in SYSPRO Analytics.

This is only available if you select {none} as the overall visual theme.
Toolbar Settings  
Toolbar theme Change the toolbar theme.

This is only available if you select {none} as the overall visual theme.

Master Database Settings

When you open SYSPRO Analytics Setup from the SYSPRO main menu for the first time, the SYSPRO Analytics Master Database Settings window will open and you will be prompted to create the SYSPRO Analytics Master Database.

You cannot perform any tasks until you have created this database, as it stores the Analytics setup information and contains all the information about Analytics that is not site specific.

This database can be accessed from either the client or the server, and its implementation ensures easier control and updating of the data.

Field Description
SQL Server details  
Sql server name Select the SQL server on which the master database must be created on.

This can be any SQL server and does not have to be the server used for SYSPRO Analytics.

[Note]

If you are using a different server, or if you are using NT authentication, then you need to ensure that the master database server can access the SYSPRO Analytics server.
Sql server version This indicates the version of SQL Server installed on the selected SQL Server.
How should SQL Server verify the authenticity of the Login ID? Indicate to connect with Windows NT authentication or SQL Server authentication.
Login ID Enter the login ID for the SQL server.
[Note]

This field is only available if you selected SQL Server as the authentication method.
Password Enter the password for the SQL server.
[Note]

This field is only available if you selected SQL Server as the authentication method.
Test SQL Server connection Test the connection to the SQL server using the selected login options.
[Note]

The system must successfully validate your entries before you will be allowed to proceed to the database details.
Database details  
New database name The default database name is SYSPRO_Analytics_Master. You can change this if required.
Use existing database Enable this option if you have already created the master database, and indicate your database selection from the drop down menu.
Collation The default is Latin1_General_BIN.

We recommend that you use this collation.
Recreate Master database Recreate the master database.

You can use this option if you suspect that the master database is corrupted.
Update Scripts Load the standard Analytics scripts (saved in the ..\Base\SysproAnalyticsData\Scripts folder on the SYSPRO application server) into the master database.

SYSPRO Analytics Servers

A SYSPRO Analytics server refers to the physical installation of the SYSPRO Analytics server components. By linking the SQL server name and port number (for remote connection) to the Analytics server, you create a unique server.

The Analytics Servers program allows you to:

  • Add and delete the connection to previously installed Analytics server components

  • Create local or remote servers

  • Start and stop the business layer service for a remote Analytics server

  • Update the Analytics server

The SYSPRO Analytics Server installation installs the following required services:

  • SYSPRO Analytics Administration Service

    This service starts and stops the Business Layer Service, checks to see if the server is available, and provides an update mechanism.

  • SYSPRO Analytics Business Layer Service

    The Business Layer Service contains the business layer logic.

The SQL Server Analysis Services server is where multidimensional data is provided in response to client queries. A client will connect to the server, ask for multidimensional data and the server will then publish data to the client. It is also the area where pre-calculated summaries of data are stored.

When you design your cubes, you design the calculated summaries of data (aggregates) and the SQL Server Analysis Services server is the physical place where this data resides.

SQL Server Integration Services provides tools for moving data from SYSPRO to Analytics.

Toolbar

Field Description
New Create a new SYSPRO Analytics server.
Delete

Delete a SYSPRO Analytics server.

[Note]

Before you can delete a server, you need to stop the SYSPRO Analytics Business Layer service.
Save Save the SYSPRO Analytics server.
[Note]

This option is not available if you are creating a remote server via the web service and you did not test the administration layer connection.
Refresh Refresh the list of existing servers in the SYSPRO Analytics Servers pane.
Start Start the SYSPRO Analytics Business Layer service on the selected SQL server.
[Note]

You cannot start the SYSPRO Analytics Business Layer service if the SYSPRO Analytics Administration service is not running on the server.
Stop Stop the SYSPRO Analytics Business Layer service on the selected SQL server.
Update Update the SYSPRO Analytics server with any changes or upgrades made to Analytics.

The client and the SYSPRO application server allow for automatic self-healing of the Analytics client, however, as there is no communication between the SYSPRO application server and the Analytics Server, automatic self healing cannot occur.

This means that updates are saved on the client and the SYSPRO Analytics administrator will manually self heal the program to the SYSPRO Analytics server.

Server Details

Field Description
Server Details  
How should the SYSPRO Analytics client connect Indicate which server type you require:

  • Local connection

    A local server is used for testing or demonstration purposes only. In a live environment, you must select Remote via Web service.

  • Remote via Web service

    If you use a remote server, then you need to install the SYSPRO Analytics Server component from the SYSPRO installation DVD onto the SQL server that you are going use as the SYSPRO Analytics server.
Remote Server Settings  
Server Enter the server name where the SQL Server and SYSPRO Analytics Server components are installed.
[Note]

You must enter only the server name if you are running instances of SQL server.

For example, if you are running two SQL server instances, TESTSQL\2005 and TESTSQL\2008, then you need to enter TESTSQL as the server.
Server connection method Indicate your connection method of HTTP or NET.TCP.
[Note]

If you want to change the connection method or the ports, you will need to:

  1. Stop all services

  2. Maintain the port entries and/or connection method in the BusinessChannel.xml file

  3. Restart the services
Administration port The default port to be used for the Analytics administration service is 8086.

We recommend that you use this port, unless its already used for another service.
Business Layer port The default port to be used for the Analytics business layer service is 8087.

We recommend that you use this port, unless its already used for another service.
Administration Web service path This path is generated automatically according to your selections.
Test Administration Layer connection Test the connection to the remote server.

A successful connection is made if the server is accessible and if the SYSPRO Analytics Administration Layer service is running on this server.

[Note]

If you cannot connect to the remote server, you need to:

  1. Check that you entered a valid SQL server name.

  2. Check that the SYSPRO Analytics Administration Layer service is running.

    If it is not running, then you need to start the service using the Services program in Windows.

SYSPRO Analytics Sites

A SYSPRO Analytics site is a single and unique instance of the Analytics server and can be understood as being a copy of the Analytics server. This is so that any administration that needs to take place will be done on the site instead of on the server, thus keeping the data complete for other sites that could be hosted on the same server. Furthermore, a single site is able to obtain information from multiple companies and/or other data sources.

A SYSPRO Analytics site is made up of the following parts:

  • Microsoft SQL Server database

    This database holds the SYSPRO Analytics data and is both a staging area and a data warehouse. The Temp and Stage tables form part of the Staging area and the Fact and Dim tables (or views) form the data warehouse portion.

    The data that is in this database is both cleansed and uncleansed. In other words, it is a replication of the SYSPRO tables and Fact tables which have been cleansed for analysis purposes.

  • SQL Server Analysis Services OLAP database

    This database reads the Microsoft SQL data warehouse.

Toolbar

Field Description
New Create a new SYSPRO Analytics site.

If selected, the SYSPRO Analytics Site Wizard window is displayed and will guide you through the creation and configuration of the new site (Analytics Site Wizard).
Delete Delete a SYSPRO Analytics site.

If you delete a site, all the relevant details are deleted from the master database. However the actual SYSPRO Analytics site database and Analysis Services OLAP database are not deleted.

You can choose to delete the SSIS packages.
Save Save the SYSPRO Analytics site.
Refresh Refresh the list of existing sites that are displayed in the SYSPRO Analytics Sites pane.
Re-Index Re-index the tables in the site database.

Analytics Site Wizard

When you select New from the SYSPRO Analytics Sites toolbar, the SYSPRO Analytics Site Wizard is displayed.

Field Description
Welcome screen This screen displays information regarding the required information for the setup of the new SYSPRO Analytics site.
Site Details  
Analytics server name Select the name of the Analytics server.

This server must have been configured using the SYSPRO Analytics Servers program.

Remote servers have the server name as well as the port used for the SYSPRO Analytics Business Layer Service in the server name, for example Remote on ‘SQLSERVER:8087’.

[Note]

If there are no available servers displayed in the drop-down list, then you did not add any SYSPRO Analytics servers yet.
Description Enter a description for the Analytics site.
Prevent further logins Enable this to lock the site and prevent operators from logging into it.

This value can later be changed in the Site Details pane.
Reason Enter a reason for locking the site.

This reason is displayed to operators attempting to log into this site.

[Note]

This option is only available if you selected Prevent further logins.
Date dimension range Ensure that the date dimension range you select here is over a long period, starting at least 20 years prior to the current year, and 50 years into the future.
FieldDescription
Start DateSpecify the initial date range that must be populated for this site's date dimension.
End DateSpecify the initial date range that must be populated for this site's date dimension.
[Note]

When you select Next in the wizard, the business tier connection is tested automatically.

If the Next option is disabled, then it means that the SYSPRO Analytics Business Layer Service is not running. You need to start this service using the Start option in the SYSPRO Analytics Servers pane.
SQL Server Details  
SQL server

Select or enter the SQL server that will host the Analytics site database.

If you are using named instances on your SQL server, then you should enter the full SQL server name, for example TESTSQL\2008.
SQL server version This indicates the SQL Server version and is updated when you select to test the SQL connection.
Authentication method Select the authentication method to use when connecting to the selected SQL server.
Login id

Enter the login for the SQL server.

[Note]

This field is only available if you selected SQL Server as the authentication method.
Password Enter the password for the SQL server.
[Note]

This field is only available if you selected SQL Server as the authentication method.
Test SQL Connection Test the connection to the SQL server.
[Note]

You need to do this before you can continue.
Use existing SQL database Use an existing SQL database if the site database has been created before.
Database name Enter a name for the site database or select an existing database.

The default database name is the site name with invalid characters removed.
Collation The default is Latin1_General_BIN.
[Note]

This must be the same as the company collation.
Analysis Server Details  
Analysis server Select the Analysis SQL server that will host the Analytics OLAP database.
[Note]

  • Microsoft SQL Server Analysis Services must be installed in multi-dimensional mode and must be running on this server.

  • The collation of the server must be the same as the site.

  • Analytics does not support instances of Microsoft Analysis Services.
Analysis server version Indicates the version of Analysis Services that are installed on this server.
Analysis server collation Indicates the collation of the Analysis Server.
Test Analysis Server Connection Test the connection to the SQL server.
[Note]

  • You need to do this before you can continue.

  • You can only connect to the Microsoft Analysis Server using Windows NT authentication.

If you cannot connect successfully, then you need to:

  • Check that Microsoft Analysis Services are installed on the SQL server

  • Ensure that the SQL Server Analysis Services service is running

  • Ensure that you can connect to the default instances Microsoft Analysis Server in the SQL Server Management Studio

  • Check the user permissions against the business service
Use existing OLAP database Use an existing OLAP database if it has been created before.
Database name Select an existing OLAP database, or enter an OLAP database name.

The default database name is the site's SQL database name.
Integration Server Details  
Integration server Select the name of the SQL Server that will host the Analytics SSIS packages.

This is the server where Microsoft Integration Services are installed and running, and must be the same version as the SSAS server.
Operators A list of SYSPRO operators is displayed.

Select the SYSPRO operators that will have access to this site.

Only operators that are either set up as Analytics administrators and/or Analytics viewers are displayed in this list.

[Note]

One operator can have access to more than one site.
Companies A list of SYSPRO SQL companies is displayed.

Select the SYSPRO companies that must be included in this site.

After including them in the SYSPRO Analytics site, you still need to enable them for analysis using the SYSPRO Companies pane in the SYSPRO Analytics Setup window.

Once you have entered all the information into the SYSPRO Analytics Site Wizard program, you can confirm the information and finish the wizard.

The wizard will then do the following:

  1. Validate all the selected information.

    If any of the information entered is invalid, you can use the Back option to correct it.

  2. Connect to the SYSPRO Analytics 7.0 Business Layer Service on the SYSPRO Analytics server defined.

  3. Create the site database.

  4. Create the staging tables within the site database.

  5. Run the required SQL scripts (stored in the SYSPRO Analytics master database) to create the analysis objects and load them with the initial date dimension values.

  6. Save the site, operator and company information to the SYSPRO Analytics Master Database.

  7. Create the initial date dimension entries.

  8. Create the OLAP database.

Site Details tab

Field Description
Site Details  
Description This is the site description. You can change the site description at any time.
Analytics server name This indicates the server name of your Analytics server (i.e. the server on which the SYSPRO Analytics Server components are installed).
Site version This is the version of SYSPRO on which Analytics is installed.

This is used to determine whether or not the Analytics database has been converted for the new version of SYSPRO, and as such whether or not the user can log into the site.
Prevent further logins Lock the site and prevent operators from logging into it.
Reason Enter a reason for locking the site.

This reason is displayed to operators attempting to log into this site.

[Note]

This option is only available if you selected Prevent further logins.
Convert Site to latest version Select this to load the site conversion function.
[Note]

This is only available if the site version is not the latest version.
Additional detail  
SQL database This indicates the name of the site database.
SQL database Collation This indicates the collation of the site database.
Analysis server name This indicates the SQL server where Microsoft Analysis Services are installed and the OLAP database is hosted.
Analysis server version The version of Analysis Services installed on this server.
Analysis server collation This indicates the collation of the Analysis server.
Analysis database This indicates the name of the Analytics OLAP database.
SSIS server name This indicates the name of the SSIS Server that is hosting the Analytics SSIS packages.
Date dimension range
[Note]

Ensure that the date dimension range you select here is over a long period. Starting at least 20 years prior to the current year, and 50 years into the future.
Start date Select and save a start date to be used for the initial date range that must be populated for this site's date dimension.
End date Select and save an end date to be used for the initial date range that must be populated for this site's date dimension.
SQL Authentication  
SQL server This indicates the SQL server name, including the specific SQL instance that is hosting the SYSPRO Analytics site database.
SQL server version This indicates the version of Microsoft SQL Server installed on this server.
Authentication method Select the authentication method to use when connecting to the selected SQL server. You can select either Windows NT or SQL Server authentication.
Login id Enter the login for the SQL server. We recommend you create a dedicated user account in SQL called SYSADMIN.
[Note]

This field is only available if you selected SQL Server as the authentication method.
Password Enter the password for the SQL server.
[Note]

This field is only available if you selected SQL Server as the authentication method.

Operators tab

This pane indicates a list of SYSPRO operators that are linked to the site, including if these operators have access to the SYSPRO Analytics Viewer program and/or if these operators are SYSPRO Analytics administrators.

Within this pane you can select or deselect operators to be linked to the SYSPRO Analytics site, however you cannot deselect the currently active operator.

Companies tab

This pane indicates a list of all the SYSPRO companies that are linked to the site.

Within this pane you can select or deselect companies to be linked to the SYSPRO Analytics site.

Fiscal Periods tab

This pane allows you to match the fiscal period to specific calendar dates or periods.

Field Description
Fiscal Period 1 - 12 Enter the relevant calendar period against the specific fiscal period if your company's fiscal year is not the same as the calendar year.

For example, if your company's financial year is April to March, then you can enter Period 4 against Fiscal Period 1 and follow the entries from there (Fiscal Period 12 will be Period 3).

SYSPRO Companies

Analytics companies simply refer to companies within SYSPRO that have been identified and enabled for SYSPRO Analytics.

The Analytics Companies program allows you to:

  • Enable companies for analysis.

    When you enable a new company for SYSPRO Analytics, the following occurs:

    • a record is created in the Master database that contains the connection information for the company

    • SQL passwords are encrypted

    • the company ID for Analytics is written into wDim_Company

  • Disable companies for analytics.

  • Change the Analytics company's name.

  • List all SYSPRO companies available for Analytics and if so, which companies are currently selected for analysis.

  • View and/or change SQL authentication details for each SYSPRO company.

    This connection is the one used when extracting SYSPRO data into the SYSPRO Analytics data warehouse. It is recommended that you create a SQL user dedicated to this task that does not adhere to password policies.

Company Details

If you select an available company from the SYSPRO Companies pane, then the details are displayed in this pane.

Field Description
Company Details  
Analytics Company Name This defaults to the SYSPRO’s company name.

  • You can change the name if required.

  • Changing the name here does not affect the company name in SYSPRO.

  • This name must be unique for every company you want to use in SYSPRO Analytics.
SYSPRO Company name This indicates the licensed SYSPRO company name that cannot be changed.
Version This indicates the version of your SYSPRO software.
Format This indicates if the database is C-ISAM or SQL.
[Note]

C-ISAM databases cannot be enabled for SYSPRO Analytics.
Database name This indicates the SYSPRO SQL database name (the data source database) linked to the company.
Enable company for Analysis Enable analysis for this company.

If you do not enable a company, then you cannot create a job in the SYSPRO Analytics Job Wizard program for this company. This means that you cannot extract data from this company's SYSPRO SQL database.

When you enable a new company for SYSPRO Analytics:

  • a record is created in the master database that contains the connection information for the company

  • SQL passwords are encrypted

  • the company ID for Analytics is written into Dim_Company, however this ID is not related to the company ID in SYSPRO.
SQL Authentication  
SQL Server This is the SQL server and instance name of the server on which your SYSPRO SQL database is running.
SQL server version This indicates the version of SQL Server installed on that server.
Use the current operator's SYSPRO SQL credentials Use the current operator's SYSPRO SQL credentials to log into the server on which your SYSPRO SQL database is running.

You define these credentials against the operator in the Admin Operator Browse program.
Authentication method Define the authentication method you want to use for logging into this SQL server.
[Note]

This option is only available if you did not select the option Use the current operator's SYSPRO SQL credentials.
Login id Enter the login for the SQL server.
[Note]

This field is only available if you selected SQL Server as the authentication method.
Password Enter the password for the SQL server.
[Note]

This field is only available if you selected SQL Server as the authentication method.
Test SQL Connection Test the connection to the SQL server.

Operators

In SYSPRO Analytics there are two types of operators:

  • Administrators

    These operators set up and maintain analytics, add and remove permissions for other operators and build templates.

  • Viewers

    These operators work predominantly with the SYSPRO Analytics Viewer and are authorized to view and analyze the company data.

This Operators program is used to view the list of operators and change their permissions, as well as specify which templates, books and cubes an operator with the appropriate permission is allowed to view.

Operators

By default only the operators that are set up as Analytics administrators and/or viewers are displayed in the this pane. You can select the Show All Operators option to show all the SYSPRO operators.

Details tab

If you select an existing operator from the Operators pane, the relevant details are displayed in the this pane.

You use this pane to define the operator access for viewing data in the SYSPRO Analytics Viewer.

Field Description
Operator Details  
Name This is the operator name.
Operator locked out of Analytics Enable this option to prevent the operator from accessing the SYSPRO Analytics Viewer or administration functions.
Reason Enter a reason for locking the highlighted operator out of Analytics.
[Note]

This is only available if you selected Operator locked out of Analytics.
Default site Specify the site that should be selected automatically every time this operator opens the SYSPRO Analytics Viewer.
Viewer Permissions  
View Templates Allows the operator to view Analytics templates.
View Analytics Books Allows the operator to view books created by other users.
Create Analytics Books Allows the operator to create books that will contain their custom views.
Change Analytics Books Allows the operator to change the type and name of a book and/or to set a password for a book.
Delete Analytics Books Allows the operator to delete books that may contain custom views.
View Cubes Allows the operator to view Analytics cubes.

Templates tab

The standard and custom templates, which are defined per site, are displayed in the Templates pane.

You can select which templates the operator has access to view.

[Note]

If you cannot select any templates, then you did not allow the operator access to View Templates.

Books tab

Customized views or books are displayed in the Books pane.

You can select which books the operator has access to view.

Cubes tab

The existing cubes are displayed in the Cubes pane.

You can select which cubes the operator has access to view.

[Note]

If you cannot select any cubes, then you did not allow the operator access to View Cubes.

If the operator does not have access to view the cubes, the operator can still view the templates in the SYSPRO Analytics Viewer.

Global Variables

Within SYSPRO Analytics, a Global Variable is an entry (constant, string or formula) that is applied to all the templates in the SYSPRO Analytics Viewer at runtime. This is a measure that is defined by the Analytics administrator and is included in all the Multi-dimensional Expression (MDX) statements against the templates used in the SYSPRO Analytics Viewer.

[Note]

Because the global variables are only applied to the MDX statements at the Viewer runtime, there is no need to rerun the setup or the jobs in order to apply them.

Examples of global variables could include:

  • Apply a fixed exchange rate to currency values

  • Find a range of data for certain fields

  • Append a description to data outliers

This program is used to add, delete and/or change global variables. However these are optional and you only define them when you are customizing SYSPRO Analytics.

Toolbar

Field Description
New Add a new global variable.
Delete Delete the global variable.
Save Save the global variable.
Refresh Refresh the list of global variables.
Import Import global variable definitions from an XML file.
Export Export global variable definitions to an XML file.

Global Variable Details

Field Description
Name Enter a name for the global variable.
Description Enter a description for the global variable.
Value Enter a constant value, string or formula that defines the global variable.
[Note]

Formulas are not validated by the program.

Analysis Objects

Analysis Objects are the building blocks for creating the templates/cubes you want to analyze and they define the SYSPRO tables that need to be transferred during the ETL processes. This is required to build the dimensions of the business processes that you want to analyze (e.g. product, customer, sales, etc).

You also use this Analysis Objects program to select the Cubes and Dimensions that you want the analysis object to update.

[Note]

Simply linking an analysis object to a cube or dimension will not cause that cube or dimension to automatically reflect the data for the Analysis object. The cube or dimension would have to be customized for this to happen.

Defining Analysis Objects are optional and you only maintain them or add new ones when you are customizing SYSPRO Analytics.

Toolbar

Field Description
New Add a new analysis object to SYSPRO Analytics.
Delete Delete an analysis object.
[Note]

You cannot delete an analysis object if the type is Standard.
Save Save an analysis object.

When you save a standard analysis object, the type is automatically changed from Standard to Custom.
Refresh Refresh the list of analysis objects.
Import Import a previously exported analysis object xml file.
Export Export an analysis object as an xml file.
Show All Analysis Objects View all the analysis objects, not just the analysis objects currently in cubes and dimensions.

Details tab

Field Description
Analysis Object Details  
Name This is the name of the analysis object.
[Note]

An analysis object has been created for every SYSPRO table and given the same name as the table.

Identifying the analysis object with the table name within SYSPRO allows administrators to easily identify the matching Analytics table in the SYSPRO Analytics Server.
Description This is the description of the analysis object.
SSIS Package name This is the name of the SSIS package linked to the analysis object.
Type This indicates the type of analysis object, which can either be standard or custom.
History retention This defines the period of time that the history of this analysis object must be retained.

Expired records can be used to compare values this month against values last month. For example, what was my profit versus stock on hand this month, versus the same measure for last month, or last year.

The default value is All, which indicates that history is not deleted.

[Note]

Selecting All could result in a large amount of data residing in your data warehouse, depending on how you conduct your business.
History period (months) Enter a history period (in months) if you selected the Custom History retention option.
[Note]

Entry is limited to 99999.

Cubes tab

Field Description
Select All Enable the analysis object to update all the existing cubes.
Deselect All Disable the analysis object from updating any of the existing cubes.

Dimensions tab

Field Description
Select All Enable the analysis object to update all the existing dimensions.
Deselect All Disable the analysis object from updating any of the existing dimensions.

Templates

Templates refer to a series of predefined key performance indicators (KPIs) that are shipped standard with SYSPRO Analytics Setup.

The templates are defined in Multidimensional Expressions (MDX) query definitions, and you use MDX to query multidimensional data or to create MDX expressions for use within a cube.

The templates are collections of pre-defined measures.

The Analytics Templates program allows you to:

  • Create a new template

  • Modify an existing template

  • Delete custom templates from the database

Defining templates are optional (you only maintain them or add new ones when you are customizing SYSPRO Analytics).

Toolbar

Field Description
New Add custom templates to SYSPRO Analytics.
Delete Delete a template.
[Note]

You cannot delete a template if the type is Standard.
Save Save changes to a template.
[Note]

When you save a standard template, the type is automatically changed from Standard to Custom.
Refresh Refresh the list of templates.
Import Import a previously exported template xml file.
Export Export a template as an xml file.
[Note]

Developers can export customized templates to an xml file and then import them on the customer workstation.

Template Details tab

Field Description
Name This is the name of the template.
Description This is the template description.
Type This indicates the type of template, which can either be Standard or Custom.

Measures tab

Field Description
New Select this to add a new measure against the template.

When you select this, the Maintain Measure window is displayed and the basic format of the MDX statement is loaded within the MDX Statement pane.
Change/Maintain Change the name of the measure or to edit the MDX statement defined against the measure.
Delete Delete the measure from the template.

Notes and warnings

SYSPRO Setup considerations

The Analytics module will not be accessible until you have made the following critical changes to the SYSPRO configuration:

  1. Ensure that the following services are all running with the correct permissions (as a Windows user with sufficient permission) on the relevant SQL servers:

    • Microsoft Analysis Services

    • Microsoft Integration Services

    • SQL Server Agent

  2. Import the Analytics license.

  3. Assign Analytics Viewer seats to the relevant operators.

  4. Enable the Analytics administrator option against the relevant operator.

Preparation steps 2, 3 and 4 are all done from the SYSPRO Ribbon Bar, and must be performed in sequence.

Analytics Sites considerations

  • SYSPRO Analytics does not cater for the adding of data from data sources other than tables in the SYSPRO data dictionary.

    If you have data in other sources that you want to bring into SYSPRO Analytics, then you will have to engage a BI specialist to do this on your behalf.

Analytics Company considerations

  • If you disable a company you will not be able to add Analytics jobs, however existing jobs will still continue to work.

Analytics Operator considerations

  • A SYSPRO operator can be a SYSPRO Analytics administrator only, or an administrator and a viewer, or a viewer only.

  • A SYSPRO Analytics administrator is not the same as a SYSPRO system administrator.

  • Analytics administrators and viewer users are specified by the SYSPRO administrator in the Operator Maintenance program.

  • The Operators permissions set in this program are only valid when using the SYSPRO Analytics Viewer.

    If using a 3rd party program (e.g. Excel), the permissions would need to be set via SQL Server Analysis Services.

    Any advanced permissions, such as which warehouse an operator is allowed to see, is not carried through from SYSPRO to the OLAP database and will have to be manually created and maintained

Analysis Object considerations

  • Before adding a new Analysis Object, you should first create and develop the SSIS package that defines the data extraction from the SYSPRO table and the data transformation (cleansing of source data).

    You use Business Intelligence Development Studio (BIDS) to develop SSIS packages.

  • Developers can export customized analysis objects to an xml file and then import them on the customer workstation using the Export and Import functions.

Hints and Tips

Multidimensional Expressions (MDX)

  • MDX is a part of OLE DB specification for OLAP. This means that while it is not proprietary to Microsoft, it is part of a greater stand in which they describe the accessing of data.

    They describe how to access multidimensional format data, and have been designed with a specific view of reporting and analyzing against multidimensional formatted data.

    Its primary function is to interrogate multidimensional databases and return results that provide the drag and drop functionality that you see in most analytical viewers.

  • MDX has 2 distinct roles:

    • Query statements

      A very simple select statement/query that is designed to get source data for a report.

    • Formulae

      As part of the query statement it can include formulae on the fly. This is similar to Excel functionality in terms of cells formulae and functions.

    MDX takes the best of query statement language, along with the best of Excel-like functionality and combines the two into a query language.