Setting up the TrueSight Smart Reporting - Platform repository database

This topic provides instructions on setting up the TrueSight Smart Reporting - Platform repository database.

TrueSight Smart Reporting - Platform stores information such as metadata, users, and user permissions for reports in the TrueSight Smart Reporting - Platform repository database. The repository database can be one of the following:

PostgreSQL
Oracle
SQL Server

If you are using Microsoft SQL Server or PostgreSQL as the repository database, you have several options:

You can create a new database with a new user before you install the product.
You can create a new database with a new user while you install the product. In this option, you can create a new database and user from the installer.
You can use an existing database and create a new user before you install the product.
You can use an existing database and create a new user while you install the product. In this option, you can create a new user from the installer.
You can use an existing database and an existing user for the product.

Setting up Microsoft SQL Server as the repository database

You can create a new repository database and database user or use an existing database and user. You can also choose to create a new database and user from the installer while you install the product.

BMC recommends that you create a new database and database user. If you use an existing database, ensure the following:

The database does not contain any tables.
If you are using an existing user with an existing database, the user has the db_owner permission on the database.

For more information on the Microsoft SQL Server database, see the Microsoft SQL Server documentation (https://docs.microsoft.com/en-us/?view=sql-server-ver15).

Setting up Oracle as the repository database

You can use the existing database instance of your component as the repository database. Create the tablespace and database user that you must provide when you install TrueSight Smart Reporting - Platform.

Note: Avoid an error when the Oracle server and client are installed on the same computer

An error might occur when the Oracle server is installed on the same computer with Oracle client because of incorrect sequence of the Oracle path in the PATH variable. To avoid this issue, set the client entry before the Oracle server entry in the path variable. For example:

PATH : D:\oracle\product\11.2.0\client_1\bin;D:\oracle\product\11.2.0\db_1\bin;

For more information on the Oracle database, see the Oracle documentation. (https://docs.oracle.com/en/database/oracle/oracle-database/index.html).

Creating tablespaces and user on the Oracle database for TrueSight Smart Reporting

On the Oracle database, you can use the same tablespace as your component. However, you can create a new tablespace for TrueSight Smart Reporting - Platform.

On the Oracle database, you must create a user with appropriate rights for TrueSight Smart Reporting - Platform.

If you want to create a new tablespace for TrueSight Smart Reporting - Platform, ask your database administrator to do the following:

Create a tablespace in the Oracle database. Use the following command:

CREATE TABLESPACE TSR_REPOSITORY LOGGING DATAFILE 'REPOSITORY_01.dbf' SIZE 1G REUSE AUTOEXTEND ON NEXT 500M MAXSIZE 30G EXTENT MANAGEMENT LOCAL AUTOALLOCATE SEGMENT SPACE MANAGEMENT AUTO
Create a user in the Oracle database. Use the following command:

Command
CREATE USER <user> PROFILE DEFAULT IDENTIFIED BY <password> DEFAULT TABLESPACE TSR_REPOSITORY TEMPORARY TABLESPACE TEMP ACCOUNT UNLOCK;
Example
CREATE USER SMARTREPORTINGUSER PROFILE DEFAULT IDENTIFIED BY SMARTREPORTINGPASSWORD DEFAULT TABLESPACE TSR_REPOSITORY TEMPORARY TABLESPACE TEMP ACCOUNT UNLOCK;
Run the following commands in the given order to grant privileges to the user:
Note: Replace the user name

In all the following commands, replace SMARTREPORTINGUSER with the user name that you created.
- GRANT CREATE SESSION TO SMARTREPORTINGUSER;
- GRANT CREATE TRIGGER TO SMARTREPORTINGUSER;
- GRANT CREATE SEQUENCE TO SMARTREPORTINGUSER;
- GRANT CREATE PROCEDURE TO SMARTREPORTINGUSER;
- GRANT CREATE OPERATOR TO SMARTREPORTINGUSER;
- GRANT CREATE INDEXTYPE TO SMARTREPORTINGUSER;
- GRANT CREATE TABLE TO SMARTREPORTINGUSER;
- GRANT CREATE TYPE TO SMARTREPORTINGUSER;
- GRANT CREATE CLUSTER TO SMARTREPORTINGUSER;
- ALTER USER SMARTREPORTINGUSER QUOTA UNLIMITED ON TSR_REPOSITORY
All these permissions are required to lay down the TrueSight Smart Reporting - Platform repository schema.

Setting up PostgreSQL as the repository database

You can create a new repository database and database user or use an existing database and user. You can also choose to create a new database and user from the installer while you install the product.

BMC recommends that you create a new database and database user. If you use an existing database, ensure the following:

The database does not contain any tables.
If you are using an existing user, the user has the LOGIN and INHERIT permissions.

Before installing the product, use the installation files provided with TrueSight Smart Reporting - Platform on the EPD website to install the PostgreSQL database.

For more information on the database, see the the PostgreSQL documentation (https://www.postgresql.org/docs/10/index.html)

Where to go from here

If you are installing the product with the SQL Server database with Windows authentication, you must grant the log on as a service permission. For instructions, Granting the log on as a service permission for installation on the SQL database with Windows authentication.