Skip to main content

Connect to Data Sources

Overview

When you are logged into your repository in Administrator, you will see the Data Sources node in the tree structure in the left panel. When you expand this, you see two items:

Connections - stores the information (database type, user name and password) required to connect to your ERP Database(s).

Profiles - designate the Data Source connection(s) to be used in Hubble as well as define the parameters for Hubble to derive correct information from your ERP System.

Create a New Connection

Follow the steps below to create a new database connection:

  1. Log into your repository if you are not logged in already.
  2. Expand the Data Sources node in the left panel.
  3. Then either:
    • Right-click on Connections, select New, then Repository Database Connection.
    • Or highlight Connections, click the New button on the Ribbon and select Repository Database Connection to open the Database Connection dialog.
  4. Configure the connection in the Database Connection dialog

    1. Identification - set a unique Name for this connection (e.g. ‘JDE90 Production’) and, optionally, set a Description.
    2. Data SourceType - specify the Type of database in which your ERP Data is located:
      1. Microsoft SQL Server®
      2. Oracle
      3. IBM DB2
      4. UDB
      5. Qlik (driver used to access IBM Optim archived files)
      6. Local Database file
      7. Oracle ADG
      8. PostgreSQL
    3. Data Source Provider - specify the Provider or ‘driver’ you are using to connect to your ERP data. The options for creating connections are appropriate to the data source type already selected:
      1. SqlClient Data Provider
      2. Built-in Data Provider
      3. OracleClient Data Provider
      4. Data Provider
  5. Click Configure. The Database Provider Configuration dialog is then displayed. For example:

  6. The settings in the Database Provider Configuration dialog will vary depending on the type of source database the connection is located in (the example above is of the dialog if the Oracle ADG source type was selected).
    1. If using SQL Server:
      1. Server - select the server on which your database is located or manually type in the location.
      2. Login – specify the details needed to access the server. If you have no password, click the Blank checkbox.
      3. Database - select the database on which your ERP data, security, Data Dictionary or UDC tables are stored (whichever this connection is being used for).
      4. Test Connection – mark this option if you want Administrator to verify that all of the connection details are working as expected when you click OK in the next step. If there are any errors, a message box will appear and identify them so they can be addressed. When the configuration is working, the Test Connection message confirms that it is successful.
    1. If using IBM DB2:
      1. Server – specify the Host Name for the database and mark the checkbox if your database version is pre-V5R3.
      2. Login - specify the login details needed to access the server on which the ERP tables are located. If you prefer to enter the password each time, choose the second option.
      3. Settings - specify the Default Library and the names of the Data, Control and Security Libraries.
      4. Test Connection – mark this option if you want Administrator to verify that all of the connection details are working as expected when you click OK in the next step. If there are any errors, a message box will appear and identify them so they can be addressed. When the configuration is working, the Test Connection message confirms that it is successful.
    2. If using Oracle DB:
      1. Server - specify the Host (Server) Name of your server, the Service Name (SID) of the instance you wish to connect to and its Port number.
      2. Login - specify the login details needed to access the server. If you prefer to enter the password each time, choose the second option.
      3. Test Connection – mark this option if you want Administrator to verify that all of the connection details are working as expected when you click OK in the next step. If there are any errors, a message box will appear and identify them so they can be addressed. When the configuration is working, the Test Connection message confirms that it is successful.
    3. If using Database Accelerator:
      1. Server - specify the Host (Server) Name of your server and its Port number.
      2. Login - specify the login details needed to access the server. If you prefer to enter the password each time, choose the second option.
      3. Database - specify the required database name.
      4. Test Connection – mark this option if you want Administrator to verify that all of the connection details are working as expected when you click OK in the next step. If there are any errors, a message box will appear and identify them so they can be addressed. When the configuration is working, the Test Connection message confirms that it is successful.
    4. If using Oracle ADG:
      1. ADG Server - specify the Host (Server) Name of your ADG server, the Service Name (SID) of the instance you wish to connect to and its Port number.
      2. Production Server - specify the Host (Server) Name of your production server, the Service Name (SID) of the instance you wish to connect to and its Port number.
      3. DB Link - enter the name of the DB Link to be used by Hubble to create Temp Tables. See Database Connections for Oracle Active Data Guard (ADG) for details.
      4. Login - specify the login details needed to access the server. If you prefer to enter the password each time, choose the second option.
      5. Test Connection – mark this option if you want Administrator to verify that all of the connection details are working as expected when you click OK in the next step. If there are any errors, a message box will appear and identify them so they can be addressed. When the configuration is working, the Test Connection message confirms that it is successful.
    5. If using PostgreSQL:
      1. Server - specify the Host (Server) Name of your server and its Port number it will use.
      2. Login - specify the login details needed to access the server. If you prefer to enter the password each time, choose the second option.
      3. Database - specify the required database name.
      4. Advanced - click this button and set the following:
        1. SSL Mode to Required.
        2. Trust Server Certificate to True.
  7. Click OK.
  8. If you require more than one connection to your ERP tables, you will repeat this process for each connection.

Built-In Data Provider

The advantage of using a built-in data provider is simplicity; it is very simple to set up in Administrator. The SQL Server and Oracle Server Data Providers can be used if using those servers. The ODBC Data Provider requires more maintenance; with this option, you must set up an ODBC data source on each Hubble installation instance (server and individual PCs). The ODBCs must be set up exactly the same (name, description, and all settings) on each installation.

Load Balancing and Failover Configuration

Load Balancing and Failover Configuration can be done within the Oracle database connection configuration in Administrator. This is to support Oracle Real Application Clusters (RAC), which provides software for clustering and high availability in Oracle database environments.

To configure multiple servers for load balancing or failover, you can specify the TNS description instead of a Host Name in the Oracle Built-in Data Provider setup, such as the example shown below:

For example, if your TNS description is as follows:

(DESCRIPTION= (ADDRESS_LIST=

(LOAD_BALANCE=YES) (FAILOVER=YES)

(ADDRESS=(PROTOCOL=tcp)(HOST=servername1)(PORT=1521)) (ADDRESS=(PROTOCOL=tcp)(HOST=servername2)(PORT=1521))

)

(CONNECT_DATA= (SERVICE_NAME=acme)

)

)

Remove the line breaks, and then cut and paste the description into the Host Name field. Following the same example, it would look as shown below:

(DESCRIPTION=(ADDRESS_LIST=(LOAD_BALANCE=YES)(FAILOVER=YES)(ADDRESS=(PROT OCOL=tcp)(HOST=servername1)(PORT=1521))(ADDRESS=(PROTOCOL=tcp)(HOST=serve rname2)(PORT=1521)))(CONNECT_DATA= (SERVICE_NAME=acme)))

Create New Profiles

Once Data Source Connections have been created in Administrator, you can create a Profile that uses these Connections. A Profile can be made up of multiple Connections. This is why the ERP Wizard allows you to create a new Connection or make use of an already existing one. Additionally, for ease of use, a Connection can also be reused by multiple Profiles.

Create a JD Edwards Profile

  1. Log into your repository if you are not logged in already.
  2. Expand the Data Sources node in the left panel.
  3. Use one of these options:
    1. Right-click on Profiles, select New, then JDE Profile.
    2. Highlight Profiles, click the New button on the Ribbon and select JDE Profile.
    3. Select Connections, right-click on a Connection in the right panel and select Create Profile.
  4. If option a. or b. (above) was selected, the New ERP Profile dialog opens. Enter a unique name that identifies this Profile. Click OK.
    If option c. was selected, the new profile will automatically be given the same name as the Connection. If a profile with that name already exists, a sequence number will be added to the end of the new profile name to distinguish it.
  5. In the Create/Edit ERP Profile Wizard, click Next.
  6. For clients using JD Edwards, you will next designate the Connections that link Hubble with the JD Edwards database tables. Choose the relevant connection from the previously defined list of available Connections. If a Connection has not been defined, click the New button and you will be presented with the Database Connection dialog and from here, a new Connection can be created.

    7. If the JD Edwards Security, Data Dictionary or UDC tables reside in the same database on the same machine as the Data tables, it is possible to use the same Connection for all of these tables. In such instances, the Same as Data Connection checkbox should be enabled for the relevant connection and no further connection details need to be provided.

    1. Data Connection – the Connection that links to the main JD Edwards Data tables, such as F0902 and F0911.
    2. Security Connection – the Connection that links to the JD Edwards Security tables, such as F0092 and F0093.
    3. Data Dictionary Connection – the Connection that links to the JD Edwards Data Dictionary table F9201(World) or F9210 (E1).
    4. UDC Connection – the Connection that links to the JD Edwards UDC Data table, such as F0005.
  7. Click Next.
  8. Platform - select the JD Edwards edition and version to which you will be connecting.
  9. Click Next.
  10. For DB2 for i, there are specific database settings due to restrictions on what can be done with regards to SQL commands and query sizes:
    1. DB2 / AS/400 Options - select any of the applicable options. If you use a V5 release of OS400 you do not need to select any of these.
    2. When enabling the SQL setting Column Limit, you will specify a maximum limit on the number of columns allowed in a SQL Query. You can allow the configuration tool to attempt discovery of this value by using the Detect option when enabled. Depending on your database and version, the number of columns will differ. It is recommend that you use the Detect functionality and enable the Column Limit.
  11. Click Next.
  12. In the Module Selection screen, activate the modules and options you wish to use in this Profile. (Note that when applicable, only the modules or Features your organization is licensed for are available to select.)
    1. Modules
    2. Features:

      1. Budgeting - if you have a license for this feature, you can activate it for this Profile.

      2. Designer Express - if you have a license for this feature, you can activate it for this Profile.

      3. DX Data Entry - this functionality enables you to design a budget or forecasting input form using any table in JD Edwards. If you have a license for this feature, you can activate it for this Profile.

      4. Currency Restatement – if you have a license for this feature, you can activate it for this Profile.

      5. Localized Captions - gives the ability to derive different language captions from the underlying JD Edwards system.

      6. Attachments - this features enables you to add attachment columns to reports and link to attachments from the ERP database.

    3. GL & JC Settings - Weekly reporting - available if you use this option in JD Edwards, this option is directly related to the Date Fiscal Patterns – 52 Period Accounting Table (F0008B).
    4. AR & SOP & CRM Settings - Line of Business Support and Use Date Effective Categories - settings available if you use these Accounts Receivable/Sales Order Processing/Customer Relationship Management options in JD Edwards.
    5. Line of Business Support – when enabled, Hubble will use the Customer Master by Line of Business Table (F03012). When disabled, Hubble will use the Customer Master Table (F0301).
    6. Effective Categories – when enabled, Hubble will be able to derive data from the Customer Date Effective Category Codes Table (F03012A).

      7. Note: If you are unsure whether to enable either of these options, click Verify. If you have only one F0009 table, click Test. The results will tell you if either is activated in that table. If the F0009 is located in more than one library in your database, identify the table owner/library such as below.

  13. Click Next. (There will be a pause while the data source is analyzed for relevant control and data tables. The amount of time this process takes will vary depending on the size of the JD Edwards implementation.)
  14. If you are using Budgeting and/or the Percent Split functionality, complete the information in the Budgeting Connection screen. This is where you define the connection and the database or library where uploaded data will be stored.
    1. Data Entry section:
      1. Connection – identify the connection being used to connect to the Budgeting Repository.
      2. Database/Library – identify the database/library being used for the Budgeting Repository. If it has already been initialized, it will be listed in the drop-down box; otherwise, the location must be typed in.
        • For SQL Server, enter the name of the database in SQL Server, followed by a period, followed by the database owner username (typically ‘dbo’).
        • For DB2 and Oracle, you only need name of the database/library.
      3. Repository Prefix - a repository may contain multiple budgeting repositories. This is controlled on a profile basis. A repository prefix must be added to table names to distinguish them by entering it here or selecting it from the drop-down.
    2. Budgeting Upload Table section - You can upload the F0902Z1 using a different connection than the Data Connection (the F0902Z1 can be located on a different server than the Budgeting Repository and JD Edwards.) Therefore within this section you define which connection is being used to connect to the F0902Z1.

      3. >    If using JD Edwards World, you need a 3rd party program to upload the data.

      1. Connection - identify the connection being used to connect to the F0902Z1 table.
      2. Upload - define the name of the database and owner or library name where the F0902Z1 Table is located. (This will be listed automatically if it is visible via the JD Edwards Data Connection; otherwise, enter the database name, followed by a period, followed by the database owner or library name.) If it has already been initialized, it will be listed in the drop-down box; otherwise, the location must be typed in.
        • For SQL Server, enter the name of the database in SQL Server, followed by a period, followed by the database owner username (typically ‘dbo’).
        • For DB2 and Oracle, you only need name of the database/library.
    3. New Connection - if you wish to have a separate Connection to the location of the F0902Z1 table but have not yet created it, create it by clicking on the New Connection button.
  15. In the Select Additional Tables to Configure screen, select the specific tables needed for Designer Express (if enabled). This screen will not be displayed if the Designer Express feature is not enabled.
    1. Connection - set how to connect to the Object Librarian Master Table (F9860 in E1 and F9801 World), whether it is the same Hubble Connection as that being used to connect to your JD Edwards Data, or whether it is a different Connection.
    2. Object Library - define the library where the F9860/F9801 table resides (allows you to map to tables not configured in the profile).
    3. Choose Tables – define the tables which will be used in new templates if they are not already mapped.
      1. All available tables are listed in the left panel. To select tables, move them to the right panel either by using the arrows or double-clicking on the specific table in the left panel. Optionally, use the QBE to find any table in order to move it to the right panel. All tables in the right panel will be available.
      2. Tables can be flagged as “non-ERP” so that they are not checked against the data dictionary. To do this, select the tables, click Table Origin, and tick the checkboxes of those tables to be flagged as non-ERP.
      3. Click OK once all selections have been made.

        Tip: It is strongly recommended that you only add the tables you need and that you not add all the tables.

    4. Import Tables - import custom data entry tables from an xml file. Imported tables are automatically configured (there is no need to choose them using the Choose Tables facility).
      1. Click Clear to clear the imported tables.
      2. Click Export to export them to another file.
  1. Click Next.
  2. In the Table Locations screen, define all of the tables needed for Hubble with the appropriate Owner, Library or Schema name.
  • If there is uncertainty over any of the table selections explained below, please consult your JD Edwards Administrator.

    • The tables available are split between mandatory and optional, both of which can be defined on this screen. The default setting for the table definition is to only show those tables that have multiple selectable prefixes. Click Show All Tables to see all database tables. If all tables have the same prefix, this screen will be blank until you check Show All Tables.

    1. Table Owners/Library Names - In the IBM DB2/400 database, this grouping is called a library. For Oracle and SQL Server databases, the equivalent of a library is the Table Owner (SQL Server) or schema (Oracle). A table owner/schema is associated with the database user who owns, and most likely created, the database table. In this Profile Wizard we use the term Owners/library names to cover all possibilities.
    2. If the correct default libraries have been supplied in the ODBC connection for DB2/400, Oracle or SQL Server, the library prefixes should automatically be completed. Please ensure that all of the tables in the list either have a valid prefix or are set to ‘Do not use’ where appropriate. If this is not the case, valid prefixes can be manually entered by’ double-clicking on the appropriate prefix field and entering the name manually.
    3. To select multiple rows, use either the Shift or Ctrl keys to select the fields with a common owner/table. Highlight the rows and right-click. This will provide you with a selection of Library Prefix names that are common to all highlighted tables:

      After selecting the common owner/table for the selected fields, you see that the table prefix has changed to the value you selected.

  • When all of these prefixes have been applied, click Next to continue.
  • Click Next.
  • Define the location of the Data Dictionary table and choose whether you want to use any Data Dictionary overrides.
  • The Data Dictionary library is an information library that contains data definitions to access when using Hubble. If using EnterpriseOne or World version A81 or higher, the table is F9210. When using World versions lower than A81, the table is F9201. Therefore the syntax will either be <LIBRARY>.F9210 or <LIBRARY>.F9201. In some environments, the database name is needed. In such cases, the syntax will be <DATABASE>.<LIBRARY>.<TABLE>.
  • Click Next.
  • In the Test the Configuration screen, the application tests what has been entered so far in the Profile Wizard. If you wish to see the number of rows returned in each test, mark the checkbox to Include Row Count Information.
  • Click Run Test.
  • If a warning message is received at this point, click the Back button to return to the screen that is relevant to the issue highlighted in the message. Examine and alter those settings that are at fault before returning to test the configuration.
  • Click Next.
  • In the Environment and Model Business Unit screen:
    1. Environment – when users sign in to Hubble, the application validates that they have access to the JD Edwards Environment defined here. This can be selected from the drop-down list of options or it can be entered manually.
    2. Enter in a JD Edwards Model Business Unit to validate object accounts selected by the user.

      >    In JD Edwards, Model Business Units (BU) are identified as such within the Business Unit Master table – F0006. Typically a Model BU will be kept up to date and will contain a complete account structure. A Model BU typically does not have any transactional data.

      If there is no single model Chart of Accounts but there are separate model balance sheets and model profit and loss accounts, multiple Business Units can be entered. Initially the dialog allows for one General Model Business Unit option, which is then applied to all modules.

    3. Enable the Override for specific Modules option if you wish to use a different Model Business Unit for the General Ledger, Job Cost and/or Fixed Asset modules.
    4. Either manually enter the Model Business Unit(s) (comma separated for multiple Models) in the field provided or press the Search button to the right of the field to search for and select a Model Business Unit.

      >    If you do not have a complete Model BU available, you may opt to enter an alternative BU (one that is not defined as a Model BU in JD Edwards). If you choose to enter a ‘live’ Business Unit in this dialog, all users will be able to see the transactional data for that Business Unit.

  • Click Next.
  • There are several tabs in the Options screen:
    1. Subledger Locations – using the drop-down list, choose to include or exclude each Subledger type from the Subledger filter. (Optionally, you can use the Select All button to select all at once.)
      1. Optionally, mark the option to Use temporary table in Subledger joins.
        • Some clients have experienced reduced performance when joining to JD Edwards Subledger tables in Hubble. This is because the key fields are stored in a different format in the main and Subledger tables. This option can assist with these performance concerns.
      2. For the A, C and W Subledger types, you can further narrow them down by clicking on the button next to the Sub Type column. When presented with the list to choose from, you can use the Shift and Ctrl keys to multi-select.
      3. If the Address Book Subledger type has been selected, only include the sub types that are needed as this can dramatically speed up the processing of the Subledger Visual Assist.
      4. Within the Subledger type `A` – Address Book, it is also possible to select which Address Book record description should be used within Hubble by clicking the Advanced button next to the Description column. The choices include: Mail Name (WWMLNM from F0111), Alpha Name (WWALPH from F0111) and Alpha Name (ABALPH from F0101).
      5. In Hubble, for Subledger Type `S` only, we will suppress all the * selections when retrieving the Structured Subledger type `S` items. The * selection is wildcard searching functionality that can be achieved in the Query by Example (QBE) filters of the Query Assist dialog.
    2.  Balance Sheet Overrides - define which Automatic Accounting Instructions values from the AAI table, F0012, define the start and end items for your balance sheet and profit and loss range of accounts. By default this is GLG2 and GLG5.

      You can change the start and end of the AAI value range that Hubble uses by selecting No Override and using the corresponding drop-down menus. Alternatively, you can select an override. Note that override settings do NOT get written back to your JD Edwards database and therefore they will not modify the values in the Automatic Accounting Instructions table.

      1. Select the Define an override system option.
      2. Click Override.
      3. In the Specify Account Ranges dialog, click New to specify the start and end account of your balance sheet account range. (You can also edit and remove account ranges in this dialog.)
      4. Click OK.
    3. Options: Description Settings - specify which combination of Object Account and Subsidiary will be used to derive the description value that Hubble will display:
      1. Model BU/First Subsidiary or Object Account - this default option uses the configured Model Business Unit for the inquiry result.
      2. Exact BU/First Subsidiary or Object Account - stores in a temporary table the descriptions based upon selections from your Account Master.
      3. Exact BU/Blank Subsidiary or Object Account - joins directly to the F0901 to retrieve the descriptions.
      4. Exact BU/Exact Object/Exact Subsidiary- joins directly to the F0901 to retrieve the descriptions.
    4. Data Settings:
      1. Data contains NULL values - you may disable this option to increase performance if you know that your data definitely does not contain null data.
        • If you are unsure about this setting, please do not change the default setting unless advised by our Support Team.
      2. Load Data Dictionary at start-up - this setting tells the application how to handle data types and padding. When checked, Hubble runs one large query at the time of login in order to cache the Data Dictionary information. When unchecked, whenever Hubble needs to know how to work with an individual item, it runs an individual query to get that information from the Data Dictionary and then caches the result.

        In other words, this option enables you to choose which option is better for your profile: one large query or queries on demand. For sites that host the database remotely, the option being turned on will help with performance as time is lost when queries are continuously being run as the information is needed.

        • If you are unsure about this setting, please do not change the default setting unless advised by our Support Team.
      3. Financial Year setting - Financial years in JD Edwards are stored in 2 fields, CTRY (century) and FY (financial year). If you have data for the same year in different centuries, such as 2007 and 2107, you should be using the Use CTRY and FY to determine year. Otherwise if you know that your Company’s Date Fiscal Patterns (F0008) only span across one century, you will be able to enable the option Use FY only to determine year (best performance).
      4. World Security Options – available to sites using JD Edwards World, in this dialog you set the following:
      5.  Security Settings:
        1. JD Edwards Business Unit Security turns on/off Business Unit Security.
        2. Allow blanks gives you the option of allowing blanks in the Business Unit Security Settings.
        3. Implement security using temporary tables (recommended) - creates temporary tables to handle the JD Edwards security settings. This was added as it can improve inquiry performance times.
        4. Use Tax ID / Social Security Number Masking - used in combination with the Advanced Capabilities for Social Security Number Options (Advanced Capabilities: Options > JDE), Hubble can mask the display of Tax IDs / Social Security Numbers (SSNs).
      6. Connection Settings allow you to select which security authentication mode you want your users to use as part of the logon process:
        1. Option 1 - uses the username and password of the person logging onto Hubble to verify against the AS/400.
        2. Option 2 - connects using the username and password defined in the Connection in Administrator but still validates the person logging in (using their user-name and password) against the AS/400.
        3. Option 3 - connect using option 1; upon failure, connect using option 2.
        4. Option 4 - same as option 2 except the person logging in is validated in the object repository, not the AS/400.

          Note: If Hubble Web functionality is to be used in the project, option 4. system credentials, verify user with Hubble password management setting must be selected.

    5. Security Options – available to sites using JD Edwards E1, in this dialog you set the following:
      1. Use JD Edwards Security - enables JD Edwards security. The following further settings are available:
        1. GL and JC Balance Templates - tick these checkboxes as required to apply JDE *ALL Table Security for xxMCU and xxCO columns only to table F0902 for GL and JC balances.
        2. All Transaction Templates - tick these checkboxes as required to apply JDE *ALL Table Security for xxMCU and xxCO columns only to table F0911 for transactions.
        3. DX Reports - Enable Streamline Security - tick to choose to apply the streamlined security to DX reports.

          The *ALL table security is useful in JDE is because applications and reports typically only use one or two tables, and usually both tables do not have the MCU or CO columns.

          In the case of templates such as Balances, the above security is applied to the F0902, F0901 (and Company table/F0010 if included). This repetitive security is not necessary and results in a performance impact.

      2. Implement JD Edwards Security using temporary tables (recommended) – when selected, Hubble will create temporary tables to handle the JD Edwards security settings. This was added as it can improve inquiry performance times.
      3. Use Tax ID / Social Security Number masking - used in combination with the Capabilities for Social Security Number options, Hubble can mask the display of Tax IDs / Social Security Numbers.
      4. Use Inclusive Row Security to Prevent Table Access (Add/View/Change/Delete all equal N) - used to enable Inclusive row security, where a role cannot view a table unless explicitly granted access to it.
    6. DB2 Options– available if your JD Edwards system is using a DB2 database:
      1. Indices - this option can improve the performance for Object Account/Subsidiary validation by relying on your indices on the F0901 table. Click the Test button to verify that Hubble can use your indices and then check the box as appropriate.
      2. Globalization - this option must be checked if you want double byte for localizations so that the double byte text can be converted to Unicode. Select in the drop-down list the Coded Character Set Identifier which was used when the Asian text was entered into the database. (The conversion will be applied to JD Edwards descriptive columns only, e.g DL01, EX).
    7. Joins - Within the General Ledger and Job Cost modules, we are using tables F0901 and F0902. This tab allows us to define how we want to join the two files:
      1. Full Join - joins on all aspects of an account, i.e. Account I.D, Company, Business Unit, Object Account and Subsidiary. By joining on all of these fields, you are sacrificing speed for integrity due to the additional checks Hubble will need to perform.
      2. Account ID Only Join - only joins on Account ID (which is the same way that JD Edwards joins these tables). This type of join sacrifices integrity for speed. You can check the integrity of your system by running the Account ID Integrity Test under Extended Tests in the Integrity Checker; this will allow you to see if it is safe to use this faster option.
  • Click Next. Pressing Cancel on this screen will result in your profile not being saved.
  • Click Finish. You will see the Updating ERP Profile screen, which runs through a number of process, for example Setup Standard Templates and Setup Budgeting Repository. These processes will load all of the information, templates etc. that will be used by the configured ERP Profile. Once completed, the status bar columns will show as OK.
  • Click Close to exit the screen if it doesn’t exit automatically.
  • If the process has not updated correctly, the status column will show as Error. This will mean that your profile will not be completed. You will need to fix the error before re-running the process. Double-click on the line to receive further information.
  • On completion, your ERP Profile will appear as an item within Profiles.

Create an Oracle Profile

Follow the steps below to create a new Oracle profile:

  1. Log into your repository if you are not logged in already.
  2. Expand the Data Sources node in the left panel.
  3. Use one of these options:
    1. Right-click on Profiles, select New, then Oracle Profile.
    2. Highlight Profiles, click the New button on the Ribbon and select Oracle Profile.
    3. Select Connections, right-click on a Connection in the right panel and select Create Profile.
  4. If option a. or b. (above) was selected, the New ERP Profile dialog opens. Enter a unique name that identifies this Profile. Click OK.

    If option c. was selected, the new profile will automatically be given the same name as the Connection. If a profile with that name already exists, a sequence number will be added to the end of the new profile name to distinguish it.

  5. In the Create/Edit ERP Profile Wizard, click Next.
  6. Designate the Connection that links Hubble with your Oracle database tables. Choose the relevant connection from the previously defined list of available Connections.

    If a Connection has not been defined, click the New button and you will be presented with the Database Connection dialog and from here, a new Connection can be created.

    Note: For Oracle ERP data, only one connection is needed to connect to all data. Therefore, only the Data Connection needs to be identified. Set the Same as Data Connection checkbox for the Security, Data Dictionary and UDC Connections.

  7. If the HR and/or Payroll modules are to be used, an HR Security connection to the APPS schema is required (refer to the Installation & Maintenance Guide for Oracle EBS DBAs for details). Select the connection from the HR Security Connection list and mark the Use Data Connection checkbox.
  8. If the selected Data Connection is an Accelerator connection (an Actian Vector database), select a Replication Schema from the Replication Schema list.
  9. Click Next.
  10. Platform - select the Oracle version to which you will be connecting.
  11. Click Next.
  12. In the Module Selection screen, activate the modules and options you wish to use in this Profile.
    • Only those modules for which your organization is licensed are available to select.
      1. Modules
      2. Features:
        1. FSG Row Set Importer - enables the option to import FSG Row Sets
        2. Parent Code Filter Support –enables users to filter on parent codes. (For example, Account 1000 (Cash) is the parent code and has no postings directly against it. If this option is enabled, users can filter on Account 1000 and see the sum of all its descendants.)
  13. Click Next.
  14. If you are using Budgeting functionality, complete the information in the Oracle Connectivity (Data Entry Connection) screen. This is where you define the connection and the database or library where uploaded data will be stored.
    1. Data Entry Settings section:
      1. Connection – identify the connection being used to connect to the Budgeting Repository.
      2. Database/Library – identify the database/library being used for the Budgeting Repository. If it has already been initialized, it will be listed in the drop-down box; otherwise, the location must be typed in.
        • For SQL Server, enter the name of the database in SQL Server, followed by a period, followed by the database owner username (typically ‘dbo’).
        • For DB2 and Oracle, you only need name of the database/library.
      3. Repository Prefix - a repository may contain multiple budgeting repositories. This is controlled on a profile basis. A repository prefix must be added to table names to distinguish them by entering it here or selecting it from the drop-down.
    2. New Connection - if you wish to have a separate Connection to the location of the table but have not yet created it, create it by clicking on the New Connection button.
  15. Click Next.
  16. In the Select Additional Tables to Configure screen, select the specific tables needed for Designer Express (if enabled). This screen will not be displayed if the Designer Express feature is not enabled.
    1. Choose Tables – select the tables which will be used in new templates if they are not already mapped.
      1. All available tables are listed in the left panel. To select tables, move them to the right panel either by using the arrows or double-clicking on the specific table in the left panel. Optionally, use the QBE to find any table in order to move it to the right panel. All tables in the right panel will be available.
      2. Click OK once all selections have been made. The tables are then configured.

        Tip: It is strongly recommended that you only add the tables you need and that you not add all the tables.

  17. Import Tables - import custom data entry tables from an xml file. Imported tables are automatically configured (there is no need to choose them using the Choose Tables facility).
    1. Click Clear to clear the imported tables.
    2. Click Export to export them to another file.
  1. Click Next.
  1. In the Table Locations screen, define all of the tables needed for Hubble with the appropriate Schema name. If there is uncertainty over any of the table selections explained below, please consult your Oracle EBS Administrator.

    2. The tables available are split between mandatory and optional, both of which can be defined on this screen. The default setting for the table definition is to only show those tables that have multiple selectable prefixes. Click Show All Tables to see all database tables. If all tables have the same prefix, this screen will be blank until you check Show All Tables.

    1. Table Owners/Library Names - For an Oracle database, this is the schema. A table owner/ schema is associated with the database user who owns, and most likely created, the database table.
    2. If the correct default libraries have been supplied in the Data Source Connections, the prefixes should automatically be completed. Please ensure that all of the tables in the list either have a valid prefix or are set to ‘Do not use’ where appropriate. If this is not the case, valid prefixes can be manually entered by double-clicking on the appropriate prefix field and entering the name manually.
    3. If a Replication Schema has been specified for the connection, the replicated tables will be displayed with the Replication Schema name as their prefixes.
    4. To select multiple rows, use either the Shift or Ctrl keys to select the fields with a common owner/table. Highlight the rows and right-click. This will provide you with a selection of Library Prefix names that are common to all highlighted table. After selecting the common owner/table for the selected fields, you see that the table prefix has changed to the value you selected.
  2. When all of these prefixes have been applied, click Next to continue.
  3. The mandatory views, synonyms, types and packages required by Hubble are then created. The progress and results of this process is displayed in the wizard.
  4. Click Next once the process is completed successfully.
  5. In this screen, Hubble tests what has been entered so far in the Profile Wizard. If you want to see the number of rows returned for each table, click Include Row Count Information prior to running the test.
  6. Click Run Test. If a warning message is received at this point, click the Back button to return to the screen that is relevant to the issue highlighted in the message. Examine and alter those settings that are at fault before returning to test the configuration.
  7. If desired, click Export to export all test results before continuing.
  8. Click Next.
  9. In the Profile Options screen, there are several options:
    1. Allow expired users to log in - This enables organizations to allow their expired Oracle EBS users to log on. (While this can be beneficial in terms of efficient use of Oracle licenses, it can raise security concerns and will require manual monitoring by the organization to ensure it is not abused.)
    2. Enable EBS Segment and Org Security - This enables organizations to switch off the reference to the EBS. Org and Segment security for completely open reporting. (This is beneficial for consolidation reporting but can cause security concerns for some organizations and should be treated with caution; once it is switched off, it applies to all users accessing the affected Hubble profile.)
    3. Segment filter values from all COAs are made available – Typically users should see one ledger per responsibility. When this is activated, it allows you to see all ledgers when logging into Hubble.

      Important: This setting is off by default and should remain off except in exceptional circumstances as it causes individual queries to run for each segment for every ledger. This can result in hundreds, if not thousands, of inquiries being run at login.

    4. Using Projects time-phased budgeting – Activate this option if budgeting is done periodically, as in every period/month (time-phased). Leave this option unchecked if the entire budget amount for a project is allocated against a single period.
      1. > For example, if Project ABC runs from April 2012 to April 2015, you can either budget for every month throughout that period (time-phased) or allocate the entire budget to one period, most likely the final one in the life of the project which is April 2015.
    5. Apply Parent Code Filtering Support (PCFS) Subset Optimization - This can be used if there are complex structures, i.e. many parent/child levels within multiple segments.

      This allows you to enter a parent code in the filter and the data returned includes all the child levels associated with the parent code. Similar to a hierarchy, which is a parent/child relationship, you can input the parent code in a filter and once the inquiry runs, all child levels are returned in the inquiry results. However with this option checked, there is no need to create the hierarchy.

    6. Load Data Dictionary at start-up - This setting tells the application how to handle data types and padding. When checked, Hubble runs one large query at the time of login in order to cache the Data Dictionary information. When unchecked, at the time Hubble needs to know how to work with an individual item, the application runs an individual query to get that information from the Data Dictionary and then caches the result.

      In other words, this option enables you to choose which option is better for your profile: one large query or queries on demand. For sites that host the database remotely, the option being turned on will help with performance as time is lost when queries are continuously being run as the information is needed.

      >    If you are unsure about this setting, please do not change the default setting unless advised by our Support Team.

  10. Click Next. Pressing Cancel on this screen will result in your profile not being saved.
  11. Click Finish. You will see the Updating ERP Profile screen, which runs the Standard Templates Setup. This process will load all of the information and templates that will be used by the configured ERP Profile. Once completed, the status bar columns will show as OK.
  12. Click Close to exit the screen if it doesn’t exit automatically.
  13. If the process has not updated correctly, the status column will show as Error. This will mean that your profile will not be completed. You will need to fix the error before re-running the process. Double-click on the line to receive further information.
  14. On completion, your ERP Profile will appear as an item within Profiles.

Configure Additional Tables

The Configure Additional Tables option for a Profile can be quickly accessed in a Profile by right-clicking on the specific Profile and selecting Configure Additional Tables.

This will display the Select Additional Tables to Configure and Table Locations screens in the Profile, bypassing the other screens that you would see by going through the full profile wizard.

Import and Export the CustomerPK Configuration

The Hubble profile configuration file, CustomerPK.xml, can be imported, exported and cleared.

Right-click on Profiles and select CustomerPK Configuration:

To import a new CustomerPK file, select the Import option. The following message is then displayed regarding the profile wizard:

To export the current CustomerPK file, select the Export option. You will then be asked where the file is to be saved:

Select the required location and click Save.

To clear the current CustomerPK file, select the Clear option. The following message is then displayed regarding the profile wizard:

Integrity Checker

The integrity checker is designed to detect any problems or inconsistencies within the database that might interfere with the operation of Hubble.

To run an integrity check:

  1. Right-click on a specific profile from the right-hand panel of Administrator.
  2. Select Integrity Checker.
  3. Under Available Tests, use the checkboxes to select which tests or groups of tests you want to run. Expand Common and Extended Tests as needed to see the detailed list of tests:
    1. Common Tests - recommended tests, which detect common inconsistent settings. These run quickly and can be run as part of the configuration of the database connection.
    2. Extended Tests - additional tests, which may take considerably longer to run, are designed to tackle more complex data issues. We would typically recommend that you run these tests overnight.
  4. Click Run. The results of the test appear in the bottom panel of the dialog.
  5. When the results appear in the bottom-half of the dialog, you can export them to a CSV file by right-clicking on the results panel. If you have Microsoft Excel installed, the results will launch in Microsoft Excel.
  6. The feedback from the data integrity tool is divided into two categories:
    1. Warnings - these suggest that current database settings may result in unexpected behavior.
    2. Errors - these indicate settings that are not compatible with Hubble and need to change in order to ensure a complete and proper configuration.
  7. If there are any errors or warnings, please seek advice from your Systems Administrator.

Account ID Integrity Test

The Account ID test is typically the most commonly used extended integrity test. The Account ID test is particularly helpful in these scenarios:

  • Trial balances in Hubble are different from the balances in JD Edwards.
  • Transactions displayed in the Hubble Transactions template are not visible in the Hubble Balances template even though they are posted transactions.

In JD Edwards, for a unique Account ID in the Accounts Masters table (F0901), there is only one unique combination of Company, Business Unit, Object Account and Subsidiary. In Hubble, you can set whether the application joins the Balances and Transactions tables the same way or by a full join (this is set in Profile Options in Administrator).

When the profile is set to join by Account ID, the join is the same as what is used in JD Edwards and therefore inquiry results will be the same as what is seen in JD Edwards. When the profile is set to a full join between the F0901 and F0902, the join is on Account ID, Company, Business Unit, Object Account and Subsidiary. In this case there can be a discrepancy in data between Hubble inquiries and JD Edwards data. It is slower to perform queries on this join option; however it will point out discrepancies that can then be fixed in your JD Edwards data.

This test is performed across the JD Edwards Balances tables; the inconsistency could be in a single table or multiple tables. Any integrity issues found by running this test need to be fixed in your JD Edwards data.

So for example, you have the following account in the Account Master:

ACCOUNT ID (AID) CO (CO)  BU (MCU) OBJ (OBJ) SUB (SUB)
00000100 00001  1 1110  BEAR

In the Balances or Transactions table you would have postings against the same structure and the transactions balance:

ACCOUNT ID (AID)

CO (CO)

BU (MCU)

OBJ (OBJ)

SUB (SUB)

VALUE

00000100

00001

1

1110

BEAR

100

00000100

00001

1

1110

BEAR

-100

If you sum the values by Company (Co), this would balance to 0:

COMPANY (CO) VALUE
00001 100
00001 -100
Total for Company 00001  0

And again by Business Unit (MCU), this would also balance to 0:

BUSINESS UNIT (MCU) VALUE
1 100
1 -100
Total for Business Unit 00001 0

Scenario with incorrect data

If, for example, you have a single Account ID associated with more than one BU:

ACCOUNT ID (AID)

CO (CO)

BU (MCU)

OBJ (OBJ)

SUB (SUB)

VALUE

00000100

00001

1

1110

BEAR

100

00000100

00001

2

1110

BEAR

-100

In this scenario, your trial balance by company would be correct but your trial balance by Business Unit would be inaccurate.

If you sum the values by Company (Co), this would balance to 0:

COMPANY (CO) VALUE
00001 100
00001 -100
Total for Business Unit 00001 0

However, by Business Unit (MCU), this would not balance to 0:

BUSINESS UNIT (MCU) VALUE
0 0
1 100
Total for BU1 100

To get the correct result in Hubble, the data must be consistent across the Balances and Transaction tables.

Continuing with the same scenario, the test results for the Account ID integrity test would be as follows:

Account ID Test -- Warning: The following Company, Business-Unit, Subsidiary and Object-Account combinations from table [dbo].[F0902] are linked to more than one Account ID: '00104 - 020713    - - 8300 ', '00104 - 020713    -    - 8300 ', '00104 - 020713    -    - 8300 '

These results show that in the F0902 file there is more than one account ID present for the combination of Company, Business Unit, Subsidiary and Object Account.

  • Note that the combination of Company, Business Unit, Subsidiary and Object Account is separated by “-“ and the complete combinations are comma separated.

The second test that the Account ID integrity test does is the reverse of the first. It checks to verify that for each Account ID there is only one combination.

Account ID Test -- Warning: The following Account IDs are linked to more than one combination of Company, Business-Unit, Subsidiary and Object-Account: '00055319', '00055327', '00055458', '00055589', '00072477'

Data Dictionary Overrides

Hubble knows to add leading zeros to a company column when the columns data dictionary is set with Display Rule of MASK/0 (F9210.FROWDR = MASK and F9210.FRODR1 = 0) which is why tables with a xxPKCO column will automatically have leading zeros. However, columns like CO, KCO, and KCOO no longer have the Display Rule set with MASK/0 in JD Edwards, so a Profile Data Dictionary Override is needed to add the leading zeros.

The Data Dictionary Override is designed to override settings held within the JD Edwards Data Dictionary in order to negate any inconsistencies within the database that might interfere with the operation of Hubble. Note that any override set for the profile does not modify the Data Dictionary in your JD Edwards database. The override information is stored within the Hubble Object Repository.

To set up overrides to the Data Dictionary:

  1. Log into your repository if you are not logged in already.
  2. Expand the Data Sources node in the left panel.
  3. Click on Profiles.
  4. In the right panel, right-click on the profile that you wish to amend.
  5. Select Assign Data Dictionary Override.
  6. Using the drop-down menu next to Use override, select the override you wish to use.
  7. If you need to create a new override, click Manage.
  8. In the Data Dictionary Overrides dialog, click New.
  9. Enter a name for the new Data Dictionary Override.
  10. Click OK.
  11. Enter the Name of the item as entered on the Data Dictionary table, and click Retrieve to import the attributes to the override table.
    • The naming convention for Data Dictionary Overrides ONLY allows for letters, numbers and underscores. Space characters are not valid.
  12. You can now modify the settings for that particular data item. These new settings will be the values used within Hubble once it is restarted.
Example of a Data Dictionary Override

By default, a Data Dictionary Override is set up for column CO so that you do not need to enter in leading zeros if there are less than 5 values in a company number: