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 "out of memory" 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 "More Information" 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
