The purpose of this article is to provide guidance on the data files/formats that are to be used to import into Pigment. Included in the article is an Excel guide that includes an example structure of common use cases.
Before preparing your file to be imported, its important to note that all excel files must be converted into a .CSV before they can be imported into Pigment. Check out this support article from Microsoft on how to save as .CSV
Tips for Data imports into Pigment 👩🏻🏫
-
Separate Dimension IDs from Dimension Names ⛓
Where you have a dimension with IDs in your source system you should include:
- the IDs in a column,
- the Name of the dimension in a second column.
This will allow you to follow best practice to load your data with IDs, but, also have a display name based on the Name. If you don't have Ids, we can load using the 'Name', however, this can cause issues down the line if dimensions start to change names (E.g. Account 51000 - Revenue becomes Account 51000 - Revenues).
Note: If you have a large transaction list, it might be better to import just the ID and maintain the Display names through a separate import into the dimension list.
2. Include a Unique Transaction ID 🆔
Unique transaction IDs increase sustainability and auditability.
They also allow you to update Pigment data by importing a file with the IDs that are already in the Transaction List. If you don't have a Unique ID, you either have to update records manually, or you have to delete the relevant transactions and upload them again.
3. Only include data that is required on Pigment in the data extracts 🎯
Less data = Faster Processing time
4. Include ‘Period’ as a column (if you don't have period, then have an 'Upload Date' column) 📅
This will allow the assignation of data to the relevant periods, and will allow us to track data over time. for example, the Cost Centre's headcount at the time each file was uploaded.
5. Remove unnecessary formatting 💷
Include minimal formatting in the data rows. Remove all special characters including currencies. If you need to include the currency in the import, include them in a separate column.
As a result we can upload the relevant columns in the format they will be used , for example, $200 would be uploaded in two columns, column 1 for currency "USD" and Column 2 for Amount which would be "200".
The alternative would be that we upload these columns as text and then convert them, for example, $200 would be imported. We then have to created 2 additional properties within the transaction list to calculate the currency of each transaction and the value. This against best practice as we are creating 3 columns, 2 with formulas of what could be achieved with 1. More Cells, larger processing time.
6. Import data using a flat file structure 🥿
It's generally better to have the file in a flat format, with all the data being formatted in columns
Flat files are more dynamic.If new items/columns are added to the pivoted section in a pivot upload, then the import would have to be rebuilt. Flat file imports wouldn't need to a new import if you added a new item.
7. Minimise the number of blank rows and missing data ❌
A lower number of blank rows, leads to a lower number of transactions which results in faster processing times and greater auditability.
If fewer columns are missing data, then the less functionality that has to be implemented on Pigment to clean the data.
8. Remove all Sub Totals & Totals 🔢
All sub totals and totals will be calculated on Pigment in blocks or through the use of the hierarchies. Uploading totals only creates additional transactions that we have to ignore by creating functionality. It also increases the chance of errors.
