Cube - General Ledger

Contents

Usage/Purpose

Main analysis scenarios are:

To see which dimensions that can be used together with each fact in, for example, Excel pivot analysis, see sections Fact and Dimension Connections below.

Overview Facts

Cube Fact (DSV name) Measure Group Referenced Information Source BI Access View Access Type
FACT_GL_BALANCE GL BALANCE FACT_GL_BALANCE FACT_GL_BALANCE_BI_OPT On Line
FACT_GL_PERIOD_BUDGET GL PERIOD BUDGET FACT_GL_PERIOD_BUDGET FACT_GL_PERIOD_BUDGET_BI_OPT On Line
FACT_GL_PROJECT_BALANCE GL PROJECT BALANCE FACT_GL_PROJECT_BALANCE FACT_GL_PROJECT_BALANCE_BI_OPT On Line
FACT_GL_TRANSACTION GL TRANSACTION FACT_GL_TRANSACTION FACT_GL_TRANSACTION_BI_OPT On Line
FACT_GL_BAL_SET_ANALYS GL BALANCE SET FACT_GL_BAL_SET_ANALYS FACT_GL_BAL_SET_ANALYS_BI_OPT On Line
FACT_PLAN_TRANS_VALID BUSINESS PLANNING FACT_PLAN_TRANS_VALID FACT_PLAN_TRANS_VALID_BI On
Line
FACT_GL_BAL_SET_CONF_ANALYS* GL ANALYSIS FACT_GL_BAL_SET_ANALYS FACT_GL_BAL_SET_ANALYS_BI_OPT On Line
FACT_REPORTING_CURRENCY_RATES CURRENCY RATES FACT_CURRENCY_RATES FACT_CURRENCY_RATES_BI_OPT On Line

* FACT_GL_BAL_SET_CONF_ANALYS is based on FACT_GL_BAL_SET_ANALYS and described in the same document.

Each measure in the facts are described in chapter Measures. Corresponding Information Sources are described below.

General Ledger Balance

General Ledger Balance Information Source is based on acconting balances in IFS/General Ledger. This Information Source can be used to design reports based on general ledger balances in accounting currency, transaction currency, and reporting currency for any accounting period, account and/or code parts. Quantity analysis based on accounts and code parts can also be performed. Detailed balance analysis can be carried out based on projects as well.

General Ledger Period Budget

General Ledger Period Budget Information Source is based period budget in IFS/General Ledger. This information source can be used to design reports based on general ledger periodic budget amounts in accounting currency and reporting currency with quantities for any budget version. Analysis can be extended to include accounts and other code parts including projects.

General Ledger Project Balance

General Ledger Project Balance information source is based on project balances in IFS/General Ledger. This Information Source can be used to design reports based on general ledger project balances in accounting currency, transaction currency, and reporting currency with or without quantity balances for any accounting period for each accounting project. Reports can be further analyzed based on accounts and other code parts.

General Ledger Transaction

General Ledger Transaction Information Source is based transactions in IFS/General Ledger. This Ínformation Source can be used to design reports based on general ledger transactions in accounting currency, transaction currency and third currency for any accounting period. These amounts can be analyzed based on accounting periods, accounts, code parts, journal numbers, voucher types and voucher numbers etc.

General Ledger Balance Set Analysis

This Information Source is based on Budget Process transactions, GL Period Budget transactions and GL Balances. GL Balance Sets are defined and configured in IFS Applications, defining the source to affect a given period in an accounting year. The ETL process must also be configured to define GL Balance Sets representing measures like Actual (ACT), Budget (Bud), Current Forecast (CFC) and Next Forecast (NFC).  All amounts are in base currency.

Business Planning Transaction - Valid Version

Business Planning Transaction - Valid Version information source is based on IFS/Business Planning. This information source can be used to design reports based on transactions in planning units which are connected to business plans. Information in the report can be modified and these modified values can be written back to IFS Applications. The business planning user settings which are defined when entering business plans and planning units will be applicable when accessing and writing back planning transactions through Business Planning Transaction - Valid Version information source.

Business Planning Transaction - Valid Version information source always retrieve balances related to planning transactions in the valid version of the planning units only. Similarly planning transactions can be modified and written back only to valid versions of the planning units via this information source.

  • Revenue/cost related planning based on accounting period, accounts, and other code parts including additional code parts.
  • Preliminary/final annual finance plan
  • Writeback report of finance plan

    • Overview Dimensions

    Cube Dimension Note
    ACC ATTR ACCOUNT Account related Accounting Attribute dimension. Requires that all companies to be analyzed have an Account related accounting attribute with an identity as defined in the Configuration tool. The identity is defined in Accounting Attr Lookup tab for Code Part Type=ACCOUNT.
    ACC ATTR CODE B Code Part B related Accounting Attribute dimension. Requires that all companies to be analyzed have a Code B related accounting attribute with an identity as defined in the Configuration tool. The identity is defined in Accounting Attr Lookup tab for Code Part Type=CODE_B.
    ACC STRUCT BS Balance Sheet Account related Accounting Structure dimension. Requires that all companies to be analyzed have an Account related structure with an identity as defined in the Configuration tool. The identity is defined in Accounting Struct Lookup tab for Code Part Type=ACCOUNT_BS.
    ACC STRUCT CODE B Code Part B related Accounting Structure dimension. Requires that all companies to be analyzed have a Code B related structure with an identity as defined in the Configuration tool. The identity is defined in Accounting Struct Lookup tab for Code Part Type=CODE_B.
    ACC STRUCT IS Income Statement Account rerlated Accounting Structure dimension. Requires that all companies to be analyzed have an Account related structure with an identity as defined in the Configuration tool. The identity is defined in Accounting Struct Lookup tab for Code Part Type=ACCOUNT_IS.
    ACCOUNT  
    ACCOUNTING CURRENCY See CURRENCY dimension
    ACCOUNTING PERIOD  
    ACCOUNTING PROJECT  
    BUSINESS PLAN  
    CODE B  
    CODE C  
    CODE D  
    CODE E  
    CODE F  
    CODE G  
    CODE H  
    CODE I  
    CODE J  
    CODE N  
    CODE O  
    CODE P  
    CODE Q  
    CODE R  
    CODE S  
    CODE T  
    COMPANY  
    COUNTERPART ONE  
    COUNTERPART TWO  
    CURRENCY  
    GL BALANCE SET* * Also requires tables DM/DW/IFSAppl_BI_GL_BAL_SET_CUBE_CONFIG to have data, related to packages *.1.115 Load ......
    GL BUDGET VERSION  
    GL TRANSACTION This is a cube-only dimension, i.e. not  supported by a standard dimension.
    PLANNING ENITY  
    PLANNING SCENARIO  
    PLANNING STRUCTURE  
    PLANNING UNIT  
    PROJECT  
    REPORTING CURRENCY This dimension as well as the fact CURRENCY_RATES are used to support calculations in reporting currency. Please lookup more information in the fact specific documentation.
    REPORTING DATE Time dimension based on the reporting date, i.e. the from date of the accounting period associated with a fact transaction
    VOUCHER DATE  
    VOUCHER TYPE  

    Configurations

    The General Ledger cube is using the Company Reporting Period dimension, which means that it is necessary to create a specific Reporting Period Definition to be used by this cube. The created/selected identity must also be defined in the lookup configuration for the ETL process, using Source = 'Financials'. It is also necessary to define a master company that represents the Accounting Period calendar to be used.

    The cube supports calculation of base currency amounts to a reporting currency. To make this work it is necessary to create/select a currency rate type in one company as the rate source. The relevant configuration lookup must also be defined.

    Please refer technical documentation for more informtation.

    Limitations

    The cube is also using Accounting Structures. The identities to use are predefined and cannot in the version be configured.

    Combinations of facts and dimensions that do not match will not give any results (See chapter Fact and Dimension Connections).

    Fact and Dimension Connections

    The following picture shows how different facts in the cube are connected to the available dimensions.


    Measures

    General

    Aggregation of measures is based on SUM if not explicitly noted.

    Balances/Amounts do not include Period 0 or periods >12. These are only visible using measure Balance Orig. It can be chosen whether to use Reverse Income Statement balances for Cost and Revenue accounts. In this case db-column Balance_RIS is used. This setting is done in SSIS Config Utility per Fact table.  This behaviour is common for all balances and amounts.

    Basic Measure

    This sections describes the basic measures in the cube. These are either measures defined in the core Information Source or in the ETL process. The columns have the following meaning:

     

    Measure Measure
    Group    		 Core Measure ETL
    Measure    		 Cube
    Measure    		 Measure Expression Reporting
    Currency
    Measure    		 Aggregated Method Note
    Balance GL BALANCE NO NO YES NO NO SUM Balance
    Deb Balance GL BALANCE YES NO NO NO NO SUM  
    Cred Balance GL BALANCE YES NO NO NO NO SUM  
    Quantity GL BALANCE YES NO NO NO NO SUM  
    Curr Balance GL BALANCE YES NO NO NO NO SUM  
    Balance Orig GL BALANCE YES NO NO NO NO SUM Balance
    Balance OBY GL BALANCE NO YES NO NO NO SUM Year Opening Balance. Only used in calculations. Hidden.
    Curr Balance OBY GL BALANCE NO YES NO NO NO SUM Year Opening Curr Balance. Only used in calculations. Hidden.
    GL Balance Count GL BALANCE NO NO YES NO NO COUNT Count of records
    Balance RC GL BALANCE NO NO YES YES YES SUM Balance in reporting currency. 
    Balance OBY RC GL BALANCE NO YES NO YES YES SUM Balance OBY  in reporting currency.  Only used in calculations. Hidden.
    Budget Amount GL PERIOD BUDGET NO NO YES NO NO SUM Budget Amount
    Budget Quantity GL PERIOD BUDGET YES NO NO NO NO SUM  
    Budget Amount OBY GL PERIOD BUDGET NO YES NO NO NO SUM Year Opening Budget Amount. Only used in calculations. Hidden.
    Budget Curr Amount GL PERIOD BUDGET YES NO NO NO NO SUM  
    Budget Curr Amount OBY GL PERIOD BUDGET NO YES NO NO NO SUM Year Opening Budget Curr Amount. Only used in calculations. Hidden.
    Budget Amount Orig GL PERIOD BUDGET NO YES NO NO NO SUM Original Budget Amount
    Budget Amount RC GL PERIOD BUDGET NO NO YES YES YES SUM Budget amount in reporting currency
    Budget Amount OBY RC GL PERIOD BUDGET NO YES NO YES YES SUM Year Opening Budget Amount in reporting currency. Only used in calculations. Hidden.
    Proj Deb Balance GL PROJECT BALANCE YES NO NO NO NO SUM  
    Proj Cred Balance GL PROJECT BALANCE YES NO NO NO NO SUM  
    Proj Balance GL PROJECT BALANCE NO NO YES NO NO SUM Project Balance
    Proj Curr Balance GL PROJECT BALANCE YES NO NO NO NO SUM  
    Proj Quantity GL PROJECT BALANCE YES NO NO NO NO SUM  
    Proj Balance RC GL PROJECT BALANCE NO NO YES YES YES SUM Project Balance in reporting currency
    Proj Balance OBY RC GL PROJECT BALANCE NO YES NO NO YES SUM Year Opening Project Balance in reporting currency. Only used in calculations. Hidden.
    Proj Balance Orig GL PROJECT BALANCE NO YES NO NO NO SUM Original Project Balance.
    Trans Deb Amount GL TRANSACTION YES NO NO NO NO SUM  
    Trans Cred Amount GL TRANSACTION YES NO NO NO NO SUM  
    Trans Amount GL TRANSACTION NO NO YES NO NO SUM Trans Amount
    Trans Curr Deb Amount GL TRANSACTION YES NO NO NO NO SUM  
    Trans Curr Cred Amount GL TRANSACTION YES NO NO NO NO SUM  
    Trans Curr Amount GL TRANSACTION NO NO YES NO NO SUM Trans Curr Amount
    Trans Quantity GL TRANSACTION YES NO NO NO NO SUM  
    Trans Amount RC GL TRANSACTION NO YES NO YES YES SUM Transaction amount in reporting currency
    Trans Amount OBY GL TRANSACTION NO YES NO NO NO SUM Year Opening Trans Amount. Only used in calculations. Hidden.
    Trans Curr Amount OBY GL TRANSACTION NO YES NO NO NO SUM Year Opening Trans Curr Amount. Only used in calculations. Hidden.
    Trans Amount OBY RC GL TRANSACTION NO YES NO NO YES SUM Transaction amount in reporting currency. Onlyn used in calculations. Hidden
    Trans Amount Orig GL TRANSACTION NO YES NO NO NO SUM Original Trans Amount.
    BS Balance GL BALANCE SET NO NO YES NO NO SUM BS Balance
    BS Balance Orig GL BALANCE SET YES NO NO NO NO SUM BS Balance Orig
    BS Balance OBY GL BALANCE SET NO YES NO NO NO SUM Year Opening BS Balance. Only used in calculations. Hidden.
    Count GL Balance Set GL BALANCE SET NO NO YES NO NO COUNT Count of records
    BS Balance RC GL BALANCE SET NO NO YES YES YES SUM BS Balance in reporting currency. 
    BS Balance OBY RC GL BALANCE SET NO YES NO YES YES SUM BS Balance OBY  in reporting currency.  Only used in calculations. Hidden.
    BS Balance TC GL BALANCE SET NO NO YES NO NO SUM BS Balance Transaction Currency
    BS Balance Orig TC GL BALANCE SET YES NO NO NO NO SUM BS Balance Orig Transaction Cirrency
    BS Balance OBY TC GL BALANCE SET NO YES NO NO NO SUM Year Opening BS Balance Transaction Currency. Only used in calculations. Hidden.
    Act (Actual) GL ANALYSIS NO NO YES NO NO SUM Act
    Act Orig GL ANALYSIS YES NO NO NO NO SUM Act Orig
    Act OBY GL ANALYSIS NO YES NO NO NO SUM Year Opening Act. Only used in calculations. Hidden.
    Act RC GL ANALYSIS NO NO YES YES YES SUM Act in reporting currency. 
    Act OBY RC GL ANALYSIS NO YES NO YES YES SUM Act OBY  in reporting currency.  Only used in calculations. Hidden.
    Act TC GL ANALYSIS NO NO YES NO NO SUM Act in Transaction Currency
    Act OBY TC GL ANALYSIS NO YES NO YES YES SUM Year Opening Act in Transaction Currency. Only used in calculations. Hidden.
    Bud (Budget) GL ANALYSIS NO NO YES NO NO SUM Bud
    Bud Orig GL ANALYSIS YES NO NO NO NO SUM Bud Orig
    Bud OBY GL ANALYSIS NO YES NO NO NO SUM Year Opening Bud. Only used in calculations. Hidden.
    Bud RC GL ANALYSIS NO NO YES YES YES SUM Bud in reporting currency. 
    Bud OBY RC GL ANALYSIS NO YES NO YES YES SUM Bud OBY  in reporting currency.  Only used in calculations. Hidden.
    Bud TC GL ANALYSIS NO NO YES NO NO SUM Bud in Transaction Currency
    Bud OBY TC GL ANALYSIS NO YES NO YES YES SUM Year Opening Bud in Transaction Currency. Only used in calculations. Hidden.
    CFC(Current Forecast) GL ANALYSIS NO NO YES NO NO SUM CFC
    CFC Orig GL ANALYSIS YES NO NO NO NO SUM CFC Orig
    CFC OBY GL ANALYSIS NO YES NO NO NO SUM Year Opening CFC. Only used in calculations. Hidden.
    CFC RC GL ANALYSIS NO NO YES YES YES SUM CFC in reporting currency. 
    CFC OBY RC GL ANALYSIS NO YES NO YES YES SUM CFC OBY  in reporting currency.  Only used in calculations. Hidden.
    CFC TC GL ANALYSIS NO NO YES NO NO SUM CFC in Transaction Currency
    CFC OBY TC GL ANALYSIS NO YES NO YES YES SUM Year Opening CFC in Transaction Currency. Only used in calculations. Hidden.
    NFC (Next Forecast) GL ANALYSIS NO NO YES NO NO SUM NFC
    NFC Orig GL ANALYSIS YES NO NO NO NO SUM NFC Orig
    NFC OBY GL ANALYSIS NO YES NO NO NO SUM Year Opening NFC. Only used in calculations. Hidden.
    NFC RC GL ANALYSIS NO NO YES YES YES SUM NFC in reporting currency. 
    NFC OBY RC GL ANALYSIS NO YES NO YES YES SUM NFC OBY  in reporting currency.  Only used in calculations. Hidden.
    CFC TC GL ANALYSIS NO NO YES NO NO SUM NFC in Transaction Currency
    CFC OBY TC GL ANALYSIS NO YES NO YES YES SUM Year Opening NFC in Transaction Currency. Only used in calculations. Hidden.
    Count GL Analysis GL ANALYSIS NO NO YES NO NO COUNT Count of records
    Plan Amount TC BUSINESS PLANNING NO YES NO NO NO SUM Planning Amount in Transaction Currency
    Plan Amount OBY TC BUSINESS PLANNING NO YES NO NO NO SUM Opening Year Balance Planning Amount in Transaction Currency. Only used in calculations. Hidden
    Plan Amount OB TC BUSINESS PLANNING NO NO YES NO NO SUM Opening  Balance Planning Amount in Transaction Currency.
    Plan Amount Avg TC BUSINESS PLANNING NO NO YES NO NO AVG Average Planning Amount in Transaction Currency. Calculated as Planning Amount / Number of periods.
    Plan Amount PlanRC BUSINESS PLANNING NO YES NO NO NO SUM Planning Amount in Planned Reporting Currency
    Plan Amount OBY PlanRC BUSINESS PLANNING NO YES NO NO NO SUM Opening Year Balance Planning Amount in Planned Reporting Currency. Only used in calculations. Hidden
    Plan Amount AC BUSINESS PLANNING NO YES NO NO NO SUM Planning Amount in Accounting Currency
    Plan Amount OBY AC BUSINESS PLANNING NO YES NO NO NO SUM Opening Year Balance Planning Amount in Accounting Currency. Only used in calculations. Hidden
    Plan Amount OB AC BUSINESS PLANNING NO NO YES NO NO SUM Opening  Balance Planning Amount in Accounting Currency.
    Plan Amount Avg AC BUSINESS PLANNING NO NO YES NO NO AVG Average Planning Amount in Accounting Currency. Calculated as Planning Amount / Number of periods.
    Plan Amount RC BUSINESS PLANNING NO YES NO YES YES SUM Planning Amount in Reporting Currency
    Plan Amount OBY RC BUSINESS PLANNING NO YES NO YES YES SUM Opening Year Balance Planning Amount in Reporting Currency. Only used in calculations. Hidden
    Plan Amount OB RC BUSINESS PLANNING NO NO YES NO YES SUM Opening  Balance Planning Amount in Reporting Currency.
    Plan Amount Avg RC BUSINESS PLANNING NO NO YES NO YES AVG Average Planning Amount in Reporting Currency. Calculated as Planning Amount / Number of periods.
    Count Busienss Planning BUSINESS
    PLANNING    		 NO NO YES NO NO COUNT Count of records
    Currency Rate CURRENCY RATES YES YES NO NO NO SUM Currency rate used to calculate amounts to reporting currency.
    The core measure is modified in ETL

    Calculated Measures

    Calculated measures are in the cube calculated with respect to other measures. The table below defines the general meaning/definition of each calculated measure.

    Measure Meaning
    Diff <amount> - Budget <amount> <amount> Balance - Budget <amount>
    % Diff of Budget (<amount>) (<amount> Balance - Budget <amount>)/abs(Budget <amount>)

    Time Based Measures

    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 defined in the table below.

    Measure Meaning
    YTD Aggregated value for current period in current accounting year. E.g. for period 201210 YTD represents sum of periods 201201-201210.
    Based on hierarchies [REPORTING DATE].[RepAccPerFHy] or [RepAccPerSHy].
    R12 - Rolling 12 For a given period, the sum all periods from one year back up to the current period. E.g. Rolling 12 for 201210 is the sum of periods 201111 - 201210
    Based on hierarchies [REPORTING DATE].[RepAccPerFHy] or [RepAccPerSHy].
    PY - Previous Year The value of the corresponding period one year back. E.g. for period 201210, the value of period 201110.
    Based on hierarchies [REPORTING DATE].[RepAccPerFHy] or [RepAccPerSHy].
    YTD PY - YTD Previous Year Aggregated value for corresponding period one year back. E.g. for period 201210 YTD Previous Year represents sum of periods 201101-201110.
    Based on hierarchies [REPORTING DATE].[RepAccPerFHy] or [RepAccPerSHy].
    R12 PY - Rolling 12 Previous Year For a given period, first going one year back, then taking the sum all periods from one (more) year back up to the previous period. E.g. for period 201210 the sum of period 201011-201110
    Based on hierarchies [REPORTING DATE].[RepAccPerFHy] or [RepAccPerSHy].

    Included Example Reports and Dashboards

    This section lists dashboards and reports in the example content for the BI Analysis Package that is using this cube:

    UI Name Type
    Income Statement Excel Dashboard
    Actuals vs Budget by Accounting Period and Account Report

    Required Components

    The BI specific and also related core components listed in this section are required, i.e. they must along with other dependent components, be part of the customer installation to successfully use this cube.

    NOTE that only directly used components are listed. Each such component can have its own list of dependent components. Please refer to the Component Deployment Profile File for each component to get the full picture.

    Component Type Component Name Product
    APTOOL BI Common scripts and utilities, BI Anapac Business Intelligence
    APPSDI BI Application Services Analysis Package Dimension Business Intelligence
    ENTEDI BI Enterprise Analysis Package Dimensions Business Intelligence
    ACCRDI BI Accounting Rules Analysis Package Dimensions Business Intelligence
    ACCRFT BI Accounting Rules Analysis Package Facts Business Intelligence
    GENLDI BI General Ledger Analysis Package Dimensions Business Intelligence
    GENLFT BI General Ledger Analysis Package Facts Business Intelligence
    GLPNLC BI General Ledger PNL Cube Business Intelligence
    BUDPDI BI Budget Process Dimensions Business Intelligence
    BUDPFT BI Budget Process Facts Business Intelligence
    BUSPDI BI Business Planning Dimensions Business Intelligence
    BUSPFT BI Business Planning Facts Business Intelligence
    ENTERP CORE Enterprise Financials
    BUDPRO EXTENSION Budget Process Financials
    ACCRUL CORE Accounting Rules Financials
    GENLED CORE General Ledger Financials

    Note: BI Component used will be GLPNLC