Channel from Multiple Source Databases

SuperCHANNEL can only connect to a single database at a time. However, if you need to connect to multiple source databases to build your SuperSTAR database, you can do this by connecting to them one after the other and incrementally updating the target database.

For example, you might have classification tables stored in a different database to the fact tables, or the several fact tables required to construct your database may be located in a number of different source databases.

The order of the incremental build is important, because referential integrity needs to be maintained when building the target database. If a table has a foreign key reference to another table then the primary key in the table it is connected to must already exist in the SuperSTAR database.

Therefore:

• Before you can channel a fact table, all the classification tables it refers to must already exist in the SuperSTAR database.
• Before you can channel a classification table, all the classification tables it refers to must already exist in the SuperSTAR database (for example in hierarchical classifications).
• Before you can channel a fact table, all the fact tables it refers to must already exist in the SuperSTAR database.

Classification Tables and Fact Tables in Different Source Databases

When you have to channel classification tables from one source database and fact tables from another, you must connect to the classification source database first and channel the classification tables.

However, the SuperSTAR database format also requires that there is at least one fact table in the target database. If necessary, you may need to create a dummy fact table in your target database design. This should have the same name and columns as the one you will subsequently be channelling from the other data sources.

Once you have channelled the classification tables you can connect to the source database containing the fact tables and channel the actual data.

Summary of Steps Required

1. In the first source database, create a dummy (empty) fact table. This should have the same structure as (one of) the real fact tables, but with no data.
2. In SuperCHANNEL, connect to the first source database and the target.
3. Design the target database. For example:
   • Add the classification tables and the dummy fact table.
   • Set the correct usage and attributes for all of the classification tables.
   • Create any references between the dummy fact table and the classification tables.
4. Build the SuperSTAR database.
5. Disconnect from both the target and source.
6. Connect to the second source database and the SuperSTAR database you partially built in the previous step.

7. Add any new fact tables from the second source database to the target (you do not need to add the fact table that you already added as a dummy, as this is already in the target database.

8. If required, create any references between the new fact tables and the classification tables. If you have multiple fact tables then you must create the links between the fact tables at this point.
9. Use the Build All function (if you added new fact tables) or the Update All function (if you did not add new fact tables) to channel the fact table data from the second source database.

Same Fact Table in Multiple Source Databases

If you have fact table data in the same fact table stored in multiple source databases, then you need to build the database with the first source database and then update the insert the rows from the second database.

The first source database you connect to must contain all the classification tables.

Summary of Steps Required

1. In SuperCHANNEL, connect to the first source database and the target.
2. Design the target database. For example:
   • Add the classification tables and fact table.
   • Set the correct usage and attributes for all of the classification tables.
   • Create the references between the fact table and the classification tables.
3. Build the SuperSTAR database.
4. Disconnect from both the target and source.
5. Connect to the second source database and the SuperSTAR database you partially built in the previous step.
6. Use the Update All function to channel the fact table data from the fact table in the second source database.

Different Fact Tables in Different Source Databases

If you have multiple fact tables and they are in different databases, then the process for building your SuperSTAR database is similar to the first example above (classification tables and fact tables in different databases). In this case, the first source database you connect to must contain all the classifications and the master/parent fact table (rather than a dummy fact table).

As you connect to each additional fact table, you must ensure you add the links between the fact table foreign and primary keys.