Monthly Archives: September 2008

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.

-- builds the fact_pageview measure group ***
select
    -- if the referring url is empty than we know the source comes from a direct hit
    (case when ref_url = '' then 1 else 0 end) as source_id,
    -- the app_id - either mattberseth or mattberseth2
    app_id,
    -- turn the page url to an int
    checksum(url) as page_id,
    -- extract the date portion of the datetime
    convert(datetime, floor(convert(float, date))) as date,
    -- each row is a sinlge hit
    1 as hit
from
    -- my visit table
    visit_load with(nolock)

-- builds the app dimension ***
select
    'mattberseth' as app_id,
    'My Blog' as app_name
union
select
    'mattberseth2',
    'My Live Demo Site'

-- builds the source dimension ***
select
    0 as source_id,
    'Referrer' as source_name
union
select
    1,
    'Direct'

-- builds the page dimension ***
select
    checksum(url) as page_id,
    url as url
from
    visit_load with(nolock)

Next, I created views for these 4 queries, let my Analysis Services project know about them and used them as the data source for my cube. Conceptually, this diagram shows how these 4 queries are related.

Browsing and Querying the Cube

And amazingly, only after a few minutes of nexting through wizards and drag and drop design work, I deployed and processed the cube to my local Analysis Server instance. And now I can start taking a look at the data.

Browsing the Cube

Once the cube is deployed and processed, you can start browsing it. Below are a couple of screen shots that show the structure of my cube on the left, and hit counts for my two sites (mattberseth.com and mattberseth2.com) segmented by the traffic source (either direct traffic or referring site). The screen shot below that shows these counts as a percentage of the grand total. Looks like direct traffic to my demo site only makes up 2% of my total traffic ;(

Querying the Cube

And if you can stomach writing a little MDX, you can write custom queries to extract even more useful information. Below is a sample MDX query and result set that shows the average traffic per day for both mattberseth.com and mattberseth2.com segmented by week. The numbers are a little deceiving because only Week 39 consists of a full 7 days, but I think you can get the picture.

with
    -- define the Weekend and Weekday sets
    set [Weekday] as
    {
        [Time].[Day Of Week].[Day 2],
        [Time].[Day Of Week].[Day 3],
        [Time].[Day Of Week].[Day 4],
        [Time].[Day Of Week].[Day 5],
        [Time].[Day Of Week].[Day 6]
    }
    set [Weekend] as
    {
        [Time].[Day Of Week].[Day 1],
        [Time].[Day Of Week].[Day 7]
    }
    -- create a few calculated meausres based that make use of these sets
    member [Measures].[Weekday Average] as avg([Weekday], [Measures].[Hit]), format_string = '#'
    member [Measures].[Weekend Average] as avg([Weekend], [Measures].[Hit]), format_string = '#'
    member [Measures].[Weekly Average] as avg({[Weekday], [Weekend]}, [Measures].[Hit]), format_string = '#'
select
{
    [Measures].[Weekday Average],
    [Measures].[Weekend Average],
    [Measures].[Weekly Average]
} on 0,
non empty
{
    [App].children * [Time].[Week Of Year].children
} on 1
from
    [PageView]

What’s Next?

Well, I am pretty excited. I only have a handful of development hours invested in my visit cube (it honestly took longer to write this post than it did to create the cube) and I can already tell I have made the right decision by maintaining my own pageview/visit database. Of course there is still a lot to do …

• My pageview JavaScript tracking code needs some work. I have been tweaking it over the past 2 weeks to play around with different techniques to keep my tracking request from getting cached. I have finally come within a few percent of what Google Analytics is reporting so I am happy. I think I will clean the handler up and write a quick post describing what I did.
• My Source dimension on has 2 members – Direct and Referrer. I would like to break down the Referrer further to include Search Engines, Community (DNK, Reddit, Digg, DZone, etc…), and forums (forums.asp.net, stackoverflow, etc…).

• I need to extract keywords from the Search Engine sources and get them into the cube
• Look up geography information based on IP
• IP + User Agent Sessionization. I would like to track time on site, navigation paths, etc…
• Incorporate additional dimensional data from my Moveable Type database

At some point I plan on sharing the solution: JavaScript tracking code, HttpHandler, OLTP and OLAP databases as well as the Analysis Services Project …

That’s it. Enjoy!

You have probably already read the great news that Microsoft is going to begin shipping jQuery with Visual Studio and ASP.NET MVC. If not, make sure you take a minute to read the official announcements from both ScottGu and John Resig. This represents a surprising, yet tremendously welcomed change of course for Microsoft.

If you haven’t yet used jQuery with ASP.NET, you’re in for a pleasant surprise. It removes almost all of the pain from client-side development. Coming from an ASP.NET centric perspective, you may find several of my previous jQuery articles useful.

Additionally, I highly recommend the articles that Matt Berseth and Rick Strahl have written on the topic of using jQuery with ASP.NET. I am constantly amazed at the quantity and quality of content that they both generate.

Introductions aside, I’d like to take this opportunity to discuss an incompatibility between ASP.NET AJAX and at least one jQuery plugin, which needs to be fixed.

A simple combination leads to an odd problem

Last month, I came across someone having an odd problem with ASP.NET AJAX and jQuery, on the ASP.NET forums. While using the jDrawer plugin to jQuery, he found that adding an ASP.NET AJAX ScriptManager completely broke jDrawer.

Having never encountered trouble mixing ASP.NET AJAX and jQuery plugins, I was reluctant to place the blame on ASP.NET AJAX until I tried it for myself. However, the problem was readily reproducible by downloading the jDrawer sample and adding a ScriptManager. It immediately went from a working demo to throwing this JavaScript error:

Inspecting the specified section of the jDrawer code revealed this function:

PreloadImages: function() {
  for (var i in arguments.length)
    if (arguments[i].type === "array")
      for (var j in arguments[i])
        // Irrelevant image preloading code.
  else
    // Irrelevant image preloading code.
},

It looks simple enough, but we have a problem here…

Understanding the problem

In JavaScript, every function call is accompanied by a special arguments property which consists of an array of any parameters that the function was called with. This allows for functions with a variable number of parameters, like String.format.

jDrawer’s PreloadImages function is dependent on this arguments property. The property is necessary so that an arbitrary number of image URLs may be passed in for preloading. Unfortunately, the plugin author didn’t quite get the for loop right. He’s using the associative form (foreach), but arguments.length is a Number, not an array.

As I’ve previously covered, one of the nice things that ASP.NET AJAX brings to the table is an assortment of upgrades to JavaScript’s base types. Among them, the Number type is modified to include a format property, the root of this trouble:

Notice that even though the arguments property is an empty array, execution has entered the for loop. The reason for that? The jDrawer code is attempting to iterate over the Number, arguments.length, not the empty array itself.

Normally, this would cause the function to silently abort, since the base Number type has nothing to iterate over. However, ASP.NET AJAX has added a few features to the Number type, giving the faulty code the extended Number type’s properties to iterate over.

Because those certainly aren’t valid keys for the arguments array, execution always fails when line 424 tries to reference arguments[i] for the first time. Basically, it’s attempting to access arguments['format'], which throws the error.

The solution is easy!

The solution is to iterate over the arguments array using a traditional for loop:

PreloadImages: function() {
  for(var i = 0; i++; i < arguments.length)
    if (arguments[i].type === "array")
      for (var j in arguments[i])
        // Irrelevant image preloading code.
  else
    // Irrelevant image preloading code.
},

The troublesome format key is ignored, and the function operates as desired.

As a bonus, fixing the error also fixes the image preloading. Even though it wasn’t causing an error without a ScriptManager on the page, the preloading code in this plugin was never working before.

Conclusion

Don’t get me wrong — nothing this minor could possibly begin to dampen my excitement over jQuery getting this official nod from Microsoft.

However, with today’s news about official jQuery support and Microsoft’s efforts toward peaceful client-side coexistence in general, I think this issue is something that should be addressed if at all possible.

It’s not the ScriptManager’s fault that this happened, but it isn’t a problem unless the ScriptManager is present. Not many developers are going to track down obscure JavaScript errors caused by two third party pieces of code. They’ll see the cause of adding the ScriptManager, the effect of it breaking the page, and logically lay the blame on the ScriptManager (unfairly).

Hopefully, having the problem and solution identified here will mean that anyone who runs into it in the meantime will be able to fix it with a quick Google search.

As mentioned previously, I have several posts on using jQuery with ASP.NET AJAX that may be helpful if you’re just getting started with the combination.

If the topic interests you, be sure that you’re subscribed to Encosia updates via either my RSS feed or its email-based counterpart. More posts on ASP.NET and jQuery are on the way soon.

###

Originally posted at Encosia.com. If you’re reading this on another site, come on over and take a look at the real thing.

Avoid this tricky conflict between ASP.NET AJAX and jQuery