Handling Adjustments to Actuals

Document created by Bob Gill on Feb 8, 2018Last modified by Bob Gill on Feb 8, 2018
Version 2Show Document
  • View in full screen mode

 

About the Challenge

The project pulls financial data from Great Plains and combines it with KPIs for dashboards and financial reporting. This particular company has had a number of changes including reorgs and complex accounting. It was hard to reconcile various other accounting systems over history. To accommodate this, we needed a transparent way for users to enter adjustments to actuals, that could be explained to auditors.

 

Solution/Project info

Our approach was to simply make a cube with the same dimensionality as our actuals and call it Actual Adjustments. The Actual Adjustments screen was only available in an administrative capsule. The data in the actual adjustments cube was added to data pulled from a source, whenever that process runs. Here are the steps in our process to load data from greatplains .

 

Three cubes with the same dimensionality:

Steps to pull data from Great Plains and add adjustments:

 

 

Takeaways/ Hints for other community Members:

 

Notes

  • First we run the data reader to pull from the source (3) and then immediately after call a procedure to add the adjustments
  • We chose to call a child procedure because there are a few steps required. We need to copy the actual and the adjustment to a third cube first. Then we can copy the result back to the Actual Balance cube.  
    •  a + b --> c
    • c --> a
  • We also call a procedure to copy the new actuals to appropriate periods of forecast versions (How to Put Actuals in Prior Forecast Periods )

 

SQL For Datasources

  • This website has a good summary of the tables in Great Plains containing GL information (Victoria Yudin | GL Tables )
  • Actual
    • For Actual, we used a view on top of GL10110
  • Budget 
    • For Budget, we used the following SQL to give us the three segments of our GL string and the budget balances.
      SELECT dbo.GL00201.YEAR1, dbo.GL00201.ACTNUMBR_1, dbo.GL00201.ACTNUMBR_3, dbo.GL00201.PERIODID, dbo.GL00201.BUDGETID, dbo.GL00201.ACTNUMBR_2, dbo.GL00201.BUDGETAMT
      FROM dbo.GL00201, dbo.GL00200
      WHERE dbo.GL00200.BUDGETID = dbo.GL00201.BUDGETID AND YEAR(dbo.GL00200.FROM_DATE) >= 2017AND dbo.GL00201.BUDGETAMT<>0

 

Pros:

  • By always taking a backup of the database before reloading from the source, we have something to rollback to, if something goes wrong
  • By clearing the cube as just the second step, we guarantee data is fresh from the source. There is no mix of old and new data.
  • By adding a cube align near the end we ensure all versions are consistent
  • By adding a refresh screen, we can put this procedure on a button on any screen and users will see numbers update right when they are available
  • By including an exit procedure step, we ensure we don't leave the procedure open at conclusion.
  • By having a text cube, we store the last time the process has run by using the @datetime substitition variable (ETL Function Reference)

 

Cons:

  • As a best practice, any change to actuals should be done in a source system, but sometimes that's not an option.
  • A journal entry module is another good way of handling this, but we decided to go with a simpler approach.

 

1 person found this helpful

Attachments

    Outcomes