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 - Platformstores 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. Do not use this option with Windows authentication.
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. Do not use this option with Windows authentication.
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. BMC recommends that you use this option with Windows authentication.

To set 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)

To set 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 yo want to create a new tablespace for TrueSight Smart Reporting - Platform, ask your 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.

Privilege	Used during	Used for	Alternative method
GRANT CREATE SESSION TO SMARTREPORTINGUSER;	Installing and using the product	Connecting to the database for all operations.	None
GRANT CREATE SEQUENCE TO SMARTREPORTINGUSER;	Installing and using the product	Generating a unique sequence number for the reporting metadata.	None
GRANT CREATE TABLE TO SMARTREPORTINGUSER;	Installing and using the product	Creating tables for the reporting metadata.	None
ALTER USER SMARTREPORTINGUSER QUOTA UNLIMITED ON TSR_REPOSITORY;	Installing and using the product	Ensuring enough space is available for TrueSight Smart Reporting - Platform to perform operations.	None

All these permissions are required to lay down the TrueSight Smart Reporting - Platform repository schema.

To set up Microsoft SQL Server as the repository database

While installing the product, if you are planning to choose the Windows Authentication option, ensure the following:

You create the database before installation.
The Windows authentication user has the db_owner permission on the database.

Except for the Windows authentication option, BMC recommends that you create a new database and database user. For the Windows authentication option, use the existing database while installing the product.

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).

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.