In the following exercise we will walk through the setup of a simple Aggregation Task. Let us assume that we want to define a new Aggregation Task called tutorial and run it against the models generated as a result of the Sample Portfolio execution.
Data Model Selection
The first step, as in the majority of tasks, is selecting Data models for the new Aggregation task to run against. We will use two Data Models in our example:
1. The DM resulting from the Sample Portfolio execution – Portfolio[Sample_portfolio].tutorial_transactions
2. The original DM named ‘tutorial_transactions’.
To select Data Models:
1. On the Models tab of the Aggregation Setup screen button of the tab’s Tool bar. The lookup displays available models.
2. Use Ctrl button for multiple select it and click OK to close the lookup.
3. Selected data models appear in the grid (Fig.).
4. At this point you can save your new Aggregation Task (notice that the Save button became active as soon as you selected models). Click Save. This will open Save dialog (Fig) where you enter the name and description for the Aggregation.
5. Enter name and description for the new Aggregation (Tutorial in our example) and press OK to close the Save dialog. The Comment window (Fig.) will open offering you to enter a brief comment about saving the task.
6. Whether you choose to enter the comment (recommended) or not, press OK to close the Comment dialog and finalize the SAVE.
When you save an aggregation task the following takes place:
1. The system generates a resulting Source for each data model involved in the task.
2. The system generates resulting Data Model for each data model associated with the task.
Objects representing system-generated resulting data sources and models are added to the Objects Pane of the Project Management screen.
The next step is selecting Details for the Tutorial Aggregation Task. Let us assume that we want to see the summaries broken down by the following detail elements:
1. Branch Code
2. Product Code
3. Currency Code
4. Country Residence
Selecting the above detail elements will enable you, for example, to choose a particular Branch and break down the total figure for this Branch by product or currency. Or you will be able to isolate one country and see how the totals for this domicile are distributed among various branches:
1. On the Detail tab press and select required detail elements. Use Ctrl for multiple select.
2. Press OK to close the lookup. Selected detail elements appear in the data grid (Fig.).
NOTE: Do not choose fields with FLOAT data type as Detail elements.
3. Press Save.
Note: The system will not allow you to Save if it failed to map one or several details . The error message will appear. You will have to manually adjust the mapping and Save again.
Calculated Field Selection
Calculated field is the field where the Aggregation execution results are displayed. Let us assume that we would like to use profit_loss_amount and amount_in_local_currency fields to display the summaries. While deal_amount field contains financial figures expressed in currency, crw is expressed in percentages, which result in a different setup (see below).
We will also create an additional calculated field for Cash deals and map it to the corresponding fields in the data models used in the current Aggregation task.
To select Calculated Fields:
1. Open the Calculated Fields tab.
2. Press Column Selection button to open the lookup.
3. Select required fields from the lookup and click OK. Selected Calculated fields appear on the grid of the tab (Fig.).
Although the initial field mapping is done automatically, sometimes the system fails to identify and the appropriate field in the source leaving the field in the Fields Mapping tab blank. It is also possible that the system will mis-map the field due to unclear field naming. You will have to do mapping manually by using the column lookup.
This concludes Tutorial Aggregation basic setup.
Optional Aggregation Setup Steps
In this section we will describe optional steps of the Aggregation setup. These advanced features, while expanding the scope of the system, may require proficiency in the SQL programming and database administration. We strongly encourage users to consult Axiom SL prior to using features described below.
Auxiliary Fields Setup
Auxiliary fields are fields from the data model that contain data used in aggregation calculation but does not appear in the aggregation results. This feature is useful when you need to set up a calculation field using the “Add New” feature (see above) or fine-tune populating rules for the calculated fields taken from the original source data model.
For example, your model may have a field called “foreign gross amount” and another called “exchange rate” while the report requires the results in “Domestic gross amount” (that is expressed in domestic currency). In order to express the Foreign amount in the domestic currency you would multiply “foreign amount” by “exchange rate”.
1. On the Auxiliary Fields tab use button to insert “foreign gross amount” and “exchange rate” fields as auxiliary fields into the Auxiliary Fields tab.
2. On the Calculated Fields tab use button to create a calculated field called “gross_domestic_amount” and as the calculation formula enter: “foreign gross amount” * “exchange rate”.
Note: No mapping is required for the gross_domestic_amount calculated field since it is not being populated from the corresponding field in the original source data model.
Synthetic Fields Setup
The purpose of Synthetic Fields is to enable users to perform ‘on-the-fly’ arithmetical and logical operations on the aggregation execution results generating additional figures. These fields exist only on the viewer screens and are not stored on the database.
To set up a Synthetic field, using the example above, you would:
1. Create a field in the Synthetic Fields tab and call it “domestic gross amount”
2. Use “foreign gross amount” * “exchange rate” expression as the formula for the calculation.
3. Include “foreign gross amount” and “exchange rate” columns on the Calculated Fields tab.
The results of the above setup will be that all three columns will be shown in the Aggregation Viewer.
To create a Synthetic field:
1. Click on button of the Synthetic tab’s tool bar to insert the row into the tab’s grid (Fig.).
2. Enter the name of the field ‘domestic_gross_amount’ using low case and underscores.
3. Enter the description of the entered field.
4. Into the Value (Formula) field enter foreign gross amount” * “exchange rate” expression. Use to select column names to be used in the expression.
Note: Make sure to include the fields you are intending to use in the calculation formula in the auxiliary fields list.
5. Set Value Type to Float.
Indexing (Drill-down Optimization)
Indexing (Drill-down optimization) helps to enhance the system’s performance in retrieving and loading the data. It involves building the drill down pathways following which the system generates indices that facilitate data retrieving and loading. To build a Drill down optimization path: .
1. On the Indexing tab use to insert a row into the grid (Fig.).
2. Use to open a lookup and select details for the first drill down sequence. Use Ctrl to do multiple select.
3. Click OK to close the lookup. Selected detail sequence appears in the Data Grid (Fig.).
4. Use steps 1 through 3 to create other drill down sequence combinations of the details. Consult your DBA however about the most efficient way for your particular database.
Note: Creating too many Indexing paths may create an excessive strain on the system’s resources and negatively affect its performance.
Resulting Source Parameters Setup
The system allows you to modify default settings for the system-generated source resulting from the Aggregation execution.
On the Parameters tab (Fig):
1. Continuous as the Data storage type
2. ORACLE as the DB Source
3. Check No for the Details as Keys parameter
Archiving Schedule Setup
On the Archiving tab place ‘Yes’ check marks on ‘Daily’, ‘Weekly’ and ‘Monthly’ archiving schedules. You will be able to determine exact archiving schedule on the Aggregation Execution screen.
Renaming Aggregation Task
Clicking on the Rename Task button opens an input window where you type in a new name/description for the current task.