SAP Analytics Cloud - hiding invalid data combination

[OlekHyzorek]

About

This blog post is mainly focused on workaround (as far as I know the only one at the moment) to the main problem we encounter in our latest SAC implementation – invalid data combination. We found out that defined validation rules do not restrict displayed combination of dimension automatically. Of course, you can edit plan for valid data sets only, but you still see all invalid combinations of dimensions. This blog will show you how to restrict displayed dimensions on which you like to plan data only for combinations compatible to defined validation rules.

 

Concept

Idea behind it is simple, we just have to create validation rules for our data model, and data action to insert value "0" in every row. Then when we will publish the version, we previously run the data action on, and all the invalid input will be rejected because of validation rules which will allow us to publish data only on valid data combination.

 

Data Model

For this example I have created simple data model with 3 dimensions and measure that represents store count in each city.

• StoreID

Screenshot: StoreID dimension

 

• Country

Screenshot: Country dimension

 

• City

Screenshot: City dimension

 

This is how the whole data model looks like.

Screenshot: Data Model

 

Story preview without any enhancements

I have created a story, selected option to show unbooked data so we could see all data combinations and as you can see it created cartesian product. We want to be able to enter data only on valid data combinations. For that we will have to create validation rules in our data model.

Screenshot: Table results without data validation

 

Creation of validation rules

SAP delivers option called validation rules which are used to specify valid data combinations. To use them at first, we have to enable them in our data model.

Screenshot: Enable validation rules

 

Under the Workspace section if we click on the dropdown, we will be able to see new tab - "Validation rules".

Screenshot: Validation rules segment

 

I have created simple validation rule using option "Create With Existing Attributes" and matched dimensions from both Country and City. This is the reason I previously created two additional attributes in my StoreID dimension - so it could be later connected via validation rules.

Screenshot: Sample validation rule

 

This is how the table looks like after I have defined validation rule and excluded "Unassigned". As you can see, I can only enter values on dimension combination I defined, but I see all possible combinations of dimensions (not only valid).

Screenshot: Table after validation rules

 

If you want to highlight editable cells like in example above u can do it in "Styling" section of the table.

Screenshot: Highlighting editable cells

 

Creation of Data Action

Currently SAP does not support option like "Hide invalid data combinations" so we have to create a little workaround to hide those invalid ones.

In this case we will use data actions to enter zero in every single row even in those invalid ones.

We have to create Data action specifically for this model and add „Advanced formula step".

Code inside this Data action is really simple but, if necessary, it can be enhanced for example to validate only specific row via use of membersets or specific date via parameters. It is quite useful if we plan to add new dimension members.

DATA() = 1*0

 

Starting Data Action:

Before we start our DA lets add it to our story. We can do it in "Insert section" click the + icon and simply choose "Planning Trigger" and then "Data Action Trigger" and choose your previously created Data Action.

Screenshot: Planning Trigger

 

Screenshot: Data Action Trigger

 

After adding our DA to story lets add new version of any category which will be used as target version of our Data Action. If you prepared all the steps above, you could run the Data Action and use your latest created version as your target one.

Screenshot: Table after Data Action

 

As you can see zero were inputted in every single row - that is exactly what we wanted to accomplish. Now all it is left to do is to publish the version.

Screenshot: Publishing Data

 

This is the warning we will be promoted because Validation rules which we created earlier are rejecting invalid data input.

Screenshot: Final table view

 

This is what we are left after doing all the steps above. If you do not want to display "Plan1" version, you can simply hide it by pressing on the version and selecting "Exclude" option.

Screenshot: Exclude version

 

If you want to hide your technical version, it is essential to do it like in example above via "Exclude" option. You cannot use designer in this case to just filter it by version because you would not see unbooked values for "Actual" version.

 

Limitations

This solution is not perfect as it also has some limitations.

 

• Currently it is not possible to automate it using only Data Action nor Multi Action - we have to publish it manually with standard version publication button. If we tried to do it with DA or created button in analytical application specifically for version publication, we would receive error.

 

• Data Actions steps have limits to how many rows it can be applied on and after specific number of rows it will stop working. In case we have model with many dimensions and dimension's members it will be necessary to split data scope across a few Data Action steps.