v0.1 of my Visit/PageView Analysis Services Cube
User Rating: / 0
PoorBest 
Written by Matt Berseth   
Saturday, 18 October 2008 19:56

So I created a cube using my the visit/pageview that I recently started collecting.  The cube is VERY simple - only 4 dimensions {App, Page, Source, Time} and just a single measure - {Hit}.  I created the cube using the 2005 versions of Visual Studio and Microsoft's Analysis Services.  The IDE's wizards pretty much walk you through the process, which is great because creating an Analysis Services project from scratch is more than a little intimidating.  Especially if your a web developer like me and you don't know a whole lot about querying, let alone designing a cube.

Anyway, like I said, my cube is very simple.  The App dimension only contains 2 members: 'mattberseth' for this site, and 'mattberseth2' for my live demo site.  The Page dimension contains all of the unique URLs for both sites as its members, the Source dimension is essentially a bit field for determining if the traffic was the result of a direct hit or from a referring site and finally the Time dimension represents the calendar and is used for counting hits by a time interval (i.e. Days, Weeks, Months, etc...). 

In my simple cube, all 4 dimensions and the Hit measure are currently coming from a single table.  I have simulated the standard star schema by generating the keys for the different dimensions using the following 4 SQL queries.  So the first query in the SQL snippet populates my measure group and the other three are responsible for populating the App, Source and Page dimensions.

Read more...

 

 

 

XL Cubed