Is the table GeneralJournalEntry an insert-only table?

(0) Share

Report

Posted on by Community Member

Hello!

I'm currently building a cube for a customer who uses AX2012. I'm using GeneralJournalEntry and GeneralJournalAccountEntry and copy rows into a staging table to make some calculations.

I would like to put an incremental copy to minimalize load each night but I fear that updates could take place on rows of those two tables.

Is it possible in AX2012 to update rows from these tables?

Thank you very much!

*This post is locked for comments

I have the same question (0)

All responses (8)

Answers (2)

Prasad.V 185 on at

Like (0)

Report

Hello Patrick,

I would try to see if those computations can be done as computed columns of a view and add that view to the cube instead of dealing with the copies.

Was this reply helpful? Yes No
Community Member on at

Like (0)

Report

Thank you Prasad.V for your quick answer but unfortunately, it is not possible.

I just want to be sure that it is enough to "Insert-Only" in the copy without having to ensure integrity of all previously copied rows.

Was this reply helpful? Yes No
Verified answer

Brandon Wiese 17,788 on at

Like (1)

Report

I have a custom built cube for ledger, and I'm incrementally pulling fresh records every 5 minutes to keep it fairly real time.

So the answer to your original question is.. for the most part.

Records can be deleted from GJE and GJAE. One infrequent example is year-end close, where the closing process might run multiple times before being final, each deleting the records from the previous close process. Also if you're using a consolidation company I think there are some deletes that go on.

I have to wonder if journalizing after the fact might touch those records.

I have 2 cubes, one driven by DIMENSIONFOCUSBALANCE (and union with DIMENSIONFOCUSUNPROCESSEDTRANSACTIONS) which always does a PROCESS FULL on the measure group, and another driven by GJE and GJAE that always does a PROCESS UPDATE on the measure group against a query that retrieves only records with a CREATEDDATETIME after the last update. While both can provide amount data, only GJE/GJAE can provide posting type, voucher, etc., as all of that is summarized out of DFB. I then have a spreadsheet with a custom MDX that subtracts the two measure groups and shows me differences between them, which should always be NULL on every dimension I select, assuming both measure groups update at the same time.

I will tell you that if you're serious about doing an incremental load of GJE/GJAE, you will have to solve some problems you may not have considered yet. The first, obviously, is how to identify new and only new records. You can't rely on RecId (especially if you have an AOS cluster), and CreatedTransactionId and CreatedDateTime seem simple enough but suffer from some interesting problems. The most interesting of these problems is how do deal with short and long running transactions. I can explain in more detail if you ask for more.

Was this reply helpful? Yes No
Brandon Wiese 17,788 on at

Like (1)

Report

Note that the Management Reporter data store also suffers from the same problem, i.e. year-end closing deleting records, and must be rebuilt accordingly.

I'd be interested in hearing how Management Reporter pulls new and only new records (CreatedDateTime? CreatedTransactionId?). You can see from many posts on these forums that it gets out of whack from time to time and must be rebuilt, and not because of year-end closing either. I suspect Microsoft never solved the problem of short and long running transactions for Management Reported, so in larger environments it can get thrown off pretty frequently.

Was this reply helpful? Yes No
Community Member on at

Like (0)

Report

Thanks Brandon for that complete explanation! I did not know those details. As for the RecID, you mentioned trouble using them because of AOS cluster. Well I think (I have to verify) that this situation does not apply to me. So maybe it's possible for me to use it?

Was this reply helpful? Yes No
Suggested answer

Brandon Wiese 17,788 on at

Like (0)

Report

As a rule you cannot count on RecId being a good indicator of the order of operations. In an AOS cluster this is particularly true because each AOS will grab a block of 256 RecId's from the systemsequences table upon inserting the first record after a restart, and then consume the rest of the block before grabbing another. That means that RecId's will be out of sequence when compared to CreatedDateTime.

However, even with a single AOS, you still cannot count on RecId's. Here's an example.

User A STARTS posting a long running transaction, say a sales order packing slip with lots of lines, and starts transaction A, and records are inserted into GJE/GJAE.

Then User B STARTS posting a shorter running transaction, say a simple ledger journal, and starts transaction B, and records are inserted into GJE/GJAE, presumably with a newer RecId than transaction A used since they occurred afterward. Then transaction B completes and commits the TTS.

User A's transaction is still ongoing, and so if you happen to process update the measure group at this point, you will not see any of the uncommitted records for transaction A.

Now here's the rub. Later when you do another process update to pull new and only new records, if you use transaction B's RecId, which is greater than transaction A's RecId (presumably now a committed transaction), you won't pull transaction A's records at all. They will be entirely skipped. And notice how that occurs not just with RecId but also with CreatedDateTime and CreatedTransactionId. In our environment this problem threw off my cube basically every day and took a while to solve. My solution was to NOT pull any records that were created AFTER the start of any open transaction that is writing data, which is tricky.

Good luck.

Was this reply helpful? Yes No
Verified answer

Community Member on at

Like (1)

Report

Concerning the issue with RecIds possibly being out of order, I have a solution that I use for my personal queries, and it is very fast.

I have a script that creates a global temp table named ##LedgerDimensions, where the information is pivoted: each LedgerDimension (DimensionAttributeValueCombination record) has only a single row in the global temp table, with the 7 financial dimensions we use as separate columns. I run that script once in the morning, and then throughout the rest of the day all the queries I run that involve lookups of, and filtering on, ledger dimensions are nearly instant; all the hard work has already been done upfront.

However, I also have to update the table before running a query because new ledger dimensions can be added throughout the day; and that requires finding only new records. What I do is get the top 20,000 (I could set it to any number) LedgerDimension values (DimensionAttributeValueCombination RecIds) in my global temp table, in descending order; then among those find the min value; then select from DimensionAttributeValueCombination all RecIds that are greater than that minimum value but are not in the list of top 20,000 LedgerDimensions.

The thing is, even with 7 million rows in DimensionAttributeValueCombination, the entire 'find new rows' part completed in less than 1 second when there were 1,000 new ledger dimensions found.

DECLARE @TopX INT = 20000

DECLARE @MinTopXRecId BIGINT

IF OBJECT_ID('tempdb..#LedgerDimensionsTopX') IS NOT NULL

DROP TABLE #LedgerDimensionsTopX

SELECT TOP (@TopX) LedgerDimension

INTO #LedgerDimensionsTopX

FROM ##LedgerDimensions

ORDER BY LedgerDimension DESC

SELECT @MinTopXRecId = MIN(LedgerDimension)

FROM #LedgerDimensionsTopX

IF OBJECT_ID('tempdb..#RecIdsToProcess') IS NOT NULL

DROP TABLE #RecIdsToProcess

SELECT DAVC.RecId

INTO #RecIdsToProcess

FROM DimensionAttributeValueCombination DAVC

WHERE

DAVC.RecId > @MinTopXRecId

AND

NOT EXISTS (SELECT 'x'

FROM #LedgerDimensionsTopX LDTX

WHERE LDTX.LedgerDimension = DAVC.RecId)

The script then processes only the RecIds in [tag:RecIdsToProcessI], in order to update the global ##LedgerDimensions table.

Was this reply helpful? Yes No
Kim Orloff Christensen 5 on at

Like (0)

Report

I am having same problem - It takes to long time to load all XXX millions rows from GeneralJournalAccountEntry.

So i have opted to only load the last 3 month of data into a delta table and then merge the data over to my staging table. I am using SSIS to this.

My source SQL is

SELECT gjae.*

FROM [dbo].[GENERALJOURNALACCOUNTENTRY] gjae

INNER JOIN [dbo].[GENERALJOURNALENTRY] gje ON gjae.[GENERALJOURNALENTRY] = gje.[RECID]

WHERE gje.[CREATEDDATETIME] >= DATEADD(MONTH,-2,GETDATE())

And i use

DELETE FROM [dbo].[GENERALJOURNALACCOUNTENTRY]

WHERE EXISTS

( SELECT [RECID]

FROM [delta].[GENERALJOURNALACCOUNTENTRY]

WHERE [dbo].[GENERALJOURNALACCOUNTENTRY].[RECID] = [delta].[GENERALJOURNALACCOUNTENTRY].[RECID]);

INSERT INTO [dbo].[GENERALJOURNALACCOUNTENTRY]

SELECT *

FROM [delta].[GENERALJOURNALACCOUNTENTRY];

To update the staging table.

Was this reply helpful? Yes No