Creating Matrix Data Import Files

Import updates to metric values in a matrix into Accolade using two specially named reference tables. The reference table pair begins with ACC_PMI_, which identifies the tables as reference tables that contain matrix update import data. Include only one matrix in a data/configuration file pair. Each matrix you want to update requires its own pair of reference tables.

Note:  To import this type of data to Accolade, you must have the Process Designer, Project Importer, and Reference Table Manager user roles. To import a new version of an existing file, you must be assigned as the owner of the reference table file used for the import.

Follow these steps to import matrix updates:

• Create a file that contains the data to upload (Data table).
• Map the data to fields in the Accolade database (Config table).
• Upload, validate and run the data import.
• Troubleshoot the import.

Accolade supports reference tables as spreadsheet, CSV, and XML files through the Reference Tables page, and through an auto-loader service. The information and examples provided are in spreadsheet format, contact Sopheon Customer Support for example files in additional formats.

Create a File That Contains the Matrix Update Data to Upload (Data Table)

Important! The matrix import deletes an existing matrix referenced in the data table and completely replaces it with the values you provide in the file. Therefore, you must provide values for all the rows in the matrix. If a row is missing or a value is left blank, the existing values in the matrix are replaced with a blank value on import.

The data table contains the data to import to Accolade. Administrators and Process Designers add the initial versions of the ACC_PMI data table. The assigned table owner, which requires the Reference Table Manager user role, can add new versions of the table as needed.

Create the matrix data table as a spreadsheet, CSV, or XML file, ensuring that the contents of the file meets the following requirements:

Component	Requirements
File Name	Matrix updates must be in a file named ACC_PMI_<MatrixSystemName>_Data, where <MatrixSystemName matches the <MatrixSystemName> in the matrix update configuration table and is unique within Accolade. For example, ACC_PMI_ConsumerElectronics_Data. There must be only one pair of tables that contain a given matrix system name. Accolade can contain multiple different data/config reference table pairs to update multiple matrices.
Rows	Each row in the file represents a row in the matrix.
Column Names and Position	Column headings are in the first row of the worksheet. You can name columns as you see fit for your installation. Each metric within the matrix must be in a separate column, and the file must contain all the metrics within the matrix. Note that rich text metrics cannot be imported. Name the columns that contain metric data with their metric name and their data type. For example, ProjRisk_List, or Milestone_Date. This helps to identify the type of data required in the column. The configuration table described below maps the columns in the data table to the respective fields in the Accolade database.
Data	The following columns and data are required for each matrix to update: Project ID or Metric Name - Either the Project ID that displays on the project header, or a string metric that identifies the project. Process Model Name - Depending on your system's configuration, this may display on the Project Home page. One column for each non-calculated metric in the matrix. It is not necessary to include a blank column for a calculated metric included in the matrix. The data type of each metric has to match the data type of its reference table column in the Data table except that list metrics are in string type columns. The data type of a reference table column is created automatically when the first version of the table is created, but it can be edited by the table owner.

Download Sample

Map the Matrix Data to the Accolade Database (Config Table)

The configuration table contains the data mapping information so Accolade knows what type of data to expect in the accompanying data file. Administrators and Process Designers add the initial versions of the ACC_PMI config table. The assigned table owner, which requires the Reference Table Manager user role, can add new versions of the table, as needed.

Create the matrix configuration table as a spreadsheet, CSV, or XML file, ensuring that the file meets the following requirements:

Component	Requirements
File Name	The matrix update configuration must be in a file named ACC_PMI_<MatrixSystemName>_Config, where the <MatrixSystemName> matches the matrix updates data file and is unique within Accolade. For example, ACC_PMI_ConsumerElectronics_Config. There must be only one pair of tables that contain a given matrix system name. Accolade can contain multiple different data/config reference table pairs to update multiple matrices.
Rows	Each row maps a column in the data table to a metric or a metadata item.
Column Names and Position	The matrix update configuration must have the following column names, from left to right as listed below: Export Column Name - Enter each column heading that is included in the data table. Metric System Name - Enter the system name of the metric whose column heading is in the same row. Project Metadata Name - Enter the metadata name below that identifies the data included is in each column in the data table. Unique Identifier - Enter TRUE in the either the ProjectCode row or a row that contains a string metric that specifies which column in the data table contains the data that identifies the project in which to update the matrix values. Only one string metric can serve as the identifier for the import.
Accolade Metadata Names	Use the following metadata names in the Project Metadata Name column of the configuration table to map the data to the appropriate locations in the Accolade database: ProjectCode ProcessModelName - Required whether ProjectCode or a string metric is used.

Download Sample