Monthly Archives: September 2008

v0.1 of my Visit/PageView Analysis Services Cube

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 ( and 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 and 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 (, 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!

TagScanner Renames and Tags Your Digital Music [Featured Windows Download]

Windows only: Rename the thousands of MP3 files in your digital music library and add or edit tags, lyrics, and album art in one fell swoop with free utility TagScanner. Not only can TagScanner clean up the artist, album, song title, and track number information for your digital music files, it can rename your songs based on a pattern you define (like %artist% - %title%), it can make music playlists, and search online databases like freedb and Amazon to automatically tag music missing information. It includes a built-in player as well so you can listen to tracks while you edit. We’ve recommended Media Monkey to whip your music’s metadata into shape, but TagScanner looks like a solid alternative. TagScanner is a free download for Windows only.

Avoid this tricky conflict between ASP.NET AJAX and jQuery

ASP.NET and jQuery's logos togetherYou 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:

arguments[i] is undefined

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:

Using Firebug to debug the jDrawer JavaScript error

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.


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 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

ChromePass Displays Google Chrome’s Saved Passwords [Featured Windows Download]

Windows only: Free utility ChromePass displays all the passwords Google Chrome has saved for your web site logins. You can view saved passwords one by one in Chrome itself, but ChromePass displays the list all at once, and lets you export your username and password list to a file (XML, HTML, comma or tab delimited text). ChromePass also displays the name of the username and password field, and the time it was created. It doesn’t look like ChromePass can import a file of passwords, so the exported file is just a reference if you want to take your saved passwords to go. ChromePass is a free download for Windows only.

ChromePass [Nirsoft via Techie Buzz]

Five Best Sites to Stream TV [Hive Five]

With or without the cooperation of television networks, your favorite TV shows have moved to the internet in a big way. On Tuesday we asked you to share the best sites to stream TV, and now we’re back with the five most popular answers. Keep reading for a breakdown of the best places on the web to find and watch the shows you love on-demand from the convenience of your browser.


Hulu streams high-resolution, ad-supported videos of both television shows and movies. More specifically, the site distributes content by NBC-Universal, Fox, and a handful of their subsidiaries. Popular shows hosted by Hulu include The Daily Show, The Colbert Report, Battlestar Galactica, The Simpsons, The Office, and Saturday Night Live. The biggest complaint leveled against Hulu is its US-only restrictions, though many of you are using previously mentioned Hotspot Shield to access Hulu from outside the US. Hulu isn’t entirely US-only, though—the first show Hulu made available to the world at large was Dr. Horrible’s Sing-Along Blog, with Lifehacker favorite Felicia Day.


SurfTheChannel aggregates video from all over the internet—including sites like YouTube, MetaCafe, and Hulu (along with much more obscure sites)—then makes all of those videos available through a simple, searchable interface. It's not always pretty, but the selection is huge. The downside: According to the STC homepage, the site will shut down on October 1st.


SideReel is a video aggregation site similar to SurfTheChannel, providing links to streaming video from all over the internet. It’s an attractive site, but if you’re there to find video it can be difficult to navigate to begin with. If video is what you’re looking for, just navigate to the Links section of a show’s page and click on the episode you want.

Straight from the Source

Apart from the sites dedicated to streaming content, more and more networks are building their own on-site tools for streaming content from the source. For example, the big three networks all have streaming sites: the ABC Full Episode Player, the NBC Video Library, and CBS Video. If you missed a show you’re dying to catch online, be sure to check the web site of the network it aired on to see if it’s available.

BBC iPlayer

The BBC iPlayer streams content from up to 250 programs from the British Broadcasting Corporation. BBC iPlayer restricts streaming to the UK-only, but like Hulu, setting up a proxy with an application like previously mentioned Hotspot Shield has worked for many people looking to get around these restrictions.

You’ve seen the best options, so now it’s time to vote for your favorite.
Which is the Best Site to Stream TV?
( surveys)
The internet is an excellent new frontier for keeping up with the TV shows you love, but it’s certainly not the only way new technology is making it easier to watch TV. For more, check out six ways to catch your favorite TV shows. Whether or not your favorite site made the list, let’s hear more about it in the comments.

EASEUS Partition Manager Professional Free Today Only [Featured Download]

Windows/Linux only: The Giveaway of the Day web site is offering up the “Professional Edition 2.0″ of EASEUS Partition Manager, a free tool for formatting, moving, and resizing partitions on a hard drive. In addition to the tools offered in EASEUS’ (previously mentioned) free version, the pro version offers support for 32- and 64-bit XP and Vista systems, includes a Linux version, and lets you create a bootable CD/DVD to use the tool when your system is rendered un-bootable. The Giveaway protocol requires downloading, installing, and activating this software within the next 16 hours. Looking for a multi-OS, always-free solution? Try our guide to partitioning and imaging with the system rescue CD.

The Ultimate Small Business Owner’s Resource Guide Available as Free PDF [Ebooks]

When your brother’s starting up a new business and keeps asking you for the best places to do things online like send faxes, get legal help, or find a virtual assistant, send him a copy of The Ultimate Small Business Owner’s Resource Guide. The book normally costs $35 for a print version, but it’s available as a free PDF download here today. The 102-page volume is a compendium of webapps, engines, indices, software, and online tools for small biz owners looking to get things done cheaply and easily. You can find most of these recommendations online with some creative Googling, but the book offers a quick look-up with a well-organized table of contents. Aside from a few odd mistakes (like calling Google Calendar “Gmail Calendar”), this book’s recommendations align with many you’ve seen here at Lifehacker. Download The Ultimate Small Business Owner’s Resource Guide PDF here.

#64- Gadget Station (Pt. 7)

Download Low Resolution
Download High Resolution


I made quite a bit of progress in this video. The legs are cut from the bubinga blanks, and I finally have an opportunity to do a dry fit. I am in for a surprise as you will see, and a change is made to the original plans.

Gadget Station (Pt. 1)
Gadget Station (Pt. 2)
Gadget Station (Pt. 3)
Gadget Station (Pt. 4)
Gadget Station (Pt. 5)
Gadget Station (Pt. 6)

Ffmpeg Your Way to Media Conversion Bliss [Media Conversion]

If you prefer sticking to the speed of the command line whenever possible, weblog CatsWhoCode details how to use Ffmpeg—the cross-platform command-line tool that runs in the background of most popular media conversion tools—to convert video, audio, and images to just about any format.

ClickGone Turns Your Mouse into an App Assassin [Featured Windows Download]

Windows only: Free application ClickGone (or One Click App Killer—there's some confusion over the actual name) turns your mouse cursor into a crosshair and forcibly kills the first app your pull the trigger on. Although it's fun to fire up Internet Explorer and kill it for sport, the application is actually intended to kill off frozen apps quickly and easily. Assuming you were to create a keyboard shortcut to run ClickGone, it could serve as a simple go-to for knocking out a stalled application. This single-use utility is freeware, Windows only.

One click & app is gone [Stylet Software via CNET]

WP Like Button Plugin by Free WordPress Templates