On this page you will find the specifications regarding the Model: Group Consolidation and Fact Table: CONSOLIDATION ANALYSIS.
| Attribute | Description (where applicable) |
Aggregation of measures is based on SUM if not explicitly noted.
This section describes the basic measures in the cube. These are either measures defined in the core Information Source, in the model itself or in the ETL process. The columns have the following meaning:
| Measure | Calculated Y/N | Contains Time Intelligence Y/N | Definition/Note (where applicable) | Aggregation Type | Hidden Y/N | Tabular Expression |
| Act | Actual | N | SUM('CONSOLIDATION ANALYSIS'[RES_ACT1]) | |||
| Act Curr | Actual Currency | N | SUM('CONSOLIDATION ANALYSIS'[RES_ACT_TC]) | |||
| Act GC | Actual Group currency | N | SUM('CONSOLIDATION ANALYSIS'[RES_ACT_GC]) | |||
| Act OB | Actual OB | N | IF([HasOneValuePeriod] = FALSE(), "", IF(ISBLANK([Act]) , BLANK() , CALCULATE([Act], DATESYTD('REPORTING DATE'[ID])) + CALCULATE([Act OBY], DATESYTD('REPORTING DATE'[ID])) - [Act] )) | |||
| Act OB Curr | Actual OB Currency | N | IF([HasOneValuePeriod] = FALSE(), "", IF(ISBLANK([Act Curr]) , BLANK() , CALCULATE([Act Curr], DATESYTD('REPORTING DATE'[ID])) + CALCULATE([Act OBY Curr], DATESYTD('REPORTING DATE'[ID])) - [Act Curr] )) | |||
| Act OB GC | Actual OB Group currency | N | IF([HasOneValuePeriod] = FALSE(), "", IF(ISBLANK([Act GC]) , BLANK() , CALCULATE([Act GC], DATESYTD('REPORTING DATE'[ID])) + CALCULATE([Act OBY GC], DATESYTD('REPORTING DATE'[ID])) - [Act GC] )) | |||
| Act OB RC | Actual OB Reporting currency | N | IF([HasOneValuePeriod] = FALSE(), "", IF(ISBLANK([Act RC]) , BLANK() , CALCULATE([Act RC], DATESYTD('REPORTING DATE'[ID])) + CALCULATE([Act OBY RC], DATESYTD('REPORTING DATE'[ID])) - [Act RC] )) | |||
| Act OBY | Actual OB | Y | SUM('CONSOLIDATION ANALYSIS'[RES_ACT_OB1]) | |||
| Act OBY Curr | Actual OBY Currency | Y | SUM('CONSOLIDATION ANALYSIS'[RES_ACT_OB_TC]) | |||
| Act OBY GC | Actual OB Group currency | Y | SUM('CONSOLIDATION ANALYSIS'[RES_ACT_OB_GC]) | |||
| Act OBY RC | Actual OB Reporting currency | Y | SUM('CONSOLIDATION ANALYSIS'[RES_ACT_OB_RC1])/'CURRENCY RATES'[Currency Rate] | |||
| Act Orig | Actual Orig | Y | SUM('CONSOLIDATION ANALYSIS'[ACT_ORIG]) | |||
| Act RC | Actual Reporting currency | N | SUM('CONSOLIDATION ANALYSIS'[RES_ACT_RC1])/'CURRENCY RATES'[Currency Rate] | |||
| Bud | Budget | N | SUM('CONSOLIDATION ANALYSIS'[RES_BUD1]) | |||
| Bud Curr | Budget Currency | N | SUM('CONSOLIDATION ANALYSIS'[RES_BUD_TC]) | |||
| Bud GC | Budget Group currency | N | SUM('CONSOLIDATION ANALYSIS'[RES_BUD_GC]) | |||
| Bud OB | Bugdet OB | N | IF([HasOneValuePeriod] = FALSE(), "", IF(ISBLANK([Bud]) , BLANK() , CALCULATE([Bud], DATESYTD('REPORTING DATE'[ID])) + CALCULATE([Bud OBY], DATESYTD('REPORTING DATE'[ID])) - [Bud] )) | |||
| Bud OB Curr | Budget OB Currency | N | IF([HasOneValuePeriod] = FALSE(), "", IF(ISBLANK([Bud Curr]) , BLANK() , CALCULATE([Bud Curr], DATESYTD('REPORTING DATE'[ID])) + CALCULATE([Bud OBY Curr], DATESYTD('REPORTING DATE'[ID])) - [Bud Curr] )) | |||
| Bud OB GC | Budget OB Group currency | N | IF([HasOneValuePeriod] = FALSE(), "", IF(ISBLANK([Bud GC]) , BLANK() , CALCULATE([Bud GC], DATESYTD('REPORTING DATE'[ID])) + CALCULATE([Bud OBY GC], DATESYTD('REPORTING DATE'[ID])) - [Bud GC] )) | |||
| Bud OB RC | Budget OB Reporting currency | N | IF([HasOneValuePeriod] = FALSE(), "", IF(ISBLANK([Bud RC]) , BLANK() , CALCULATE([Bud RC], DATESYTD('REPORTING DATE'[ID])) + CALCULATE([Bud OBY RC], DATESYTD('REPORTING DATE'[ID])) - [Bud RC] )) | |||
| Bud OBY | Budget OB | Y | SUM('CONSOLIDATION ANALYSIS'[RES_BUD_OB1]) | |||
| Bud OBY Curr | Budget OBY Currency | Y | SUM('CONSOLIDATION ANALYSIS'[RES_BUD_OB_TC]) | |||
| Bud OBY GC | Budget OB Group currency | Y | SUM('CONSOLIDATION ANALYSIS'[RES_BUD_OB_GC]) | |||
| Bud OBY RC | Budget OB Reporting currency | Y | SUM('CONSOLIDATION ANALYSIS'[RES_BUD_OB_RC1])/'CURRENCY RATES'[Currency Rate] | |||
| Bud Orig | Budget Orig | Y | SUM('CONSOLIDATION ANALYSIS'[BUD_ORIG]) | |||
| Bud RC | Budget Reporting currency | N | SUM('CONSOLIDATION ANALYSIS'[RES_BUD_RC1])/'CURRENCY RATES'[Currency Rate] | |||
| CFC | Current Forecast | N | SUM('CONSOLIDATION ANALYSIS'[CFC_RES1]) | |||
| CFC Curr | Current Forecast Currency | N | SUM('CONSOLIDATION ANALYSIS'[RES_CFC_TC]) | |||
| CFC GC | Current Forecast Group currency | N | SUM('CONSOLIDATION ANALYSIS'[RES_CFC_GC]) | |||
| CFC OB | Current Forecast OB | N | IF([HasOneValuePeriod] = FALSE(), "", IF(ISBLANK([CFC]) , BLANK() , CALCULATE([CFC], DATESYTD('REPORTING DATE'[ID])) + CALCULATE([CFC OBY], DATESYTD('REPORTING DATE'[ID])) - [CFC] )) | |||
| CFC OB Curr | Current Forecast OB Currency | N | IF([HasOneValuePeriod] = FALSE(), "", IF(ISBLANK([CFC Curr]) , BLANK() , CALCULATE([CFC Curr], DATESYTD('REPORTING DATE'[ID])) + CALCULATE([CFC OBY Curr], DATESYTD('REPORTING DATE'[ID])) - [CFC Curr] )) | |||
| CFC OB GC | Current Forecast OB Group currency | N | IF([HasOneValuePeriod] = FALSE(), "", IF(ISBLANK([CFC GC]) , BLANK() , CALCULATE([CFC GC], DATESYTD('REPORTING DATE'[ID])) + CALCULATE([CFC OBY GC], DATESYTD('REPORTING DATE'[ID])) - [CFC GC] )) | |||
| CFC OB RC | Current Forecast OB Reporting currency | N | IF([HasOneValuePeriod] = FALSE(), "", IF(ISBLANK([CFC RC]) , BLANK() , CALCULATE([CFC RC], DATESYTD('REPORTING DATE'[ID])) + CALCULATE([CFC OBY RC], DATESYTD('REPORTING DATE'[ID])) - [CFC RC] )) | |||
| CFC OBY | Currenct Forecast OB | Y | SUM('CONSOLIDATION ANALYSIS'[RES_CFC_OB1]) | |||
| CFC OBY Curr | Current Forecast OBY Currency | Y | SUM('CONSOLIDATION ANALYSIS'[RES_CFC_OB_TC]) | |||
| CFC OBY GC | Current Forecast OB Group currency | Y | SUM('CONSOLIDATION ANALYSIS'[RES_CFC_OB_GC]) | |||
| CFC OBY RC | Current Forecast OB Reporting currency | Y | SUM('CONSOLIDATION ANALYSIS'[RES_CFC_OB_RC1])/'CURRENCY RATES'[Currency Rate] | |||
| CFC Orig | Current Forecast Orig | Y | SUM('CONSOLIDATION ANALYSIS'[CFC_ORIG]) | |||
| CFC RC | Current Forecast Reporting currency | N | SUM('CONSOLIDATION ANALYSIS'[RES_CFC_RC1])/'CURRENCY RATES'[Currency Rate] | |||
| Count Consolidation Balance Set | Counts number of rows in fact table | N | COUNTROWS('CONSOLIDATION ANALYSIS') | |||
| HasOneValuePeriod | Flag to indicate if a single reporting period is selected | Y | HASONEVALUE('REPORTING DATE'[Reporting Period]) | |||
| NFC | Next Forecast | N | SUM('CONSOLIDATION ANALYSIS'[NFC_RES1]) | |||
| NFC Curr | Next Forecast Currency | N | SUM('CONSOLIDATION ANALYSIS'[RES_NFC_TC]) | |||
| NFC GC | Next Forecast Group currency | N | SUM('CONSOLIDATION ANALYSIS'[RES_NFC_GC]) | |||
| NFC OB | Next Forecast OB | N | IF([HasOneValuePeriod] = FALSE(), "", IF(ISBLANK([NFC]) , BLANK() , CALCULATE([NFC], DATESYTD('REPORTING DATE'[ID])) + CALCULATE([NFC OBY], DATESYTD('REPORTING DATE'[ID])) - [NFC] )) | |||
| NFC OB Curr | Next Forecast OB Currency | N | IF([HasOneValuePeriod] = FALSE(), "", IF(ISBLANK([NFC Curr]) , BLANK() , CALCULATE([NFC Curr], DATESYTD('REPORTING DATE'[ID])) + CALCULATE([NFC OBY Curr], DATESYTD('REPORTING DATE'[ID])) - [NFC Curr] )) | |||
| NFC OB GC | Next Forecast OB Group currency | N | IF([HasOneValuePeriod] = FALSE(), "", IF(ISBLANK([NFC GC]) , BLANK() , CALCULATE([NFC GC], DATESYTD('REPORTING DATE'[ID])) + CALCULATE([NFC OBY GC], DATESYTD('REPORTING DATE'[ID])) - [NFC GC] )) | |||
| NFC OB RC | Next Forecast OB Reporting currency | N | IF([HasOneValuePeriod] = FALSE(), "", IF(ISBLANK([NFC RC]) , BLANK() , CALCULATE([NFC RC], DATESYTD('REPORTING DATE'[ID])) + CALCULATE([NFC OBY RC], DATESYTD('REPORTING DATE'[ID])) - [NFC RC] )) | |||
| NFC OBY | Next Forecast OB | Y | SUM('CONSOLIDATION ANALYSIS'[RES_NFC_OB1]) | |||
| NFC OBY Curr | Next Forecast OBY Currency | Y | SUM('CONSOLIDATION ANALYSIS'[RES_NFC_OB_TC]) | |||
| NFC OBY GC | Next Forecast OBGroup currency | Y | SUM('CONSOLIDATION ANALYSIS'[RES_NFC_OB_GC]) | |||
| NFC OBY RC | Next Forecast OB Reporting currency | Y | SUM('CONSOLIDATION ANALYSIS'[RES_NFC_OB_RC1])/'CURRENCY RATES'[Currency Rate] | |||
| NFC Orig | Next Forecast Orig | Y | SUM('CONSOLIDATION ANALYSIS'[NFC_ORIG]) | |||
| NFC RC | Next Forecast Reporting currency | N | sum('CONSOLIDATION ANALYSIS'[RES_NFC_RC1])/'CURRENCY RATES'[Currency Rate] | |||
| X1 | Extra 1 | N | SUM('CONSOLIDATION ANALYSIS'[X1_RES1]) | |||
| X1 Curr | Extra 1 Currency | N | SUM('CONSOLIDATION ANALYSIS'[RES_X1_TC]) | |||
| X1 GC | Extra 1 Group currency | N | SUM('CONSOLIDATION ANALYSIS'[RES_X1_GC]) | |||
| X1 OB | Extra 1 OB | N | IF([HasOneValuePeriod] = FALSE(), "", IF(ISBLANK([X1]) , BLANK() , CALCULATE([X1], DATESYTD('REPORTING DATE'[ID])) + CALCULATE([X1 OBY], DATESYTD('REPORTING DATE'[ID])) - [X1] )) | |||
| X1 OB Curr | Extra 1 OB Currency | N | IF([HasOneValuePeriod] = FALSE(), "", IF(ISBLANK([X1 Curr]) , BLANK() , CALCULATE([X1 Curr], DATESYTD('REPORTING DATE'[ID])) + CALCULATE([X1 OBY Curr], DATESYTD('REPORTING DATE'[ID])) - [X1 Curr] )) | |||
| X1 OB GC | Extra 1 OB Group currency | N | IF([HasOneValuePeriod] = FALSE(), "", IF(ISBLANK([X1 GC]) , BLANK() , CALCULATE([X1 GC], DATESYTD('REPORTING DATE'[ID])) + CALCULATE([X1 OBY GC], DATESYTD('REPORTING DATE'[ID])) - [X1 GC] )) | |||
| X1 OB RC | Extra 1 OB Reporting currency | N | IF([HasOneValuePeriod] = FALSE(), "", IF(ISBLANK([X1 RC]) , BLANK() , CALCULATE([X1 RC], DATESYTD('REPORTING DATE'[ID])) + CALCULATE([X1 OBY RC], DATESYTD('REPORTING DATE'[ID])) - [X1 RC] )) | |||
| X1 OBY | Extra 1 OBY | Y | SUM('CONSOLIDATION ANALYSIS'[RES_X1_OB1]) | |||
| X1 OBY Curr | Extra 1 OBY Currency | Y | SUM('CONSOLIDATION ANALYSIS'[RES_X1_OB_TC]) | |||
| X1 OBY GC | Extra 1 OBY Group currency | Y | SUM('CONSOLIDATION ANALYSIS'[RES_X1_OB_GC]) | |||
| X1 OBY RC | Extra 1 OBY Reporting currency | Y | SUM('CONSOLIDATION ANALYSIS'[RES_X1_OB_RC1])/'CURRENCY RATES'[Currency Rate] | |||
| X1 Orig | Extra 1 Orig | Y | SUM('CONSOLIDATION ANALYSIS'[X1_ORIG]) | |||
| X1 RC | Extra 1 Reporting currency | N | SUM('CONSOLIDATION ANALYSIS'[RES_X1_RC1])/'CURRENCY RATES'[Currency Rate] | |||
| X2 | Extra 2 | N | SUM('CONSOLIDATION ANALYSIS'[X2_RES1]) | |||
| X2 Curr | Extra 2 Currency | N | SUM('CONSOLIDATION ANALYSIS'[RES_X2_TC]) | |||
| X2 GC | Extra 2 Group currency | N | SUM('CONSOLIDATION ANALYSIS'[RES_X2_GC]) | |||
| X2 OB | Extra 2 OB | N | IF([HasOneValuePeriod] = FALSE(), "", IF(ISBLANK([X2]) , BLANK() , CALCULATE([X2], DATESYTD('REPORTING DATE'[ID])) + CALCULATE([X2 OBY], DATESYTD('REPORTING DATE'[ID])) - [X2] )) | |||
| X2 OB Curr | Extra 2 OB Currency | N | IF([HasOneValuePeriod] = FALSE(), "", IF(ISBLANK([X2 Curr]) , BLANK() , CALCULATE([X2 Curr], DATESYTD('REPORTING DATE'[ID])) + CALCULATE([X2 OBY Curr], DATESYTD('REPORTING DATE'[ID])) - [X2 Curr] )) | |||
| X2 OB GC | Extra 2 OB Group currency | N | IF([HasOneValuePeriod] = FALSE(), "", IF(ISBLANK([X2 GC]) , BLANK() , CALCULATE([X2 GC], DATESYTD('REPORTING DATE'[ID])) + CALCULATE([X2 OBY GC], DATESYTD('REPORTING DATE'[ID])) - [X2 GC] )) | |||
| X2 OB RC | Extra 2 OB Reporting currency | N | IF([HasOneValuePeriod] = FALSE(), "", IF(ISBLANK([X2 RC]) , BLANK() , CALCULATE([X2 RC], DATESYTD('REPORTING DATE'[ID])) + CALCULATE([X2 OBY RC], DATESYTD('REPORTING DATE'[ID])) - [X2 RC] )) | |||
| X2 OBY | Extra 2 OBY | Y | SUM('CONSOLIDATION ANALYSIS'[RES_X2_OB1]) | |||
| X2 OBY Curr | Extra 2 OBY Currency | Y | SUM('CONSOLIDATION ANALYSIS'[RES_X2_OB_TC]) | |||
| X2 OBY GC | Extra 2 OBY Group currency | Y | SUM('CONSOLIDATION ANALYSIS'[RES_X2_OB_GC]) | |||
| X2 OBY RC | Extra 2 OBY Reporting currency | Y | SUM('CONSOLIDATION ANALYSIS'[RES_X2_OB_RC1])/'CURRENCY RATES'[Currency Rate] | |||
| X2 Orig | Extra 2 Orig | Y | SUM('CONSOLIDATION ANALYSIS'[X2_ORIG]) | |||
| X2 RC | Extra 2 Reporting currency | N | SUM('CONSOLIDATION ANALYSIS'[RES_X2_RC1])/'CURRENCY RATES'[Currency Rate] | |||
| X3 | Extra 3 | N | SUM('CONSOLIDATION ANALYSIS'[X3_RES1]) | |||
| X3 Curr | Extra 3 Currency | N | SUM('CONSOLIDATION ANALYSIS'[RES_X3_TC]) | |||
| X3 GC | Extra 3 Group currency | N | SUM('CONSOLIDATION ANALYSIS'[RES_X3_GC]) | |||
| X3 OB | Extra 3 OB | N | IF([HasOneValuePeriod] = FALSE(), "", IF(ISBLANK([X3]) , BLANK() , CALCULATE([X3], DATESYTD('REPORTING DATE'[ID])) + CALCULATE([X3 OBY], DATESYTD('REPORTING DATE'[ID])) - [X3] )) | |||
| X3 OB Curr | Extra 3 OB Currency | N | IF([HasOneValuePeriod] = FALSE(), "", IF(ISBLANK([X3 Curr]) , BLANK() , CALCULATE([X3 Curr], DATESYTD('REPORTING DATE'[ID])) + CALCULATE([X3 OBY Curr], DATESYTD('REPORTING DATE'[ID])) - [X3 Curr] )) | |||
| X3 OB GC | Extra 3 OB Group currency | N | IF([HasOneValuePeriod] = FALSE(), "", IF(ISBLANK([X3 GC]) , BLANK() , CALCULATE([X3 GC], DATESYTD('REPORTING DATE'[ID])) + CALCULATE([X3 OBY GC], DATESYTD('REPORTING DATE'[ID])) - [X3 GC] )) | |||
| X3 OB RC | Extra 3 OB Reporting currency | N | IF([HasOneValuePeriod] = FALSE(), "", IF(ISBLANK([X3 RC]) , BLANK() , CALCULATE([X3 RC], DATESYTD('REPORTING DATE'[ID])) + CALCULATE([X3 OBY RC], DATESYTD('REPORTING DATE'[ID])) - [X3 RC] )) | |||
| X3 OBY | Extra 3 OBY | Y | SUM('CONSOLIDATION ANALYSIS'[RES_X3_OB1]) | |||
| X3 OBY Curr | Extra 3 OBY Currency | Y | SUM('CONSOLIDATION ANALYSIS'[RES_X3_OB_TC]) | |||
| X3 OBY GC | Extra 3 OBY Group currency | Y | SUM('CONSOLIDATION ANALYSIS'[RES_X3_OB_GC]) | |||
| X3 OBY RC | Extra 3 OBY Reporting currency | Y | SUM('CONSOLIDATION ANALYSIS'[RES_X3_OB_RC1])/'CURRENCY RATES'[Currency Rate] | |||
| X3 Orig | Extra 3 Orig | Y | SUM('CONSOLIDATION ANALYSIS'[X3_ORIG]) | |||
| X3 RC | Extra 3 Reporting currency | N | SUM('CONSOLIDATION ANALYSIS'[RES_X3_RC1])/'CURRENCY RATES'[Currency Rate] |
Time based measures are calculated in the cube with respect to other measures. The table below defines the meaning/definition of the different time measures. These measures need to be used with a specific time dimension hierarchy [REPORTING_DATE].[RepDateMFHy].
| Measure | Meaning |
| YTD |
Aggregated value for current month in current year. E.g.
for period 202010 Ytd represents sum of periods 202000-202010. |
| R12 |
For a given period, the sum all months from one year back up to the
previous month. E.g. Rolling 12 for 202010 is the sum of months 201910
- 202009 |
| PY |
Previous Year The value of the corresponding month one year back. E.g. for month 202010, the value of month 201910. |
| YTD PY |
Year to Date Previous Year Aggregated value for corresponding month one year back. E.g. for month 202010 YTD Previous Year represents sum of months 201901-201910. |
| R12 PY |
Rolling 12 (completed) Months Previous Year For a given month, first going one year back, then taking the sum of all months from one (more) year back up to the previous month. E.g. for month 202010 the sum of month 201810-201909 |
| Avg XXX Ytd | Sum XXX YTD/Count XXX YTD |
| Avg XXX R12 | Sum XXX R12/Count XXX R12 |
| XXX % YTD | Count XXX YTD/Count YYY YTD |
| XXX % R12 | Count XXX R12/Count XXX R12 |
| Model Name | Product Area |
| Group Consolidation | Financials |
The data source for this object is defined using IFS Developer Tool. The data source contains a number of transformations where the model reads data from the last step, i.e. the Data source view name (DW Source View Name).
Additional information can be found in the online documentation of Information Sources.
| Model Table | DW Source view (SQL Server) | BI Access View | Referenced Information Source | Recommended Access Type |
| CONSOLIDATION ANALYSIS | FACT_CONSOL_BAL_SET_ANALYS_TM | FACT_CONSOL_BAL_SET_ANALYS_BI | FACT_CONSOL_BAL_SET_ANALYS | Data Mart |