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.

An index should have only unique values (no duplicate elements). An index column in a relational table will have many duplicates. In general, an index should have all the values that appear in the corresponding column of the relational table.

The dialog looks at your data and makes an educated first guess about how to treat each column.

Existing index

Use this option when you already have an index in your model for the column. The existing index should contain all the values that appear in the column.

If the column label is a handle to an index, or if there is already an index that matches the name of your column and has the values that appear in your relational column, it will auto-select the existing index.

When you first select this option, you may see «click to select» as seen here

The blue underline indicates that the item can be clicked. You'll need to select the existing index in the index finder that pops up.

Once you've selected an index, or it has found one for you, the selected index identifier is blue underlined, for example

To change it to a different existing index, click the blue link and again use the index selector to locate the correct index.

See Also

• MdTable
• New variable from column
• Import and Export data