Data validation concepts

[fabpas]

Hi guys,

I have a set of TM1 cubes which are loaded using TI processes. We have many data sources (> 50 csv file) which are loaded using these complex TI processes.
We would like to put in place a solution to validate the data loading. The validation should consider errors in data ( empty csv files or missing lines) rather that technical failure (such as missing file or data format error). My idea was to create a new cube with a measure called "dataValidationMeasure" and a set of dimension related to our input files. And then write rules to implement logical test to validate the data.
Do anybody have some experience to share for this kind of use case?

[declanr]

If all you want to do is flag up CSV files that are empty or don't exist, you can just put statements in your TI processes with an ItemReject to put a message to the log file explaining what was wrong (or send data to a validation cube instead if that's preferred.)

[Wim Gielis]

Sure, for example, have the dimension }Processes in your validation cube (or a similar dimension that you keep in sync with the dimension }Processes).

Example code to do an ExecuteProcess statement and capture the result in your validation cube. Rules could then convert the numeric process outcome into custom messages:

Code: Select all

vResult = ExecuteProcess('xxx');
If(vResult <> ProcessExitNormal() );
  ProcessError;
EndIf;

Constants for vResult:

ProcessExitByChoreQuit(): 16
ProcessExitNormal(): 0
ProcessExitMinorError(): 6
ProcessExitByQuit(): 8
ProcessExitWithMessage(): 5
ProcessExitSeriousError(): 10
ProcessExitOnInit(): 11
ProcessExitByBreak(): 7

As said above, ItemReject, creating custom text files with AsciiOutput, a validation cube, ... all are possible.
If the validation is very important to you / your customer, take the time to set up a good and convenient system for errors / inconsistencies.
But make sure it doesn't create a lot of overhead.

[fabpas]

Yes that's a good point and I will consider it. But ultimately what the business want is a "red" light indicating whether or not the data is loaded.
So by creating a dedicated cube and proper rules, my idea was to give to the business a report (or Perspective spreadsheet) with a matrix with all sources and a indicator saying if data is available or not.

[Wim Gielis]

You can indeed fill a separate cube with a 1 on the correct intersection, if data is loaded for that intersection in a different cube.
Shouldn't be hard to do, I do not understand the difficulty actually.

[fabpas]

I agree it is not difficult. It is just an open question to sense what TM1 developers have implemented in this respect.
Thx for your help!

[Wim Gielis]

Okay, if you have follow-up questions, go ahead.

[tomok]

But ultimately what the business want is a "red" light indicating whether or not the data is loaded.

Well, this is in direct contradiction you your original post:

The validation should consider errors in data ( empty csv files or missing lines) rather that technical failure (such as missing file or data format error).

I have done this type of thing at a number of clients and it has never involved rules (if you are talking about rules in the TM1 context). You simply create a cube, at a minimum using the }Processes dimension, and then any other dimensionality you want like Time and then Measures and you populate the intersections with messages every time you run the process. You can then create an Active Form report to display those messages. You can use conditional formatting in the report to turn things different colors if you want. It's all very low tech the key is decide what you want to "validate" and how you actually go about performing the validation. A TM1 rule can't do that for you.