A number of our financial reports are based around cubes with multiple daily imports. Usually during a period-end close, a process may run multiple times to import the latest financial entries. Is it possible to reflect the time of import (data refresh) on the report.
An idea I’ve played with (but haven’t implemented yet) is to build an activity cube specifically to track this type of data. Write a value to a specific intersection at the end of each import. The value would have to be an encoded time stamp because only hungers can be stored as measurements, sometime like 20210408.083000 and then parse it out in a formula in the template.
If you’re only interested in the last run time, you don’t even really need a lot of dimensions. In my case I’m trying to track many activities and status of workflows, etc., so I’m thinking of making it a daily cube with an extra dimension to track user. But if you’re only looking for last run time then a monthly or quarterly or even yearly time dimension would keep the size of the cube to a minimum, using the account dimension as the type of import (assuming you have more than one import).
3 Likes
Hi Jeff, an idea similar to what @bob.smiley has mentioned above is to leverage the current time to create a time stamp.
For instance, a SQL for that would look like
select day(now()) as Day, Hour(now()) as Hour, mid(now(),15,2) as Min, now() as Now
will create the result as (below screenshot). You can then use this to re-import into an account as a time stamp.
Hope this helps,
Navin
4 Likes
Hi,
Here is something you can try.
First create a stat account called ‘Time stamp’. Set is as an input account, then in the template studio add a formula =now() to that account
Save this report as ‘Timestamp’.
Also save the dataview of this report as ‘TimeStamp’ as we will need this dataview in another report.
Now Open your main report and add the ‘TimeStamp’ dataview.
The dataview is going to bring in the time from the timestamp dataview and it will have a text which will be something like ‘44295.4752602418’ now you will use template formula to parse that data. I used
=DAY(K3) =HOUR(K3) , =MINUTE(K3), =MONTH(K3), =SECOND(K3) , =YEAR(K3).Now on a separate cell (where you actually want to see the timestamp use the Concatenate formula
Now save it.
Next, what you can do is- add a template calculation process in your existing import process and bring in your ‘Timestamp’ template in it and add an update process after the template calculation process .
So now, every time you run your import process the ‘TimeStamp’ template will do the calculation and feed the data in the cube and then on your main template you use your time formula to parse the data you wan to see.
2 Likes
@aakash.jhaveri and @navin.sadarangani, these are great helps in demonstrating how to implement this idea. Thanks so much for the extensive detail.
One thing to point out is that Navin’s approach reports on the time of the import, not necessarily the last successful export. If you are directly reading data from one and only one system (such as a GL/AP combined system), then this timestamp should be sufficient as long as you are careful not to update the timestamp if the import process fails in any way.
On the other hand, if you have multiple data sources, you may want to track timestamps by data source. The same design can apply–just be sure to track a timestamp account for each data source.
In my situation, in which I have a feed from multiple data sources at different schedules into a corporate business intelligence database which then feeds Prophix, I want to report on the success of each source system extract. So I’ll be reporting on extract successes rather than import successes. It’s still the same idea, but gathering data further up-stream.
The logistics of data movement in an enterprise environment is so important and fun to implement–like a supply-chain system for data instead of materials!
2 Likes
Thanks @bob.smiley. I’m currently working on a client that has multiple data refreshes happening thru the day, for various cubes. Hence, to track how long each import takes, and how much the data changes (say, for IS, BS, etc.) by time slot, i created another cube (like you alluded to, a daily cube) that tracks all of this stats. Automated, with no tweaks needed. And i can get some useful stat (for instance, trailing 14 days trend of total time taken during the day, movement between slots, time taken for each slot to update info, etc.). This could be another idea you could perhaps use in an enterprise instance.
a few snapshots to the metrics mentioned above:
Hope this helps spark some ideas for you.
Cheers,
Navin
3 Likes
OK! You’ve encouraged me beyond the concern that I might be overdoing it. I’ve decided to do something similar! Thanks so much.
2 Likes
Hi Bob @bob.smiley ,
To be candid, what I pasted is an anomaly, and at the risk of saying it, it’s a bit much. But if you will, this is more to analyze if there is a need to do it so often. So far, in the few weeks we’ve been doing this, I see less reason to have this as a permanent process. I would highly discourage from refreshing data so often. Once every 2-4 hrs should be the highest frequency, if they’d like to do it more often than a nightly refresh.
Have a great weekend,
Navin
2 Likes