Create and Configure a Database for Reports

To create a database for Reports, follow these steps:

1. Create a database for reports with a custom name along with prefix and suffix. Prefix and suffix are optional. For example, database name is <prefix>reportsdb<suffix>.

   The following is sample query for creating a database in MSSQL:

   CREATE DATABASE reportsdb;

   The following is a sample query for creating a database in MySQL:

   
   
   CREATE DATABASE `<DBNAME>` DEFAULT CHARACTER SET utf8 COLLATE utf8_unicode_ci;

   NOTE:  For Oracle databases, a schema name should be in capital letters.
   
   Find the word reportsdb in SQL files located in the admin scripts and replace it with REPORTSDB.

   NOTE: For Oracle, create necessary tablespaces. Refer to Prerequisites for Quantum Fabric with Oracle.

   The following details are required for Flyway configuration:

   • Schema name for reports: reportsdb
   • Placeholders for Reports:

     # For Reports (reportsdb), replace the following placeholders in SQL migrations for your database

     
     
     flyway.placeholders.KONY_METRICS_LOGGER_JNDI=java:comp/env/jdbc/konyreports
     flyway.placeholders.KONY_METRICS_LOG_LEVEL=INFO
     flyway.placeholders.KONY_METRICS_LOG_OPTION=logfile
     flyway.placeholders.KONY_METRICS_LOG_LOCATION=<log_location_for_metrics>
     

   • Tablespace Placeholders for Oracle:

     Product Name	Tablespace Placeholders for Oracle
     Reports / Metrics	METRICS_DATA_TABLESPACE, METRICS_INDEX_TABLESPACE, METRICS_LOB_TABLESPACE

   • SQL files paths for Reports in QuantumFabric_Plugins folder:

     Click here for more details

     Path for SQL files in the QuantumFabric_Plugins folder	Database	Component
     \QuantumFabric_Plugins\middleware\reportsdb_mysql	MySQL	ReportsDB
     \QuantumFabric_Plugins\middleware\reportsdb_oracle	Oracle
     \QuantumFabric_Plugins\middleware\reportsdb_mssql	SQL Server

2. Execute all SQL scripts by using the steps provided at Configuring Flyway Command-line Tool.

   Click here to view the ReportsDB (Metrics) schema diagram

Since the structure of flyway has changed from Flyway 3.2.1 to Flyway 4.0.3 in Quantum Fabric installer, execute the following statements to make the schema_version table compatible with Flyway 4.0.3.

Oracle:

drop index "schema_version_ir_idx";
drop index "schema_version_vr_idx";
ALTER TABLE "schema_version" DROP constraint "schema_version_pk" drop index;
ALTER TABLE "schema_version" DROP COLUMN "version_rank";
ALTER TABLE "schema_version" modify("version" null);
ALTER TABLE "schema_version" add constraint "schema_version_pk" primary key("installed_rank");

MySQL:

ALTER TABLE schema_version DROP INDEX schema_version_vr_idx;
ALTER TABLE schema_version DROP INDEX schema_version_ir_idx;
ALTER TABLE schema_version DROP PRIMARY KEY;
ALTER TABLE schema_version DROP COLUMN version_rank;
ALTER TABLE schema_version CHANGE version version VARCHAR(50);
ALTER TABLE schema_version ADD PRIMARY KEY (installed_rank);

SQL Server:

DROP INDEX schema_version_ir_idx ON dbo.schema_version
GO
DROP INDEX schema_version_vr_idx ON dbo.schema_version
GO
ALTER TABLE dbo.schema_version DROP CONSTRAINT schema_version_pk
GO
ALTER TABLE dbo.schema_version DROP COLUMN version_rank
GO
ALTER TABLE dbo.schema_version ADD CONSTRAINT schema_version_pk PRIMARY KEY CLUSTERED (installed_rank)
GO
ALTER TABLE dbo.schema_version ALTER COLUMN version nvarchar(50) NULL
GO