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 Acumatica application server.
  8. Click Save.

Add the ACUMATICA_SCHEMA global variable​

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

Cube extraction​

Cube extractions must be imported and built in a specific order. Importing them out of order will cause validation or build failures.

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.

Configure the cube extractions​

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 Acumatica ZIP file (DS_20XX.0.X.XXX_Acumatica 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 Acumatica database.
  8. In Destination Connection, select your cube database connection (for example, SEICube or NectariCube).
  9. In Source Schema, select your Acumatica 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.

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 Acumatica ZIP file (DS_20XX.0.X.XXX_Acumatica 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 Acumatica database.
  8. In Destination Connection, select your cube database connection (for example, SEICube or NectariCube).
  9. In Source Schema, select your Acumatica 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