Troubleshooting

 

CSV Error Handling

If there are any errors relating to the CSV file you are trying to upload, the application will inform you appropriately with an error message and explanation on top of your screen, as highlighted below:

 

 

The only file to which this does not apply is Stage_GL_Data, since that file is typically handled by the Monitor Service, due to its considerably larger size. Any specific issues with Stage_GL_Data will be reported in the "System Messages" tab when you expand Stage_GL_Data (along with a corresponding “CSV Upload Failed” status). The error messages you will receive will be similar in fashion to the error messages you receive via the web front end.

 

The following table explains commonly encountered errors with the files handled by the web front end.

 

Error Type

Cause

 

Invalid Data Type Error

[UPLOAD FAILED]: (fte_date.csv) Bad data was detected. See details below:

  • Sorry, but bad data was detected on row 18 column 7. Value given: FAIL HERE (Expected data type: int)

 

 

A data type is on a certain column that is not of the defined data type. (Example: "hello" appearing on a column that is of type int.)

Nullability Error

 

[UPLOAD FAILED]: (fte_data_null.csv) Bad data was detected. See details below:

  • Sorry, but a null value was detected on row 27 column 2 and the column was defined as not nullable.

A null value is provided on a certain column that is defined as not nullable.

Malformed CSV Error

[UPLOAD FAILED]: (fte_data_malformed.csv) Sorry, but the CSV file is not properly formed on row 20. This is usually caused by an unescaped double quote (") character or an invalid number of columns on the row. To fix this, please validate that any double quote characters within the data in the fte_data_malformed.csv is escaped by replacing the double quote (") with a single quote (') or validate that the number of columns is consistent across the CSV file. Then, please retry the upload.

A Malformed Error means that the application detected that a row of CSV data does not match the number of columns for its destination table. 

Scenarios which cause this include:

  • A row has a varchar column with a comma (,) in the value, but the column was not properly qualified with double quotes

  • Example: "Doe","John",Doe,John --> should be "Doe","John","Doe, John"

  • A row has a varchar column with a double quote (") in the value, but it was not replaced or escaped

  • Example: "Doe","John","Doe, John "Johnny"" -- fixes below

  • Option 1 (Escape the Double Quote): "Doe","John","Doe, John ""Johnny"""

  • Option 2 (Replace the Double Quote With a Single Quote): "Doe","John","Doe, John 'Johnny'"

 

 

Generate Data Integration Layer -- Errors

  1. Click on "Generate Data Integration Layer"

  2. Select Staging Fact Tables to Validate, then Click on "Step 1: Validate Staging Data"

  3. An error message should pop up indicating we detected errors, with a list of missing dimensional data, a list of non-matching calculations on the Stage_GL_Data table, or both.

 

Error Handling -- Missing Dimensional Data

 

 

Missing dimensional data means that rows on a certain fact table contain dimensional values which do not exist in the parent table (Example: Stage_FTE_Data contains an Employee_Code of "01231" but that Employee_Code does not exist in Stage_Employee).

 

Typically, this means that either the dimensional data loaded for the set was incomplete, or the fact table data loaded for the set was invalid.

In order to pinpoint the root cause, you may expand the error list by clicking on the "+" next to the Fact Table with the error. This will expand another list which will indicate what dimensional value is missing as well as how many rows contain the dimensional value.

 

Optionally, you may download a full report or a report for just a specific fact table. In order to do this, select either CSV or Excel in the dropdown of the main (or specific fact table) list. Then click the corresponding Download button. If you are downloading for reporting purposes, it is recommended you use the Excel option.

 

The fact or dimensional data must be re-uploaded in order to alleviate this error.

 

Error Handling -- Table Level Validations

 

 
A number of table level validations are performed before rebuilding the DIL. The table validation errors are designed to inform the user what is wrong with the data set for the table.

 

To get a more detailed view of the error to pinpoint the issue, click on "Download Detailed Error Report" to get an Excel file with a detailed view of the row(s) of data that are failing a particular table validation, or click the "+" next to the table in question to view the validation error messages.

 

Table Level Validations Inventory