...

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

In the application_variables table of the cxo_hfmexpressstaging_... database a number of settings is stored that determine some aspects of the process of data-extraction and dimension build-up. Some values in this table can be changed by IT and / or the Dba

...

Image Removed

Before changing anything in this table:

1. Never change any cell of the av_name column
2. Do not change any of the grey-marked cells at all
3. Strictly follow the description below

...

1 = True

0 = False

...

To run an incremental extraction, select the Incremental Load Set and press 'Extract'.

Real-time synchronization

An Incremental Load Set can also be synchronized with a fixed interval. First Enable the Synchronization by checking the box:
Image Added

Then set the required synchronization interval. Press Save to save the settings.

To start the Real Time Synchronization press the 'Synchronize' button. The system will now check each time-interval if something has changed within HFM (given the dimension context of the Load Set) and automatically retrieve the updated data. The cube (a special incremental partition) will be processed automatically.

System Variables

In table application_variables table of the cxo_hfmexpressstaging_... database a number of settings is stored that determine some aspects of the process of data-extraction and dimension build-up.

Some values in this table can be changed by IT and / or the Dba

Before changing anything in this table:

1. It is advised to only change an application_variable if there is a very good reason for that
2. Never change any cell of the av_name column
3. Strictly follow the description below
4. Don't touch the application_variable fields that are not listed below (ask the CXO team for their meaning).

As mentioned above, Custom dimensions (A01, A02, ... in CXO Cockpit) can be generated in two ways.

WYSIWYG mode: WYSIWYG stands for What You See Is What You Get. It means that the A0# dimension will exactly follow the expand and collapse actions in the Dimension Member selection screen. In other words, if the selection and expansion is as follows:
Image Removed  then the resulting CXO dimension looks like: Image Removed
Tree Selection mode (if in this mode, you will see the selected members in red): if you select a member, you automatically select all the descendants, unless you explicitly deselect some of them. In this mode the same selection and resulting CXO dimension is:
Image Removed  Image Removed

Name	Description and options	Possible values
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: Image Removed  When set to 1, the original hierarchy will be retained: Image Removed 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 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: Image Removed 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	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

[Contribution Total]

-- when StoreParentEntity = 0 and each entity appears only once → [Contribution Total] just acts as any other Value (GAP)

...

Cube Calculations

The following Cube-calculations are required for the proper calculation of YTD and Periodic views.

These Cube-calculations should replace the existing definitions of YTD and Periodic. For new applications YTD and Periodic can be defined as embedded Cube-calculations in the Cube. For existing applications this depends:

1. If YTD / Periodic are defined as Cube-calculations in the Repository (i.e. maintained via the Designer) then you are obliged to do this again;
2. Otherwise, replace the exisiting definition of YTD / Periodic within the cube.

YTD

case when [ACC].[ACC].CurrentMember.Properties('Rate')
then 1
else -1
end
*
CASE WHEN
[ACC].[ACC].CurrentMember.Properties('Sec Class')
THEN
[VIW].[VIW].[YTD_Input]
else
case 
when [ACC].[ACC].CurrentMember.Properties('Is ICP')
then SUM([ACC].[ACC].CurrentMember.Children,[VIW].[VIW].[YTD_vsFlow])
else SUM([ACC].[ACC].CurrentMember.Children,[VIW].[VIW].[YTD_vsBalance])
end
end

Periodic

CASE WHEN
IsEmpty (LookupCube("XchgRate","(" + MemberToStr([CAT].[CAT].CurrentMember) + ", " + MemberToStr([YER].[YER].CurrentMember) + ", " + MemberToStr([PER].[PER].CurrentMember) + ")"))
THEN
NULL
ELSE
CASE WHEN [ACC].[ACC].CurrentMember.Properties('Is ICP')
THEN 
[VIW].[VIW].[YTD]-([VIW].[VIW].[YTD], [PER].[PER].PrevMember)
ELSE 
[VIW].[VIW].[YTD]
END
END

Note that the condition 'IsEmpty...' can also be replaced by the condition that is currently in use to prevent Periodic calculations in periods beyond the last period of a category

The following 'helper' cube-calcs must be defined as embedded cube-calcs

YTD_vsFlow

CASE WHEN 
[ACC].[ACC].CurrentMember.Properties('Is ICP')
THEN [VIW].[VIW].[YTD_Input]
ELSE
-[VIW].[VIW].[YTD_Input]
END

YTD_vsBalance

CASE WHEN 
[ACC].[ACC].CurrentMember.Properties('Is ICP')
THEN -[VIW].[VIW].[YTD_Input]
ELSE
[VIW].[VIW].[YTD_Input]
END