Relational to Array Dialog

New to Analytica 6.3

This dialog is used to convert a relational table or "fact table" to a multi-dimensional array. The dialog is essentially a "wizard" that helps you create a call to MdTable to perform the transformation, which you can do entirely from the dialog without writing any expressions yourself.

Relational tables

Relational tables are commonly used in databases and spreadsheets. A relational table is a two-dimensional heterogeneous tables with one "record" on each row, and different quantities relating to each record in a different column. They are often heterogeneous, with some columns containing index-like labels, and other columns containing numerical quantities with varying units of measurement and varying meanings.

A relational table often depicts quantities that are effectively multi-dimensional quantities. However, the multi-dimensionality is only implied, where some columns effectively specify the "coordinate" of the record in a multi-dimensional space. In such a case, it is usually far more convenient in Analytica to convert these into actual multi-dimensional arrays, which Analytica loves to manipulate directly. This accomplishes two things: (a) Is separates our heterogenous data into separate variables, where each variable represents one type of quantity only, with one consistent unit of measurement, and (b) turns the implicit "coordinates" that appear in the index-like columns of a relational table into actual explicit indexes. Once the quantities are in a homogeneous array form, they become really easy to manipulate using Analytica's various array functions.

It is worth noting, however, that for some extremely high-dimensional cases, a relational table can be a very space-efficient sparse representation.

Here is an example of a simple 3-column relational table.

The project and route columns are "index-like", where as the Length column contains quantitative data.

Fact tables

The dialog also handles the conversion of a fact table to an array. The term fact table comes from OLAP literature. A fact table is a relational table having more than one value column, and in which the multiple value columns are all instances of the same quantity varying across one additional index (often a time index). A fact table allows one dimension to be embedded in each record, rather than having to have a separate row for every cell.

(TO DO: Insert an image)

Accessing the dialog

To bring up the Relational table to array dialog, select Relational table to array... on the right-mouse context menu while viewing a relational table in a result window.

For the option to be present and enabled,

• You must be in edit mode.
• The module containing the table's variable must be editable (it cannot be locked, browse-only). This is where it will create new variables.
• It must be a result table (not an edit table)
• The table must appear to be a relational table.
  • The table must be pivoted with the columns horizontally and the records down the vertical rows.
  • Column names must be unique (no duplicates)
  • (A few others, TBD)

Column types

In the main part of the dialog, you specify for each column of your relational table whether the column should be treated as an index, as a value or ignored. For each index column, you have the options of using an existing index in your model, creating a new index, or using a new local index. These are each covered in turn below.

One of the nicest things about using this dialog is that it can take care of creating the index nodes for you, eliminating a few steps when writing an expression yourself that calls MdTable.