This blog post had 3 different titles and ended up as a Surprise 🙂
I was working at an engagement, where I had to load data from Source to Target – we do this all the time.
What was different was how some products were graded. The data coming from source was missing some key dimensions.
Let’s say that data from source has Dimension A and Dimension B combinations. However for the calculation logic (MDX) to work it should sit at Dimension A, Dimension B, Dimension C, Dimension D and Dimension E. Now to achieve this I created a flagging member.
I can then move the data when it matches A and B from flag.
I replicated the same in ASOSamp and the above said logic can be achieved as depicted below.
Load Data
Above given is the format of the source data.
Flag
Above given is the format of the flag, you can see that it has more detail
Column G and H matches Column B and Column C of data load.
I can create a Member with MDX formula and it’ll perform the seeding for me 🙂 Ta da.
NONEMPTYTUPLE ([PCT_Flag],[Base])
([Graded_PCT],[&CurrYr],[Base],[No Sale],[Cash],[No Promotion],[Under 20,000],[No Store],[NA Age],[&CurrMth])
I don’t need to check whether Data is missing for a combination, just use the NONEMPTY clauses for Optimization. This is explained in Techref under MDX Optimization Properties.
With a big grin and boasting about writing a seeding code with a one liner, I retrieved the results.
Did it work? No it didn’t. I was expecting 10 (Row 3) to appear only once at Jan, Row 4 to appear only for Feb (Flagging member drives this)
I thought of using NONEMPTMEMBER instead of NONEMPTYTUPLE (only difference is one uses only members and other a tuple)
NONEMPTYMEMBER [PCT_Flag]
([Graded_PCT],[&CurrYr],[Base],[No Sale],[Cash],[No Promotion],[Under 20,000],[No Store],[NA Age],[&CurrClosedMth])
Results
Well that was the result I was looking for. I knew that it is a “surprise” and thought of checking with Cameron, Dan and Tim. They are/were of the opinion that they both should behave the same. I went on a wild goose chase of what was happening to NONEMPTYTUPLE.
It was behaving as if NONEMPTYTUPLE has no effect.
Is it because I’m performing a data assignment? Or Is NONEMPTYTUPLE having no effect?
Well I know that there is some effect adding NONEMPTYTUPLE (is was giving a performance boost for my procedural calcs).
To test the first question, I created a dummy member (the calc doesn’t make any sense, however it gave answers :))
NONEMPTYTUPLE([Base],[&CurrYr])
([IL],[Base],[Units])-([MT],[Base],[Units])
Results
What are those number, they don’t make any sense. Well they do what the calc did was to get this tuple ([IL],[Base],[Jan]) irrespective of which month you are in and subtract from MT.
I was under impression that NONEMPTYTUPLE is looking at the first nonmissing (Jan in my case) and then running with that value. To prove this I cleared Jan values from IL and MT – well the calc won’t even run. It posted #Missing everywhere.
So NONEMPTYTUPLE is looking at Jan’s value and using that for the calculation. (atleast in my case)
How can I make this work? I can use NONEMPTYMEMBER and it’ll work fine (we saw this early).
Or the below MDX
NONEMPTYTUPLE([Base],[&CurrYr],[IL])
([IL],[Base],[Units])-([MT],[Base],[Units])
Yes it works. Why I don’t know 🙂
I’ve tested this on 11.1.2.3.500 and 11.1.2.3.501 and it behaves the same way.
Conclusion – Do not use NONEMTPTYTUPLE for optimization (at-least for now)
Hope you enjoyed the madness 🙂
