Reverse Channel from SXV4 Data Sources - SuperCHANNEL

You can use SuperCHANNEL to extract information from an SXV4 file and import the data back into a relational database system. Simply connect to the SXV4 as your source and the relational database as your target.

This process is referred to as "reverse channelling".

Because the SXV4 format contains unit record data, this can be a useful way to retrieve data from an SXV4 (for example if you no longer have access to the original data source that was used to build that SXV4).

Please note that the resulting database may not contain all of the information that was in the original source database used to create the SXV4:

Any plain tables from the original source that were not included in the build cannot be retrieved from the SXV4.
Any columns or tables that were explicitly excluded from the build will not be in the SXV4.
It will not be possible to reverse the effect of any cleansing actions applied during the original build. For example:
- If a row was skipped then this row will not be in the SXV4.
- If a value was binned to a particular value (for example an empty cell value was binned to a value of "Unknown") then it will not be possible to retrieve the original value of that cell. Only the binned value is stored in the SXV4.

If the SXV4 you are using as the source was created prior to version 8.0 of SuperCHANNEL, and it contains non English/ASCII characters, then you may need to change the system locale in order to ensure all characters are represented correctly in the output. See Unicode - SuperSTAR for more information.

Reverse Channelling from an SXV4

To reverse channel from an SXV4 into a relational database:

Go to your relational database and create a new empty database to store the output from the SXV4.
In SuperCHANNEL, select File > Connect to Source. The Connect to Source Database dialog displays.
In the Driver field, select sxv4 (str.jdbc.sxv4.Driver).
Click the ... button and browse to and select the SXV4 you want to use as your source.
Click OK.
The Source View updates to show the tables and columns in the SXV4 database.
Select File > Connect to Target.
In the Location field, enter the connection string for connecting to your relational database.
For example:
- To connect to SQL Server using the JTDS driver, use the connection string jdbc:jtds:sqlserver://<servername>:<port>/<databasename>;useCursors=true
- To connect to MySQL, use the connection string jdbc:mysql://<servername>:<port>/<databasename>?relaxAutoCommit=true (please note that the relaxAutoCommit paramater is required when reverse channelling to MySQL).
Enter the username and password and click OK.
SuperCHANNEL connects to your target database.
Drag one of the fact tables across from the Source View to the Target View:
As the relationships between fact and classification tables will already be defined, this should bring all the other linked tables in the SXV4 across to the Target View too:
Click Build All to channel from the SXV4 back to the relational database.
Once the build is complete, you can view the data in your relational database. For example:

Known Issue with Reverse Channelling to Some Relational Databases

In some cases, SuperCHANNEL incorrectly shortens the length of the classification code column when it builds the SXV4. This results in a mismatch between the length of the column in the classification table and the length of the fact table column that is joined to it. If the database that you are reverse channelling to requires columns participating in a foreign key relationship to be the same length, then you may see errors similar to the following in the SuperCHANNEL logs:

CODE

str.channel.ChannelException: A channelling error occurred.
Cause:
    str.realtimechanneler.RealTimeChannelException: An error occurred creating table 'F_Customer'
    Cause:
        str.database.DatabaseException: An error occured while executing an SQL command
        Cause:
            com.microsoft.sqlserver.jdbc.SQLServerException: Column 'C_Gender.Code' is not the same length or scale as referencing column 'F_Customer.Gender' in foreign key 'FK__F_Custome__Gende__01142BA1'. Columns participating in a foreign key relationship must be defined with the same length and scale.
            SQLState: S0000
            Vendor Code: 1753

This issue will be resolved in a future release.

If you encounter this issue you have two options to work around the problem:

Use an RDBMS that does not require columns in a foreign key relationship to be the same length (for example, H2).
Delete any references in Target View where there is a mismatch in column length (for example in the above case you would delete the link between F_Customer.Gender and C_Gender.Code). Keep deleting references until you can successfully run the reverse channel. You can then alter the table definitions in your RDBMS and recreate the links if necessary.