8. Install a database system to handle the ETL_Temp database

Applies to the following products: 
Questionmark Perception
Applies to the following Perception versions: 
Perception 5.4

A database system for the ETL_Temp database needs to be installed on your ETL Services Server server.

A database system is needed to hold result data while it is being readied for the Results Warehouse. Because your Extract, Transform, and Load service is on a different server from the database, it becomes more efficient to ETL the data to a local database and then move it to the destination database in the correct format. This is illustrated in the diagram below:

For instructions on how to install either SQL Server 2008 (or 2008 R2) or Oracle 11g, please refer to the accompanying documentation of each database system. Once you have installed the database system you will be using for the ETL service, see the appropriate section below to configure your database system for use with Perception.

SQL Server 2008

Enable TCP/IP

The following network options that are disabled by default will need to be enabled.

  1. From the Start menu, navigate to Programs | Microsoft SQL Server 2008 | Configuration Tools | SQL Server Configuration Manager
  2. Expand SQL Server Network Configuration, and select Protocols
  3. Enable TCP/IP and Named Pipes
  4. Close the SQL Server Configuration Manager

Start SQL Browser

  1. From the Start menu, navigate to Programs | Microsoft SQL Server 2008 | Configuration Tools | SQL Server Configuration Manager
  2. Click SQL Server Services in the left-hand pane, or double-click SQL Server Services in the right-hand pane
  3. Start SQL Server Browser

Add Firewall exceptions

If you have a software firewall installed on your server, you will need to add exceptions for the SQL Server and SQL Browser services. If you are using Windows Firewall, do the following:

  1. From the Start menu, navigate to Control Panel | Windows Firewall
  2. Click Allow a program through Windows Firewall
  3. Go to the Exceptions tab
  4. Click Add Program
  5. Browse to and select C:\Program Files\Microsoft SQL Server\MSSQL10.<instance_name>\MSSQL\Binn\sqlserver.exe or C:\Program Files (x86)\Microsoft SQL Server\MSSQL10_50.<instance_name>\MSSQL\Binn\sqlservr.exe if you installed the 32-bit version of SQL Server 2008 on a 64-bit operating system (the folder locations may be slightly different for your installation)
  6. Click OK
  7. Click Add Program
  8. Browse to and select C:\Program Files\Microsoft SQL Server\90\Shared\sqlbrowser.exe or C:\Program Files (x86)\Microsoft SQL Server\90\Shared\sqlbrowser.exe
  9. Click OK and then click OK again

If you are using a third-party software firewall, you will need to follow their instructions to add exceptions for sqlservr.exe and sqlbrowser.exe.

After you have installed SQL Server, continue to step 9 using the link below.

Oracle 11g

The 32-bit Oracle client must be installed for use with the ETL service and temporary ETL database, even if you are using a 64-bit version of Windows Server 2008. The 64-bit Oracle client will not work with the ETL service and temporary ETL database.

The 32-bit version of Oracle 11 or 11g R2 can be downloaded from the following location:

http://www.oracle.com/technetwork/database/enterprise-edition/downloads/index.html

Follow the relevant Oracle instructions to install Oracle 11g R2.

After you have installed Oracle, continue to step 9 using the link below.