Database Design Overview
SuperCHANNEL is the tool for converting your data into the SuperSTAR proprietary database format, SXV4. You will need to use SuperCHANNEL to convert your data to this format before you can use it with SuperSTAR.
SXV4 databases use a “star schema” or “snowflake schema” design, so called because it consists of “fact tables” connected to “classification tables” by relationships:
What are Fact Tables and Classification Tables?
Fact tables contain the actual source data. This might be your customer records, the responses to your survey, or the census data. For example, a simple fact table containing customer records might look as follows:
Classification tables, also known as Dimension or Lookup tables represent discrete sets of values that define all the possible results for a column. As you can see in this example, the data has a column to indicate the customer’s gender, using codes such as M and F. Classification tables contain the descriptions for all the codes that could possibly appear in that column.
The classification table for Gender might look like this:
| The classification table for Marital Status might look like this:
|
As shown here, classification tables must have at least two columns:
- The Code column must be the primary key and unique identifier for the classification type. It is the link between the fact table and the descriptions in the classification table. For example: "M" or "F".
- The Name column contains the description of the classification. For example: "Male" or "Female". Classification tables may also have additional columns, to control the sorting of the classification items and for creating hierarchies.
SXV4 Field Types for Cross Tabulations
There are two main types of field in an SXV4: cross tabulation fields and measures.
Cross Tabulation Fields
This is a field that has a fixed set of possible values. These fields can be used to create tables in SuperCROSS and SuperWEB2. When you design your SXV4 in SuperCHANNEL, you tell SuperCHANNEL which columns in the fact table map to which classification tables. This automatically makes these columns into cross tabulation fields, which will be available for building tables in SuperCROSS and SuperWEB2.
For example:
Measures
Measures are columns in the fact table that can be summed. They must be numeric values. For example: income or expenditure.
By default the measure will be summed, but other statistical functions can also be applied to the calculation.
In the above example data the customer income could be defined as a measure. This would allow you to calculate things such as the total income, mean income, etc.
The measures will be available as Summation Options in SuperCROSS and SuperWEB2.
Other Field Types
There are other types of field that can be included in an SXV4 database:
- Date/time Field: Fields in date/time format can be included in an SXV4 and will be available for use in special calculations in SuperCROSS (as part of User Defined Fields).
- Plain Field: a field that is not a classification, measure or date field is referred to as a plain field. It can be included in the SXV4 (and will be available to access through Record View) but cannot be used in cross tabulations. In the above example, the customer's ID number, name and email address would be treated as plain fields.
Primary Keys
Each classification and fact table must have a field designed as the primary key: this is used to uniquely identify a row in a table. In the case of the example fact table shown above, the Customer ID would be the primary key.
Classification tables must also have a primary key. This must be the column that contains the code for the classification. This primary key is required so that we can connect columns in the fact table to their classifications. For example, we need to connect the Gender column in the fact table to the Code column in the Gender classification table.