During one of my projects I found that the SSAS query log table seemed to be periodically getting truncated. I would notice this because I would run some queries against the cube, watch them get logged into the logging table, and then a few hours later they would be gone! I could not figure this out and it kept erasing all of our historical usage data! I eventually tracked down the issue, explained in this Microsoft Knowledge Base Article. Every time we were using Synchronization to sync up our environments it was truncating the log table! Even if the Database IDs and Cube names were exactly the same. It even happened if it attempted to sync and found no differences between the two versions!
From the article, this quote explains it best:
“This problem is not fixed in SQL Server 2008 or in SQL Server 2008 R2. SQL Server 2008 truncates the OlapQueryLog table every time that synchronization runs, because synchronization replaces the destination database by using a fresh copy of the database. This affects scenarios such as synchronization, attaching an existing database, or restoring an existing database.”
Solution:
The article does offer some suggestions on how to get around this, but for us none of them were an option at the time. For this reason, we simply created a tiny SSIS package that would run and pull all the transactions out of the query log table and put them into a separate fact table. This package was set to run every time before Synchronization was kicked off. The package only takes a matter of seconds to run, and when the query log table is truncated afterwards, no harm done.
On top of this, that gave us a static table that we could use to build these dashboards!
