Facts and Dimensions

Fact Table

The Fact Table identifies the name of the table in the database from which facts are retrieved. One fact table is usually associated with multiple dimension tables - this is a star schema. If each dimension table is in turn associated with other dimension tables, then it is a snowflake schema. Both schema types are supported. This table name is also used as the name of the directory within the Home Directory where indexes are stored.

Fact SQL

By default the SQL used for reading the facts is of the form:
SELECT * FROM FactTable
where FactTable is the name of the Fact Table described above. If only a subset of columns are required then you can save memory on the server by only selecting those columns.

Dimensions

The Dimensions list shows the dimensions tables that are available for indexing. At this point we just need to identify what tables are available to the cube. We will connect them into a star or snowflake structure on the Indexes panel.

Dimension Dialog

Name

The Name of the database table that holds the dimension information. You can either type this name, or select a table name using the chooser button. The chooser will only work correctly if the connection information is valid. Using the chooser is often preferred as it will also set the Primary Key, when one can be identified.

Primary Key

The Primary Key is the name of the column in the dimension table that uniquely identifies each row. This is the column that will be mapped to the foreign key held by the Fact Table (or another Dimension, if using a snowflake schema). The value will be filled automatically when using the chooser button for Dimension Name.

SQL Override

By default the system will use
SELECT * FROM DimensionName
to read dimension data from the database. If there is extra information in the table that isn't required, you can save memory by overriding the default SQL. Leaving this blank will revert to the default SQL.