Skip to main content

Sales and Finance OLAP cubes

If you have the Data Management add-on and plan to use the Sales and Finance cubes, extra steps are required after the standard installation. These cubes run on a separate data warehouse database, so you need to configure an extra source connection and import the cube extractions before building and loading the cubes.

important

Complete the standard installation before following these steps.

Create a data warehouse source connection​

  1. Open DataSync and click Connections.
  2. Next to Source Connections, click New.
  3. Choose the connection type: SQL Server, or SQL Azure (for SEI Cloud).
  4. In the Connection Properties panel, enter Data Warehouse (Source) as the description.
  5. Enter the Server and Database names matching those used by your data warehouse.
  6. Enter the data warehouse database credentials for Username and Password.
  7. In the Advanced Settings panel, set Tracking Type to Date.
    The time zone must match the Sage X3 application server.
  8. Click Save.

Configure the global variables​

Create the X3_CUBESCHEMA global variable​

  1. From the DataSync welcome screen, select Variables.
  2. Next to Variables, click New.
  3. In Variable Name, enter X3_CUBESCHEMA.
  4. In Variable Type, select String.
  5. In Variable Value, enter [YOUR_SCHEMA].
  6. Click Save.

Create the X3_LANG global variable​

  1. From the DataSync welcome screen, select Variables.
  2. Next to Variables, click New.
  3. In Variable Name, enter X3_LANG.
  4. In Variable Type, select String.
  5. In Variable Value, the language code you want to use for dimension descriptions (e.g., ENG).
  6. Click Save.

Create the OPENBAL global variable​

  1. From the DataSync welcome screen, select Variables.
  2. Next to Variables, click New.
  3. In Variable Name, enter OPENBAL.
  4. In Variable Type, select String.
  5. In Variable Value, enter one of the following:
    • SIM — simulate opening balances when none exist in Sage X3
    • X3 — only use opening balances from Sage X3
  6. Click Save.

Configure the cube extractions​

Extraction TypeDescription
1 – Table StagingLoads the staging tables required for cube processing. Executed only once.
2 – Dimension MappingLoads the dimension‑mapping tables used by the cubes and refreshes the data.

1 – Table Staging extraction​

  1. In the left panel, select the Extractions icon.
  2. Click the Import Extraction icon in the top-right corner to open the dialog.
  3. Click Choose a zip file and select the Sage X3 ZIP file (DS_20XX.0.X.XXX_Sage X3 OLAP ETL Staging.zip), or drag and drop the file into the dialog.
  4. Click Next.
  5. Confirm the default extraction type is selected. Click Next again.
  6. In the Description field, enter a meaningful name, such as Cube - Table Staging.
  7. In Source Connection, select the connection that points to your Sage X3 database.
  8. In Destination Connection, select your cube database connection (for example, SEICube or NectariCube).
  9. In Source Schema, select your Sage X3 folder (for example, PROD or SEED).
  10. In Destination Schema, select the custom schema configured in Environment & Data Sources in SEI.
  11. Click Import.
  12. In the left panel, click Extractions again.
  13. Select the extraction you just created, and click the Validate and Build icon in the top-right corner.
  14. In the dialog, for the first company only, select Drop the previously created object and recreate the objects based on the new definition.
  15. Click Build.
    When the process is complete, review the validation report and the Status column. If an error appears, click the link in the Error column to view the Log Page.
  16. Select your extraction from the list and click the Run Extraction Now icon.
  17. Select the tables to populate.
  18. In the upper right, set the load mode to Truncate and Load, then click Run. This replaces all destination data with current source data.
    Wait for the process to finish. The results appear in the Status column. If there's an error, click the link in the Error column to view details in the Logs page.

2 – Dimension Mapping extraction​

  1. In the left panel, select the Extractions icon.
  2. Click the Import Extraction icon in the top-right corner to open the dialog.
  3. Click Choose a zip file and select the Sage X3 ZIP file (DS_20XX.0.X.XXX_Sage X3 OLAP ETL Refresh.zip), or drag and drop the file into the dialog.
  4. Click Next.
  5. Confirm the default extraction type is selected. Click Next again.
  6. In the Description field, enter a meaningful name, such as Cube - Dimension Mapping.
  7. In Source Connection, select the connection that points to your Sage X3 database.
  8. In Destination Connection, select your cube database connection (for example, SEICube or NectariCube).
  9. In Source Schema, select your Sage X3 folder (for example, PROD or SEED).
  10. In Destination Schema, select the custom schema configured in Environments & Data Sources in SEI.
  11. Click Import.
  12. In the left panel, click Extractions again.
  13. Select the extraction you just created, and click the Validate and Build icon in the top-right corner.
  14. In the dialog, for the first company only, select Drop the previously created object and recreate the objects based on the new definition.
  15. Click Build.
    When the process is complete, review the validation report and the Status column. If an error appears, click the link in the Error column to view the Log Page.
  16. Select your extraction from the list and click the Run Extraction Now icon.
  17. Select the tables to populate.
  18. In the upper right, set the load mode to Truncate and Load, then click Run. This replaces all destination data with current source data.
    Wait for the process to finish. The results appear in the Status column. If there's an error, click the link in the Error column to view details in the Logs page.

Before continuing