Creating a data warehouse database schema

To support the reporting functions provided by BMC Database Automation, or to use BMC Decision Support – Database Automation to create reports for your environment, you must create a schema on the data warehouse database where the data to create the reports will be stored. This topic describes how to create a data warehouse database schema.

• • Before you begin
  • To create a new data warehouse database schema
  • To update an existing data warehouse database schema
  • Where to go next

Before you begin

Ensure the following:

• Bash shell is set up.
• Oracle Instant Client has been installed on the Manager.
• Host has an Oracle database installed and configured with one of the supported versions.
• Oracle database has all services running.
• If you are using BMC Decision Support – Database Automation to collect report data, the Listener and TNS files have to be configured to have access the servers used in the BMC Decision Support – Database Automation deployment.
• There are no port conflicts.
• Steps below are performed as the Oracle owner on the host where the database is running and with the ORACLE_HOME and ORACLE_SID environment variables set appropriately.

To create a new data warehouse database schema

1. Obtain the data_warehouse.zip file. The data_warehouse.zip file is included in the Additional Utilities component from the BMC Software Electronic Product Distribution (EPD) website. See Downloading-the-installation-files for more information.
2. Copy the data_warehouse.zip file from your computer to the server that hosts the data warehouse database.
   The .zip file contains one top level directory (data_warehouse) and three sub-directories (export_data, export_tools, and patches).
3. Unpack the top-level directory contained in the data_warehouse.zip file to the current working directory and cd to the data_warehouse directory.

4. Create a data warehouse database user:
   export_tools/create_repos_user.shSYS_USER SYS_PASSWORD DWDB_USER DWDB_PASSWORD DWDB_DEFAULT_TABLESPACE DWDB_TEMP_TABLESPACE DIRECTORY_ALIAS DIRECTORY_PATH
   
    For example:
   export_tools/create_repos_user.sh sys xxx clarity_dwdb xxx USERS TEMP DWDB_EXP '/tmp/'
   
   

   Warning

   Note

   In the previous example, use the following values for the parameters:

   SYS_PASSWORD can be any value (the script connects with "as sysdba" and uses OS-level authentication).

   DWDB_USER and DWDB_PASSWORD are values for a new user and password.

   DIRECTORY_ALIAS is arbitrary, but it is used in the script without protection. Therefore, any value not already used as a directory name in Oracle (and without special characters) is acceptable.

5. Set the DIRECTORY_PATH used in Step 4 to your .../data_warehouse/export_data directory.
6. Enter this command:
   export_tools/import_cmdb.shDWDB_USER DWDB_PASSWORD DIRECTORY_ALIAS DWDB_DEFAULT_TABLESPACE
   
    For example:
   export_tools/import_cmdb.sh clarity_dwdb xxx DWDB_EXP USERS

To update an existing data warehouse database schema

If you are upgrading to version 8.5 of BMC Database Automation and are running Agents over IPv6 protocol, you must update the data warehouse database schema. For more information, see Updating-the-data-warehouse-database-schema.

Where to go next

Configure the connection between the Manager and the data warehouse.