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.