A1. Create the databases manually

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

Perception 5.4 requires several databases, as depicted in the image below:

As you can see, two databases will need to be created on your database server. These are your Main (Perception_Main) and Results Warehouse (Perception_RW) databases. A third database is also required to be installed on your Perception server, where the ETL service is installed. This third database, the temporary ETL (ETL_Temp) database, is used by the ETL service to ensure the data from the repository is correctly formatted for the Results Warehouse database.

If you want to create these three databases automatically, please refer to step 11 (Create the Main and RW databases) and step 12 (Create the temporary ETL database) of this installation guide.

To manually create your databases and users, you will need to:

  • Create the necessary database logins
  • Create the necessary databases
  • Create a relevant ODBC connection to the database from your Perception server

Click on the relevant section to expand it and view instructions on creating these three databases (Perception_Main, Perception_RW, and ETL_Temp).

SQL Server 2008

In the steps below, the following example names are used to make the instructions easier to follow.

For:

Example login name

Example database name

Configuration and People database

Perception_Login

Perception_Main

Results Warehouse database

Perception_RW

Temporary ETL database

Perception_ETL_Temp

 

The steps below are intended for the creation of the new databases. They do not include the creation of the shared repository database. If you are installing a fresh installation of Perception, you will first have to create it. For further information, refer to step 8 (Create the shared repository database on your database server).

Also, if you don't use the default names, please ensure you do not use illegal characters when creating your database. For more information, please refer to A4. Illegal characters.

To create the necessary databases manually:

Configure SQL Server authentication

To ensure that SQL Server is configured to provide authentication based on the SQL Server login, you will:

  1. Open the SQL Server Management Studio
  2. Right-click on the database server, and click Properties
  3. Open the Security page
  4. Ensure that the SQL Server and Windows Authentication Mode option button is selected and click OK

Create a database login

You will need to create a login for your databases to use. You will need to use the same login name for all the databases.

To do so:

  1. Open SQL Server Management Studio and login with your administrator details
  2. Expand the server folder in the navigation pane
  3. Expand the Security sub-folder
  4. Right-click Logins, and select New login...
  5. Enter a name for the new login (e.g., Perception_Login)
  6. Select SQL Server Authentication and enter a password
  7. Disable the Enforce password expiration and User must change password at next login checkboxes
  8. Select Server Roles from the Select a page navigation menu on the left
  9. Enable the bulkadmin checkbox
  10. Click OK to create the login

Play Video

Create the Perception_Main and Perception_RW databases and assign them the login

You now need to create the Main and Results Warehouse databases.

To do this:

  1. Open SQL Server Management Studio and login with your administrator details
  2. Expand the server folder in the navigation pane
  3. Right-click Database and select New Database...
  4. Enter a name for the database in the Database name text-box.

    For example: Perception_Main if you are creating the Configuration/People database, or Perception_RW if you are creating the Results Warehouse database.

  5. Click the Browse (...) button next to the Owner text-box
  6. The Select Database Owner screen displays. Click Browse...
  7. Locate the user you created above and enable the checkbox next to it
  8. Click OK
  9. Select Options from the Select a page navigation menu of the left
  10. In the Collation drop-down box, select SQL_Latin1_General_Cp1_CI_AS
  11. Click OK to create the database

Play Video

Create the temporary ETL_Temp login and database

In this single-server installation, the temporary ETL database (ETL_Temp) needs to be created on the same server as the ETL service, i.e., your Perception server.

First, you need to create a database login. To do so:

To do so:

  1. Open SQL Server Management Studio and login with your administrator details
  2. Expand the server folder in the navigation pane
  3. Expand the Security sub-folder
  4. Right-click Logins, and select New login...
  5. Enter a name for the new login (E.g., Perception_Login. This should match the user you created for your SQL Server databases.)
  6. Select SQL Server Authentication and enter a password
  7. Disable the Enforce password expiration and User must change password at next login checkboxes
  8. Select Server Roles from the Select a page navigation menu on the left
  9. Enable the bulkadmin checkbox
  10. Click OK to create the login

Now, you need to create the actual ETL_Temp database. To do so:

  1. Open SQL Server Management Studio and login with your administrator details
  2. Expand the server folder in the navigation pane
  3. Right-click Database, and select New Database...
  4. Enter a name for the database in the Database name text-box (e.g., Perception_ETL_Temp)
  5. Click the Browse (...) button next to the Owner text-box
  6. The Select Database Owner screen displays. Click Browse....
  7. Locate the user you created above and enable the checkbox next to it
  8. Click OK
  9. Select Options from the Select a page navigation menu of the left
  10. In the Collation drop-down box, select SQL_Latin1_General_Cp1_CI_AS
  11. Click OK to create the database

You have now created the ETL login and database.

Oracle

In the steps below, the following example names are used to make the instructions easier to follow.

For:

Example login name

Example database name

Configuration and People database

Perception_Main

Perception_Main

Result Warehouse database

Perception_RW

Perception_RW

Temporary ETL database

Perception_ETL_Temp

Perception_ETL_Temp

 

The steps below are intended for the creation of the new databases. They do not include the creation of the shared repository database. If you are installing a fresh installation of Perception, you will first have to create it. For further information, refer to step 8 (Create the shared repository database on your database server).

Also, if you don't use the default names, please ensure you do not use illegal characters when creating your database. For more information, please refer to A4. Illegal characters.

As part of the manual process for Oracle users will need to:

Create the required Oracle schemas

A schema is a collection of database objects. A schema is owned by a user and has the same name as its Tablespace. Schema objects are logical structures created by users to contain, or reference, their data. You can create and manipulate schema objects using Oracle Enterprise Manager. To create the necessary user and Tablespace for your Perception schemas, we will use SQL commands.

To enter SQL commands, you will need to login to SQLPLUS as an administrative user who has the appropriate permissions. For example, the SYSTEM user.

To enter the SQLPLUS command line interface enter the following at a command line prompt:

sqlplus system/<password>@<myserver>/<mysid>

...where:

<password> is the password associated with the SYSTEM user

<myserver> is the server name for your Oracle installation

<mysid> is the System ID for your Oracle server set during installation

Once you have successfully logged in, enter the following code to generate a Role that will define the required permissions:

CREATE ROLE <Role>;

GRANT CREATE SESSION, CREATE DATABASE LINK, CREATE PROCEDURE, CREATE SEQUENCE, CREATE SYNONYM, CREATE TABLE, CREATE TRIGGER, CREATE VIEW, CTXAPP TO <Role>;

Once you have created a role, you will need to create a Tablespace and a User for that Tablespace. The User and Tablespace should use the same name (the Perception_MAIN Tablespace should have a Perception_MAIN User).

CREATE TABLESPACE <Tablespace>

LOGGING

DATAFILE '/<Tablespace>.dbf'

SIZE 32m AUTOEXTEND ON

NEXT 32m MAXSIZE 2048m

EXTENT MANAGEMENT LOCAL

SEGMENT SPACE MANAGEMENT AUTO;

CREATE USER <User> IDENTIFIED BY <Password>

DEFAULT TABLESPACE <Tablespace>

TEMPORARY TABLESPACE TEMP

QUOTA UNLIMITED ON <Tablespace>;

GRANT <Role> to <User>;

...where:

<Role> is a set of permissions to apply to a user. For example Perception_ROLE

<Tablespace> is the name of the Database you are creating. For example, Perception_Main or Perception_RW.

<File Name> is the physical file where the Tablespace data will be stored.

<User> is the username associated with the Tablespace above. This needs to be the same as the Tablespace. For example, if you are creating Perception_Main, the user should also be Perception_Main.

<Password> is the password assigned for the user. Please ensure you have taken a note of the passwords assigned for each Schema as this and the above details will need to be entered in the configuration application.

Repeat the above process until both the Perception_Main and Perception_RW schemas are created.

Create the temporary ETL_Temp schema

Now, you need to create a schema for the ETL_Temp database. This can be done using the SQLPLUS command line to create the relevant Tablespace and User.

To enter SQL commands, you will need to login to SQLPLUS as an administrative user who has the appropriate permissions. For example, the SYSTEM user.

To enter the SQLPLUS command line interface enter the following at a command line prompt:

sqlplus system/<password>@<myserver>/<mysid>

...where:

<password> is the password associated with the SYSTEM user

<myserver> is the server name for your Oracle installation

<mysid> is the System ID for your Oracle server set during installation

Once you have successfully logged in, enter the following code to generate your ETL_Temp schema:

CREATE TABLESPACE <Tablespace>

LOGGING

DATAFILE '/<Tablespace>.dbf'

SIZE 32m AUTOEXTEND ON

NEXT 32m MAXSIZE 2048m

EXTENT MANAGEMENT LOCAL

SEGMENT SPACE MANAGEMENT AUTO;

CREATE ROLE <Role>;

GRANT

CREATE SESSION,

CREATE PROCEDURE,

CREATE SEQUENCE,

CREATE SYNONYM,

CREATE TABLE,

CREATE TRIGGER,

CREATE VIEW,

CREATE DATABASE LINK,

CTXAPP

TO <Role>;

CREATE USER <User> IDENTIFIED BY <Password>

DEFAULT TABLESPACE <Tablespace>

TEMPORARY TABLESPACE TEMP

QUOTA UNLIMITED ON <Tablespace>;

GRANT <Role> to <User>;

...where:

<Role> is the name of a role that can be assigned to a user. For example, Questionmark_Role. The role determines which permissions a user can have.

<Tablespace> is the name of the Database you are creating. For example, Perception_ETL_Temp.

<File Name> is the physical file where the Tablespace data will be stored.

<User> is the username associated with the Tablespace above. This needs to be the same as the Tablespace. For example, Perception_ETL_Temp.

<Password> is the password assigned for the user. Please ensure you have taken a note of the passwords assigned for each Schema as this and the above details will need to be entered in the configuration application.

You have now created the ETL_Temp database.