Tutorial - SuperCHANNEL

If you are new to SuperCHANNEL, we recommend you start by following this tutorial. This will introduce some of the key concepts by taking you through the end-to-end process of converting data from a source format into SXV4.

Step 1 - Obtain the Sample Source Data

To make it easy to complete this tutorial, we have provided some sample source data for use with SuperCHANNEL. This source data allows you to design and build the sample Retail Banking database.

Download the source data from the download library (download the Retail Banking ZIP file). Once you have downloaded the file, extract the contents of the ZIP file to a directory somewhere on your computer.

In most cases, the real source data you are working with will be stored in a relational database, so you would need to download and install a JDBC driver for connecting to the relevant relational database before starting SuperCHANNEL. However, this step is not required for this tutorial, as the supplied sample data uses Space-Time Research's proprietary textual database format, TDD (Textual Data Definition) format. The driver for connecting to TDD data sources is provided with SuperCHANNEL out of the box.

Step 2 - Connect to the Source Database

1. Start SuperCHANNEL, either by double-clicking the desktop icon or selecting SuperSTAR > SuperCHANNEL > SuperCHANNEL from the Windows Start menu.

2. Select File > Connect to Source.

   The Connect to Source Database dialog displays.

3. In the Driver field, select sctextdriver from the drop-down list.

4. In the Database field, click ... and select the directory where you extracted the sample source files.

   

5. Click OK.

   SuperCHANNEL connects to your source database and displays the tables and columns in the Source View pane:

   

   In this example we are using sample data that has been prepared according to the recommended naming conventions. Although these conventions are not mandatory, they make it much easier to work with source data.

   This source data also contains registry tables, which have enabled SuperCHANNEL to make some initial assumptions about the source data, such as determining which tables are fact tables and which are classification tables.

Step 3 - Connect to the Target Database

1. Select File > Connect to Target.

   The Connect to Target Database dialog displays.

   Enter the details for your target SXV4 file, as follows:

   Field	Description
   Driver	Select sxv4 (str.jdbc.sxv4.Driver) from the drop-down list.
   Database	Click the ... button and browse to the location where you want to create the file, then enter a filename.

2. Click OK.

   

   SuperCHANNEL populates the Target View.

   

Step 4 - Define the Fact Tables

Once you have connected to both the source and target databases, you can start to define the table structure.

Add the Fact Tables

The first step is to define the fact tables. In this example there are two fact tables: Accounts and Customers.

To add them to the Fact Tables folder on the right, do one of the following:

• Either drag and drop:

  

• Or right-click and select Add as Fact Table:

  

Set the Primary Keys

It is also necessary to define the primary keys for each fact table. Right-click the column you want to set as the primary key and select Set Primary Key.

1. Right-click the primary key in the Target View and select Set Primary Key. In this example the columns have been named according to the recommended naming convention, so it is easy to identify which ones need to be set as the primary key, because they have (PK) in the column name.

   

2. A key appears on the icon to indicate that this column is now the primary key for the table:

   

3. In this example there are two fact tables, so it is also necessary to set the primary key for the second fact table:

   

   

Define the Link Between Fact Tables

If you have multiple fact tables (in this case there are 2), then it is also necessary to define the link between the two tables, using primary keys and foreign keys.

This is a very important step. If you do not do this then the SXV4 database will still build, but there will be nothing available for cross tabulation.

In this example, the Accounts table has a foreign key (FK_Cust_Key). This needs to be linked to the primary key on the Customers table.

To define the link between the fact tables:

1. Right-click FK_Cust_Key and select Create Reference.

   

   The mouse pointer changes:

   

2. Double-click the primary key on the Customers table. SuperCHANNEL creates the link between the two tables:

   

Step 5 - Define the Classification Tables

The next step is to define the classification tables.

Add the Classification Tables

For each classification table, you need to do one of the following:

• Either drag and drop:

  

• Or right-click and select Add as Classification:

  

Repeat this step until you have added all the classification tables to the Target View.

Set the Classification Table Column Usage

Classification tables must contain at least two columns:

• The classification table primary key. This column is used to link to the value in the fact table. You need to tell SuperCHANNEL which column this is by setting it as the primary key and setting its usage type to Code.
• The column that contains the names of the values to be shown in the SuperSTAR client applications. You need to tell SuperCHANNEL which column this is by setting its usage type to Name.

To set the column types:

1. Select Attributes.

   

2. Do the following for each classification table:

   1. Select the column that contains the code (in this example the source data follows the naming conventions, so it is easy to tell which one this is):

      

   2. In the Target Attributes section, select the Primary Key checkbox and set the Usage to Code.

      

   3. Now select the name column, and set the Usage to Name:

      

3. Repeat this step until you have defined all the code and name columns for all the classification tables.

In this example, the columns have been named Code and Name (following the recommended naming convention), so it is easy to tell which is which. While it is not a requirement that you follow this convention with your source data, you will find it much easier to work with the data in SuperCHANNEL.

Step 6 - Set Fact Table Column Usage

The next step is to set the usage types for each column in the fact tables. This determines which columns are available in SuperSTAR clients as cross tabulation fields and measures.

Create the Reference between the Fact Table Columns and their Classifications

To make a column in the fact table available for cross tabulation, you need to create a reference link to its classification table, by doing the following:

1. Right-click the column and select Create Reference.

   

2. Double-click the primary key of this column's classification table.

   For example, the Age column in the Customers fact table needs to be linked to the C_Age classification table:

   

   SuperCHANNEL creates the reference:

   

   SuperCHANNEL also automatically sets the Usage attribute for this fact table column to Classified:

   

3. Repeat these steps to create references between all the columns in the fact table and their classification tables.

   For the sample Retail Banking database, create the following references:

   Fact Table	Fact Table Column	Create Reference to the Primary Key of...
   Accounts	Account Open Calendar Date	C_Cal_Date_0
   Accounts	Account Open Financial Date	C_Fin_Date_0
   Accounts	Account Open Quarter	C_Quarter
   Accounts	Account Open Month	C_Month
   Accounts	Account Open Week	C_Week
   Accounts	Account Open Day of Month	C_DayOfMonth
   Accounts	Account Open Day of Week	C_WeekDay
   Accounts	Last Transaction Calendar Date	C_Cal_Last_Tran_Date_0
   Accounts	Last Transaction Financial Date	C_Fin_Last_Tran_Date_0
   Accounts	Last Transaction Quarter	C_Quarter
   Accounts	Last Transaction Month	C_Month
   Accounts	Last Transaction Week	C_Week
   Accounts	Last Transaction Day of Month	C_DayOfMonth
   Accounts	Last Transaction Day of Week	C_WeekDay
   Accounts	Product Type	C_Product_Type
   Customers	Age	C_Age
   Customers	Age Groups	C_Age_Group_0
   Customers	Area	C_Geography_0
   Customers	Customer Mail Indicator	C_Cust_Mail_Indicator
   Customers	Customer Open Calendar Date	C_Cal_Date_0
   Customers	Customer Open Financial Date	C_Fin_Date_0
   Customers	Customer Open Quarter	C_Quarter
   Customers	Customer Open Month	C_Month
   Customers	Customer Open Week	C_Week
   Customers	Customer Open Day of Month	C_DayOfMonth
   Customers	Customer Open Day of Week	C_WeekDay
   Customers	Gender	C_Gender
   Customers	Marital Status	C_Marital_Status
   Customers	Occupation	C_Occupation

   

   Some of these columns contain hierarchical data. For example, Area contains a hierarchy of states, cities, suburbs, and postcodes.

   To create these cross tabulation fields we start by creating a reference to the lowest level in the hierarchy (for example we create a link between the fact table column Area and the classification table C_Geography_0, which contains postcodes).

   In the next section we will create some links between classification tables to create the hierarchy.

You may see the following warning message when attempting to create the references:

This message indicates that there is a mismatch between the data type of the column in the fact table and the primary key of the classification table.

To resolve the issue:

1. Select each column in the Target View and check the Data Type in the Target Attributes:

   

   

   In this example, one of the column has a data type of INTEGER(5) and one is INTEGER(4), so we need to change the one that is currently INTEGER(4) to INTEGER(5).

2. Change the Data Type for one of the columns so that both columns have the same data type:

   

3. Once you have fixed the data types, try to create the reference again.

Hierarchical Fields

The sample Retail Banking database contains data for a number of hierarchical fields, such as the Area field in the Customers table, which appears as follows in the SuperSTAR clients:

For these fields, the classifications at each level are stored in their own classification table. We have already created a reference between the fact table column and the lowest level of the hierarchy. We now need to create references between each of the classification tables that contain the different levels of the hierarchy.

For example, in the Retail Banking database, the Area field uses the following classification tables:

Each of the classification tables used in the hierarchy has a foreign key. We need to create a reference between this foreign key and the primary key of the classification table at the next level. For example:

To create the references, start at the lowest level in the hierarchy and work up to the top. For example:

1. Right-click FK_SSD and select Create Reference, then double-click the Code for C_Geography_1.
2. Right-click FK_SD and select Create Reference, then double-click the Code for C_Geography_2.
3. Right-click FK_State and select Create Reference, then double-click the Code for C_State.

For the sample Retail Banking database you need to create the following references:

Define Plain Fact Table Columns

This step is optional. You can set columns in the fact table to Plain. These columns will be included in the target database but cannot be cross tabulated (the values of these columns can be viewed in Record View).

For example, the Customer table has a column containing the customer's date of birth. To include this in the build, select the column in the Target View, then select the Included in Build check box and the Plain usage type:

Step 7 - Define the Measures

You also need to define the measures for your database. Measures can be used in calculations. They are generally continuous variables such as income (which are not suitable as classifications). The measures appear in SuperSTAR clients as the Summation Options.

To define a measure, select a column in the Target View and then set the Usage to Measure in the Target Attributes.

For the sample Retail Banking database, set the following columns as measures:

You can only set a column to be a measure if it the data type is double or integer.

A count of the records in each fact table will also be available in the summation options. SuperCHANNEL creates this summation option automatically.

Set the Default Summation Option

You also need to select the default summation option. This will be used when a user builds a table in one of the clients but does not select a summation option. This can either be one of the measures or a count of the records in one of the fact tables.

To set the default summation option, right-click either the measure you want to use or the fact table (if you want to use a record count) and select Set Default Summation.

For the sample Retail Banking database:

1. Right-click the Customers fact table.

2. Select Set Default Summation.

   

   A + sign appears next to the fact table name, to indicate that it is the default summation:

   

Step 8 - Define Groupings and Field Names

The final step before building the database is to arrange, rename and organise the fields.

Click Grouping to access the Grouping tool. SuperCHANNEL displays the defined cross tabulation fields and measures.

There are a number of changes you may wish to make here:

Reorder the Fields

The order in which the fields are listed here is the order they will appear in the clients. Drag and drop the fields to change the order:

Add or Remove Folders

You can create folders to group fields together. In the example above there is a folder call Customer Open Date that contains all the related classification fields.

To create a folder, right-click where you want to create it and select Add Group. You can then drag fields into and out of the sub folder.

To remove a folder, you must move all the fields out of it. You can then use the Remove Group option on the right-click menu.

Rename a Field or Folder

You can also rename any fields or folders:

1. Click Attributes to open the Target Attributes tool (if it is not already open):

   

2. Select the field you want to rename.

3. In the Label field, enter your preferred name for the field. This is the name that will be displayed to end users in the SuperSTAR clients.

   

Step 9 - Build the Database

When you have completed all the steps:

1. Click Build All to build the SXV4 database:

   

   SuperCHANNEL runs the build and generates an SXV4 database file in the location you chose in Step 3.

   The left side of the screen will display some logging information. When the build completes, it will tell you whether or not it has successfully generated an SXV4.

2. If the build has completed successfully, you can now add the SXV4 to the database catalogue in SuperADMIN, using the cat command. For example:

   CODE

   > cat adddb banking "Retail Banking Database" "D:\SXV4s\RetailBanking.sxv4" myserver

3. Then log in to one of the clients and check your SXV4. For example:

   

   

   You are strongly recommended to save the project file in SuperCHANNEL (select File > Save Project As). This saves all your configuration settings for this SXV4 so that you can easily return to the project and make further changes, without having to repeat all the steps.

   

   If you decide to make further changes in SuperCHANNEL you can rebuild the SXV4. If you want to do this, you will need to remove the SXV4 from the SuperSTAR database catalogue first (in SuperADMIN, use the command cat remove followed by the ID you set when you added it to the catalogue). If you do not do this then SuperCHANNEL will not be able to rebuild the SXV4 because the file will be locked by the SuperSTAR process.

   

   If you close SuperCHANNEL after having built a SuperSTAR database, and then subsequently open the project again, SuperCHANNEL will detect that the target database exists and will open it in "update mode". Existing columns in the target database will be greyed out. You will be able to add new mapped columns, and update the data in the existing target database, but you will not be able to modify the settings for previously mapped and built columns or rebuild the entire target database.

   If you wish to edit the existing mappings and rebuild the database, you must remove or rename the existing SXV4 file before you open the project in SuperCHANNEL.

   See Rebuild an Existing SXV4 - SuperCHANNEL for more information.