Ways to retrieve Validation transform information for documentation

[Former Member]

Hi All,

We have a requirement which needs to document validation rule in all validation transform of data flow in a project.

Does anyone know if there is a way which we can retrieve validation rules from DI repository? E.g. a SQL statement.

I searched the DI repo tables but no luck.

Hope someone can give me some light.

Thanks,

Bobby

[Former Member]

In the Auto Documentation in the Management Console, you can browse all the objects in your repository and for the validation transform it will show the rules used in that transform. This is for browsing only though, the print feature does not print the details for any of the transforms, so also for the Validation transform the details (i.e. the validation rules) are not printed.

Unfortunately, today there is not an easy way to extract the validation rules via a simple query. It is a common request though that we will address in a future release (import/export of validation rules).

That being said, technically there are some options to get access to the rules. Not straightforward though...

The validation rules are part of the transform definition, which on its turn is part of the whole dataflow definition. So they are not stored as separate objects in a table somewhere, but embedded into the ATL language we use to describe the DI objects. What you could do is export the repository to an ATL file (or select from AL_LANG_TEXT table in the repo) and scan this file for the validation rules. Below is an example of how such a rule would look like, in this case the rule name is "MyRuleName" and the rule is : Query.JOB_KEY = 1.

As a side note, the part that would be easy to get via a SQL query are the run-time statistics of the validation transform. These statistics (number of records passed/failed for each validation rule) are stored in the repo tables al_qd_* and are also used by the Validation Dashboards in the management console. Keep in mind that you need to check the option to 'collect data validation statistics' in order to collect these details.

CALL TRANSFORM Validation ()

INPUT(Query)

OUTPUT(Validation_Pass ( JOB_NAME varchar(192),

JOB_KEY int,

JOB_RUNID varchar(384),

RUN_SEQ int,

PATH varchar(765),

OBJECT_NAME varchar(765),

OBJECT_TYPE varchar(765),

ROW_COUNT varchar(765),

START_TIME varchar(765),

END_TIME varchar(765),

EXECUTION_TIME varchar(765),

DATAFLOW_NAME varchar(765),

JOB_ID int ) ,

Validation_Fail ( JOB_NAME varchar(192),

JOB_KEY int,

JOB_RUNID varchar(384),

RUN_SEQ int,

PATH varchar(765),

OBJECT_NAME varchar(765),

OBJECT_TYPE varchar(765),

ROW_COUNT varchar(765),

START_TIME varchar(765),

END_TIME varchar(765),

EXECUTION_TIME varchar(765),

DATAFLOW_NAME varchar(765),

JOB_ID int,

DI_ERRORACTION varchar(1),

DI_ERRORCOLUMNS varchar(500) ) )

SET("validation_rules" = '<?xml version="1.0"; encoding="UTF-8"?>

<Rules collectStats="true" collectData="false" >

<Column name= "Query.JOB_KEY"; enableValidation="true" noValidationWhenNull="false" >

<RuleName> MyRuleName </RuleName>

<Description></Description>

<Expression uiSelection="1">

<UIValue1>=</UIValue1>

<UIValue2>1</UIValue2>

<Custom> Query.JOB_KEY = 1 </Custom>

</Expression>

<Action sendTo="0" substOnFail="false" substValue="" />

</Column>

</Rules>

');

[Former Member]

I found an easier method. All XML are stored inside the table "AL_LANGXMLTEXT" so you can just query directly like this:

SELECT text_value

FROM al_langxmltext

WHERE object_normname = 'DF_VALIDATION'

ORDER BY seqnum

The DF_VALIDATION is the name of my data flow.

Just make sure you concatenates all the rows to make it a complete XML. And then look for those <Rules> Tag via XML path.

Regards,

Derek