While we can't describe in detail the solution to every problem that you might encounter when configuring or working with SYSPRO Analytics, we have provided guidelines for identifying and troubleshooting the most common problems encountered.
The following topics are covered:
-
General setup
-
Analytics servers
-
Analytics sites
-
Analytics companies
-
Analytics operators
-
Analysis objects
-
Analytics jobs
-
Analytics viewer
-
Dimensions table
- General setup
- Analytics servers
- Analytics sites
- Analytics companies
- Analytics operators
- Analysis objects
- Analytics jobs
- Analytics viewer
- Dimensions table
The SYSPRO Analytics module will not be accessible until you have made the following critical changes to your SYSPRO configuration:
-
Ensure that the following services are all running with the correct permissions (i.e. as a Windows user with the sufficient permission) on the relevant SQL servers:
-
Microsoft Analysis Services
-
Microsoft Integration Services
-
SQL Server Agent
-
Microsoft SQL Server
-
Distributed Transaction Coordinator
This service is required to run SSIS packages within an ETL transaction.
![[Note]](images/note.png)
If you do not use this service, then you must disable the Run ETL within transaction option when creating a job.
In an environment where the company database and the SYSPRO Analytics database are on different machines, this service is required to be configured and running on both machines.
-
-
Import the Analytics license.
-
Assign Analytics Viewer seats to the relevant operators.
-
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.
When checking the permissions configured for each component service and the user related to it, open SQL Server Management Studio and follow these steps:
-
Select the applicable Role and open the Login Properties.
Ensure that the following Server roles have been enabled as required:
The sysadmin permission is not required for ETL users.
-
Public
-
sysadmin
-
-
Open the User Mapping tab and ensure that:
-
the correct database has been enabled under Users mapped to this login
-
the following Database role memberships are enabled:
These permissions are not required for sysadmin users.
-
db_datareader
-
db_datawriter
-
public
-
-
The following list describes some of the factors that should be considered if you are experiencing problems with your Analytics server and/or it's configuration:
-
In a live environment, ensure that you always enable the SYSPRO Analytics Client to connect via the Remote via Web service.
A Local Connection server is only used for testing or demonstration purposes.
-
Ensure that there are no proxy servers which are preventing access to the Administration and Business Layer services.
-
If you wish to make any changes to the configuration of your analytics server, follow these steps in sequence:
-
Stop the Business Layer Service
-
Update the server details and save your changes
-
Restart the Business Layer Service
-
The following list describes some of the factors that should be considered if you are experiencing problems with your Analytics site and/or it's configuration:
-
Ensure that the Collation configured in the Site Details is the same for the Analytics site database, the SYSPRO database and the Analytics server.
Generally, this should default to be your company's database collation.
-
When selecting multiple companies to be linked to an Analytics site, ensure that all companies enabled use the same database collations.
-
Ensure that the fiscal periods setup in the Site Details matches your calendar.
-
If you cannot connect successfully to the Test Analysis Server Connection, 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 Layer Service
-
-
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.
| Error message | Description and Remedy | ||||
|---|---|---|---|---|---|
| Errors in the OLAP storage engine: The attribute key cannot be found when processing: Table: 'x', Column: 'x', Value: 'x'. The attribute is 'x' | This error occurs when the site's database collation
is not the same as SYSPRO's database collation of
Latin1_General_BIN. To resolve this error, a new site will have to be created with the collation set to Latin1_General_BIN.
|
||||
The following list describes some of the factors that should be considered if you are experiencing problems with your Analytics companies:
-
A SYSPRO Analytics company name cannot include any unacceptable characters such as ", #, &, *, . or ,.
-
If you disable a company for analysis then you will not be able to add Analytics jobs to that company, however existing jobs will still continue to work.
The following list describes some of the factors that should be considered if you are experiencing problems with your Analytics operators:
-
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 administrator and viewer users are specified by the SYSPRO system administrator in the Operators program.
-
The Operators permissions set in this program are only valid when using the SYSPRO Analytics Viewer.
If you are using a third 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.
The following list describes some of the factors that should be considered if you are experiencing problems with your analysis objects:
-
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.
-
When configuring the History retention under the Analysis Object Details, do not select the option None.
There is currently a bug in the SYSPRO Analytics program that could cause the jobs to fail if this option is enabled.
The following list describes a few factors that should be considered if you are experiencing problems with your Analytics jobs:
-
Dimensions are shared (as seen in the Dimensions table), and for a cube to be processed, all related dimensions need to be processed.
Therefore it is recommended that you do not process single cubes as this will affect other cubes with unprocessed data.
-
Ensure that job steps are executed in the correct order.
| Error | Description and Remedy |
|---|---|
| None of the Dimensions and Cubes steps succeed when running the SQL job. | When this type of error occurs it could be for
various reasons, therefore try each of the following
solutions:
|
| OLE DB error: OLE DB or ODBC error: Login failed for user | This error occurs if the Local
Service account does not have access to the
Analytics databases. To solve this, change the user which is set against the SQL Server Agent to one that does have permission, or enable the correct permission against the user. |
SSIS steps populate the Stage tables from the SYSPRO production tables where specified.
The following is an example of the steps followed in an SSIS package:
Once the Stage tables have been populated, the Fact_**_Insert procedures are executed.
These Fact_**_Insert procedures are stored procedures which insert data into the Fact tables, and conform to the following rules:
-
All expired records are deleted.
Whenever data changes in SYSPRO (i.e. sales order line), the original Stage table record is flagged as expired in the database (i.e. the DateValidTo field is populated with a value). This record is then deleted and replaced with the new / changed record (i.e. new sales order line) in the Dim/Fact table.
-
All dimension ID's that have changed are updated (e.g. customer name change) in the Fact table as follows:
-
The original Customer Name record is flagged as expired
-
A new record is added with the new Customer Name and a new ID
-
The old ID is then replaced with the new ID in all of the related tables
-
-
The formatted data is then inserted into the Fact table, including the joins to the dimensions for each Fact insert.
Dimension types:
-
Dimensions populated from the Stage tables (i.e. these are the SQL views within the analytics database)
-
Hard coded system dimensions (i.e. these are the Dim_* tables - the data inserted via procedures when the Analytics site database is created)
-
Dimensions derived from within the design using the Fact table information
When dimensions process, Analysis Services formulates and runs queries against Dimension tables to return information that is required for processing.
Finally the OLAP Cubes are processed.
Processing a cube causes processing of all the measure groups within the cube and the dimensions that are currently in an unprocessed state. This is the last step and ties all the loose ends together.
If there were any errors, or steps missed, the cube will not process.
The following list describes some of the factors that should be considered if you are experiencing problems with the SYSPRO Analytics Viewer:
-
If a value is returned in the SYSPRO Analytics Viewer in a scientific format, set the Text format for the field to be Standard.
This setting is located in the Commands and Options menu.
-
If an operator is unable to view certain cubes, confirm that the correct access is configured in the operator's cube settings within the SYSPRO Analytics Setup program.
-
There are row limitations when working with Office Web Component (OWC), therefore a work-around for this limitation is to analyse large amounts of data in Microsoft Excel.
You can then use Excel's full functionality by adding charts, etc.
| Error message | Description and Remedy |
|---|---|
|
Could not load file or assembly
'Microsoft.Office.Interop.Owc11,version=11.0.0.0,
Culture=neutral, PublicKeyToken=71e9bce111e9429c' or one of
its dependencies.
The system cannot find the file specified. |
This error occurs if the correct version of Office Web Components has not been installed on the client machine. |
| No Details: The query could not be processed: 0 The data provider didn't supply any further error information | When this type of error occurs it could be for
various reasons:
|
