(Not) an exception to the rule
Believe it or not, these blog posts come from real world issues and resolutions, no matter how inane they seem. My issue this time was using an api.Data.Calculate (fast, easy, mostly comprehensible) and skipping a member in a User Defined dimension.
Had I known that that this was a requirement from the beginning (Who would believe that I don’t get all of the requirements the first time? Everyone, I reckon.), I’d have probably tackled the calculation differently, likely in an explicitly looped Data Buffer with an if test on member name. But, as is well known, I am just about as lazy as can be and thus I didn’t want to rewrite the code. Also that seems like an atomic bomb in search of a walnut to crack. What to do?
It occurred to me that I if my member filter was U1#Total_Products.Base, I should be able to use the .Remove function to drop U1#10_010. Maybe.
I’m Gonna Get That Member Right Out of My Hair
Of course, as always, I had no idea how to actually do that, but I did manage to remember that there was an example in the Member Filter Builder:
Hallelujah, Hooray, Huzzah, it exists. Also, what does, “perform an additional expansion on the REMAINING items” mean? Huh. If I try that example with my dimensionality…ugh:
That is most definitely not what I want, despite the fact that I’ve got the tippytoppymost Total_Products member there and what looks like a way to get to Base. The filter didn’t get rid of 10_010 and – and this is an added bonus – it looks like it duplicated No_Product. Bugger me sideways.
What’s happening? Damfino. Well, I can guess that it’s trying to remove (in the case of this dimension) 10_010 from the children of Total_Products and then return the base members of those children. I can see Whole_Bean and its children (which include 10_010). 10_010 can’t be removed from that level because it’s a grandchild of Total_Products. Double ugh. I think I even understand the doubled No_Product as that member is a child of Total_Products and then the filter gets No_Product’s level 0 members. It’s still confusing and definitely not what I want.
The place – totes obvs — to look is the Design and Reference Guide. Alas and alack, that isn’t much easier to understand. Some would say, someone like Yr. Obt. Svt., that this is even less comprehensible:
Simplify, then add lightness
But the docs do give a clue in that first sentence, “remove some of the Members from the results”. If all I really want to do is get to the base members and then remove one, maybe getting that base list and then just removing the member I don’t want is all that’s actually required. Could it be?
It could:
Just in case you can’t read it, that’s U1#Total_Products.Base.Remove(10_010). I will not dwell on how long it took me to get that result as I kept on trying to mimic the examples in the guide as well as the sample from the Member Filter Builder. It seems so obvious now; actually, it is. Duh.
And of course, it can be used with other functions:
Why oh why oh why then are the OneStream-provided examples so complex? I believe – although of course I have no real idea but as I’ve never let ignorance bother me before I’ll not do so now – that they’re trying to provide an example of how to use combined member filters. These functions are powerful and thus the examples are as well. Hopefully this post provides a simple example that can be built upon.
Coming back, however reluctantly, to my use case, if you recall this blog post on unbalanced math, you’ll see that Product 10_010 gets calculated:
Here’s the code that calculates all base level products:
If I replace U1#Total_Products.Base with U1#Total_Products.Base.Remove(10_010):
Success! Boil In Bag!
There we aren’t. 10_010 isn’t being calculated. Victory!
What have we learnt? Have we learnt anything? Maybe? Hopefully.
When I look at this, the following lessons come to mind:
- Congratulations Cameron, you remembered that this function exists. You must be a mad lad for looking at all of these functions and then never actually using them but at least you had a place to start.
- The documentation isn’t likely to match your use case.
- However, if you give a thought as to what the documentation says, maybe, just maybe it’ll hold the key to your problem.
- Fortune favors the brave, who dares wins, etc. Try it and see.
That’s quite a lot from one itsy-bitsy member function. Good. And doubleplusgood that it worked.
There’s another thing to learn from this example although it’s not explicitly called out: api.Data.Calculate is fast, fast, fast and it’s actually pretty simple, unbalanced math aside. If you can at all use it, do so. The code to do this as I originally conceived with an explicitly defined buffer and a loop thereof and an if test for the member name would work, but wow what a lot of code to type and then debug. I much prefer doing it in a single statement and you should too.
Be seeing you.
