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;
ENDFIX
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.
RealTriGeek 