Before you start to connect your own Excel files with the data1.io Cloud Service, we strongly recommend to do these simple exercises with the Template Wizard in order to understand how the connection with the spreadsheet works.
1. Save the Default Template to your application
Simply start by entering the Template Wizard tab and clicking the Save button. This will store the Default Template (shown in the bottom area of the screen) as an Excel file to your application:
2. Test it
Now switch to the Preview tab - the Default Template will be loaded in the main area of the screen and the dimension tree - defined earlier in the process - is shown in the tree pane on the right. Double click a bottom level element of the dimension tree in order to test data entry with this spreadsheet:
After entering a few values and some comments press the Write button - the collected data will be transferred into your application:
Now switch to the Fact Table tab - you`ll learn that every entry in a cell in the spreadsheet was stored as a recordset into the application. You can see that the allocation to columns was executed as defined in the so called dimension and measure mapping in the Template Wizard:
3. Add new fields, rows and columns and understand dimension and measure mapping
3.1 Adding a field
Back in the Template Wizard you can add a field in the header of the Excel Template by clicking the Add field button. Then enter the text "From Excel sheet" in the empty blue field and map the new field to the dimension DIM004 of the Fact Table (which is part of the Dimension Dim004). Do not forget to press the Save button in order to store the changes into your application:
Now switch again to the Preview tab and double click again the first bottom-level element in the dimension tree. This wil execute a READ process and bring the already stored information back to the Excel Template (which shows the new field in row 7). You do not need to change the values (but of course you could), just press the Write button:
Back in the Fact Table tab you can see that all records have got the entered value in the column Dim04 Key:
3.2 Adding a row
After you know how to add a field you can try to add a new row to your Excel Template. Switch back to the Template Wizard and press the Add row button. In the blue field of the added row enter the text "Employees involved" - this is a new metric that should be collected as well:
Test it again by switching into the Preview tab. Double click the bottom level element with the stored information, enter some data in the new fields for the new metric "Employees involved" (row 13) and press the Write button:
In the Fact Table you can see that 2 new records for the 2 entered values were added:
3.3 Adding a column
Finally we add a new column to the Excel Template. Press the Add column button, select a date, map the new column to the field Text02 and type the text "Comment 2" directly into the spreadsheet:
Now test it again in the Preview tab and press the Write button to writeback the changed data:
In the Fact Table, the new comment was saved to field Text02:
Basically quite easy, isn`t it? :)