SQL Server Performance Forum – Threads Archive

Is it possible toget a usage log of my cubes

Hi, I have managed to setup some cubes on the server, have roles defined, and users are using these and the results are mathcing… Is there a way that I can get some sort of user based usage log of the cubes andget details of when and what type of data did any user see/make use of? I’ll appreciateany help in this regard.

---

Yes, there is an Access database set up by default in AS which does precisely this. I can’t remember the exact location offhand, but will check it when I get to work and report back to you. There was a thread here about this topic too posted not long ago at all. Tom Pullen
DBA, Oxfam GB

---

It’s here http://www.sql-server-performance.com/forum/topic.asp?TOPIC_ID=5496 and the log will be found here:- Microsoft Analysis Servicesinmsmdqlog.mdb Tom Pullen
DBA, Oxfam GB

---

Thanks Tom. Will check out if the MDB contains the info. that I am looking for. Regards.

---

Hi Tom, I got the MDB file but when I opened it,there was no rows (data)in it???? Maybe this MDB file is not being populated??? Any clues how I can get it populated so that I can get the results (i.e. usage log) of my cubes by different users. Many TIA.

---

check the permissions on it as per the thread i linked previously…
Tom Pullen
DBA, Oxfam GB

---

Before you can create a Usage Log you need to set certian properties in the AS. Open up the server properties and click the logging tab there you will find what you are looking for. You must restart OlAP Services so that it can take effect and also all client cache must be wiped out for the queries to be logged. Look up Usage based optimization in BOL for details. Raulie
Hewlett-Packard Company

---

Hi Tom and Raulie,<br /><br />Thanks (again) for your help and replies <img src=’/community/emoticons/emotion-1.gif’ alt=’‘ /><br /><br />I was thinking of wheteher I need to give FULL right on the ‘Microsoft Analysis Servicesin’ to ALL users (onmy server) regardless of whether theyare member of the’OLAP Administrator Group" or not. I was confused (and still am) regardingwhat should bedonebut thenI came across Raulie’s reply. I enabled the logging on my test machine and after restarting OLAP Service, noticed that the mdb file is now getting populated. <br /><br />Question #1: So isit correct to assume that ifI do the same onmy actual server, the MDB file (on the server) will also start getting populated?<br /><br />Now my 2nd and more important question:<br /><br />I looked at the MDB file and it has 10 fields… I looked at the contents of this table and except for a few columns (fields) they are anything but easy to understand…. Any clues regarding how I can translate into more understandable and easy information regarding my requirement.<br /><br />Will appreciate your reply.<br /><br />

---

Question 1: Yes.. if all permissions are set correctly and the option checked as detailed by Raulie. Question 2: I have no idea, check out the Operations Guide [linked by Satya at the top of this forum].. There is a little bit in it about the query log but not much factual detail about what columns mean. generally speaking, the standard documentation for AS is very weak, which is unlike Microsoft. here’s hoping they get their act together in SQL 2005 AS.
Tom Pullen
DBA, Oxfam GB

---

Hi All,<br /><br />I had it working and had to re-start our server and try a few things by changing the date to thenext day…. everyting else (for which we hadto re-starttheserver and changethe date to the next day went fine)but surprisingly the MDB file holding the logs appears to have lost all the records for the past day??? This is confusing and dissapointing becuase I had the solution finally (with your help) and though the columns were difficult to understand and really use but at least I was getting an approximate idea regarding usage per user but now I am back to square one.<br /><br />Will this happen everytime the date changes i.e. when the next day actually starts. Though my conclusion does not sound logical but I am lost <img src=’/community/emoticons/emotion-6.gif’ alt=’‘ /><br /><br />Please help.

---

I do not know why that is happening. I checked the log on my live AS server and it has entries from since the server was originally built, and of course it’s been re-booted many times since then. Maybe changing the date has screwed things up somehow? Tom Pullen
DBA, Oxfam GB

---

Hi, Maybe something that youhave mentioned in your quoted reply above is what’s causing the problem??? i.e. "…and also all client cache must be wiped out for the queries to be logged." Can you please help me figure out from where and how can I "wipe out" the client cache? I mean do i have to open Analysis Services and then select something and then do a right click andthen… Ihope youunderstand my question. Will appreciate your reply. Many thanks.

---

My reply only means that the queries will not be logged if they are cached, but it doesnt mean that the log will be erased. Raulie
Hewlett-Packard Company

---

Hi Rauile,<br /><br />That’swhat’s really the confusion (and my question is) i.e. What do you mean by:<br />a) Client cache<br />b) How can I wipe out the client cache<br /><br />Maybe the change of data messed up and "erased" all the logs but the past day there was no such thing… Could see the logs being populated (via the Usage Analysis Wizard), then processed my cubes and the next thing I know is that again the logs appear to have been washed <img src=’/community/emoticons/emotion-6.gif’ alt=’‘ /><br /><br />Any clues what’s happening.<br /><br />I have tried to open the MDB file in which the logs are saved but there was nothingin it after Ihad processedthe cube whereas there were many records in it before that…. <br /><br />Hope my question is clearer now.

---

Hi,
Anyone got any clues as towhy my logs get deleted/washed from the MDB file after I have done a full process of my dimensions and cubes??? Thanks.

---

]]>