KPI integration

The article describes the integration product KPI Integration.

Solution Description

From which systems can KPI data be integrated into Stratsys?

We have the capability to import KPI data into Stratsys from any system that can deliver data in one of the formats described here or via the Stratsys API as described here. We have existing KPI integration from systems including QlikView, Qlik Sense, department4, Cognos, Heroma, Power BI, Mercur, Skola24, Vitec, and DF Respons.

 

Technologies

Stratsys offers two different technologies for integrating KPI data.

File Import is one option, which involves placing files in an SFTP directory that is then read into Stratsys. The import into Stratsys occurs according to the following schedule: a scheduled job runs on a server at Stratsys that reads these files and updates the KPIs in Stratsys in accordance with the information in the file.

The adapter is installed once in Stratsys through an implementation project together with a project manager and Integration Consultant at Stratsys. After the project, we provide the necessary conditions for you to be able to import data into additional KPIs.

The second option offered is KPI integration via the Stratsys API. This requires the customer to have programming skills and be able to develop against a REST API. You will then have access to the Stratsys API for KPI import.

 

Prerequisites

• The organizational tree in Stratsys needs to be mapped to the organization in the system from which data will be retrieved. Mapping is done using external identifiers in Stratsys. Generally, a 1-to-1 mapping between an external identifier and an entity applies. The identifier identifies an entity in Stratsys.
• KPIs need to be created in Stratsys. We recommend collecting your KPIs in a KPI bank from which you can then distribute the metrics to where they will be visualized. This simplifies the administration of KPIs.
• An external identifier needs to be defined for the KPI to create a common language between Stratsys and the external system. If there is no existing identifier for the KPI, Stratsys integration consultants will help you create identifiers.
• The customer needs to deliver pre-aggregated data by organizational departments in Stratsys, KPI, and according to the desired periodicity. Stratsys cannot handle data conversion.
• The customer needs to deliver data in one of Stratsys file formats or via REST API calls in accordance with the format defined here.

Appearance of the Import File

The columns should be tab-separated and contain the following information:

• Date (determines the period in which the value is saved)
• External identifier for the department
• External identifier for the KPI
• External identifier for the KPI column
• The actual numeric value to be imported

For the import to be successful, a period mark must be used as the decimal separator (not a comma). Additionally, there should be no thousand separators. For example, the value "25 610,34" will not be read correctly and must be changed to "25610.34". Negative values are acceptable. Dates should be in the Swedish format yyyy-mm-dd, e.g., "2020-12-24". Decimal points, percentage signs, and thousand separators can be added directly in Stratsys; you can get help from your consultant with this during implementation. The file import is not case sensitive, meaning that if you have a capital letter where the external identifier is in lowercase, the import will still work.

There are two types of layouts:

• Row-wise import: One row for each value to be imported.
• Column-wise import: Each row represents all the KPI columns to be updated for the KPI.

For both types of import (both row-wise and column-wise), it is required that there is a value in all value fields; no NULL entries are allowed.

Row-wise Import

The file should be named: “kpidata_yyyy-mm-dd.txt” or “kpidata_yyyy-mm-dd_header.txt”.

If the "header" option is used, the first row in the file will be interpreted as a header row and ignored during the import.

To find the correct KPIs and update the right values, Stratsys needs to know the following:

• Date (Swedish format yyyy-mm-dd)
• External identifier for the department
• External identifier for the KPI
• External identifier for the KPI column
• Value (decimal separator is a period '.')

Example of a row-wise import file with a header:

Column-wise Import

• The file should be named: "kpicolumndata_yyyy-mm-dd.txt" or "kpicolumndataheader_yyyy-mm-dd.txt".

  If the "header" option is used, the first row in the file will be interpreted as a header row that indicates which KPI columns the file pertains to. The number of KPI columns is limited by the KPI column names or external identifiers in the header row.

  If the file does not contain a header row, the KPI columns will be read in the order they are set up for the KPI in Stratsys.

  To find the correct KPIs and update the right values, Stratsys needs to know the following:

  • Date (Swedish format yyyy-mm-dd)
  • External identifier for the department
  • External identifier for the KPI
  • Value - decimal separator is a period (.)

Example of a column-wise import

Setup Guide

Pre-Startup Instructions

Before your startup meeting, please reflect on and answer the following questions. If you need support or guidance, contact your project manager or account manager for assistance.

1. Which KPI do you want to import data for into Stratsys?

   • List the specific KPI you plan to track and import into Stratsys.

2. To which KPI columns do you want to import data?

   • Examples of KPI columns include: accumulated outcome, budget, forecast, outcome for women/men.
   • Specify the KPI columns you will use.

3. To which organizational departments do you want to import data within the project?

   • Identify the organizational departments that will be receiving the data.

4. Where is the data located in the external system?

   • Provide details on the data source and its location within the external system.

5. Does the data need to be aggregated or adjusted to be readable in Stratsys?

   • For example, aggregation by departments in your organizational tree in Stratsys, data adjustments such as summing cost centers, or similar modifications.

6. Which technology do you want to use? File import (and if so, which file format do you prefer?) or API?

   • Decide on the technology and format you will use for the integration.

7. Do you have the right resources allocated to the project? The resources needed are:

   • A person with a business perspective who can act as a requirements setter and quality assurer in the project.
   • A person knowledgeable about extracting data from the external system to be integrated with Stratsys. In most cases, a consultant from the other system is needed to participate in the project.
   • A person from your IT department who can configure the integration flow on your side, such as handling SFTP information or API clients.
   • A person with overall responsibility/project manager from your side.

   Note that it is entirely possible for one person to fulfill multiple roles.

By answering these questions, you will be well-prepared for the startup meeting and can ensure that all necessary aspects of the integration are considered.

Step-by-Step Implementation Guide

Regular review meetings will be held as needed throughout the implementation.

1. Startup Meeting:

   • Gather the project team.
   • Set the timeline and review the points under "Pre-Startup" to define the project's goals and scope.

2. Define External identifiers for departments, KPIs, and KPI columns:

   • Work with a Stratsys integration consultant to determine the external identifiers for departments, KPIs, and KPI columns.
   • For more information on what external identifiers are in Stratsys, please watch the provided video.

3. Enter External identifiers in Stratsys:

   • Once the external identifiers are defined, email them to the Stratsys integration consultant.
   • The consultant will then import the external identifiers into Stratsys.

4. Create Test Database (Optional):

   • If you prefer to test in a test environment, request a test environment from your integration consultant.

5. Prepare Data in External System According to Stratsys Format:

   • As the customer, you are responsible for extracting data in the Stratsys format.

6. Create SFTP Area/API Client Linked to Stratsys:

   • The Stratsys integration consultant will create either an SFTP area for file import or an API client for integration with the Stratsys API, depending on the chosen technology.
   • The necessary credentials and details will then be sent to you.

7. Test and Validate in Test Database (Optional):

   • Test sending data to a test database in Stratsys via SFTP or API, depending on the chosen technology.
   • As an initial step, you may also manually import the file to ensure the format is correct.

8. Test and Validate in Production Environment:

   • Test sending data to the production database in Stratsys via SFTP or API, depending on the chosen technology.
   • As an initial step, you may also manually import the file to ensure the format is correct.

9. Closing Meeting:

   • Gather the project team to conclude the project.
   • Review the project goals and officially hand over to maintenance.

Maintenance and Support

Email Notifications for Errors and Warnings

It is possible to enable the function to receive emails if the file import generates warnings or errors. To activate this function, go to Administration > Source Systems and External Systems. Select the wrench icon for the source system and then the wrench icon to the left of File Import for KPI Data and Activity Costs. In the text box under Send email for errors/warnings to these email addresses, enter the email address you wish to receive notifications.

Manual File Import

As a complement, for troubleshooting or testing purposes, it is also possible to manually import files directly into Stratsys. This can be done under Administration > Source Systems and External identifiers > External Systems. Select the wrench icon for the source system and then the wrench icon again for File Import for KPI Data and Activity Costs.

Add and Change External identifiers for departments, KPI, and KPI columns

Add and Change External identifiers for departments, KPI, and KPI columns

External identifier for departments

Scenario – "We want to integrate KPI data for an existing KPI to a new department in the organizational tree"

How to do it:

1. Create the department in Stratsys.
2. Add an external identifier to the department in Stratsys.
3. Ensure that the KPI node is assigned to the new department:
   • Assign the KPI to the new department by hovering over the KPI's name and clicking on the edit icon. Go to the departments tab and check the new department in the list.
4. Ensure that data for the new department is included in the file from the source system.

Administration of external identifiers for organizational departments can be done in two ways. One option is to go to Administration > Organization and follow the steps shown in the video below.

The second option is to, after creating the department as shown in the video above, go to Administration > Source Systems & External identifiers > External identifiers to add an external identifier. Select the appropriate source system from the "Select Source System" dropdown and departments from the "External identifier" dropdown. Check "Show all departments" to also display departments without an external identifier. To add a new external identifier to a department, click the green plus sign, enter the external identifier in the pop-up window, and save (see video below). To adjust an existing external identifier, select the wrench icon for the department, adjust the external identifier in the pop-up window, and save.

1. External identifiers for KPI

   Scenario – "We want to integrate data for a new KPI from our external system"

   How to do it:

   1. Create the KPI in Stratsys.
   2. Add an external identifier to the KPI in Stratsys:
      • Add an external identifier to the KPI by hovering over the KPI's name and clicking on the edit icon.
      • Go to the Settings tab, scroll down, and click on External identifier. Enter the external identifier for the KPI on the row for the relevant source system integration and click Save.
   3. Ensure that data for the new KPI is included in the file from the source system.
   
   

External identifiers for KPI columns

1. Scenario – "We want to integrate data into an additional column for an existing KPI, such as a forecast"

   How to do it:

   1. Ensure the column exists on the KPI:

      • Add a KPI column to the KPI by hovering over the KPI's name and clicking on the edit icon.
      • Go to the Settings tab to view the KPI columns and select +Add Column to add a new KPI column. The KPI column will be added specifically for the KPI you are working on. If you want the KPI column to be a standard for newly created KPIs, you can add the KPI column in a KPI template.

   2. Add an external identifier to the column:

      • Go to Administration > Source Systems & External identifiers and select External identifiers.
      • Choose the correct source system and KPI columns under "External identifier".
      • Select "Add" to add an external identifier for a new KPI column.
      • In the pop-up window, enter the correct name of the KPI column as it exists in Stratsys. Under External identifier, enter the external identifier for the KPI column.

   3. Ensure that data for the new column is included in the file from the source system.

   
   

Troubleshooting

You can find the import log under Administration > Source Systems and External identifiers > Import Log.

The purpose of the import log is to serve as a tool for easily checking whether a file import has gone through successfully. Any errors in the import file are captured in the import log, making it easy for users to identify and resolve these errors.

The most common types of errors are mapping errors (i.e., an external identifier is incorrectly set up or missing) or that KPI, columns, or departments being imported are not set up in Stratsys.

1. Start by selecting the source system from which you want to check file imports.

   • This can be done in the first drop-down menu.

2. You can choose to filter by status depending on what you want to see in the log:

   • Information
   • Warnings
   • Errors

3. You can also filter by date if you are interested in seeing a specific file import that has been done.

Import without Errors

When the import goes through without errors, it looks like the following:

Errors: 0, Warnings: 0, Successfully updated KPIs: 5 (the number of KPIs in your file with updated values).

Import with Errors

Example Above: The Import Has Generated 5 Warnings

Row 2 and 3: "Unable to find kpi" - This means we tried to import a KPI that does not exist in Stratsys. Alternatively, the KPI may be misspelled in the file or in the mapping context for the relevant KPI.

Solution: Verify that the KPI is set up in Stratsys. If it exists, check the spelling of the KPI in the mapping context compared to what was sent in the import file.

Row 4: "Could not find department for identifier 50" - This means there is no department mapped with the external identifier 50 in Stratsys. Alternatively, the department identifier may be misspelled in the mapping context or in the file.

Solution: Check the department mapping in the mapping context for the relevant source system – is the identifier misspelled or missing in the mapping context?

Row 5 and 6: "Unable to find kpi column" - This means we included a column that does not exist in Stratsys. Alternatively, the column may be misspelled in the file or in the mapping context in Stratsys.

Solution: Verify that the column exists in Stratsys for the relevant KPI. Also, check that the spelling matches in the mapping context compared to the file.

In the document "Manual externkodsmappning" you can read how to check and update your external identifiers for departments, KPIs, and KPI columns.

Error in Kolada Import

If you get an error in your Kolada import, such as "No KpiData found in external system," it may be because Kolada has removed the KPI or data for the department you are trying to import.

You can search at Kolada Free Search Tool to see if the external identifier still exists. Read more about the Kolada integration here: KPI from Kolada.

• • Checklist for Possible Errors

    1. Ensure that the department, KPI, and KPI column have external identifiers that match 100% with what is coming in via the file.

    2. Review the import log for the selected source system and check the following:

       • Has there been a run for the current source system today?
       • Does the issue seem to be with the specific KPI? Look for the external identifier for the KPI, department, and/or KPI column.

    3. Investigate the event history for the relevant KPI:

       • Entries marked with "Automatjobb system" are updated from the file import.

File Import Schedule

The import job runs at the following times every day:

• 05:00
• 06:00
• 07:30
• 08:00
• 08:30
• 09:00
• 09:30
• 10:00
• 10:30
• 11:00
• 11:30
• 12:00
• 12:30
• 13:00
• 13:30
• 17:00