We have shown how the PersisTable toolkit allows data to be managed from within Excel without requiring expert intervention. The demonstration data has been ranges of data that can be Tabulated and fed into a CSV file.
There are circumstances where multiple ranges are logically part of the same data set. The annual financial statement and the associated balance sheet may need to be held together, a client list and the meetings held with those clients, the details of a legal contract.
The PersisTables Toolkit allows complex data sets to be easily captured, saved and reloaded. A Table represents the data in a range of cells within an Excel worksheet. When a Table is created, it is represented by a Handle and all PersisTable Toolkit functions operate on that Handle (effectively the data held in the range has been compressed into the one cell where the Handle is).
If a Range used to create a Table includes a Handle then the Table the Handle represents is embedded in the newly created Table. If the Table is saved then the parent and child Tables are saved and can be reloaded at a later time.
Parent Table holds multiple Child Tables
The Range used to create the Parent Table contains the Handles to 2 Child Tables. The Child Tables are automatically included in the Parent Table. We can save and reload the Parent Table and access the Child Tables. Note we cannot use a CSV format Protocol for saving hierarchical Tables.
We have discussed Arenas as places where PersisTable data is stored. This physically separates sets of data; your local data is private to you while a shared Arena allows you to collaborate with colleagues.
Genres allow PersisTable information to be logically separated. A Genre represents a class or type of information. So Genres may be defined for process inputs and outputs, they may be set up for weekly sales results and monthly accounts. The choice of Genres is driven by the business requirements but careful definition of Genres to be used will assist the smooth operation of PersisTable driven EUC. It is relatively simple to modify Genres after the event, a Genre is automatically created if the Genre name has not previously been used.
Having split the PersisTable data into Genres, the data within a Genre can be further sub-divided into Categories. Again these relate to the specific business requirements; choosing a sensible set of categories (year, month, day might be the categories chosen for data collected on a daily basis) will make it simpler to find PersisTable data in the future. For those used to using databases, the Genre, Categories and PersisTable name act as the primary key to the data.
It is recommended that all data within a Genre is held within the same set of Categories as this makes it easier to automate processing of the data. Again Categories are automatically created if required. We can see the structure of Genres and Categories within the Persistence Viewer.
Once an Excel workbook has become useful, it is likely to be copied. Maybe a Sales ledger workbook has proved useful in one office and there is then a temptation to copy the workbook to other offices so they can use the functionality. Unfortunately this means that any problems or improvements to the original workbook must be replicated across multiple copies.
Standard EUC approach
The real problem is that the spreadsheet holds both the data and the functionality to transform it into something useful. We have seen that the PersisTables toolkit allows data to be secured and shared. So we can use these tools to extract the data from the spreadsheet so all that remains is the functionality.
Modifies appraoch where data is held off sheet and only transform occurs on sheet
This approach means that a single template workbook can be used on different data sets. By loading the data set appropriate for the user into the template workbook, it allows each user to access the data they want using common functionality.
Making the template workbook ‘read only’ means it cannot be altered by one user and saved, having an adverse impact on others. No longer will an overwritten formula accidentally saved cause a problem when the workbook is reopened.
This approach allows bugs to be fixed in a single copy of the workbook and for enhancements to be rolled out to all users simultaneously. A more detailed description of how a spreadsheet can be built which captures user input, retrieves historic inputs and allows the data to be saved is provided in the PersisTables User Guide available with the installed package.
We have seen how to save and load data and how to find previosuly saved data using the PersisTable Viewer. Arenas were mentioned as a place to store data. However, the Local Arena is only accessible to the logged-on User and so that does not allow data to be shared easily.
To share data we need to create an Arena that is accessible to our colleagues. This is done using the PersisTables Management->Arena Manager accessed by right clicking on an empty cell.
Arena Manager accessed from PersisTables Management context menuArena Manager with pre-defined Local Arena settings
With a new installation we have one Arena defined, ‘Local’. The Local’ Arena is a windows file based persistence store, located within the User ‘PersisTables’ AppData area using a Text based encoding protocol and is selected as the default Arena. (If a moniker does not mention an Arena name then data is automatically written to the default Arena).
To share data we need to create a new Arena using the Add Arena button.
Defining a new shared Arena
Here the ‘Shared’ Arena has been defined which saves data to the windows file system located on a dropbox shared account. In this case the encoding of data usess the Comma Separated Values (CSV) format and we have ‘Selected’ it to be the default Arena. In addition we have defined the Local Arena as a fallback Arena; this allows us to see any data saved in Local as if it was part of Shared. Note that all data written to the default Arena is saved into the Shared Arena.
Opening the Persistence Viewer shows the new Arena (and because it is the Selected Arena it is displayed automatically).
Viewing Shared Arena
The Shared Arena shows the data held in the Local Arena as well as its own storage area, note the View is of CSV files and the ‘Include Fallback Arenas’ flag is set.
Normally the fallback Arena would be located on a shared resource; using Local will only see PersisTables saved by the current user. A more usual format would be to have a Test Arena with a fallback set to Production. In this case all the production PersisTables would be viewable from the Test Arena but changes to data would only exist in the Test Arena.
The basic PersisTables toolkit provides storage using the file system, hence the EndPoint is defined as ‘WinFile’. Other Endpoints allowing data to be saved to databases and document stores are available from Persis-Solutions.
So far we have used the CSV Protocol to save data. This creates a file where the contents of each row is output in a single line of a text file with commas separating the cell values. This is a common format used with Excel based applications, however it has short comings. If a cell contains a string with a comma embedded in it, this can break the protocol, and useful meta data cannot be stored with the data, that requires the format of the file to be explicitly given in the Moniker.
There are alternative protocols available which format the data as XML, JSON or text. These allow for auto-recognition of the Protocol when a file is loaded so it is not necessary to specify this information in the Moniker. Perhaps more importantly it is possible to create more complex data representations with multiple element of data being packaged into a single PersisTable by embedding Tables within other Tables.
The PersisTables Toolkit makes it easy to save data. As we have seen we just need to specify the moniker and name we want to give to the data using the TKTable_Save() function. Reloading it is done with the same moniker and name. However as the volume of data increases it becomes less easy to remember the moniker and name of a specific PersisTable.
The PersisTable Toolkit comes with an easy to use Persistence Viewer which allows you to search for a particular PersisTable and can be accessed by right clicking on a blank cell within the Excel workbook.
Context sensitive menu
– right click on empty cell
Clicking the ‘Persistence Viewer’ menu option opens the PersisTable Finder.
The Viewer allows you to search for PersisTables that have been previously saved. To understand how it works we need to introduce a couple of new concepts. The Arena is a place where PersisTables can be saved to. In this case we see that we are looking at the ‘Local’ Arena and this is located in the logged in User’s AppData area. The Local Arena is defined for each user and is private to that user. We will discuss how to set up a shared Arena later.
If we click on the + button next to the Arena name it opens to show us a list of Genres. A Genre holds a collection of PersisTables that represent a collection of data which are in some business sense related. A Genre may hold Customer details, another Regional Sales Accounts, one may hold a copy of input data to a spreadsheet and another holds a copy of the output data. The division of PersisTable data into Genres is a business decision but can be key to how useful the PersisTables Toolkit is. A sensible division will enable information to be found quickly. The Genre name is preceeded by ‘\\’ and is the only part of a moniker that is mandatory. In our example we are saving the Version details to an ‘\\Archive’ Genre.
Contents of the Archive genre highlighted after switching View to CSV
Here in the right hand pane we can see the PersisTable we saved earlier as a CSV file in the Archive genre. If the item is selected we can ‘View’ the contents of the PersisTable, ‘Link’ to it which will write the moniker for the PersisTable to the cell from which the Persistence Viewer was launched, or ‘Select’ the PersisTable which will load the PerisTable into the launching cell.
A common complaint with spreadsheets is that it is difficult to monitor changes to the information within them. If a spreadsheet gets corrupted the data may be lost, creating problems for the business relying on it. Where data held within Excel has become the business books and records, changes need to be audited.
Exporting data from Excel is generally hard work. Creating a CSV file requires the data to be assembled on a single worksheet. Saving as a CSV file requires changing the workbook file type and then restoring it after saving, otherwise changes to the rest of the workbook are lost. Integration with a backend database is possible but requires specific knowledge of data base design, ODBC drivers and is usually delegated to the technology team. Getting amendments made to an existing database can take time and limit the business’s ability to innovate.
The PersisTables toolkit provides functions that allow ranges of data to be scraped from an existing spreadsheet without impacting the behaviour of the spreadsheet. They are straightforward to use by non-technical users.
The basic PersisTables building block is the Table, this equates to a range of cells on a spreadsheet. The TKTable_Create() function captures the data in the range it points to into a ‘Handle’. The Handle will capture any changes made to data within the selected range (as long as Excel Calculation is set to auto).
Creating a Table from a Data Range
A Table can be saved outside of Excel using the TKTable_Save() function. This function allows the saved Table to be named, its location specified and the format of the saved data to be selected.
Here we see the software inventory of the PersisTables Toolkit displayed and then captured in a Table called ‘Versions’. The Table Handle is referenced by the TKTable_Save function which saves the data to a location defined by the moniker and called ‘Software Version’.
It really is as simple as that!
Some points to note. The moniker includes the ‘/CSV’ suffix which tells the toolkit to save the file as a comma seperated values file (a common Excel export format). Other formats for saving data are available and the reasons why they may be used are explained here.
Finally notice that the range selected to create a Table is larger than the data set, this allows for additional rows of data to be added and captured automatically, blank rows and columns are automatically discarded below and to the right of the selected range.
A common problem is to find that a reference to a range of data on a worksheet is not expanded as the data in that range grows, leading to missed input data to a function. The PersisTable Toolkit allows the user to request the TKTable_Create() function to fail if the Range of data is likely to exceed the area captureed by the function. This feature and others can be examined in more detail by invoking the function wizard relating to the PersisTable function.
The function wizard can be opened by clicking on the fx next to the formula bar, circled in red. Highlighting each field will give a description of the information needed to complete the field and whether the field is optional (can be left blank).
