BPC Drillthrough for CMS Actuals

Actuals Activity

Current year Actuals data is pulled from 1 table:

GTRAN – G/L Transactions

For previous year Actuals, data is pulled from:

GTLYN – Last Year G/L Transactions

Opening Balances

Retrieved from GLMT

FinanceDrillThrough Table

For Activity and Opening Balances, data is written to the FinanceDrillThrough table which looks like this:

Column_name Type Length Nullable
Entity nvarchar 64 no
Account numeric 9 no
Period numeric 5 no
JournalType nvarchar 2 yes
TranDate datetime 8 yes
TranSource nvarchar 4 no
Amount numeric 9 no
RefNum nvarchar 20 yes
RefDesc nvarchar 60 no
RevEntry nvarchar 2 yes
Century numeric 5 yes
Year numeric 5 yes
PostDate datetime 8 yes
Batch numeric 5 yes
MinorSalesCode nvarchar 6 yes
MajorSalesCode nvarchar 6 yes


The GetFinanceDrillThrough stored procedure calls functions that explode rollups into base level members.

Pulling Data From CMS into BPC

Several Files (tables) in CMS are used when pulling data.

MAST – G/L Chart of Account Header
GLMT – G/L Chart of Account Totals for Year

Most of the data comes from GLMT. That is, activity and opening balance comes from GLMT. Properties like account type and company are retrieved from MAST.

Retrieving data from any system requires the same thing. Pulling Company(Entity), Account, Account Type (P&L, BS), Opening Balance and Activity. In some cases, Company is broken up as well such as Payroll and Operations. In those cases that breakdown must be retrieved as well.

BPC Security in a Nutshell

Security is broken down to 4 aspects:

  1. Users
    1. Basic component of security. User, Email, IM, etc.
  2. Teams
    1. Basic grouping of users
    2. You can assign Task and Member Access Profiles to Teams
  3. Task Profiles
    1. Application related security
    2. User/Team Activities and roles specified here
    3. There is a finite number of Tasks configured in BPC. See Task Profile Descriptions in the BPC Administration Help
  4. Member Access Profiles
    1. Dimension and member related security
    2. Example, you can specify which periods are ready to receive forecasts

Security Assignment:
          Task Profiles

          Member Access Profiles

Changing Account Rollups in BPC

To change the rollup for an account in BPC do the following:

  1. Change PARENTH1 on the account to the new rollup
  2. Process the dimension (Do a Full Process)
  3. Make the Application available
    1. Click on the Noble node
    2. Click on Set application set status
    3. Select Available
    4. Click Update application set status
  4. Refresh dimension members on the client.
    1. eTools -> Refresh Dimension Members

This will probably work for any dimension, not just accounts.

Find Which Table(s) Contain a Column and the INFORMATION_SCHEMA Namespace.

To find which table(s) contain the PersonID column try this:


In the AdventureWorks DB this is what you see:

So of course you can search for any column, not just PersonID.

Another interesting tidbit is that any ANSI compliant DBMS provides the INFORMATION_SCHEMA namespaces. From this namespace you have access to the metadata on any DB object. You can look up information on Stored Procedures and Functions using INFORMATION_SCHEMA.ROUTINES or use any of the many different objects within this namespace: COLUMNS, ROUTINES, CHECK_CONSTRAINTS, PARAMETERS, TABLES, etc.


Modifying Forecast Input Templates


1. eTools -> Open Dynamic Templates
2. Input Schedules -> 09 Input Templates
3. Open ‘Forecast Input Template 2009.xlt’


1. Change Cell A4 and BA6 to the new FCST Month


1. If we are modifying to prepare for October Month End, then we will copy Cells Q15:Q233 to R15:R233
2. Update Cells R12 and R13 to say ACT and OCT in Black Font


1. If we are modifying to prepare for October Month End, then we will copy Cells BI15:BI233 to BJ15:BJ233

Opening a Period for Forecasts

. Run the BPC Admin Tool
. Click Manage Security
. Expand Member Access Profiles
. Select Category Upload Switch
. Click Modify member access profile
. Click on BPC Access tab
. Find the Member row (i.e. AprFcst) and change its Access to Read & Write
. Click Next
. Click Apply