Scroll

Vertical Table Support

Vertical tables are a table format which differ from relational tables in the way the data is organized. Exago BI supports vertical tables, but they require some special configuration to be used.

The first section of this article describes how vertical tables differ from relational tables. The second section describes how to use them in Exago BI.

Relational table

In a relational table, each row contains a group of common data fields, associated by a unique key. Take the following table, for example:

Key

FirstName

LastName

Title

1

Nancy

Davolio

Sales Representative

2

Steven

Buchanan

Sales Manager

3

Laura

Callahan

Inside Sales Coordinator

Each row is a unique "person," who has values for the data fields FirstName, LastName, and Title. Rows are distinguished by a unique Key (which often is, but does not always have to be, a discrete field).

This table format can efficiently hold a large quantity of unique values, which can be efficiently joined with other tables by associating unique keys. This is how the relational model works, and is the basis for this application's functionality.

Vertical table

In a vertical table, each row contains one value for one data field for a unique key.

The following table contains the same data as the relational table, but is organized vertically.

Key Value

Field Name

Value

Data Type

1

LastName

Davolio

0

1

FirstName

Nancy

0

1

Title

Sales Representative

0

2

FirstName

Steven

0

3

Title

Inside Sales Coordinator

0

3

LastName

Callahan

0

2

LastName

Buchanan

0

2

Title

Sales Manager

0

3

FirstName

Laura

0

In this table, the names of the data fields are described in a "Field Name" column, and the value for the fields is described in a "Value" column. The "Key Value" is used in order to know what data belongs together. For instance, 'Davolio', 'Nancy', and 'Sales Representative' belong together in the same data 'row', so they all have the same key value. The field rows do not need to be adjacent; the table is sorted/grouped by Key Value after it is retrieved.

The table can also have an optional "Data Type," which gives information on how to format fields. Data Types are defined by integers, describing the following types:

  • 0: string
  • 1: date
  • 2: integer
  • 3: boolean
  • 4: numeric
  • 5: float
  • 6: decimal
  • 7: GUID
  • 8: DateTime
  • 9: Time
  • 10: Image

This type of table is often used in situations where the data fields are not known at design time, or are very dynamic. An example of this is for user defined data, which allows an end user to add their own data fields. Since the data fields can be added at any time, their names are often stored as rows as shown above.

The main advantage of a vertical table is that data fields are defined in rows, not columns. Vertical tables are more predictable, since they can only grow vertically, not horizontally. The disadvantage is that they can grow exponentially larger, by rows, than relational tables. And to join them to other tables, they require a query-time transformation, which can be computationally expensive.

Transforming Vertical Tables

Vertical tables can be configured to view "horizontally" in the application. This allows the tables to be joined and otherwise used as normal.

Some additional information must be supplied in the configuration file xml. This cannot currently be done in the Administration Console.

To transform a vertical table:

  1. Add the table to the application config. See Data Objects for instructions. If the table already exists, skip to step 2.

    Note. The unique key field is the column containing the key values.

  2. Edit the configuration file in a text or xml editor.

  3. Within the table <entity> supply a <transform> element with the following nodes:

    col_name

    The name of the column which contains the data field names

    val_name

    The name of the column which contains the data values

    datatype_name

    Optional: The name of a column which contains the data types. If this does not exist, you can set the data types using Column Metadata. Otherwise, the application will interpret values as strings unless formatted on the report.

    non_transform_col

    Optional: Any number of nodes which contains the name and optional data type of any fields that should NOT be transformed, whose values can be shown as-is along with the value on a report.

Example

<entity>
<entity_name>TransformTable</entity_name>
<db_name>transform_table</db_name>
<datasource_id>0</datasource_id>
<object_type>table</object_type>
<key>
<col_name>key_value</col_name>
</key>
<transform>
<col_name>field_name</col_name>
<val_name>field_value</val_name>
<datatype_name>data_type</datatype_name>
<non_transform_col>
<col_name>effective_date</col_name>
<data_type>1</data_type>
</non_transform_col>
</transform>
</entity>

table_in_exago.png

"Transformed" vertical table in an ExpressView


Hidden Article Information

Article Author
Exago Development
created 2017-05-19 13:53:45 UTC
updated 2017-06-12 14:46:16 UTC

Labels
transform, table, vertical, horizontal, relational, unstructured,
Have more questions? Submit a request