Clearing a Portion of Data from an ASO Cube

Today, I had an FDM developer ask if I could automate clearing data from a specific portion of an ASO cube.  After a few minutes of researching, I found a (very easy) solution!

To clear it manually, you right-click on the database name and choose “Clear”->”Partial Data”.

A screen will show asking for the MDX of what to clear.  I wanted to clear a month’s worth of data for a specific entity, and here is the MDX I used:

{([Mar],[2013],[Actual],[EntityName])}

Easy enough!

To try and use that in a MaxL script, you will have to decide if you want to physically clear the database or logically clear the database.  What is the difference?  Physically clearing the database actually clears the input cells for your MDX range of data.  Logically clearing the database will create another set of data that is the opposite of the data being cleared, so it looks like zero.  A big note on logically clearing the database is that your database will grow because of the offset.

Here is the MaxL for a physically cleared database:

ALTER DATABASE ‘Sample’.’Basic’ CLEAR DATA IN REGION ‘{([Mar],[2013],[Actual],[EntityName])}’ PHYSICAL;

Now you can clear, say, current month data via MaxL.

4 comments

  1. How about clearing data for a particular period , say Jun to August of 2013 . I did try {([Jun:Aug],[2013],[Actual],[EntityName])} did not work though.

  2. Thank you for the post on clearing data from ASO. I would like to provide a bit of extra color commentary on this topic. When you utilize the EAS menu option: Clear Data -> Partial the result is a logical clear. When you execute MDX the in the fashion you suggested, it performs a physical clear. There is a subtle but important difference between the two. A logical clear results in zero. A physical clear results in #Missing. And as a side note, if you submit #Missing from Smart View or a Planning interface, the result is a logical clear.

    Hope this is useful! Please keep up the good work!

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s