Data Source Upload
Learn how to structure your data in the excel file so that it can be used in Valsight.
Go to the data management section in Valsight by clicking on the grid icon on the left. Create a data source with the New Datasource button above the list and choose XLSX from its dropdown. To swap the file of an existing data source, open it and use the Replace Data Source section at the bottom: pick the new file and click Update Data Source. The replacement removes the previous file.
Each excel sheet is loaded into an internal database table. Every data node or DATA function references exactly one excel sheet. Sheets that start with a number sign (#) are ignored, and so are worksheets that are hidden in Excel: visibility is checked before the number-sign rule, so hiding a sheet silently removes its table from the upload and can trip the ‘Table is still in use’ error if the model references it. The first line of each sheet is interpreted as the header row and determines the column names of the resulting internal table. The same number-sign convention works per column: a column whose header starts with (#), or is empty, is ignored, which is how helper and comment columns are kept out of the table. Valsight recognizes the formatting options of the cells in Excel, hence all structural information (labels, text, levels, level values) have to be formatted as “General” or “Text”. On the other hand, all measures have to be formatted as “Number”.
Data structure - best practices
We recommend to split your data into two excel files, one to define dimensions and another one to upload data. You may create more than one data sheet, e.g. if there are different data sources, or very large data sheets that take long to load. When formatting your Excel sheets adhere to the Naming Guidelines for best results. Excel files are always uploaded in the data management section, which is linked from the “grid icon” in the left menu.
Create hierarchies & dimensions
See Dimension & Hierarchy Management for more information how to create hierarchies & dimensions.
Upload data & formatting data in your Excel sheets
| Recommended: create standard upload tables | Advanced: create a 2D/3D data table |
|---|---|
| Normal upload tables, flexible number of levels, easy to understand. | Useful in situations where your data comes pre-formatted from legacy systems. |
| See Create Upload Tables | See Multidimensional Data Upload (2D/3D/4D Tables) |
Find examples at the Example Excel Sheets.
Further information
Limitations
Recognized column names
Valsight links a column to a level whose name matches its header, plus a fixed set of aliases for measures, time levels and Sequence. See Recognized Column Names.
Create one Excel sheet for multiple data nodes
Create one Excel Sheet for Multiple Data Nodes (This upload method is not recommended for new models.)
Integration scenarios
Of course, later on the integration of ERP Software is possible - get in contact with the Valsight Support team to evaluate your integration scenarios.