You can upload your actuals into the fact table of your application using the Excel Upload function:
After selecting the Excel file with the records to be imported you will get
How to fill the upload file - technical requirements
For a successful upload, the file must fulfill some technical requirements:
- The import data must be in the first sheet of the file and the import data must be in a table in the left top corner of the sheet (starting in cell "A1")
- The first row in the sheet must contain the field names from the fact table (as you can see them in the table browser of the Web App). Field names which do not match will be ignored in the import process (if you get a "15 of 15 columns ... were recognized" then you know that all your columns in the file were matched to fields in your fact table)
- The (matching) columns in the Excel file must be compatible with the data type defined in the data1.io system ...
... if you import e.g. text ("abc") in the column "Measure" (which is the typ "float" what means in SQL that this must be a figure) you`ll get an error message:
- Fields which are defined as "required" must be delivered in the upload file, all other columns are optional (from a technical perspective). In the Fact Table the field "SYSTEM IsDeleted" is the only column with the required-property.
- System fields like the "UID" and "Created UTC" are ignored from the upload file since those fields are filled automatically by the system
So the upload file could look like this:
How to fill the upload file - dimensions and measure fields
From a content point of view you should be aware of the following information:
- DIM001.Key: Must be filled with the Keys as defined in the "Dimension Table" (e.g. "EMP01" used as key for a specific sales representative in your dimension tree)
- DIM002.Key to DIM010.Key: Can be filled optionally depending on the "Dimensional Design" of the Excel Template -> usable for further dimensions like scenario, dataunit, KPIs, article/product, customer, region, etc.
- DATE01.Key: If you want to show your historical data in a certain month then give all records the same date within this month (e.g. "2019-01-31") since data1.io cannot aggregate different dates to a month, quarter etc.
- MEASURE01: The value that will be shown. Several records for the same "node" will be summed up and the sum will be shown in the Excel Template as historical data.
- TEXT01: Can be used to show a text or comment on the data. Several records for the same "node" will not be aggregated, therefore the text is only shown if there is 1 record per "node". Please note the text can only be shown in the Excel Template if the MEASURE01 field of the record is empty.
- SYSTEM.Delta: must be filled with a any text (must be the same for all records)
- SYSTEM.IsDeleted: must be set to "False" (otherwise the records will not be shown in the Excel Template).
How to update existing records?
If you want to update already uploaded records, than you have to delete them before you upload the currently valid new data. You can use the table filter with the field "Created UTC" since all records from an upload get the same timestamp which can be used later als filter criteria to delete a bulk of uploaded records:
