Re: Analysis Services Access Log
From: Ramunas Balukonis (ramblk1_at_hotmail.com)
Date: 10/11/04
- Next message: Pratibha Agrawal: "KPI value not shown in aspx page"
- Previous message: RaVaGe: "Analysis Services in a mixed Novell/Windows environment"
- In reply to: Javier Catala: "RE: Analysis Services Access Log"
- Next in thread: Michael Vardinghus: "Re: Analysis Services Access Log"
- Messages sorted by: [ date ] [ thread ]
Date: Mon, 11 Oct 2004 15:33:51 +0200
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/columnists/rbalukonis/analysisaboutanalysis
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@discussions.microsoft.com> wrote in message
news:EBBFE3E3-2CEA-405B-859D-F5B1AF0D8456@microsoft.com...
> 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:
>
> > 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:
> >
> > > 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.
- Next message: Pratibha Agrawal: "KPI value not shown in aspx page"
- Previous message: RaVaGe: "Analysis Services in a mixed Novell/Windows environment"
- In reply to: Javier Catala: "RE: Analysis Services Access Log"
- Next in thread: Michael Vardinghus: "Re: Analysis Services Access Log"
- Messages sorted by: [ date ] [ thread ]
Relevant Pages
|
