Alan Faulkner's Forefront Security Blog

Why is my Hits Count the same for all pages in Sql Server 2005?

So you are seeing that your Hits Count on your Top Requested Pages report indicating that all pages have the same exact hit count?  We have identified that this a problem which is traced to the Page Usage cube definition in Sql Server 2005.  This does NOT affect Sql Server 2000 cube definitions. 

Here is how to fix this issue:

• Open Sql Server Management Studio
• Connect to the Analysis Server that contains the Commerce Server Analysis database.
• In the Page Usage Cube - And use the Create To Scripting option to output the cube create script to a file (or output window).
• Add the below snippet to the cube definition script:
• The code addition begins with <Dimension xsi:type="RegularMeasureGroupDimension">

<Cube xmlns:xsd="http://www.w3.org/2001/XMLSchema" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">
<ID>Page Usage</ID>

....

<MeasureGroups>
<MeasureGroup>
...
<Dimensions>
...
<Dimension xsi:type="RegularMeasureGroupDimension">
<CubeDimensionID>Uri</CubeDimensionID>


<Attributes>
       <Attribute>
               <AttributeID>Uri attribute</AttributeID>
               <KeyColumns>
                     <KeyColumn>
                           <DataType>BigInt</DataType>
                           <Source xsi:type="ColumnBinding">
                                  <TableID>HitsInfo</TableID>
                                  <ColumnID>uriKey</ColumnID>
                           </Source>
                     </KeyColumn>
              </KeyColumns>
              <Type>Granularity</Type>
       </Attribute>
</Attributes>

</Dimension>

• Delete the existing Page Usage Cube
• Create the new Page Usage cube using the updated create script.

That's it - you should see updated hits count for your requested pages in Commerce Server reports.

Hope this helps!

Alan