Transcript
OBIEE – BI Apps – Finance Analytics – Group Account Number Configuration
admin
Categories: Other If you are configuring Financial Analytics (OBIEE BI Apps), it is critical that the GL account numbers are mapped to the group account numbers (or domain values) because the metrics in the GL reporting layer use these values. For a list of domain values for GL account numbers, see Oracle Business Analytics Warehouse Data Model Reference (available from metalink only)
You can categorize your Oracle General Ledger accounts into specific group account numbers. The group account number is used during data extraction as well as front-end reporting. The GROUP_ACCT_NUM field in the GL Account dimension table W_GL_ACCOUNT_D denotes the nature the nature of the General Ledger accounts (for example, cash account, payroll account). Refer to the GROUP_ACCOUNT_NUM column in the file_group_acct_names.csv file for values you can use. For a list of the Group Account Number domain values, see Oracle Business Analytics Warehouse Data Model Reference. The mappings to General Ledger Accounts Numbers are important for both Profitability analysis and General Ledger analysis (for example, Balance Sheets). The logic for assigning the accounts is located in the file_group_acct_codes_ora.csv file. The table below shows an example configuration of the file_group_acct_codes_ora.csv file. Basically we specified the Financial Statement Item configuration through a CSV (comma separated value) file. This is an example of the Financial Statement Item configuration file. The Financial statement item configuration is part of the configuration steps required for the Finance module. It needs to be constructed by the user before the ETL program starts running. In this CSV file, the user specify the GL accounts, and the nature (which we call Financial Statement Item) of the GL accounts. The nature is indicated by the values in Financial Statement Item column. If consecutive GL accounts have the same nature, you can specify them in ranges as shown above. There are 6 possible domain values for the Financial Statement Item: they are AP, AR, Revenue, TAX, COGS, and Others. The 6 possible values corresponds to our 6 base fact tables: IA_AP_XACTS, IA_AR_XACTS, IA_GL_REVENUE, IA_TAX_XACTS, IA_GL_COGS, and IA_GL_OTHERS. The set of books is an accounting entity. It may be an Oracle specific term. A company can use one single set of books or multiple set of books to keep track of its accounting. When defining a set of books in Oracle, the user specified the chart of account to be used to organize its GL accounts, and a common currency to keep all the transaction amount in. For instance, Siebel US may use a set of book called ‘US Set of Books’ to keep track of its accounting entries, Siebel Europe may use a different set of book to keep track of its accounting entries. The set of books ID is basically the numeric ID of that set of books in the OLTP system. In the above example, accounts 1000 to 1100 for set of books 100 are assigned to AP. Accounts 1200 to 1300 are assigned to AR. A GL account can be assigned to only one Financial statement item. We have another configuration file similar to the Financial Statement Item Configuration file. It is called the Group Account Number configuration file. It allows the user to configure the GL accounts at a more detail level than Financial Statement Item.
This is an example of the Group Account Number Configuration file. This configuration is mainly used during the PLP process when we want to aggregate records from the Base fact tables to the Base Aggregate tables. The base fact tables stores records at GL account level whereas the base aggregate tables stores summarized records at Group Account Number level. The group account number is also used in the Siebel Analytics RPD to d efine metrics definition. For instance, I can have a metric called ‘Sales and Marketing Cost’. The underlying definition of that metric would be, the total amount of all transactions charging to any accounts with Group Account Number ‘SM COST’. In this case all transactions charging to accounts between 4000 to 4100.