Warehousing OpenInsight Tables to MSSQL Server 7.0 (General,Warehousing)

Introduction

 

The following provides a step-by-step walkthrough example of using the OpenInsight Data Warehousing Wizard to warehouse linear hash tables into a MS SQL Server Database using ODBC connectivity.  Revelation Software has successfully performed  Warehousing OpenInsight Tables to MSSQL Server 7.0  using OpenInsight 3.72 and ODBC.

 

Note:  For instructions specific to MSSQL Server version 6.5, please refer to the end of this document. 

 

 

Pre-Requisites

 

1) Verify that you have a 32-bit SQL Server ODBC driver file.  An example is  SQLSRV32.DLL version 3.70.06.23 dated 11/20/98 (installed, for example, during the MS SQL Server 7.0 Client Install, see step 2 below).  You should be able to see this driver listed within your ODBC 32-bit Data Sources (located within the Control Panel) under the  Drivers  tab.

 

2) Install the SQL Server Client to the workstation.  Minimum install requires the Client Connectivity tools (as can be seen in the Desktop custom install). Use the Client Network Utility to add the SQL Server to the list of available servers.

 

3) For this walkthrough, we recommend installing the MS SQL Server 7.0 Query Analyzer (Under Management Tools, during setup). Test your connection by using Query Analyzer on a known table on the server.

 

4) You will have to  thunk  your ODBC.INI file.  See the OpenInsight KB article on  Seeing 32-Bit DataSources in OI  (click here for the link to it).

 

 

Walkthrough

 

1.  Preparing OpenInsight 3.72 for SQL 7.0 Data Warehousing:

        Create a directory named SQL7TEST on a local hard drive of the workstation (this walkthrough will use SQL7TEST as the application name, user name, table name, etc. for demonstration purposes).

        Install a fresh copy of OpenInsight 3.7.2 to that directory

 

2.  Creating a application

        Start OpenInsight 3.72.

        Open the SYSPROG application.

        In SYSPROG Application Manager view, choose FILE  NEW APPLICATION from the menu.

        Create a new application called SQL7TEST with the username SQL7TEST

 

3. Creating a table

        In the SQL7TEST Application Manager choose TOOLS  TABLE BUILDER from the menu.

        From the Table Builder menu choose FILE  NEW

        Click OK to accept the default file location.

        Enter the fields in Table 1 into the table.

        From the Table Builder menu choose FILE  SAVE

        When asked for a table name, enter SQL7TEST.

        Close Table Builder.

 

Example Table 1

 

 

4. Creating a form

        Once back in the SQL7TEST Application Manager choose TOOLS  FORM DESIGNER from the menu.

        From the Form Designer menu choose FILE  NEW FORM

        Under the Data Source heading choose OPENINSIGHT TABLES

        Under the Tables heading double-click SQL7TEST.

        Highlight all of the fields under the COLUMNS heading.

        Click ADD to add the fields to the form.

        Click OK to finish designing the form.

 

5. Saving and running the form

        From the Form Designer menu choose FILE  TEST RUN

        In the Test Run Setup window, make sure that both  Actual data tables  and   Always save  are checked.

        Click OK.

        When asked what to save the form as, type SQL7TEST.

        When the form is saved it will run and accept data entry.

 

6.  Populating the form with data

        Populate the table with the ten records in Table 2.

        Once the table has been populated close the form by choosing FILE  CLOSE from the form menu.

        Close the Form Designer by choosing FILE | EXIT from the Form Designer menu.

        You should now be back in the Application Manager.

 

       Example Table 2

 

7.  Creating an SQL Connection in OpenInsight

        From the Application Manager, choose the TOOLS  CLIENT/SERVER WORKSPACE icon.

        From the Client/Server Workspace menu choose FILE  NEW            

        At the New Entity window, highlight Connection and click OK.

        From the Connection window make sure that the data source type is ODBC.

        Double-Click on the Value field for Data Source and you should see something similar to below:

        Choose your data source and then click OK. (The SQL7TEST data source will only appear after you have created it.  For the   purposes of this demonstration, SQL7TEST was chosen as the data source).

        From the Client/Server Workspace menu choose FILE  SAVE AS.

        When prompted for a filename, enter SQL7TEST.

 

 8.  Setting the system defaults

        From the Client/Server Workspace choose TOOL  CONNECTION DEFAULTS.

        Select ODBC as the Data Source.

        Ensure that Use Transaction Processing is not checked.

        Click the Accept button.

 

9.  Starting the Data Warehousing Procedure

        From the Client/Server Workstation menu choose TOOLS  CREATE WAREHOUSE PROCEDURE

        From the drop down menu choose the SQL7TEST table.

        Click the NEXT button.

        Click the NEXT button to accept the default number of SQL tables.

        Make sure that the net window lists the ID field as the primary key.

        Click on the DEFINE button to start the Table Definition Wizard.

 

10. Continuing the Data Warehouse Procedure with the Table Definition Wizard

        When asked for a unique table name, enter SQL7TEST.

        Click the NEXT button.

        Click the NEXT button to accept the defaults concerning primary keys.

        At the next window, click the ADD button to add specific rows to be warehoused.

        When a listing of the rows appears, highlight each row, and then click the NEXT button.

 

11.  Modifying the Default SQL Scripts and Finishing the Data Warehousing Procedure

        A window containing SQL scripts like those below should now appear:

 

                 create table SQL7TEST

                         (

                         ID text not null primary key,

                         FIELD_1 text null,

                         FIELD_2 text null,

                         FIELD_3 text null

                         )

 

        We are going to delete the bolded portions of the SQL scripts:

 

                 create table SQL7TEST

                         (

                         ID text not null primary key,

                         FIELD_1 text null,

                         FIELD_2 text null,

                         FIELD_3 text null

                         )

 

 

        The final script should look like this:

 

                 create table SQL7TEST

                         (

                         ID text,

                         FIELD_1 text,

                         FIELD_2 text,

 

                         FIELD_3 text

                         )

 

        Click the FINISH button.  The table we just created should be listed.

        Click the NEXT button.

        Click the FINISH button to complete the Data Warehousing Procedure.

 

12. Using Warehouse Manager

        From the Client/Server Workstation menu choose TOOLS  WAREHOUSE MANAGER

        At the next window, make sure that the SQL7TEST table is listed, and the checkbox is checked.

        Click the NEXT button.

        Click the NEXT button to accept the defaults concerning workstations

        At the next window make sure  Yes, Create The Data Warehousing Tables for Me  is checked.

        Click the NEXT button.

        Click the NEXT button to accept the defaults concerning clearing tables.

 

13. Connecting to a Database:

        From the Initiate Warehousing window, click the CONNECT button.

        At the Connect to Data Source make sure that the correct SQL connection is specified (i.e., SQL7TEST).

        Make sure the Login Name and Password are correct.

        Click the OK button.

        Click the NEXT button.

 

14.  Running the Warehouse Procedure

        From the Initiate Warehousing window, make sure Run Each Warehousing Procedure Once is checked.

        Click the FINISH button.  This will open the Warehouse Manager window.

        From the Warehouse Manager window, click the START button.

 

The system will now go through the warehouse procedure.  If there is an error in the process the Review Log button will become enabled and clicking on it will reveal the errors encountered in the warehousing process.  If there are no errors and all of your records were processed, then your new table has been created.

 

NOTE:  If the Review Log button is enabled, repeat the Data Warehousing steps again and verify all the above steps were followed exactly.

 

15.  Verifying Success of the Data Warehouse Procedure

        From the Client/Server Workspace choose TOOL  QUERY WINDOW

        At the  Connect to Data Source  window, select SQL7TEST as the data source.

        Make sure the Login Name and Password are correct.

        Click the OK button.

        At the top of the New Query window (where the flashing cursor is) type the following:

SELECT * FROM SQL7TEST

 

        Click on the Test Run button. The rows from your warehoused table should appear:

 

 

 

 

MSSQL Server 6.5 Notes

MSSQL Server 6.5 has been successfully tested to warehouse large amounts of OpenInsight (version 3.72) data using a native connection without any OpenInsight feature limitation.  The instructions below are specific to those preferring a native connection to SQL Server 6.5.

 

Omit steps 7-8 in the MSSQL 7.0 instructions above, and replace with steps a-e below.  Upon completion of steps a-e, proceed to step 9 of this document.

a.  Obtaining and running the SQL batch file

        Open the MS6BATCH.TXT file located in your root OINSIGHT directory (This is also located on our website www.revelation.com__ in our  For WORKS  Downloads section).         Copy all of it, open your MSSQL Query Analyzer, start a New Query, paste the MS6BATCH.TXT contents in the query window and run the query.   b.  Obtaining the appropriate .DLLs         Place the following .dlls in your Windows/system directory for Win95/98 users, or your Winnt/system directory for Windows NT users: charset_utf-8charset_utf-8   c.  Creating a native SQL Connection in OpenInsight         From the Client/Server Workspace menu choose FILE  NEW         At the New Entity window, highlight Connection and click OK.         From the Connection window make sure that the data source type is SQLServer.         Fill out the information shown in Figure 1 (Connection window) below.         From the Client/Server Workspace menu choose FILE  SAVE AS         When prompted for a filename, enter SQL7TEST.   Figure 1   d. Setting the system defaults         From the Client/Server Workspace choose TOOL  CONNECTION DEFAULTS.  Select SQLServer as the Data Source.         Ensure that Use Transaction Processing is not checked.         Click the Accept button.   e. Testing the SQL connection         From the Client/Server Workspace choose TOOL  QUERY WINDOW         At the Connect to Data Source make sure that the correct SQL.connection is specified.         Make sure the Login Name and Password are correct.         Click the OK button.   Proceed to Step 9 of this document.