AARO Adapter

The data displayed in CXO reports can be extracted from AARO by starting jobs from the Source System Manager and by scheduling them.
We will first describe the technical infrastructure of the solution and then give instructions on how to start extraction jobs. Finally, we will add some guidelines for maintenance (server-ids, passwords, etc).

Architecture, processes and interactions
The below picture shows the data-flow (red arrows) and the interactions between the various objects that are being used in the Extraction process.

Open

Schematic overview of databases and data flows
The header of the diagram shows the servers that are (currently) being used. This can change over time.

Data flow:

1. The CXO Hub connects to the AARO application to get the meta-data and the data. Data and Meta-data is collected in an SQL Server database (cxo_AAROstaging_prod).

2. In this staging database we defined a number of Stored Procedures which are called to post-process the data and copy it to a final fact-database (cxo_fact_prod).

3. The final step is that an Analysis Services (OLAP) database (cxo_prod_extended) is being processed on the basis of the dimensions and facts in this fact database. (The term ‘extended’ refers to the fact that we use an SSAS Database with 15 analytical dimensions. The standard CXO cube is limited to 10 analytical dimensions only.)

Other interactions (blue arrows):
With the CXO-Cockpit Source System Manager you can run jobs that can

• Run a Stored Procedure

• Run an executable (in this case the CXO Hub)

• Process an OLAP Database.
  
  

These jobs can be defined in XML formatted scripts and imported in a special database used to organize these jobs and monitor their progress (cxo_psstaging_prod). A trigger to start a job is also sent to this database (arrow 1).

Once a job is triggered from the Source System Manager it will be picked up by the CXO Agent (one of the 2 services of CXO that continuously poll for tasks) (arrow 2).

One of the tasks in a job can be the execution of the CXO Hub (arrow 3), which connects to AARO to extract data and meta-data.

Connection to AARO is mediated by an ODBC System DSN which in turn uses the AARO ODBC64 Driver, which has been installed on the CXO Server (S0006524) (arrows 4 and 5).

Open

When started without any argument, the CXO Hub is fully governed by a table named ‘queries’ in the cxo_AAROstaging_prod database (arrow 6).

Starting Data and Meta-data jobs
To execute jobs for maintaining data (extractions) and meta-data, start the Source System Manager and select the Source System ‘ExtractAARO’ (This is a special Source System jobs and cannot be used for building reports.)

To get an overview of the available jobs and start a job, press the button ‘Integration Settings’. The following dialog will show:

Jobs:

1. Display Selected Members:

This job will display all Codes, Periods, Currencies and Currency Translations (Stored in database cxo_aarostaging_prod in tables cxo_selected_codes, cxo_selected_periods, cxo_selected_currencies and cxo_selected_currency_translations respectively.) that will be extracted in case of a Full Data Extract

Click on Run:

Press OK:

Then you will see the job-execution in the window below. As soon as it says ‘Succeeded’, click on the link and you will get a Status overview:

To get the list of members for each dimension, click on the line and press Ctrl-C. This will copy the line to the clipboard. You can then paste it in e.g. Notepad. E.g., for the Codes:

2. Add Members to Selection

With this Job you can add members to the selections for the Dimensions Codes, Periods, Currencies and Currency Translations. These members should be added as a comma-separated string.

If you start this job, you can enter the requested additions in the popup-dialog:

Keep the text-box empty if no members need to be added.

Only members that actually exist in the AARO application will be added. To retrieve the actual set of dimensions and members, execute job ‘Get AARO dimensions’

NOTE: Avoid adding Codes with a complex calculation in AARO. It is better to recalculate them in CXO by means of Cube-calculations. We had negative experiences with the Codes: CEAVG_5, EQAVG, EQAVG_5, EQR12, NPR12, NSR12 (so basically all Codes with AVG and / or R12 in the name). We found out that these can cause time-out errors on the AARO server.

3. Remove Members from Selection

The reverse of the previous job. E.g., if a new year starts you could consider to remove a number of historic Periods:

4. Get AARO dimensions

With this job you can obtain a copy of the latest Dimension Tables from AARO. You should run this job each time the meta-data of the AARO application – dimension members – have been updated. This is also important to ensure the right aliases of the dimension members in the CXO reports.

5. Full Data Extract

This job enables a complete data-extract for all selected members of the Codes, Periods, Currencies and Currency Translations dimensions (see Job 1).

Any data-extraction (full, incremental) is broken down into a series of SQL Queries against AARO. Each Period is extracted individually, together with all selected Currencies, all selected Currency Translations and a subset of the selected Codes.

The size of this subset of Codes can be specified in an application parameter. This can be done by manually changing a field in table application_variables of database cxo_AAROstaging_prod:

The current value is 50.
The total number of queries that will be generated in full data-extraction is calculated as:

No. of selected periods x (no. of Codes / 50) x 2

Currently, we have selected 898 Codes and 53 Periods. This brings the total no. of queries to 1908.

The multiplication by 2 is because each data-query against AARO is followed by a post-processing query within the cxo_AAROstaging_prod database. Splitting the extraction in so many queries is done to ensure that the AARO server doesn’t get overburdened.

You can press the In Progress link to get the status of the job, including possible Errors.

6. Incremental Data Extraction

You can also run extractions incrementally. This is much faster and particularly convenient during closing periods where you only want to (re-) extract the current Acuals and where you want to repeat this several times a day.

Obviously, the burden on AARO is also (much) less.

When starting a job, you get the following popup:

Only enter members for the dimensions where you want to restrict the selection (Periods in this case). You can again enter a comma-separated list of members.
Note that you can also enter members that are not (yet) part of the selections used for the Full Data Extract. However, then it’s wise to also add the same members through an ‘Add Members to Selection’ job.

Maintaining Connections

The connection to AARO is established via an ODBC System DSN which makes use of the (installed) AARO Driver (64 bit).
To update the System DSN:

1. Start ODBC Data Sources 64-bit (via Administrative Tools):

2. Press Button ‘Configure’:

3. Change the settings:

Hub configuration file

The CXO-Hub is being used to connect to AARO and execute the queries generated during job-execution.
The tool is located in:

The highlighted file is the Configuration file with a number of parameters needed for a proper working of the Hub.
Most important parameters are

1. The connectivity to SQL Server:

Here you have to enter the credentials of the SQL Server instance where the AARO extractions will be stored:

As you can see, the password is stored encrypted. You can get the encrypted password by running the Hub
from the command line as follows (example of a password ‘mypassword’):

The string can be copied from this dialog into the yellow-marked area of the HUB configuration file

2. The connectivity to AARO (ODBC):

Changing the password works the same way as for the SQL Server

Hub log-file

The process steps and errors related to the Hub are stored in the following file:

This file is not archived. Archive and/or delete the file if it grows too big (and can hardly be opened by Notepad anymore).