Sage X3

About the Data Source

The following characteristics should be noted when using this data source:

  • Keys and relationships. Automatically configured to ensure data quality and performance in the cube.

  • Captions and translations. Captions for tables, columns, and menus (enums) for one language are automatically migrated. Tables in the data warehouse have user-friendly names.

Supported versions of Sage X3

Data Hub supports connections to Sage X3 versions 6.5, 7, 8, 9, 11 and 12, either deployed on an SQL Server or an Oracle Database.

Connect to the data source

Specify the following connection settings. Differences between the Oracle version of the data source and the non-Oracle version are highlighted below.

  1. Server - The name of the SQL Server instance hosting the Sage X3 database you want to access. Just the server name may be entered if there is only one SQL Server instance on that server. If the server hosts multiple instances, use the syntax ServerName\InstanceName. Type in the instance name directly, or choose from a list of instances on your local network using the drop-down.

  2. Authentication - (Non-Oracle version only). Provide credentials to authenticate to the SQL Server instance hosting the Sage Enterprise Management database (specified above) in two ways. The method used will depend on how your SQL Server instance is configured:

    1. Windows login - Enter a Windows user name and password.

    2. SQL Server login - Enter a SQL Server user name and password.

  3. User Name - Type a user name in the same format you used to log in, directly to Windows or SQL Server (depending on which authentication method you chose above for the non-Oracle data source) or Oracle (for the Oracle version of the data source). For non-Oracle connections, this entry may be just a user name, or it may include the domain name in canonical form (domain\user) or UPN form (user@fully-qualified_domain_name), depending on your environment.

  4. Password - Type a password for the specified user name.

  5. Database - Type the Sage database name to use as the data source, or select the Sage X3 database from a drop-down list of databases found on the specified SQL Server instance.

  6. (Optional) Sage X3 Service Connection - Use these settings to dynamically extract Active Directory information about users.

Non-connection options

  1. Click Test Connection to verify that data source connection is working. Further options are specified after Test Connection is clicked. If successful, a green check mark appears.

    Note

    Once you finish creating your model and exit the wizard, you should review the properties for the data source to determine if any additional, non-connection settings need to be defined.

     

  2. Specify the following settings after Test Connection is clicked:

    1. Folder - Location in Sage X3 source from which model information will be extracted.

    2. Caption language - Language for the Sage X3 captions. All languages supported by your Sage X3 installation are available. Choose a language used by your captions.

  3. Click Next to proceed to the next step in the wizard.

Set advanced data source properties

Advanced connection settings can be added using either of the following:

  • Advanced panel on the Connect screen when adding the data source.

  • Properties panel when viewing an existing data source, under the Advanced section.

Sage X3 Dynamic Security

The Sage X3 data source supports dynamic dimension security for Microsoft Active Directory users, when used as part of the Sage X3 v7.1+ solutions.

Dynamic security ensures the cube security produced by the data model, when accessed using Data Hub, mimics the security settings in Sage X3, based on the currently logged-in user.

Security information by site, company, and module is extracted from Sage X3, which secures the traditional pipelines access by site and company.

Requirements for Dynamic Security

The following requirements must be in place for dynamic dimension security to work:

  • Although the Sage X3 data source supports dynamic security by extracting users from Syracuse, it is only in effect for models built from the Sage X3 7.1+ solutions.

  • Sage X3 version 7 or later, must be used, as service connection extractions are only supported for these versions.

  • An LDAP server using Active Directory is used for authentication by Sage X3.

    The LDAP server is configured in the All > Administration > Administration > Users screen in Sage X3.

SAGE 5 mod-data-source-sage-users-ldap.png

  • Active Directory users have been synchronized with Sage X3, and there is an LDAP user in Sage X3 for each Data Hub user.

  • Every LDAP user in Sage X3 must be configured with an Endpoints login, corresponding to the user's Login field in Sage X3.

SAGE 7 mod-data-source-sage-users-endpoint.png

Note

The Endpoints login, which must match the Login field shown above, may be configured on the All > Administration > Administration > Users screen in Sage Enterprise Management. The user must have the same endpoint folder.

  • The Login field may be viewed in the All > Setup > Users screen in Sage X3.

SAGE 8 mod-data-source-sage-users-screen.png

  • The function profile can be set in: All > Setup > Users > Function Profile.

SAE 9 sage-em-users-function-profile.png

  • Alternatively, the function profile can be set for a user, by specifying the Profession code (which will have a function profile associated with it).

SAGE 10 sage-em-users-profession-code.png

  • The Trade profile may be viewed in the All > Setup > Users > Trade Profile screen in Sage X3.

SAGE 11 sage-em-trade-profile.png

  • In All > Setup > Users, if both the Function profile and the Profession code are specified, the Function profile in the User profile takes precedence over the Function profile in the Trade profile.

Sage X3 Service connection settings

The Sage X3 data source can extract Active Directory information about users for the endpoint folder specified in the Folder drop-down.

To activate the feature:

  1. Select the Connect to Sage X3 Service check box.

  2. Enter appropriate values for the URL, User name, and Password text boxes to set up a connection to the Sdata Sage X3 service.

    • Only extract LDAP users check box ensures that only users with LDAP user authentication are synchronized from the Sage X3 Service Connection.

    • Clear this check box to extract all users from Sage X3, regardless of authentication type.

      This is used for Sage X3 servers where users are set up with a different authentication method, with the same user name as their Active Directory user name (such as users synchronized from the LDAP server but then changed to a different authentication).

  3. Click Test Connection to confirm the settings work.

Note

When connecting to versions of Sage X3 before version 7, you must leave the Connect to Sage X3 Service check box cleared (unchecked), as the Sdata service is unavailable for these versions.

Additional features

The following items should be noted when using Sage X3 dynamic security.

Security logical tables

Once a Sage X3 service connection has been established, two new logical tables will appear in the data source's table list:

  • Zap.SitesAndModules. Lists all combinations of sites and table modules; it is used by the Sage X3 solution to secure the entire model.

  • Zap.UserSecurity. Stores information about all available LDAP Active Directory users.

Module column and relationships

A new Module column is automatically added when adding a table as a pipeline from the data source screen.

This column is used in combination with the Site column to relate to the pipeline that originates from the Zap.SitesAndModules logical table. Relationships to the SitesAndModules pipeline are automatically added if the product detects a module exists and a Site column is related to the Site pipeline.

In this section: