Create OWB Repository and target schema's

This section outlines the steps for creating the Reporting schema's i.e. Repository Owner and the target schema's for Staging, Central and Datamarts that need to be created.

  1. In order to change the language used in localized property names which is defaulted to English, then please modify the Component.locale.order.installedLanguage variable in BIApplication.properties and also modify the language code in the 3 initialdata.sql files (Please refer to Appendix L on Globalization for full details)
  2. Update all Properties in BIBootstrap.properties, which can be found at..\Reporting\project\properties. Ensure schema names match the project naming standards. Each user name is a schema:

    staging.db.username=CuramST

    central.db.username=CuramDW

    centraldm.db.username=CuramDM

  3. Additional usernames for Oracle 11gR2:

    design.db.username=CuramBI

    runtime.db.username=CuramBI

    design.db.workspacename=CuramBI

    The variables design.db.username and runtime.db.username need to be the same name. Please ensure that they match. These have been named to keep in accordance with other usernames but we highly recommend that you standardize the names to your own preference.

  4. In the BIBootstrap.properties file please ensure that all the xxx.db.name properties are set to be the Oracle Net Service Name, e.g.

    staging.db.name=orcl

    The Oracle Net Service Name is found in the tnsnames.ora file (%ORACLE_HOME%\NETWORK\ADMIN) and the Net Service Name is in bold:

    ORCL = (DESCRIPTION =(ADDRESS = (PROTOCOL = TCP)(HOST = server name)(PORT = 1521))(CONNECT_DATA =(SERVER = DEDICATED) (SERVICE_NAME = ORCL)))

  5. In the case that Database User OWBSYS is locked, the Database User OWBSYS can be unlocked using either SQL Developer or by entering the following commands in a command prompt opened in ..\Reporting\components\:
    • 'sqlplus'
    • 'sys as sysdba'
    • password (will not appear, just click click enter once complete)
    • 'alter user owbsys identified by password account unlock'
  6. Prior to creating the Workspace, Workspace owner and Reporting BI schemas, if the users local naming conventions are different to the standard naming conventions which are pre-set in \Reporting\components\project\properties\BIBootstrap.properties then manually update the username and/or workspace names in the BIBootstrap.properties file. Please refer to Appendix M for more advanced updates/additions which can be made to the the Reporting BI schemas other than the names.
  7. Start a command prompt and navigate to.. .\Reporting\components. Run the 'build database.create.bischemas' command to create the 3 Reporting BI Schemas. This will create the Staging, Central and Datamart Schemas on the Database and connects to the Database using the User Name and Password specified in the file..\Reporting\components\BIBuildTools\scripts\rep_oraschemas.properties. They are defaulted to SYS as SYSDBA and p. Please set them as required before running the command.
  8. Open the Warehouse Repository Assistant which can be found here: Start - All Programs - Oracle_Home - Warehouse Builder - Administration - Repository Assistant

    Oracle Version 11gR2

    Step 1:Database Information - Enter the Host Name, Port Number and Oracle Service Name, which can all be found in your tnsnames.ora file.

    Step 2:Operation - Select Manage Warehouse Builder Workspace' option and click next

    Step 3:Workspace Operations - Select 'Create a new Warehouse Builder workspace' option and click next

    Step 4:New or Existing User - Select 'Create a workspace with a new user as workspace owner' option and click next

    Step 5:DBA Information - Enter username (e.g. sys) and password and click next

    Step 6:Workspace Owner - Enter workspace owner's username, e.g. CuramBI, password and workspace name, e.g. CuramBI (Workspace details should match the workspace details in BIBootstrap.properties file) and click next

    Step 7: In the OWBSYS information window, enter the OWBSYS username and password (This will not appear in subsequent runs of the Repository Assistant)

    Step 8: In the Select Languages window, accept the defaults and click Next (This will not appear in subsequent runs of the Repository Assistant)

    Step 9: Select Workspace Users - Just click next here.

    Step 10:Summary - Review the Summary and click finish to build the Workspace and register the Target Schemas.

  9. If setting up a Development Environment, please ensure that the 2 BIApplication.properties autogrant variables are set as follows:
    1. Environment.databses.curam.privaleges.autogrant should be set to true if the Curam source data is from a database that resides on the same database instance as the staging database. Otherwise it should be set to false.
    2. Environment.databases.bi.privilages.autogrant should be set to true

    Please see Appendix G for more information, and also for information on setting up the privileges on non-development environments.

  10. From your local environment, add \product\11.2.0\dbhome_1\jdbc\lib\ojdbc5.jar file to Reporting\components\BIBuildTools\drivers directory
  11. Open a command prompt and navigate to...\Reporting\components. Run 'build configtest'. This will test if everything is installed correctly.
  12. Run 'build owb.environment.tests.import' - this imports the Locations, meta data and 6 ETL's to test if they run correctly.
  13. Log into the OWB Design Center and expand the Reporting Project in the Project Explorer / Projects Navigator panel.
  14. For Oracle 11gR2: In the Locations Navigator window, double click on the DEFAULT_CONTROL_CENTER panel in Control Centers folder. Click on Data locations and ensure all the following locations:
    • DEFAULT_AGENT
    • SOURCE_LOCATION
    • STAGING_LOCATION
    • STATIC_DATA_LOCATION
    • DATAMARTS_LOCATION
    • CDW_LOCATION

    are in the 'Selected Locations' with both Source and Target box's ticked for all except STATIC_DATA_LOCATION which should have just the Source box ticked.

  15. Open the Control Center in Tools-Control Center Manager, to check that all 5 of the Locations are listed.
  16. Log out of OWB and click Yes to Save the changes.
  17. Open Oracle - Application Development - SQL Developer. On first use, you'll be asked to chose connection for java.exe. This should be located in the java bin folder. e.g.: C:\Program Files\Java\jdk1.5.0_06\bin. Right click on Connections, choose new connection and fill in the following details for Source, Staging, Central and Datamarts:
    • Connection Name
    • Username
    • Password
    • Hostname
    • Port
    • Service name
  18. Open a command prompt and navigate to..\Reporting\components. Run 'build owb.environment.tests.run'.

    This builds the database for each schema, and deploys 2 ETL's from each schema and tests the environment has been correctly configured. When this step executes successfully check the following tables and ensure they are populated with data:

    • S_ETLCONTROL
    • DW_ETLCONTROL
    • DM_ETLCONTROL

    If there is data in the source database then the following tables should also be populated:

    • DM_DIMCASETYPES
    • S_CODETABLEITEM
    • DW_CASETYPE

    This confirms that the environment has been correctly configured. This step may between 5 and 20 minutes to execute.

    Please note that the previous command executed an 'build staticdata'. This copies/merges the static data files and the control files to the...\Reporting\bin\data_manager directory. Note: IF the OWB client is not installed on the Oracle server then this static data will need to be manually copied to the Oracle server and ensure the Static Data File path points to that location.

    It also executed an 'build grant.all' command. This grant.all command grants permissions to the staging, central and datamart schema's in order for the ETL's to run successfully. In order for this grant.all to execute successfully the source, staging and central users must have the correct grant authority. Please see Appendix G for more information, and also for information on setting up the privileges on non-development environments.

    It also executed an 'build database.source.updatenulls' command. This command updates the last written column values in the Application Tables if they are null. This ensures that all of the records are extracted from the Source Tables into the Warehouse. If the last written columns are null then the records will not get loaded as they will not pass the join to the Control Table in each ETL.

  19. If the above tables have data in them then your Data Warehouse is set up correctly!
  20. Please follow the Steps in the next Sections to Import the Meta Data into OWB, Deploy the ETL's and Run them.
Parent topic: Oracle Environment