I need to come up with financial-validation rules which can ensure data integrity when we import data from our accounting software to our BI software.
all ideas appreciated.
what's an example of a financial validation rule? What BI software are you using?
Ensure all accounts are mapped to your BI application database, and mapped only once, something the receiving database can review. Employ comparison totals [before/after] on a test or comprehensive basis. Usually it is the transferee database that has to be validated, after which the BI application performs its functions.
This can be a complex issue. If you are mapping one G/L system with a single chart of accounts for reporting, you can run some validation reports making sure record counts and dollars tie. This is fairly straight forward.
If you are mapping multiple G/L systems with multiple charts of accounts, you need to make sure each account from each COA is mapped to the master COA 100%. I say 100% because often this mapping requires two accounts to map to one master COA account or one account to split to two or more master COA accounts. If each account being mapped is mapped 100%, you've got it. But still run the validation reports to make sure totals tie.
In a bi project data mapping is one of the most time consuming tasks and should never be under estimated
Everyone THINKS its easy but there is no guarantee that this is the case. It all depends on how many chart of accounts, if the numbering system used is contiguous, how the data is to be analysted in the bi system (you did not say what you are using) if th go has an concept of analysis codes or if this is part of the CoA.
Plan about 5 days per connection plus time to check the mapping. You will then need to create validation rules in the etl tool or bi system. So add twice the number of days consulting for the customer checking. This may sound a lot but when you have done this a few times it's amazing what issues you can hit.