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

Create the S300_SCHEMA global variable

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

Configure the cube extractions

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

Extraction Type	Description
1 – Table Staging	Loads the staging tables required for cube processing. Executed only once.
2 – Dimension Mapping	Loads 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 300 ZIP file (DS_20XX.0.X.XXX_Sage 300 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 300 database.
8. In Destination Connection, select your cube database connection (for example, SEICube or NectariCube).
9. In Source Schema, select your Sage 300 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 Sage 300 ZIP file (DS_20XX.0.X.XXX_Sage 300 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 300 database.
8. In Destination Connection, select your cube database connection (for example, SEICube or NectariCube).
9. In Source Schema, select your Sage 300 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.

Build and load from the OLAP Manager

Before continuing

• If not already done, create an OLAP cube data source in SEI.

Build the OLAP cubes

1. In the navigation panel, select the gear icon to open Administration.
2. Select OLAP Manager.
3. In the Options panel on the right, click the Manage icon.
4. In the Cubes list, select all the template cubes.
   Template cubes are typically named according the template name.
5. In the Manage panel, make sure Build is selected from the Actions dropdown list.
6. Select the environment.
7. Select Confirm.
8. In the confirmation dialog, check the box to confirm you understand all current cube data will be lost, then click Yes.
   If errors occur, refer to Logs to enable and review logging.

Load the OLAP cubes

1. In th Options panel on the right, click the Manage icon again.
2. Check the built template cubes from the Cubes list.
3. In the Manage panel, make sure Load All is selected from the Actions dropdown list.
4. Select the environment.
5. Select Confirm.
6. In the confirmation dialog, check the box to confirm you understand all current cube data will be lost, then click Yes.

Schedule the OLAP cubes

You can schedule regular data refresh jobs for your OLAP cubes. The scheduler helps you automate when and how often the cube is refreshed or reloaded.

1. In the OLAP Manager, select a template cube from the Cubes list.
2. In the Options panel on the right, click the Navigation icon.
3. Select Scheduler. The Jobs window appears.
4. In the Options panel on the right, click the + icon to add a new job.
5. Complete the required parameters as needed.
6. Click Save. The job nows appears in the Jobs list.
   Repeat these steps for any additional template cubes you want to schedule.

Job Details parameters

Parameter	Description
Description	Enter a job name.
Action	Select: Load All – Removes the data and reload everything into the cube. Refresh – Adds only the new records based on the Tracking defined at the Data Source level.
Environments	Select the environment.
Active	Activate the job on its schedule. Unchecked jobs will not run.
Settings	Choose between: Once – Runs the job once based on your settings. Daily – Runs the job on a daily basis based on your settings. Weekly – Runs the job on a weekly basis based on your settings. Montly – Runs the job on a monthly basis based on your settings.
Once	Start On – Choose the specific date for delivery. At / From – Choose a fixed time (At) or a time range (From…until) with defined delivery intervals (in minutes).
Daily	Start On – Choose the specific date for delivery. At / From – Choose a fixed time (At) or a time range (From…until) with defined delivery intervals (in minutes). Recur every X days – Set the number of days (1–31) between deliveries, for example, every 20th day.
Weekly	Start On – Choose the specific date for delivery. At / From – Choose a fixed time (At) or a time range (From…until) with defined delivery intervals (in minutes). Recur every X weeks on – Choose how many weeks (1–52) between deliveries and the specific day(s) of the week to receive it. You can also enable the Everyday toggle for daily delivery.
Monthly	Start On – Choose the specific date for delivery. At / From – Choose a fixed time (At) or a time range (From…until) with defined delivery intervals (in minutes). Months – Select specific months for delivery or enable the Every Month toggle. Days / On – Choose a specific day of the month for delivery or enable the Everyday toggle for daily delivery. Alternatively, you can use the On option to specify the First, Second, Third, Fourth, or Last occurrence of a particular weekday. Multiple occurrences can be selected.
Time Zone	Select the time zone for job execution.
Advanced Settings	(Optional) Configure when the job will expire if needed.