Data did not reflected in cube but its in Transaction log!

[krishnadixit]

Hi all,

I encountered a strange situation last fortnight. There is a input template on web where the Business Users log in & punch-in data (Price per ton). The web sheet has dbrw functions to write the data in corresponding cube.

Now, one of the users logged in & punched in some data for specific slice. (Initially, the template is already pre-filled with some data).
For example on Oct 5, he keys in 2950 where initially it was 2985, rebuilds the sheet & he gets all other further calculation on the sheet. He then logs off. Done.

A week later (Oct 12) when he extracts the report, he was astonished to see the same old numbers i.e. 2985 instead of 2950!!! So he again input 2950 in the place of 2985.

We investigated into transaction logs, shockingly there is a log that says there was a transaction where the OLD VALUE was 2985 & it was changed to NEW VALUE: 2950. We got two lines that say

Code: Select all

Oct 05: OLD VALUE=2985 : NEW VALUE=2950 : <same slice & cell of the cube>
Oct 12: OLD VALUE=2985 : NEW VALUE=2950 : <same slice & cell of the cube>

How could this happen? If transaction log says there was value change on Oct 5th, then why didn't it reflect in cube. And if by chance it was there in the cube & probably some other user changed it again to old, then why that transaction was not recorded?

This is a real strange situation. Has anybody experienced this? Any guess?

Thanks

Krishna Dixit

[qml]

If I were to guess what happened without knowing anything else about your environment I would say there is a TI process (possibly scheduled as part of a chore) that runs from time to time and loads some data, including data in the affected cell. The reason why you cannot see this in the transaction log is that the process switches off logging for that cube for the duration of the load.

[Edward Stuart]

Much less likely than the prediction above is that the server crashed and then the logs were not recovered upon restarting.

[krishnadixit]

@qml
There is no TI in chore that loads the data in that specific slice or cell. It was a good guess though.

@Edward
The server never crashed.

[qml]

Well, it still looks like something/someone switches off logging for that cube, changes some data, and then switches logging back on. Check the transaction log for any changes in the }CubeProperties cube for your cube and the LOGGING measure. Maybe you will find something there.

[Edward Stuart]

Search your data directory for "CubeSetLogChanges"

Double check your transaction log query references the correct parameters

[AmbPin]

I have seen this happen in the early stages of a development:-

We have two TM1 databases on a server, the primary database initiates a data load process which at the end reboots the machine.
In the secondary database the majority of the data updates are made by hand. We noticed that all of these updates were being lost even though they were recorded in the log, infact I used the log at one stage to re-apply the updates. The updates would only be retained by the secondary database if we performed a DataSave (something I know we should heve been doing anyway).

It appears that when the machine and databases restarted, the secondary database was not applying accumulated updates from the transaction log. I stopped this happening by scheduling a DataSave and shutdown of the secondary database before the begining of the primary database data load process.

I know I have described some obvious bad practice above (but as I said, it was in the early stages), however I still would have expected TM1 to reapply the transaction log. The only logical explanations I can think of:-

• Since the primary database closed down the machine, the transaction log of the secondary database was not closed properly or became corrupted in some way;
• TM1 had not recorded the fact that it was using the transaction log of the secondary database so that when it restarted it used a prior transaction log thus missing out all recent updates;

[hbell]

This is probably the least likely explanation of your problem, but reflects a strange experience we had. We're on 9.5.1 and find that the log files occasionally corrupt by adding a seemingly random value into the (normally blank or "empty quote") first field. When this happened (and it only occurred for some records), the log reader would not pick up the affected records - and it looked as though the data in the cube and log file were out of sync. However, if you opened the log file using a text editor the missing records were, of course, visible. So, if you're looking at the log in the log viewer, you might want to try opening it with Notepad or something similar.

[qml]

We're on 9.5.1 and find that the log files occasionally corrupt by adding a seemingly random value into the (normally blank or "empty quote") first field.

This first field was added in version 9.5 if memory serves well. I don't think it's documented, but it definitely has its use. It is populated by an alphanumeric identifier that binds batch jobs. Among other things I believe it facilitates functions like Undo/Redo.

I have seen a case where these alphanumeric identifiers got corrupted on one server and had a fragment of a seemingly random dimension element's name added at the end each time, but this didn't seem to affect anything else.

[bradohare]

I have seen this scenario, albiet very rare (twice over 1.75 years) were this occured. As you mentioned the transaction appears in the transaction log, however was not reflected in the cube viewer. Never quite got to the bottom of the issue, however a reboot "fixed" the problem when the instance re-read the transaction log while coming up. This was on 9.5.1.

[paulsimon]

Hi

When you look at the cell is it white, or grey, ie is there any chance that there is a rule applying to the cell? Does the system load in rules from a file?

Other than that, I have no other explanations, other than the ones that have already been made.

I do have one suggestion - check the TM1Server log and also the Event Viewers on the system to see if the server suffered an unexpected re-start, or whether some external issue happened such as running out of disk space, etc.

Regards

Paul Simon