Microsoft KB Archive/310976

= Information About Decreasing the [URI] Dimension =

Article ID: 310976

Article Last Modified on 10/17/2002

-

APPLIES TO


 * Microsoft Commerce Server 2000 Standard Edition

-



This article was previously published under Q310976



SYMPTOMS
You may encounter either of the following behaviors:
 * The online analytical processing (OLAP) server runs out of memory while you are importing Campaign data.

-or-
 * The [URI] dimension increases over time, which results in a continual increase in the amount of memory that OLAP requires.



CAUSE
The &quot;out of memory&quot; errors in the OLAP server may be caused by the inclusion of URIs that Campaign Event entries reference and this causes the [URI] dimension to increase beyond the capabilities of the OLAP server.

When dimensions are incrementally processed, new members are added to the existing dimension structure. If the underlying table is continually changing, the dimension increases with each incremental process. The [URI] dimension is based on the RequestsByURI table and the contents of the [URI] dimension change with each processing run; therefore, each incremental process can increase the size of the [URI] dimension.



RESOLUTION
To resolve this problem, obtain the latest service pack for Commerce Server 2000. For additional information, click the following article number to view the article in the Microsoft Knowledge Base:

297216 INFO: How to Obtain the Latest Commerce Server 2000 Service Pack



WORKAROUND
To work around this behavior:  Edit the csdw_MakeAggRequestsByURI stored procedure to eliminate the addition of URIs from the CampaignEvent table (to do this, refer to the &quot;More Information&quot; section in this article). In Analysis Manager, remove the [URI] dimension from the [Campaign] cube in OLAP.  In Query Manager, run the following query against the datawarehouse database to delete the Ad Placement report: DELETE report WHERE DisplayName = 'Ad Placement' 

To work around the gradual augmentation of the [URI] dimension, run the Report preparation task periodically in Full mode to purge old dimension members.



STATUS
Microsoft has confirmed that this is a problem in the Microsoft products that are listed at the beginning of this article. This problem was first corrected in Commerce Server 2000 Service Pack 2 (SP2).



MORE INFORMATION
Use the folowing sample code to create a version of the csdw_spMakeAggRequestsByUri stored procedure which loads the URIs for the 5000 most visited pages, entry pages, and exit pages but does not include the URIs referenced by CampaignEvents. IF Exists (SELECT * FROM sysobjects WHERE id = object_id(N'dbo.CSDW_MakeAggRequestsByUri'))

drop procedure dbo.CSDW_MakeAggRequestsByUri

GO

create procedure [dbo].[CSDW_MakeAggRequestsByUri]

as

declare @UriMemberCount nvarchar(32)

declare @cmdstr nvarchar(4000)

set nocount on

set @UriMemberCount = convert(nvarchar(32),5000)

exec('truncate table RequestsByUri')

create table #TopEntryPages( UriId binary(6) not null, Uri nvarchar(255) not null, VisitCount int not null )

create table #TopExitPages( UriId binary(6) not null, Uri nvarchar(255) not null, VisitCount int not null )

declare @siteid binary(6)

declare @sitename varchar ( 255)

DECLARE SiteCursor CURSOR LOCAL FOR

select siteid,sitename from site where siteid > 0x0000000003E7

OPEN SiteCursor

--

-- For each site, Get the top N Uri by hits

--

FETCH NEXT FROM SiteCursor INTO @siteid,@sitename

WHILE @@FETCH_STATUS = 0

BEGIN

select @cmdstr = 'insert into RequestsByUri ([UriId], [HitsCount],[Uri])

select top ' + @UriMemberCount + ' a.uriid,

sum(a.HitsCount) as HitsCount,b.[Uri]

from HitsInfo a inner join uri b on a.uriid = b.uriid

inner join site c on a.siteid = c.siteid

where c.sitename =  + @sitename + ' +

' group by a.uriid,b.uri

order by HitsCount desc, uri desc'

exec( @cmdstr )

select @cmdstr ='insert into #TopEntryPages( Uriid, VisitCount, Uri )

select top ' + @UriMemberCount + ' b.uriid, sum(a.VisitCount), b.Uri

from firsturibydate a inner join uri b on a.uriid=b.uriid

inner join site c on a.siteid = c.siteid

where c.sitename =  + @sitename + ' +

' group by b.uriid, b.uri

order by sum(a.VisitCount) desc, uri desc'

exec( @cmdstr )

select @cmdstr ='insert into #TopExitPages( Uriid, VisitCount, Uri )

select top ' + @UriMemberCount + ' b.uriid, sum(a.VisitCount), b.Uri

from lasturibydate a inner join uri b on a.uriid=b.uriid

inner join site c on a.siteid = c.siteid

where c.sitename =  + @sitename + ' +

' group by b.uriid, b.uri

order by sum(a.VisitCount) desc, uri desc'

exec( @cmdstr )

FETCH NEXT FROM SiteCursor

INTO @siteid,@sitename

END

CLOSE SiteCursor

DEALLOCATE SiteCursor

-- include top N most visited entry pages

delete from #TopEntryPages where uriid in (select uriid from RequestsByUri)

insert into RequestsByUri ([Uriid], [HitsCount],[Uri])

select uriid, 0 as HitsCount,Uri

from #TopEntryPages

-- include top N most visited exit pages

delete from #TopExitPages where uriid in (select uriid from RequestsByUri)

insert into RequestsByUri ([Uriid], [HitsCount],[Uri])

select uriid, 0 as HitsCount,Uri

from #TopExitPages

GO

Keywords: kbbug kbfix kbcommserv2000sp2fix KB310976

-

[mailto:TECHNET@MICROSOFT.COM Send feedback to Microsoft]

© Microsoft Corporation. All rights reserved.