Set Up Budget Database

Before installing or upgrading Budgeting, a database administrator must create an empty database or library for its repository (separate from the Hubble Object Repository). This Budget Database is used to hold records of budgeting data, history, setup information, workflow status, and data entry tables. The Budget Database is required whether implementing Budgeting or Strategic Planning functionality.

The steps required to create a Budget Database, as well as the required permissions for the connection user will vary by database type and ERP. Considerations for each ERP and Database type are outlined below.

Tip: Best Practice: Name the Budget Database after the Hubble release, so you can easily associate the name.

For example, for Hubble release 20.3 name the Budget Database ‘BUD_20_3’.

Upgrade Existing Budget Database to a New Major Release

For every major release (e.g. 19.0, 19.2, 19.3, 19.4, 20.1, 20.2, 20.3, 20.4 etc.) create a new Budget Database.

Your existing production budgeting data should be restored to a new Budget Database for the new version (e.g. backup BUD_19_4 and restore to a new BUD_20_2 database).
You will NOT be able to use the existing production Budget Database for testing the upgraded version.

Database Accelerator

Budget Database

The database should default to the Hubble schema on the Accelerator. The Budget Tables will be created in this hubble schema if the Budgeting feature is enabled in the profile during the profile setup.

Connection User Permissions

The connection user will be “Hubble ”, so it will automatically have full permission to the Hubble schema.

Microsoft SQL Server – JD Edwards

Create a New Database in Microsoft SQL Server

Please ask your Database Administrator to complete this task. Create a new database using the database requirements and best practice information listed below.

Database Requirements

The database needs to be on the same server as your JD Edwards business data. This is because we can only query one server per query.
The database must be created with the same collation as the JD Edwards business data database.
The User ID used in the connection to the JD Edwards tables should have database owner (dbo) access (I.e. full permissions) to this Budget Database as well.
The User ID used in the connection to the JD Edwards tables should have full permissions to the F0902Z1 table.

Best Practices

Name the Budget Database after the Hubble release so you can associate the name easily, for example, ‘BUD_21_4’.
Use the same User ID for the Budget Database, as is used to access the JD Edwards databases.

For additional information, please refer to the MTR Budgeting and Planning Guide for your specific release.

IBM DB2 – JD Edwards

Create a New Library/Schema in IBM DB2

Please ask your Database Administrator to complete this task. Create a new library/schema using the database requirements and best practice information listed below.

Depending on the version of DB2 for i (formerly AS/400) you are in, the terminology will be different; what was called ‘library’ in older versions is now called ‘schema’ in newer versions.

Library/Schema Requirements

The library/schema needs to be on the same server as your JD Edwards business data. This is because we can only query one server per query.
The library/schema must be created with an SQL collection either via *STRSQL or the iSeries Navigator. In either case, the Object Repository requires that Journaling be enabled for the library/schema. For example, this command will create the budget library with journaling turned on: CREATE COLLECTION BUD_24_2
The User ID used in the connection to the JD Edwards tables should have full permissions to this Budget Database as well.
The User ID used in the connection to the JD Edwards tables should have full permissions to the F0902Z1 table.

Best Practices

Name the Budget Database after the Hubble release so you can associate the name easily, for example, ‘BUD_21_4’.
Use the same User ID for the Budget Database, as is used to access the JD Edwards databases.

For additional information, please refer to the MTR Budgeting and Planning Guide for your specific release, which is located on our documentation page.

Oracle – JD Edwards

Create an Oracle Schema

Please ask your Database Administrator to complete this task. Create either a new, empty Oracle database or a new schema within an existing Oracle database using the schema requirements and best practice information listed below.

Schema Requirements

The schema needs to be on the same server as your JD Edwards business data. This is because we can only query one server per query.
The User ID used in the connection to the JD Edwards tables should have database owner access (I.e. full permissions) to this Budget Database as well. The following is an example of the necessary grants, where HUBBLE is the schema specified in JDE Data connection and BUD_24_2 is the new budgeting database.
Grant select, update, insert, delete on BUD_24_2.ISBUDGETCATALOG to HUBBLE;

Grant select, update, insert, delete on BUD_24_2.ISBUDGETDATA4 to HUBBLE;

Grant select, update, insert, delete on BUD_24_2.ISBUDGETDATA4HISTORY to HUBBLE;

Grant select, update, insert, delete on BUD_24_2.ISBUDGETOBJECTS to HUBBLE;

Grant select, update, insert, delete on BUD_24_2.ISBUDGETSTATUS2 to HUBBLE;
The User ID used in the connection to the JD Edwards tables should have full permissions to the F0902Z1 table. The following is an example of the necessary grant, where HUBBLE is the schema specified in JDE Data connection.
Grant select, update, insert, delete on PRODDTA.F0902Z1 to HUBBLE
Additionally, the budgeting user (BUD_24_2 in the above example) needs access to read all the JDE Data tables (for example F0901, F0902, F0006, etc). Note: In 19c, it might be necessary to also modify the select role to be default.
If you are also setting up Strategic Planning (DXE licenses), you will need to grant the user CONNECT, RESOURCE role or a defined role with the following privileges prior to running the profile wizard: ‘CREATE SESSION’, ‘CREATE TABLE’, ‘SELECT ANY TABLE‘, ‘READ ANY TABLE‘, ‘CREATE VIEW’, ‘CREATE SYNONYM’, ‘CREATE ANY INDEX’, ‘CREATE TRIGGER’, ‘CREATE SEQUENCE’, ‘CREATE PROCEDURE’, ‘DEBUG CONNECT SESSION’ etc., and privileges as required in a multi-tenant environment. If desired, these privileges can be revoked again after completing the Profile Wizard.

Tip: Name the Budget Database after the Hubble release so you can associate the name easily, for example, ‘BUD_21_4’.

For additional information, please refer to the MTR Budgeting and Planning Guide for your specific release, which is located on our documentation page.

Oracle - EBS

Create an Oracle Schema

Ask your Database Administrator to run the DataEntryUser.sql script located in the Hubble install path of your new version, as this will create the new budget schema. More information can be found for the script in our Installation & Maintenance Guide for Oracle EBS DBAs.

Schema Requirements

After the budget schema has been created, the Hubble Administrator will need to edit (or create a new) Hubble Profile, making sure to specify the new schema in the Oracle Connectivity (Data Entry Connection) screen of the profile. After the profile wizard has completed, the DBA must be notified so he/she can run the additional grants at the end of the DataEntryUser.sql script. These grants could not be done as part of the script since the tables are created by updating the profile. The grants give relevant permissions to reporting users to ensure data can be accessed correctly.

Note: Hubble will need SYSTEM access to run the database script on the environment.
If you are also setting up Strategic Planning (DXE licenses), you will need the following permissions prior to running the profile wizard: CONNECT, RESOURCE role or a defined role with the following privileges prior to running the profile wizard: ‘CREATE SESSION’, ‘CREATE TABLE’, ‘SELECT ANY TABLE‘, ‘READ ANY TABLE‘, ‘CREATE VIEW’, ‘CREATE SYNONYM’, ‘CREATE ANY INDEX’, ‘CREATE TRIGGER’, ‘CREATE SEQUENCE’, ‘CREATE PROCEDURE’, ‘DEBUG CONNECT SESSION’ etc., and privileges as required in a multi-tenant environment.

Best Practice

Name the Budget Database after the Hubble release so you can associate the name easily, for example, ‘BUD_21_4’.

Custom Data Entry Tables for Strategic Planning

When using Strategic Planning functionality, data is entered and stored into Data Entry Tables. The profile configuration will automatically create two Data Entry Tables, ISGENERIC and ISBPAYROLL. These can be utilized to enter data and also as guides when creating Custom Data Entry tables to suit your needs. Hubble Support can also provide generic scripts that cover many requirements.

Custom Data Entry Table Requirements

Your DBA will create the custom data entry table using native database software.
The Table must reside in the Budget Database.
The Table will store all the input capable fields, it can contain Alpha, Numeric, Date fields, Amounts, etc.
The Table must have a Unique/Primary Key.
The Table must contain a source tracking column for each amount column Named the same as the amount column but with the prefix “B_F_”.
The Table must have three fields to store the CycleID, ContributorID, and FormID.
The Table must be included in the Data Entry Tables XML that is imported during the Profile Setup, as described in the Profile Setup section below.

Upgrading Existing Budget Database to a New Major Release

For every major release (e.g. 19.0, 19.2, 19.3, 19.4, 20.1, 20.2, 20.3, 20.4 etc.) create a new Budget Database.

Your existing production budgeting data should be restored to a new Budget Database for the new version (e.g. backup BUD_19_4 and restore to a new BUD_20_2 database).
You will NOT be able to use the existing production Budget Database for testing the upgraded version.

Database Accelerator

Budget Database

Connection User Permissions

The connection user will be “Hubble ”, so it will automatically have full permission to the Hubble schema.

Microsoft SQL Server – JD Edwards

Create a New Database in Microsoft SQL Server

Please ask your Database Administrator to complete this task. Create a new database using the database requirements and best practice information listed below.

Database Requirements

The database needs to be on the same server as your JD Edwards business data. This is because we can only query one server per query.
The database must be created with the same collation as the JD Edwards business data database.
The User ID used in the connection to the JD Edwards tables should have database owner (dbo) access (I.e. full permissions) to this Budget Database as well.
The User ID used in the connection to the JD Edwards tables should have full permissions to the F0902Z1 table.

Best Practices

Name the Budget Database after the Hubble release so you can associate the name easily, for example, ‘BUD_21_4’.
Use the same User ID for the Budget Database, as is used to access the JD Edwards databases.

For additional information, please refer to the MTR Budgeting and Planning Guide for your specific release.

IBM DB2 – JD Edwards

Create a New Library/Schema in IBM DB2

Please ask your Database Administrator to complete this task. Create a new library/schema using the database requirements and best practice information listed below.

Depending on the version of DB2 for i (formerly AS/400) you are in, the terminology will be different; what was called ‘library’ in older versions is now called ‘schema’ in newer versions.

Library/Schema Requirements

The library/schema needs to be on the same server as your JD Edwards business data. This is because we can only query one server per query.
The library/schema must be created with an SQL collection either via *STRSQL or the iSeries Navigator. In either case, the Object Repository requires that Journaling be enabled for the library/schema. For example, this command will create the budget library with journaling turned on: CREATE COLLECTION BUD_24_2
The User ID used in the connection to the JD Edwards tables should have full permissions to this Budget Database as well.
The User ID used in the connection to the JD Edwards tables should have full permissions to the F0902Z1 table.

Best Practices

Name the Budget Database after the Hubble release so you can associate the name easily, for example, ‘BUD_21_4’.
Use the same User ID for the Budget Database, as is used to access the JD Edwards databases.

For additional information, please refer to the MTR Budgeting and Planning Guide for your specific release, which is located on our documentation page.

Oracle – JD Edwards

Create an Oracle Schema

Schema Requirements

The schema needs to be on the same server as your JD Edwards business data. This is because we can only query one server per query.
The User ID used in the connection to the JD Edwards tables should have database owner access (I.e. full permissions) to this Budget Database as well. The following is an example of the necessary grants, where HUBBLE is the schema specified in JDE Data connection and BUD_24_2 is the new budgeting database.
Grant select, update, insert, delete on BUD_24_2.ISBUDGETCATALOG to HUBBLE;

Grant select, update, insert, delete on BUD_24_2.ISBUDGETDATA4 to HUBBLE;

Grant select, update, insert, delete on BUD_24_2.ISBUDGETDATA4HISTORY to HUBBLE;

Grant select, update, insert, delete on BUD_24_2.ISBUDGETOBJECTS to HUBBLE;

Grant select, update, insert, delete on BUD_24_2.ISBUDGETSTATUS2 to HUBBLE;
The User ID used in the connection to the JD Edwards tables should have full permissions to the F0902Z1 table. The following is an example of the necessary grant, where HUBBLE is the schema specified in JDE Data connection.
Grant select, update, insert, delete on PRODDTA.F0902Z1 to HUBBLE
Additionally, the budgeting user (BUD_24_2 in the above example) needs access to read all the JDE Data tables (for example F0901, F0902, F0006, etc). Note: In 19c, it might be necessary to also modify the select role to be default.
If you are also setting up Strategic Planning (DXE licenses), you will need to grant the user CONNECT, RESOURCE role or a defined role with the following privileges prior to running the profile wizard: ‘CREATE SESSION’, ‘CREATE TABLE’, ‘SELECT ANY TABLE‘, ‘READ ANY TABLE‘, ‘CREATE VIEW’, ‘CREATE SYNONYM’, ‘CREATE ANY INDEX’, ‘CREATE TRIGGER’, ‘CREATE SEQUENCE’, ‘CREATE PROCEDURE’, ‘DEBUG CONNECT SESSION’ etc., and privileges as required in a multi-tenant environment. If desired, these privileges can be revoked again after completing the Profile Wizard.

Tip: Name the Budget Database after the Hubble release so you can associate the name easily, for example, ‘BUD_21_4’.

For additional information, please refer to the MTR Budgeting and Planning Guide for your specific release, which is located on our documentation page.

Oracle - EBS

Create an Oracle Schema

You need to either create a new, empty Oracle database or a new schema within an existing Oracle database. Since the process of creating new databases can be done in a variety of ways within Oracle, a step-by-step guide is not provided here.

Schema Requirements

Once the Budget Database exists, Planning DB information can be added to the Hubble Profile. After the profile wizard has completed, additional grants need to be provided to the reporting schema (as written at the end of the script). These could not be done as part of the script as the tables did not yet exist and are only required when using a data entry schema/user different from the reporting schema/user. Note: Create a case with Hubble Support for the script.
If you are also setting up Strategic Planning (DXE licenses), you will need to grant the user CONNECT, RESOURCE role or a defined role with the following privileges prior to running the profile wizard: ‘CREATE SESSION’, ‘CREATE TABLE’, ‘SELECT ANY TABLE‘, ‘READ ANY TABLE‘, ‘CREATE VIEW’, ‘CREATE SYNONYM’, ‘CREATE ANY INDEX’, ‘CREATE TRIGGER’, ‘CREATE SEQUENCE’, ‘CREATE PROCEDURE’, ‘DEBUG CONNECT SESSION’ etc., and privileges as required in a multi-tenant environment.

Best Practices

Name the Budget Database after the Hubble release so you can associate the name easily, for example, ‘BUD_21_4’. To do this, your Oracle DBA will need to run a script to create the planning user and grant the relevant permissions to both the planning and reporting sure to ensure data can be accessed correctly. Please create a case with our support group to obtain the script for your release. Note: We’ll need SYSTEM access to run our database script on the environment.
Use the same User ID for the Budget Database, as is used to access the JD Edwards databases.

Note: For additional information, please refer to the MTR Budgeting and Planning Guide for your specific release and the Installation and Maintenance Guide for Oracle EBS DBAs. Both are located on our documentation page.