Validating View Data

Data must be complete and consistent if it is to provide accurate analytics. This new validation framework provides initial validation rules to check the consistency of your view key columns and hierarchy nodes.

This topic contains the following sections:

Run a Data Validation

The Data Validation Status button in the view properties panel shows the status of the last data validation, and the Validated On field shows the date and time of the last run.

To validate data (or review the results of a previous validation):

  1. Click the Data Validation Status button to open the Data Validation panel.

    Alternatively, if the Data Preview panel is already open, click the Data Validation tab.

  2. If the panel is empty (or if you want to re-run the validation), click Validate.

    If the view has one or more remote sources, you can run validations if this is supported by the connection adapter. In this case, a warning is displayed and you can choose to continue with the validation, cancel, or open the View Analyzer to determine whether one or more sources should be replicated (see Exploring Views with View Analyzer).

    The time required for data validation depends on the number of records and the complexity of calculations required. If validation is taking too long, you can click Cancel Validation in the panel toolbar. You will receive a notification when validation is complete.

  3. Review the validation results.

    There is one result for each rule and it shows the following information:

    Column

    Description

    Category

    Displays the name of the validation rule category.

    Rule

    Displays the name of the validation rule.

    Object

    Displays the name of the object being validated.

    Message

    Displays the number of records that are not consistent with the rule. Click Details to see further information.

  4. For rules with errors, click the Details link to get further information.

    The Details dialog explains the rule and lets you copy SQL (or SQLScript) code to generate a list of invalid records.

  5. In the Details dialog, click Copy SQL (or, in some cases, Copy SQLScript) to get the code necessary to generate the full list of records that are not consistent with the rule.

  6. Create a new SQL view, select the appropriate language, paste the code into it, and preview the data to see these failing records (see Creating an SQL View).

Key Validation Rules

The following rules are available in the Keys category:

Rule

Description

Unique Key Values

Each record must have unique primary key values.

For example, if an object has primary key columns ID and Language and a record has primary key values of 1 and EN, then no other record may have this combination of values.

No Null Key Values

No record may have null primary key values.

For example, if an object has primary key columns ID and Language no record may have null values in these columns.

Referential Integrity

No fact record may contain a value in a foreign key column if the value is not present in the mapped key column of the associated dimension.

For example, if the Sales fact has a foreign key column Product ID with an association to a Products dimension, then all the values in that column must be found in the primary key column of the dimension.

Unique Target Values

No dimension, text entity, or hierarchy, which is the target of an association from the view may contain records that do not provide unique values for identifying records.

For example, if an object has an association to a "Products" dimension, then the target columns mapped in the "Products" dimension must contain values uniquely identifying each record (even if these columns are not marked as keys).
For information about working with keys, see Set Key Columns to Uniquely Identify Records.

Hierarchy Validation Rules

The following rules are available in the Hierarchies category:

Rule

Description

No Multiple Parents

No hierarchy node may have multiple parents.

For example, if a hierarchy node A has a parent node B, then it may not have any other parent.

No Circular Hierarchies

No hierarchy node may have circular parent-child relationships.

For example, if a hierarchy node A has a parent node B, then it may not also have node B as a direct child or other descendant.

Missing Hierarchy Nodes

Available only in views with a Semantic Usage of Fact that have an association to a dimension that is, in turn, associated to a Hierarchy with Directory.

Each hierarchy defined in the hierarchy with directory must provide nodes for all appropriate foreign key values used in records in the fact.

For example, if a fact contains records with the DE country code but that code is not present in a hierarchy defined in an associated hierarchy with directory, then any data allocated to DE will not appear in aggregations that are viewed using this hierarchy.

This rule can generate files to collect missing nodes and allocate them to an "Unassigned" root node (see Collect Unassigned Fact Records for Inclusion in Aggregations and Visualizations).

Misplaced Hierarchy Data Nodes

Non-data node types must not have values in any data node type columns.

For example, if a hierarchy contains a non-data node type "PRODUCT_CATEGORY" and a data node type "PRODUCT", and a "PRODUCT_CATEGORY" node contains values in the "PRODUCT_ID" column (which contains product identifiers), then this "PRODUCT_ID" data will be associated incorrectly with the "PRODUCT_CATEGORY" node type, leading to inaccurate results.

To resolve this issue, ensure that the data node type columns contain values only when the node is a data node. In all other cases, these columns should contain only NULL values.
For information about working with hierarchies, see:

Data Type Validation Rules

The following rules are available in the Data Types category:

Rule

Description

Consistent Data Types

All columns must have appropriate data types.

For example, if a column A is specified as having a data type Date, and SAP HANA Cloud determines that the data type must be String(10), then this inconsistency may cause run-time errors or incorrect data to be returned.
For information about working with data types, see Column Data Types.