Information About Decreasing the [URI] Dimension (310976)


The information in this article applies to:

  • Microsoft Commerce Server 2000
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:
  1. 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).
  2. In Analysis Manager, remove the [URI] dimension from the [Campaign] cube in OLAP.
  3. 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
Modification Type:MajorLast Reviewed:10/17/2002
Keywords:kbbug kbCommServ2000SP2fix KB310976
©2002 Microsoft Corporation. All rights reserved.