Querying the transaction log

[20 Ton Squirrel]

I have a scenario where managers need to see a quick report of which analysts have entered data and when they did it. The due course is the transaction log, of course, but I want something quick and clean in a view that can be shown in something like a PAX workbook.

Is it possible to write a process that peeks at assigned areas within a cube and correlates that with the last-known transaction on that area?

For example:

• Stew handles Category A
• Luka handles Category B
• Will takes Category C
• Heimy oversees all these weirdos

Only Luka has completed their data entry so far this quarter. Heimy needs to see a report of who has completed data entry and when they did it.
It might look something like:

Code: Select all

  ┌──────────┬────────┬─────────┬────────────┐ 
  │ CATEGORY │ STATUS │ ANALYST │    DATE    │
  ├──────────┼────────┼─────────┼────────────┤  
  │ A        │   -    │ Stew    │            │
  │ B        │  DONE  │ Luka    │ 2021-08-10 │
  │ C        │   -    │ Will    │            │
  └──────────┴────────┴─────────┴────────────┘ 

It would be a simple matter to just look up if a value exists in the specified area. The trick is getting the date in there, hence my need to tap the transaction log somehow to generate this report.

Any ideas?

[gtonkin]

I had a similar requirement some years ago and built a cube to store the necessary areas being monitored into the measures.
I then used a process to read the transaction logs for a particular day, after doing a save data.
In the data section I had logic to look at the cubes changed, measures updated etc. etc. and could populate my activity cube with last update, by whom, activity etc. etc. That may answer your last "transaction" requirement.

In terms of deciding whether or not someone has completed all their tasks, you would probably need to do something different to analyse the cubes and ascertain completeness by reading values and comparing to targets/benchmarks for entry.

The above could be pulled from the cube into PAfE for review or published to TM1Web.

Not sure that you will get away from the transaction log as short of users clicking an action button to indicate an activity is complete, you have no other way of triggering a flag on data change.

HTH

[20 Ton Squirrel]

Thank ya, gtonkin, much appreciated. I was hoping there was a more direct way of getting that info from the log in memory but it seems not.

So my first step should be to create an "activity" cube with necessary dimensions (perhaps partially based on fields in transaction log)
Then the process would…

• Zero-out the activity cube
• Perform SaveDataAll on target data cube, which creates file: tm1s«yyyymmddhhmmss».log
• Create some iteration magic that finds the latest file based on that timestamp
• Ingest that file, loop through the records to find the specific areas I'm needing to target
• When specific area is found, populate value in activity cube

Evaluating if data entry is complete is simple enough just by ensuring shares add up to 100% or checking against a prior edition. In fact, that sounds brilliant. Not only will the manager have a completion report, the same report will show error checks. Hot damn, I'm on a roll.

[MariusWirtz]

Hello 20 Ton Squirrel,

there is actually a more direct way to get the info from the transaction log in memory.

You can use the TM1 REST API (or TM1py) to query the transaction log from the TM1 Server.
You can send a query to TM1 to filer the transaction log by "cube", "user", "since", and "until".

Here is a sample.
https://gist.github.com/MariusWirtz/b41 ... b40752c8d1

You could implement a chore that runs every few minutes to execute a py script that fetches the latest entries (filter with since) from the transaction log.
Once you have the retrieved entries in python, you can check if they fall into the specific areas of a cube. Then based on your findings you could update the activity cube from python. Then you are also independent of the save data.

The performance of the REST API / TM1py transaction log filtering depends on the size of the transaction log. If you have very large historic transactionlog files in the log directory this approach may be slow.

[Edward Stuart]

Data Reservation could help you but I'd be more inclined to change the business process than engineering a whole new methodology for data input.

A lot rests on how the users are inputting the data, is it directly into the cubes via Architect/ Perspectives/ PAX? This complicates things as the transaction log could identify where the users are at but it would be hard to determine if they have completed. However, if the users are being monitored then you can request the users to also update the Activity cube when they start/ finish

Are they inputting via TM1Web/ Excel templates? You can add a selection of buttons attached to processes to update the Activity cube when they move onto the data input sheet/ make any changes and subsequently complete those changes?

We have a standardised submission cube that facilitates this across a number of models, all users know it needs to be updated and it has been integrated into the TM1Web applications to make it easy to submit/ unsubmit/ submit from parent level (and close all child level) etc... any users that have not submitted get chased via email etc..

[20 Ton Squirrel]

These are both interesting suggestions, thank you Edward and Marius.

The data is rather small in size, annual time periods dating back to 2008 across a simple hierarchy for revenue and units. Definitely not on the scale of bank account or point-of-sale data.

The means of data entry is still up in the air on ALL my TM1 projects. Everyone wants change but no one wants to change. I'm leaning towards the PAW interface with either exploration views OR "application views" (whatever those are called? The former Cognos Web views but housed in PAW). There's a chance they will want PAX, which still makes me cringe a bit.

I'm fascinated with the REST API, I truly want to develop some front-end stuff using it. Someday I shall and the heavens will shake.

In the meantime, the suggestion of having a stand-alone basic "activity cube" is actually not a bad idea. The goal is to have a manager be able to see when/if an analyst has completed their work for a quarter. Why not keep it simple, go manual?