Hi everyone,

I would like to know if Analysis Server stores in any place a log of all
data access. I need to know who access to a cube, when and which data has
been accessed.

Thanks in advance.

Hi Javier,
AS can be configured to log queries to a local mdb file. Check out "Logging
Tab (Properties Dialog Box)" in BOL.

I read recently in a fantastic article
(http://www.sqlservercentral.com/colu...isservices.asp)
that you can configure this to write to SQL Server rather than an mdb file.

Regards
Jamie Thomson
http://www.conchango.com


"Javier Catala" wrote:

Thank you very much Jamie.

"Jamie Thomson" wrote:

Untill you upgrade to AS 2005 the data from these logs are reasonable
limited. Usually (by default) you only see every 10th login, and unless your
very good you can only see which user used which cube at what time and for
how long, Saying that this data becomes obscured by them accessing AS form
several sources in parrallel. The dimension information is there but
exceptionally hard to determin.

At the moment I only use the Log as a general user/usage performance
indicator and to see who was on during an "Exceprion violation".

"Javier Catala" wrote:

I just open QueryLog table, and there is a column named "Dataset". I think
that this column must have information about the data accessed by the user,
but this column has a number. Do you know what is the meaning of this number?

"HWUK" wrote:

Javier,

DataSet describes witch dimension and witch level users access your cube and
database.

if you already read my article in sqlservercentral,
(http://www.sqlservercentral.com/colu...saboutanalysis
services.asp) , you may known, how to move access table to sql server. After
that, you may want to create user defined function that resolves DataSet
column. You can also use my function.

Usage of this function should be:

select top 10 *,
olap_repository.dbo.fn_resolve_dataset ('x19-5851', MSOLAP_Database,
MSOLAP_Cube, Dataset)
as DataSet_Description
from querylog_history with (nolock)
order by starttime desc

The function code is written below:

select * ,

--ramunas balukonis, 2004.08.01

CREATE function dbo.fn_resolve_dataset (@ServerName sysname, @DatabaseName
sysname, @CubeName sysname, @DataSet varchar(255))
returns varchar(7000)
as
begin

declare @dso_server int
declare @dso_database int
declare @dso_cube int

declare @number_of_dimensions int
declare @dimension_name varchar(255)
declare @level_name varchar(255)
declare @return_value sysname
set @return_value = ''
declare @method_name sysname


declare @itmp tinyint

declare @hr int

EXEC @hr = sp_OACreate 'DSO.Server', @dso_server OUT
IF @hr <> 0 GOTO ObjectError

EXEC @hr = sp_OAMethod @dso_server, 'Connect', null, @ServerName
IF @hr <> 0 GOTO ObjectError

EXEC @hr = sp_OAMethod @dso_server, 'MDStores.Item', @dso_database out,
@DatabaseName
IF @hr <> 0 GOTO ObjectError

EXEC @hr = sp_OAMethod @dso_database, 'Cubes.Item', @dso_cube out, @CubeName
IF @hr <> 0 GOTO ObjectError

EXEC @hr = sp_OAMethod @dso_cube, 'MDStores(1).Dimensions.Count',
@number_of_dimensions out
IF @hr <> 0 GOTO ObjectError


set @itmp = 1
while @itmp <= @number_of_dimensions
begin

if substring(@DataSet, @iTmp, 1) <> '1'
begin
set @method_name = 'MDStores(1).Dimensions(' + convert(varchar, @itmp) +
').Name'
EXEC @hr = sp_OAGetProperty @dso_cube, @method_name, @dimension_name out
iF @hr <> 0 GOTO ObjectError

set @method_name = 'MDStores(1).Dimensions(' + convert(varchar, @itmp) +
').Levels(' + substring(@DataSet, @iTmp, 1) + ').Name'
EXEC @hr = sp_OAGetProperty @dso_cube, @method_name, @level_name out
iF @hr <> 0 GOTO ObjectError

set @return_value = @return_value + @dimension_name + '.' + @level_name
+ ';'


end

set @itmp = @itmp + 1
end

EXEC @hr = sp_OADestroy @dso_cube
IF @hr <> 0 GOTO ObjectError

EXEC @hr = sp_OADestroy @dso_database
IF @hr <> 0 GOTO ObjectError

EXEC @hr = sp_OAMethod @dso_server, 'CloseServer'
IF @hr <> 0 GOTO ObjectError

EXEC @hr = sp_OADestroy @dso_server
IF @hr <> 0 GOTO ObjectError


return @return_value

ObjectError:
-- BEGIN
--EXEC sp_displayoaerrorinfo @dso_database, @hr
--print 'shief, vsio propalo'
RETURN 1
-- END
end








"Javier Catala" <JavierCatala (AT) discussions (DOT) microsoft.com> wrote

I think I interpreted the dataset this way:

* The number of figures represent the number of dimensions in the cube - so
in foodmart sales there will be 12 figures
* The order of the numbers is from top two bottom in the cube editor - so in
foodmarts sales the first is Store and the last is Yearly income
* The number value represnet the level chosen where one is the most
aggregated - if you have an all member this would be one and two would be
the level below all member

--Michael Vardinghus

"Javier Catala" <JavierCatala (AT) discussions (DOT) microsoft.com> skrev i en
meddelelse news:EBBFE3E3-2CEA-405B-859D-F5B1AF0D8456 (AT) microsoft (DOT) com...

Just a general comment:
The query log is not the same as an access log.
The query log contains those *aggregates* requested by the client; it does
not contain the query itself.
For example, if the client can respond to a query by using its client cache
then no entry in the query log will be written.
If the aggregate requested can be calculated at runtime from other
aggregates without reading from disk, then what will be in the query log is
just the initial client request; not the ultimate aggregates used.
The query log was designed and implemented to be part of the "usage-based
optimization" system; not a general purpose log.
Use it for other purposes at your own risk.
--
Dave Wickert [MSFT]
dwickert (AT) online (DOT) microsoft.com
Program Manager
BI SystemsTeam
SQL BI Product Unit (Analysis Services)
--
This posting is provided "AS IS" with no warranties, and confers no rights.

"Javier Catala" <JavierCatala (AT) discussions (DOT) microsoft.com> wrote

But could you combine the query log with the analysis services performance
monitors ? I think there was
a caching object monitor with different counters but I'm not sure if it
gives me exact information on what
members and what levels are requested.

--Michael V.


"Dave Wickert [MSFT]" <dwickert (AT) online (DOT) microsoft.com> skrev i en meddelelse
news:eNNxl8%23rEHA.1272 (AT) TK2MSFTNGP09 (DOT) phx.gbl...

But there is a record in query log when answered from cache ?

Isn't there a dataset on this record ?

--Michael V.

"Ramunas Balukonis" <ramblk1 (AT) hotmail (DOT) com> skrev i en meddelelse
news:1097568724.430831 (AT) nettrafmon (DOT) ..