Very often in working with customers, my team encounters a type of data problem that traditional BI struggles at solving. The problem is how to handle User Defined Fields. When it comes to end-user customizations, there is no single answer on how to structure your database tables to easily report off the data, which often leads to creating their own method. Our customers have found that with some tools it is nearly impossible to create a single report or dashboard that can leverage these User Defined Fields across multiple users.
The metadata in traditional BI is a very structured beast where anytime the fields change, the metadata has to be updated as well. However, Logi Info is able to work with user defined fields since there is not a metadata layer associated with it. The element design allows for a more agile approach to report authoring and some of our elements allow for more flexible approaches to helping solve your data problems.
The three most common ways I have seen customers create their user defined fields are Altering Tables, Table of Key/Value Pairs, and Creation of Custom Data Tables. Each of these items require a slightly different approach, but can be easily solved by use of the Repeat Element. The Repeat Element will insert elements for each row of data returned by a datalayer. So let’s dive into a quick explanation of each method and how to use the Repeat Element to help solve your problem.
In this method, the developer chooses to alter the table as the end user adds fields. For instance, given an EmployeeDetails table like this:
If the end user wanted to add a new column, such as a ShirtSize, the application would modify the table structure so that the table would now be:
This is an actual change to the DDL (Data Definition) generally done by an “Alter table ADD columnName datatype” SQL statement.
While the Auto Columns element can handle this for a very simple use case, the Repeat Element can be leveraged for more control. In comparison, legacy BI would struggle as the metadata would need to be updated in order to show the column. However this example demonstrates how Logi Info will be able to change the definition on the fly with the use of our Repeat Element.
Adding Key/Value Pairs
In the Key/Value Pairs method, the developer decides to store all data in a single table with a Key identifying which “column” it belongs to, a value of the column, and another identifier stating what record it belongs to. This prevents changes to the structure of the database.
Let’s take the same example of Employees:
If they want to add ShirtSize to each of the records, they would have a table of UserDefinedFields that may look like:
Many of you might jump to use a simple join, but that won’t work as you would get two added columns called ColumnName and ColumnValue. Again, here comes the Repeat Element to the rescue. Where standard BI tools may have issue dynamically pivoting the data and adding the new columns, Logi Info’s application level, Join element, Crosstab Filter, and Repeater provides an elegant solution to dynamically show the columns needed without any changes to the definitions when a new “Column” is added.
Creating Custom Tables
This is a method we have seen only a couple of times and it is an interesting approach to a complex data issue. Similar to the Altering Tables method, creating custom tables changes the structure of the database. However, it abstracts the changes from the actual table to a custom table.
Given the same example of an HR application adding specific columns to a table, this method would break the changes out like this:
Table: EmployeeDetails Custom
All modifications (Alter Table commands) are done to the _Custom table.
The solution is very similar to the AlterTables method, except we can use our Join element to join the tables together as needed.
In conclusion, the Repeat Element is a very powerful element and as shown in these examples, we are using it to handle flexible/dynamic schemas and user defined fields. However, this is the first subject in a long list of capabilities and there are many other things we can do.
Interested in Learning More?
To learn more about the methods described above and how to use the Repeat Element, watch our on-demand webinar “Logi Hacks – The Power of the Repeater.”