...
- 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 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:
...
ICP Specifications
In this dialog you can also set two parameters related to HFM Intercompany (ICP) transactions:
...
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:
...
We also make a special Subset for the Budget scenario: two full years:
...
Other HFM dimensions
To complete a Base Load Set we must also select at least one member from each of the other HFM dimensions.
...
The selection mode is governed by a System Variable (see section section System Variables)
Tree selection mode (mode 1)
...
because it would mean that in CXO we e.g. only select NetProfit and none of the accounts determining this account. A more realistic selection (for NetProfit) would be:
resulting in:
...
General
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.
...
- 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. It is no problem in the following situations:
Custom Dimensions
...
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:
You can now Extract the Base Load Set.
...
Right-click the table and choose Edit:
Before changing anything in this table:
- Never change any cell of the av_name column
- Do not change any of the grey-marked cells at all
- Strictly follow the description below
...
| Name | Description and options | Possible values | |
|---|---|---|---|
| DeleteZeroRecords | When set to 1, all records in the HFM database with Data = 0 will be skipped (and hence becomes NULL / Empty in the CXO reports). When set to 0, also records with Data = 0 will be extracted. Only do this if there are not too many zeros in the HFM database (e.g., as a result of automatic load of data) and you really want to distinguish between 0 and NULL. | 1 = True 0 = False | |
| FilterSourceData | To get the raw data from HFM, queries are constructed that get the data and dimension information from the right tables of the HFM database. If FilterSourceData is set to 0, we only apply a simple filter for including or excluding ICP details, but no filter for the Entities. Entities are then filtered once the raw data is stored in the CXO staging database. When FilterSourceData = 1 then we explicitly filter by Entity already within the raw-data query (WHERE EntityId in ....). In general, if more than ~30% of the entities is selected for inclusion ion CXO it is better to set FilterSourceData = 0 because the huge WHERE clause will slow-down the query. If less than ~30% is to be included then set FilterSourceData = 1. | 1 = True 0 = False | |
| IncludeInputValues | When set to 0, the Value-dimension (stored in the CXO GAP dimension will be flat: When set to 1, the original hierarchy will be retained:
Only set IncludeInputValues = 1 if you need the Adjs detail | 1 = True 0 = False | |
| PreAggregateAccounts | As explained earlier, selections from the Account Dimension are usually done on a high level (the root node, or a few levels lower). All descendants of a selected node are then selected as well. A potential danger of this approach is that complete substructures might be repeated while you don't see them in your selection. For example, a node like NetProfit can have a lot of descendants and each time this node is used in other KPI's potentially all these descendants are duplicated as well, resulting in a lot of duplicate fact records in the cube: By setting PreAggregateAccounts = 1 we will pre-aggregate the 2nd occurrence of NetProfit and give it the name ...NetProfit: From a data point of view, NetProfit and ...NetProfit are 100% equal. However, drilling into the Descendants of ...NetProfit is not possible. The advantage of this approach is that it keeps the Account dimension smal(ler) and it generates less records in the cube. The disadvantage is that if you drill into e.g. OtherInfo you will never reach members like InterestInc(Exp). | 1 = True 0 = False | |
StoreParentEntity | When set to 1 the entity-names will always be stored as a combination of parent.child:
This variable must be set to 1 when the value [Contribution Total] is selected and the Entity selection contains duplicates. Note that if you change this setting, entity lists already created in CXO might get invalid. | 1 = True 0 = False | |
| UseCustomRollupForAccounts | Technical setting. Set to 0. | ||
| UseCustomRollupForCustomDimensions | Technical setting. Set to 0. | ||
| WYSIWYGModeForCustomDimensions | As mentioned above, Custom dimensions (A01, A02, ... in CXO Cockpit) can be generated in two ways.
| 0 = do not apply to any custom dimension Comma separated list of Custom dimensions you want to see in WYSIWYG mode (e.g., 1,3,4,7) | |
| WYSIWYGModeForAccountDimension | See WYSIWYGModeForCustomDimensions (and the description of the Account dimension) |
...
Technical preparations
-- create CC_PERIOD table
...