Displaying Timestamps in Essbase

One common requirement in this age of SOX is to show users how current the data is in a cube. This is especially important when data is being updated during the business day. There are many reasons the users would want to know that data has been updated. I've used a few techniques over the years to do this.

Days Gone By

I used to like to set the timestamp in the database note which was easily accessible via the Excel Add-In. That's not an option now that Smart View has taken over the world. It also wouldn't allow you to get the timestamp printed on your report. UPDATE: The database note is available beginning in 11.1.2.2 release of Smart View.

Member Alias

This is probably the simplest method but doesn't help you if you're updating data during the day. You can simply add a member called "Timestamp" and update it's alias when you rebuild your outline. You can either do this via a load rule or using the alter database command with load alias_table options (it's not listed under the ASO alter database specification but I haven't had any issues using it with ASO).

Substitution Variable

The idea here is to set a Substitution Variable to the decimal value that Excel uses to store time. If you use the NOW() function in Excel and format it to a decimal value you'll see a number somewhere north of 42,000. As of September 24th, 2015 it is 42,272 and a remainder. This tells us that it has been 42,272 days since January 1, 1900. The decimal portion shows how far along in the day we are. For example, 42,272.5 corresponds to September 24th, 2015 at 12:00 Noon. We can create this number using the shell if we're on Linux with the following line:

echo "scale=10;(`date +%s`/86400)+25569" |bc

The code within the quotes gets piped to a program called "bc" which is a Unix calculator. The echo command prints the command to standard output. The scale=10 command sets the output to 10 decimal places. The `date +%s` gives us the number of seconds since January 1, 1970 (the Epoch). Dividing it by 86,400 (the number of seconds in a day) gives us the number of days since the Epoch. 25,569 is the number of days between January 1st, 1900 (Excel time) and January 1st, 1970 (the Epoch). All that together gives us the number we need in Excel. You can use that to send as a variable to a MaxL statement to set a substitution variable.

You can change your Timestamp member formula to reference the substitution variable (&_Timestamp in ASO for example). That way when someone wants to include the value, they add the Timestamp member to their sheet, perform a retrieve and then format the number using Excel date/time formatting as they desire. The downside here is that the timestamp generated by the formula will only be updated when the Outline is restructured or when the database is stopped and started. This is fine for databases only updated during batch. However, it will not satisfy any timestamp requirement on a database that is updated during non-batch hours (assuming you're not restructuring or unloading the database during business hours).

You still might be able to use the substitution variable on cubes updated on an intra-day basis. There is a function, HsGetVariable, in Smart View that will grab a Substitution Variable. Users can put this function on their sheet. When the sheet is refreshed, the value will be updated and they can format it as they wish.

Load a Time Value

The last way I've updated the timestamp is by loading the value obtained in the prior section directly into an intersection in the cube. You will need to load it at a specific intersection, for example: Timestamp, No_Product, No_Store, Jan, Curr_Year, etc. This will be difficult for a user to find so I'd recommend loading the data into a member, say, Timestamp_Load and then reference the intersection in a formula for the Timestamp member such that the value shows up no matter what other members are intersecting with Timestamp. Use cross-dim operator in a BSO member formula or tuple notation in an ASO member formula.

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.