Validating data load

[mnaithan]

Hello,

User has asked for some way to validate the data load in cube against the database, is there any method to achieve this, data is loaded to the Cube from odbc table with some logic in query , now when the data is loaded user wants to check on high level if numbers tie, One way I can think of is 'AsciiOutPut' and user can compare it with query result against database, I was just wondering if there is any other way this can be done may be from TI process

Thanks

Micky

[BariAbdul]

Reconciling or data validation is primary task of TM1 developers to ensure the data load validity.You could create a view of consolidated elements and check whether these totals match database totals and AsciiOutPut and send it to the users for confirmation.I don't think you could validate by TI.Thanks

[Wim Gielis]

Hello,

Yes, reconciling data loads is often much of a "manual" task.
For example, you can import a table into Excel, create a pivot table, and compare with a view in TM1.
You choose different aggregation levels and make sure your tests are representative for the full data load.
You can use snapshots out of TM1 cube views and paste them next to Excel pivot tables, making use of differences in Excel to see where the difference <> 0.
Use conditional formatting and the like.

[tomok]

Whenever I build a model that's going to require a data load reconciliation I always make sure each dimension has a tree that adds up all the leaf elements. I then create a reconciliation view that pulls these totals. Then, using the database client of choice, you write a query that pulls the same thing as your data load but just totals everything (think GROUP BY). You can then compare that to your TM1 Recon view.

[gtonkin]

Whenever I build a model that's going to require a data load reconciliation I always make sure each dimension has a tree that adds up all the leaf elements.

Just to add to this comment-Never assume that your hierarchies are complete and/or do not duplicate. I always create a system rollup that contains all N-level items that we use for reconciliation. Many times, issues in reconciliation has been down to metadata and you can waste hours before tracking it down. I would highly recommend adding this system rollup to your relevant dimensions!

[mnaithan]

Thanks for the replies, may be I"ll go through the manual approach of validating consolidated cube view against table