BSO FIX Statement to ASO CrossJoin Statement…with SubVars!

I got asked by a coworker today about writing an MDX statement to clear data from an ASO cube and I thought I would post the migrated BSO FIX code to help others that might be stuck:

BSO FIX Statement example:

FIX(AcctA, AcctB, AcctC, AcctD, USD, &CY, &CM)
     CLEARDATA Actual;

ASO MDX Equivalent:

ALTER DATABASE ‘TestASO’.’DB_ASO’ CLEAR DATA IN REGION ‘CrossJoin( CrossJoin( CrossJoin( CrossJoin({[Actual]},{[AcctA],[AcctB],[AcctC],[AcctD]}), {[USD]}),{[&CY]}),{[&CM]})’ physical; 
That’s it!
I should mention here why “physical” is used at the end of the statement.  There are 2 different kinds of data clears from an ASO database: physical and logical.  A physical clear does just that…it actually removes the data.  A logical clear essentially takes the partial clear region and multiplies it by -1 so the data will be zero in total, but the database is actually bigger!!!  Unless you have a good reason to increase the size of your database with a data clear, go for the physical route and save yourself some headache (and server space) in the end.

Leave a Reply

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

You are commenting using your 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 )

Connecting to %s