Using the Data Warehousing feature in OpenInsight (version 3.3 and above) (Warehousing)

There are two other documents that should be read in conjunction with this one, Data Warehousing: SQL Server Specifics & OpenInsight: What s new in 3.3.  These are located on the Knowledge Base section of our website.

Following, is a brief description of how to use the Data Warehousing Wizard.  The example used here is taking a Linear Hash Table and sending it to a FoxPro data file. Some initial things to make sure that you have:

First you must have the ODBC drivers and an ODBC administrator tool to make the connection to the specified database driver. You should also be familiar with the database that you will be using.

The first step is to make sure that the ODBC connection to your database is made. This is done with an ODBC administrator. After that connection is made you need to make the connection using the Client Server Work Space (C/S) - these are two different connections, the second is through OI. Choose New under the File menu, make sure that CONNECTION not DATA SET is highlighted. Then double click in the DATA SOURCE row to bring up the selection box where you should find the connection that you made with your ODBC administrator.  In the case of this example,  it is TEST_FOX. It is a good idea to test your connection before continuing. To do this, use the option Query Window under the Tools menu. This requires some SQL syntax to see how the connection functions. For example enter select * from Table Name in the query window and then run it.  Or use the List Objects under the Query menu item.  If there is no error then everything is set up correctly.

After making sure that your ODBC connection is correct,  proceed to the Create Warehouse Procedure (found under the Tools menu item). Choose the linear hash table  you d like to warehouse the data to.  Then Choose the Options button for the linear hash table you want to warehouse.  It is suggested that you use the lowercase option and that you follow the white paper at our knowledge base for choosing the SQL data types (Data Warehousing: SQL Server Specifics).

The next step is to go to the Next window, which will allow you to choose how many tables you want to make.  You will need one table for your single field data and one table for every associated multivalue group of data.  Then you will need to hit the Next button accepting the default values.  Next, click the Define button (this will bring up the definition for this table, you must give the table a name).  This name will be what your new database file will be called. You also need to make sure that if you are using more than one table that you have a unique name for each one.

Now, click the Next button and accept the values.  If there are any associated multivalue groups of data, you will need to add that group of data with the Add button.  If you have a multivalue field, though, you need to use the Add @MV.  After clicking the Next button you will need to add the fields that you want to have show up in your new data file.  To do this, use the Add button, which produces a popup, and select the fields you need.

Your next step is to click Next.  This will put you to the create script window.  In this step, it is very important that you understand the database you are using.  The scripts that are generated automatically are suggestions for those procedures, but they may need to be modified for each database.  For example for the FoxPro database that was used for this example the null/not null and primary keys needed to be deleted from the scripts. Click the Finish button and give the procedure a name so that you can use it in the warehouse manager.

The next step is to go to the Warehouse Manager under the Tools menu option.   Select the warehouse procedure you just saved. Click the Next button, this screen lets you decide who will also be able to use this warehouse procedure.  Click Next and then say yes (since this is the first time you are creating this table) and hit the Next button.  In the next screen, it is a good idea to select the default clear the table by dropping and recreating, hit the Next button.  You then want to set the connection by clicking the Connect button and setting it to the ODBC connection made earlier (FOX_TEST). You should make sure that transactions is not checked, since this driver does not support transactions.  Choose run each warehouse procedure then click Finish.

This brings you to the next window that allows you to start your warehousing.  First click the Start button  (If there is an error in your process,  the Review log button will be enabled to see the error messages - press this button).  If there are no errors and all of your rows were processed,  then your new table has been created.  To view this, open up your application FoxPro, and then go to the directory were your ODBC connections have been made and open the file.