SAC – Data Action basic CRUD operation (+ Private Version creation)
Explored and learned basic of Data Action’s CRUD operation in a Planning project.
Would like to share a hypothetical scenario to explain the observations.
Note : I have created a “New Model” not a “Classic” model in the below example.
- Create a model with below dimensions / Measures..
- After model is saved, kindly prepare and update the dimensions with required master data.Without master data, CRUD would not work. Version would have a default value as ” Actual”.Example :
- After all required master data is maintained, we will create first Data Action to create a record in the model.
Create a new data action with “Advanced Formulas Step” along with below script and Save.
Note : Data() = 0 does not work directly so workaround is to assign Data() = 1 and then assign Data() = 0.
- Create a new story on top of “New Model” and add a data action trigger for the “Create” data action. And execute the same for the version “Actual”.
As per the script, data record is created with “0” value.
- We need to create a separate version apart from “Actual” to work with in planning projects.To do the same, right click on “Actual” cell and select “Copy Version”.Once private version is created, story output should be updated as below.Now, everyone to work on same version we need to save this as public version.Right click on “Act.test” cell and click on “Version -> Publish As”.
New version can be verified in the Version dimension of model.
Going forward all the CRUD will be performed on “Act.Test” version.
- Lets create another data action to delete all data from model for 2023 and add it into story.And execute the data action from story to delete only for version “Actual”.Note : we can execute “Delete All” again for version “Act.Test”, to all data from model.
- We created 2 data actions for “Create” and “Delete”.Our model has 5 Dimension – Version, Date(Year), Test_Blog_CC, Test_Blog_PG & Test_Blog_Type.Here Version value would be “Act.Test” and in data action there is a prompt asking the value explicitly.And in “Create” data action, we observed we have mentioned all 4 dimensions with values.
What if we remove 1 dimension from data action and execute.Modify the “Create” data action as below and observe the changes in the story output.
New “Create” data action created 4 records with all possible values for dimension Test_Blog_CC as the same dimension was not included in data action.
- Create few more records and update random values, which can be used to demonstrate copy functionality via data action.
- Create a new data action for Copy as per below
Story after “Copy” data action execution.
- What will happen if we execute the below script ?It will not copy the data, as value “CC3” would act as a filter for source (along with “ORIG”) and we don’t have data for the same.
Either we would need data for same filter value i.e “CC3” in source and target.
Or we need to overwrite the filter value explicitly in RESULTLOOKUP where it has data.
To overwrite all the values from the RESULTLOOKUP, mention the dimension along with value in DATA()