Creating Logical Information Kiosks (LINK)

Overview

Logical Information Kiosks (LINKS) represent an excellent example of SQL Integrator (SQLI) in action, providing universal data access for end users. With a SQLI Information Kiosk end users access many types of corporate data with familiar end user tools, increasing the effectiveness and competitiveness of the organization. SQLI utilizes existing network and client side interfaces and will fit into your existing information services architecture without costly application changes or system management effort.

Installation Requirements

Creation of an Information Kiosk starts with the installation of SQL Integrator. In our example we have installed SQLI Manager and Broker on the NT server running Microsoft SQL Server. The DBA has also set up Oracle's SQL*Net connection to a Sun Solaris system running an Oracle database. Our Windows 98 client system has the SQLI ODBC driver installed. After completing installation of the SQLI components the database administrator (DBA) creates a global data dictionary using SQLI's automated metadata import facility. The steps to build the dictionary are described in the SQLI product documentation. Once created, the data dictionary becomes the single data access point for any ODBC client tool, including Excel or Crystal Reports. The last step before the enduser tool can acccess the SQLI dictionary is creation of a ODBC Data Source Name entry (DSN) on the end user's desktop system. The end user application connects to the SQLI DSN, opens the SQLI global data dictionary and accesses the defined database tables. The SQLI dictionary shields end users from having to know how to access the physical data servers.

Step By Step Instructions

Lets set up an Information Kiosk to use with Segate Analysis, the complementary download version of Crystal Reports. Our DBA has installed SQL Integrator and defined a data dictionary called demo.dic. It contains the SQL Server and Oracle table information. The DBA has selected three sets of tables: global.deptmssql from SQL Server; global.customersora and global.empora from the Oracle database; and global.dept, global.customers, global.emp which are ISAM files. All of these database tables will be accessible from ODBC based client applications after we define a SQLI ODBC data source. In SQLI Manager the data dictionary entries would look like the following:

Creating an ODBC DSN for SQL Integrator

Open your Windows Control Panel folder and double click the ODBC icon.

After the ODBC Data Source Administrator window opens, click on the System DSN tab. At this point you will see a list of currently configured DSNs for your system. Click on the Add button.

From the "Create New Data Source" window, select SQLIntegrator Driver and click Finish to open the SQL Integrator ODBC Setup window. If the SQL Integrator Driver does not appear in the list you need to install the SQL Integrator ODBC Client component.

The "SQL Integrator ODBC Setup" window is used to configure your SQLI DSN. It defines a Data Source name, Description and Connection String.

The Data Source name is the name you will use to connect to your ODBC data source. It is typically a short text string that is easily recognized as belonging to the application or to SQL Integrator. An example might be "SQLI_demo" where SQLI indicates an SQL Integrator application and demo is the name of data dictionary.

The Description is simply a text string describing the data source. It is not actually used anywhere and can be anything you want such as "SQLI DSN using demo.dic for the Information Kiosk Application." For our example, we are using "InfoKiosk".

The Connection String is the information for the network connection. The syntax for this string is critical and has the following basic format:

/dictionary@port:host!service where

· dictionary is the name of the data dictionary
· port is the port number used by TCP/IP to connect to the SQL Integrator broker (default port is 1958)
· host is the name of the server (or internet address) where the SQL Integrator broker is installed, and
· service is vtx16, which is the name of the executable host program on the broker

For our example, we are using "/c:/kiosk/demo.dic@1958:cherry!vtx16" for the connection string. We could have also used /c:\\kiosk\\demo@1958:cherry!vtx16". After you click OK, you should return to the ODBC Data Source Administrator screen and see your newly created DSN. Be sure to test and verify it works. If you should need to change something, you can click the "Configure" button to return to the SQL Integrator ODBC Setup window.

Using Segate Analysis with the SQLI DSN

Now we are ready to use our Information Kiosk with Segate Analysis. Start up Segate Analysis from the Windows Start menu. Select the "Create a new Analysis document" and click OK.

Next the "Analysis Explorer" window will open allowing you to select the Data Source you wish to use. Click on the ODBC folder and it will open and reveal our ODBC data source InfoKiosk. Double click on InfoKiosk.

You will be prompted to login to the SQLI ODBC data source. Upon log in, SQLI will connect to the database engines you are using for access to the data as defined by your database administrator (DBA). SQLI never overrides the security of the host databases you are using.

After logging in the Analysis Explorer will display the database tables you can use in your report. As you can see they are the same tables as defined in the SQLI demo.dic on page one of this paper.

To add a table to the report simply double click on the name or drag it up to the top window of Segate Analysis. When placed it will display the fields defined in the tables. For our report we will use the deptmssql table (from MS SQL Server on NT) and empora table (from Oracle on Solaris).

We need to define the join field between the two database tables. In our example the field deptno is common to both tables. Select the deptno field in the deptmssql table and drag it to deptno in the empora table. Seagate Analysis will build a join for the report. The join is represented graphically with an arrow between the fields.

The next step is to select the fields to be used in our report. Double click on the field names and they will be added to the "Fields" section. We selected deptno, name, empno and ssno. Click on the "report" tab at the bottom of the window to see your report !

Summary

You have just created a multi-table and multi-database report in minutes. Using SQL Integrator's global dictionary, physical data server locations are transparent to the end user. Also you don't have to manage accessing data from multiple ODBC data sources, simplifying system maintenance. Your previously defined SQLI DSN is now a Logical Information Kiosk, providing data access to commonly used client tools including Microsoft Excel and Access. The same data we used in Seagate Analysis can be retrieved into an Excel graph or updated with an Access form window. Users finally have a direct path to the data they need to make informed and timely business decisions via a SQL Integrator Logical Information Kiosk.

Download this page as a PDF