Since the ASO (Aggregate Storage Option) cube is able to aggregate data on the fly, you may leverage the ASO cube to process the aggregation. There are a few ways to move data from BSO cube to ASO:
- Data synchronization
- Data Map
- Smart Push
- @XWRITE
- Groovy rules
Once you push data to the ASO side, and if you have cleared the data before you push the data over, you may notice that some of the data cells show the value 0 instead of #Missing. Also, when you delete a value through Smart View or data forms from the ASO side, it will turn it into a 0 instead of #Missing.
This time, we will take a look at how would we clear these 0s from the ASO cube, and how would we change these to #Missing.
1.Merge Data Option
If you are using PBCS/EPBCS application, under Create and Manage, click Rules.
Click the Database Properties button to open the Database Properties page.
Right click one of the ASO cube, and select Merge Data.
Then select All à Remove cells with zero value.
Or select Incremental à Remove cells with zero value.
2.Clear Data Option
If you have been working with Essbase, EAS before, and you are familiar with MDX, you can do a partial clear.
For partial data clear, it removes data from part of an ASO cube. There are 2 types of clears:
- Physical
- Logical
Physical data clear removes the data completely but it is slower. The logical data clear creates offsetting entries, and it will return 0s instead of #Missing. However, the logical clear is much faster.
You could do a Physical clear directly, or do a logical clear first and then merge data.
3.Schedule Jobs
We could also schedule a job to move the 0s from the ASO cue. Simply, select the Merge Data Slices Job.
Then pick the ASO cube that you would like to merge and then select Remove cells with zero value.
Once the job has finished, from the job console, you can check the job details.
Hope this post gives you some idea on clearing 0s from ASO cubes. Till next time.