Cube usage by users

Hi,

What would be the correct way to generate some kind of log or report that will tell me which user used which cube in which database along with the number of times the user connected to each cube each day? The number of queries sent by each user to each cube by day would be ok. I am not interested by the query content for this exercise. I just want Userid, DB name, Cube name and Hits.

Then I can take it from there and create a report for our security/sox guy with User name, Cube name, Week of year and number of hits by week, meaning if the users send 36 queries to Cube A on Monday and 2 queries on Friday, it counts for 2 cube accesses for that week.

This assume that I use only TCP/IP access but if the same mechanism would work for HTTP access that would be fine. I try not to use HTTP but I may be forced to do it.

Regardless of network transport, the only logins allowed are though Windows Domain Global Group Membership.

Seems simple but I do not find where to start.

Thanks,

Philippe

Hello,

I have found a solution to this issue.

In the Server property box, you will enable query loging to a table and set the interval to 1 instead of 10.

Stop and Start the AS Server

Create some query or cube to access your log data, you are done.

I cannot really provide a query example since it depends on your situation. Mine is linked to a Calendar and to a Users tables. I convert the query time to keep only the day and the username to keep only the userid, then I build some Cube and/or Report that joins all that stuff and do some DistinctCount on the daily access by Cube by User and voila. I keep my infosec guy happy and I know what are the cubes I can retire for cause of no use.

You know, sometimes they ask you to build a cube real quick, they use it once and then they forget about it (and do not tell you). So I am not maintaining useless cubes :-)

Regards,

Philippe