Amount is GP Financial Cube for Investments Accounts

David,

Thanks for reply, that's true that MS Excel doesn't show empty values by default, and when I check the "Show Items with no data on rows/columns" that shows me accounts with no values. But these account have values in GL30000 with  SOURCDOC = 'BBF'. And I want that values even if the don't have any GL Entries.

Analysis Cubes calculates BBF entries independently of GP.  It does not bring BBF entries over from GP into the data warehouse.  Instead, it creates records where the BalanceType field in the GLTransactions table of the data warehouse equals 'Opening Balance'.  These records are calculated by summing the available transactions records that have already been loaded into the data warehouse.

Therefore, in order for BBF entries to be calculated properly in the data warehouse, you must make sure you are bringing in ALL GL transactions.  During the install, you had the option to select a cutoff date for the Financials module.  Did you accept the default?  If not, and you specified a different cutoff date, then it's possible BBF entries are not being calculated because it is not finding transaction records for prior years to use in the calculation.

David

David,

You are again very right about it now I have some accounts that don't GL Entries associated with it's like investment accounts. I am sure this will help you out in understanding my problem more. Thanks for everything. 

Lets say I pick those records and insert them into Warehouse as 'GL Transaction', will I be able to get records in the cube?

Thanks Again,

Attiqe

Were these BBF records added to GP manually (i.e. through some method other than standard closing processes) or through regular GP closing processes?

Before you attempt to manually insert those records into the data warehouse, you should try to reload the GLTransactions table in its entirety to see if it picks up those records.  You can do so by running something like the following query against the data warehouse:

---------

truncate table GLTransactions

update LastUpdated

set DateUpdated = '1900-01-01', [LastRow] = 0, [TempLastRow] = 0

where TableName in ('GLTransactionsOpen','GLTransactionsHistory')

---------

If you have a test data warehouse, you should test this query there.  Additionally, this will completely remove the contents of the GLTransactions table, so you may wish to have a restorable back-up before running this query.  Once you've executed it, re-run the SQL Agent job to reload the tables and repopulate the cubes.

David

Daivd,

I followed your instruction word to word and results are same. Now what would you suggest?

Thanks,

Attiqe

Attiqe,

Do you have transaction records for that account in the GL30000 table?  Or, just the BBF entries?

Also, if you query the GLAccountMaster table for that CompanyID/AccountIndex combination, what is the "AccountPostingType" value?  Is it defined as a Balance Sheet account?

David

David,

It's just BBF entries. Here is the screenshot for GLAccountMaster:

Thanks,

Attiqe

Then, yes, I believe that is your issue.  Because GP has no underlying transactions to support or justify these BBF entries, no transactions are loaded to the data warehouse for this account.  That, in turn, means no Opening Balance records are created in the data warehouse.  You will need to either correct this in GP or make a modification to the data load to allow it to bring in BBF entries of this nature.

David

I am told that the accounts are created "Regular for the most part some are with a journal entry". What would you recommend how to change this into GP?

Thanks,

Attiqe