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.
Step 2 - Connect to the Source Database
- Start SuperCHANNEL, either by double-clicking the desktop icon or selecting SuperSTAR > SuperCHANNEL > SuperCHANNEL from the Windows Start menu.
Select File > Connect to Source.
The Connect to Source Database dialog displays.
In the Driver field, select sctextdriver from the drop-down list.
In the Database field, click ... and select the directory where you extracted the sample source files. SuperCHANNEL automatically constructs the JDBC connection string and populates it into the Location field.
The format of the Location string is:CODE
<dataset_id>is determined by the TDD control files in the selected directory. In this example, the TDD is located in the directory E:\Data\RetailBanking and the TDD control files are named bank.DBCatalog, bank.DBColumns, and so on:
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
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.
SuperCHANNEL will automatically populate the Location string, which will be in the format:
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.
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.
A key appears on the icon to indicate that this column is now the primary key for the table:
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:
Right-click FK_Cust_Key and select Create Reference.
The mouse pointer changes:
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:
- Do the following for each classification table:
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):
In the Target Attributes section, select the Primary Key checkbox and set the Usage to Code.
Now select the name column, and set the Usage to Name:
- 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:
Right-click the column and select Create Reference.
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:
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:
Select each column in the Target View and check the Data Type in the Target Attributes:
In this example, one of the columns 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).
Change the Data Type for one of the columns so that both columns have the same data type:
- Once you have fixed the data types, try to create the reference again.
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:
|Classification Table||Contains||Example Value|
|C_State||States||New South Wales|
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:
- Right-click FK_SSD and select Create Reference, then double-click the Code for C_Geography_1.
- Right-click FK_SD and select Create Reference, then double-click the Code for C_Geography_2.
- 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:
|Classification Table||Foreign Key||Create Reference to the Primary Key of...|
|C_Age_Group_0||FK_5Group||5 Years Group|
|C_Age_Group_0||FK_10Group||10 Years Group|
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:
|Accounts||Average Account Balance|
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:
- Right-click the Customers fact table.
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:
Click Attributes to open the Target Attributes tool (if it is not already open):
- Select the field you want to rename.
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.
SuperSTAR does not support multiple fields within a fact table that have the same name. Ensure that all the fields within each fact table have a unique display name.
Step 9 - Build the Database
When you have completed all the steps:
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.
If the build has completed successfully, you can now add the SXV4 to the database catalogue in SuperADMIN, using the
catcommand. For example:CODE
> cat adddb banking "Retail Banking Database" "D:\SXV4s\RetailBanking.sxv4" myserver
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 removefollowed 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 for more information.