Which Is Faster -- a Substitution Variable or a Data Value?

The other day on Network 54 someone asked how they could get substitution variables updated in a member formula without unloading and reloading the database. Well, it's not possible. Updates to substitution variables are immediate for many things but member formulas in an outline are not one of them. DanP chimed in with another option that I had completely forgotten about: load the substitution variable value into a database intersection. Brilliant!

Which got me to thinking, which performs better in an ASO member formula -- a sub var or a data value?

I've Been Here Before

I've worked for several financial institutions over the years and they often have rigid SOX requirements. One common need is for the user to have easy access to some time stamp telling them how "fresh" the data is. This can be accomplished in a number of ways -- a member alias set to a time stamp, a sub var and a member formula or a data value loaded directly into the database.

The first one doesn't require any special tricks because a member alias is in plain text. The second two require setting the sub var or data value to the Epoch (number of seconds elapsed since 1/1/1970). If you can get that number into Excel, you just need to apply a date format. You can even use the Smart View HsGetValue() function to grab the member value and format it as you desire in the sheet header or footer.

The catch is that this gets trickier to do if the data is updated during daily loads. You've got to stop and start the database (or run a restructure) in order to get that sub var refreshed. That's no good if you've got a heavily used system. Users don't like to get kicked out while they're reporting. So you only need to update the data value and have the member formula reference the intersection you're loading to. I would go into more detail on this solution now but this post really isn't about showing users a time stamp (let me know if there's interest and I'll do a full write-up on the subject). Anyway...

So Which is Faster?

If I'm designing purely for performance, which do I choose? Either. I tried performing very large queries that required the formula to be queried many times and they were nearly equivalent. In MDX on the server they were exactly equivalent over 10 runs. In large pulls through Smart View they were both in the same range -- between 2.5 and 3.1 seconds over the course of 10 queries each.