...
In the first tab, enter the connectivity details to the HFM database:
You can check the connectivity by pressing the Check button.
- DB Type: We support 2 Database Types: SQL Server and Oracle.
Settings for SQL Server: - SQL Server: The SQL Server is the SQL Server instance where the HFM data are stored. Note: as a rule this is not the same database-server as used for the CXO-Cockpit databases.Consult your HFM System Administator to get these specs.
- Database: The name of the HFM database (usually something like FM, HFM, HYPPROD, ...)
- Application: The name of the HFM application from which we want to extract.
- Windows Authentication:
- Check the box if you want to access the HFM database by Windows Authentication: Note that not only the user of the Design Studio ('you') should be authenticated, but also the Windows account that is used as a Service account for the CXO-Cockpit services. Data extractions are carried out by our Agent Service:
The Windows account(s) must have read-rights on the HFM database. - If you want to use a SQL Server account: enter a username / password. Also this account can be a read-only user.
Settings for Oracle:
For an Oracle connection a TSN TNS configuration is required on the CXO Cockpit application server:
Consult your Oracle DBA for more details. - Check the box if you want to access the HFM database by Windows Authentication: Note that not only the user of the Design Studio ('you') should be authenticated, but also the Windows account that is used as a Service account for the CXO-Cockpit services. Data extractions are carried out by our Agent Service:
...
- Extract ICP Details: check this box if you want to report on ICP transactions. Note that this may result in a lot more data records and will slow down the extraction and transformation process.
- Aggregate ICP Entities: this is a reflection of a setting within HFM named ICPEntitiesAggregationWeight. Checking the box is equivalent to a weight of 1.0 (this is most widely used). Un-checking means 0.0.
Retrieve HFM Meta-data
...
In order to setup the extraction (creating Load Sets and Subsets) we first need to retrieve a local copy the HFM Meta-data (the dimension specifications).
This can be done by pressing the button 'Get Metadata' and then wait (few seconds to couple of minutes) until the following dialog appears:
Note: Maintain a running application:
1. Copying the metadata This process has to be done repeated each time the dimensions of HFM application has changed (added / removed / moved members of a dimension)
After 'Get Metadata' there is still no data on the new members, therefore running a 'Base' load set is recommended and will extract data for all scenario's.
2. If you are not able to run a 'Base' load set an 'Incremental' load set could be triggered. First press the (2) 'Update CXO Dimensions' to send all (new) metadata to CXO and secondly run an 'Incremental' load set to extract corresponding data and process the cube.
Create a Calendar
After retrieving the meta-data for the first time, we still have to perform one manual step: fill a table in the cxo_hfmexressstaging_... database with the specifications of the calendar.
We have prepared the script in a Stored Procedure sp_create_calendar. Select the stored procedure:
, then do a right mouse click and select Modify:
The following script will appear:
This script can be used to define various calendars. To generate a calendar with a Half Year / Quarter / Month layout:
- select the first block ('truncate ...') and press F5. This will clear the current calendar table.
- then select all lines in the 2nd block and press F5
- you can then close the window. Ignore the requests for saving.
In the comment-area you can see an alternative calendar: only a Quarter level and the months : new dimension members added, members removed, aliases changed, etc.
After retrieving the HFM meta-data you should check the selected Dimension Members (see below).
Manual update of CXO dimensions
After retrieving the latest version of the HFM meta-data, usually a full (base) extraction is done (see details further in this document) to make sure that all current and (restated) historic data is retrieved taking the updated dimensions into account. A full (base) extract automatically implies that also the CXO dimensions are updated.
Sometimes this is not feasible because the phase of the reporting cycle does not permit the waiting times associated with a full data-refresh. In that case you can apply a shortcut by pressing the button 'Update CXO Dimensions':
Now you can skip a full data-refresh and start an incremental load or a real-time synchronization.
Create a Calendar
After retrieving the meta-data for the first time, we still have to perform one manual step: fill a table in the cxo_hfmexressstaging_... database with the specifications of the calendar.
We have prepared the script in a Stored Procedure sp_create_calendar. Select the stored procedure:, then do a right mouse click and select Modify:
The following script will appear:
This script can be used to define various calendars. To generate a calendar with a Half Year / Quarter / Month layout:
- select the first block ('truncate ...') and press F5. This will clear the current calendar table.
- then select all lines in the 2nd block and press F5
- you can then close the window. Ignore the requests for saving.
In the comment-area you can see an alternative calendar: only a Quarter level and the months abbreviated to 3 characters. Of course, you are free to change the layout, for example to accommodate a broken fiscal year.
...
Note that creation of the calendar is a one-time action and does not require any maintenance.
...
Include the [None] member if you want to report on / use Currency Conversion rates.
Note that we will not extract 'XXX Total' itself (in CXO simply called 'XXX'): always the underlying 'XXX' (in CXO represented as 'XXX Input') and 'XXX Adjs' members are extracted. In the example of 'USD Total', in CXO we will see:
If only the total level is needed, we can pre-aggregate the values and in that way flatten the dimension (saves records and an extra hierarchy). This is governed by a system variable. See section System Variables. :
- In CXO the post-fix 'Total' will be omitted
- <Entity Curr Total> will not appear in CXO. To work with local currency a Cube Calculation has to be defined.
Note that - opposite to the older adapter versions - the HFM Value dimension is not stored in the VAL dimension of CXO anymore but in the GAP dimension. The reason is that the VAL dimension is a flat dimension not allowing parent-child structures. This should be kept in mind in migrations.
...
A fragment of the resulting dimension in CXO looks like:
WYSIWYG mode (mode 2)
Tree-selection mode is the preferred mode because it requires the least amount of maintenance. For instance, if in HFM accounts are added or deleted they are automatically selected for CXO if their root node is selected.
WYSIWYG mode (mode 2)
(WYSIWYG mode is added for legacy reasons)
In this mode we use black font. Similar to the Entity dimension, the resulting Account dimension in CXO exactly looks the way you expanded the selection.
...
Irrespective of the selected mode, best practice is to avoid multiple occurrences of members in the selection. This will slow-down the Processing of the OLAP database and/or the report queries. In doing that you should always realize that we extract all details under a certain parent node: even if you did not select duplicate members they can be there implicitly among the descendants of selected nodes.
Opposite to the Entity dimension:
...
Custom Dimensions
The same logic as the Account dimension applies to selecting members from Custom dimensions:
You can now Extract the Base Load Set.among the descendants of selected nodes.
Opposite to the Entity dimension:
- It is not allowed to skip parent-nodes
- It is risky to skip certain sibling nodes: unlike with Entities, the aggregation of child accounts into parent accounts is done within the OLAP layer of CXO. If you deliberately skip siblings then the numbers on higher levels may be incorrect.
In this example, the Sales account is excluded, which means that TotalRevenues doesn't have a correct value. Sometimes you can use your knowledge of the underlying system to de-select a child. For example, it could be that you know for sure that SalesReturns will never contain any amount. In that case it's permitted to skip it.
Another condition that allows for skipping siblings is that he parent (and grandparents) is either not used or is a so-called Group Label. For example, 'Otherinfo' is a group labell:
Custom Dimensions
The same logic as the Account dimension applies to selecting members from Custom dimensions:
In this case, we selected. Closingbalance and the entire tree below it.
We deliberately skipped the [None] member in the root because we know that it is also present in the ClosingBalance tree.
Running extractions
After specifying the Base Load Set, including the selection of the dimension members, the extraction can be started:
The trigger for an extraction will be picked up by the CXO Agent Service. To monitor the progress, you can (repeatedly) press the button 'Show Progress':
After finishing the extraction you can press the button 'Process OLAP' to feed the extracted data into the OLAP cube.
Incremental Extractions
Running a full Base extraction is only required after an update of the HFM meta-data, when the history of Actuals is restated or if a new Scenario / Year / Period combination is added.
During the closing cycle it is sufficient to run incremental extractions: only one or a few combinations of Scenario / Year / Period will be extracted and replaced in the over-all facts.
You can add an Incremental Load Set by pressing the '+' button and then Edit the name of the Load Set. By definition, each Load Set added to the Base is considered to be an Incremental Load Set. Note that an Incremental Load Set cannot be split into Subsets anymore.
System Variables
...