Data Copy in ASO cube based on UDA.

I had a requirement at a client where I had to copy data from one intersection to another based on UDA’s attached on Accounts dimension.

Following is a simple example that I worked on before real implementation on different cube.

Outline:

Below is the MDX script

This copies data from Actual-Jan-E1  to Forecast-Jan-E1 based on UDA on Account.

I have 2 Accounts Sales and A2. Only Sales has UDA ie MYUDA.

Note escape characters in MYUDA. You need this or else it will throw syntax error.

I have made use of INTERSECT here.

INTERSECT function gets me the common eg

Intersect(

{[New York], [California], [Oregon]},

{[California], [Washington], [Oregon]}

)

returns the set: {[California], [Oregon]}

In the above example, it will return only those Accounts that have UDA as “MYYDA” attached to it.

Output

Note here that only Sales Account has UDA attached. So it copied only Sales data from Actual to Forecast.