September 27, 2011
by Rick
0 comments

Rick’s Rants: Application Aggravations

As a developer, one of my major pet peeves is developers who don’t use their application.  If you are a Microsoft developer you can see this with SSMS.  The IDE compared to Visual Studio is awful.  It took until SQL 2008 to get intellisense and it’s a piss-poor implementation.

With the proliferation of mobile devices some of these things are more widely seen.  Some of the items below, are purely on the website developers while other things are just some ideas that I have.  In many of the situations, I suggest there is some kind of mechanism for deterring if you want the behavior.  Some of the things I want would involve browsers storing settings and passing those to pages.  Those kinds of wants require some major revamping to how the web works, but why should that stop me from ranting?

  1. Don’t make me fill in a zip code  Anytime I use my iPhone or iPad, many sites ask me if they can use my location and then it filters the results to those locations.  This is great and save me the hassle of entering this information.  Several sites such as Best Buy, Lowes, & Home Depot want your location to either search stock or when you first hit the page.  They don’t seem to be able to store this information on a consistent basis.  This means that sites I visit regularly, I have to constantly enter this information.  Some websites are coming around and auto populate.   It’s not perfect in all situations but it’s a start.  When I search from work, for instance, my company’s headquarters is several states away and our IP looks like it’s from that state.  So they get it wrong, but they are making the attempt and they can’t accurately tell where I am from.
  2. Let me set the paging results page size  I love Amazon and use it all the time. I despise they don’t let me set the number of items I want in a search result.  This maybe be done here and other places for performance reasons but it still bugs me.  Other sites have options that are usually like 10,20,30, etc. or so, I would like the ability to pick this value, have the browser remember it, and then any site that using that mechanism pickup on my default value.  Or even better, more companies should do what Facebook and Under Armour do; when you hit the bottom of the page, they load the next set for you automatically.  I really like this approach to the problem.
  3. Don’t make me enter a birthdate to prove my age  You generally see this practice on Alcohol distributors and for video games sites where the game is rated M.  How did it become accepted practice to do this?  Does the owner of the site actually believe it absolves them of anything?  How is it anything but annoying?  Anybody about 7 or older that wants to see that information can just enter a date that allows entry.  BAM!  The kid is in.  Way to go.  Recently I went to Sam Adams site and they have this practice, but on the following page had me re-enter my birth year to be sure.  Yeah that really helped.  I would like to see this gone completely, but that isn’t likely, so let me store this in my browser.
  4. If you are being fancy with results and not reposting the entire page, remember where I am if I follow a link and come back.  Many sites can load up the next page of results without refreshing the page.  That is cool JQuery/AJAX at work and makes for a very nice user experience.  Some sites get it very wrong through.  If I am on page 3, click and item and then come back, put me back on page 3 and preferably where I was scrolled too.  Starting me back over at page 1 is awful.  Especially if the page controls look something like this:   Page 1, 2, …. 43.
  5. Don’t restrict my “mobile” device to the mobile site.  It’s pretty great that sites look and notice you are on  a mobile device and redirect you to a mobile version.  What sucks is when they force you to stay on the mobile version, especially when the mobile version doesn’t have all the same content.  This is really annoying when on an iPad.  It has more resolution that some netbooks.  Let me see the full site.  It’s one thing if they have flash (don’t get me started), but when they restrict me just to restrict me?  Drives me nuts.  I am looking at you ESPN.com

 

If you made it this far, thanks for sticking through the rants.  If you have some Application Aggravations, leave a comment.  Look forward to more rants in the future.

August 20, 2011
by Rick
2 Comments

Using Dapper with MDX

At my company we were beginning to use MVC for our next generation web platform reporting.  In the recent past we began using Microsoft SQL Server Analysis Services for our aggregation needs.  Prior to MVC, we were doing custom ASP.Net WebForms applications.  I had written a data object using ADOMD to retrieve the data from our cube and serve it up for reporting.  Through many trials, we had to utilize the LoadXML feature of ADOMD instead of the CellSet.  This offered us much more speed in execution but we lost some of the features of being able to walk up and down the dimensions.

As our focus shifted to MVC, we wanted to migrate away from this layer.  We started to look at our alternatives and we immediately gravitated towards Dapper.  If you haven’t checked out Dapper, I highly recommend that you do. It’s from the super programmers at Stack Exchange and provides some lightning quick execution using IDbConnection objects.  Dapper has two main Query methods, one for generics where you give it a strongly typed class and it maps your query to that class and returns you an IEnumerable of that type.  The second is an IEnumerable of dynamic.  If you aren’t aware, dynamics are new in .NET 4.0 (watch this space for a more in depth discussion of dynamics).  They are similar in nature to anonymous types in that you can build properties on the fly.  One difference is they are late bound or checked at runtime so you don’t get any intellisense with them.   The dynamic object returned has a properties for each field that is returned in your query.

While this is great in general, it gave us a small setback trying to utilize it with an MDX query.  MDX, unlike SQL, doesn’t allow you to alias your column names.  The queries you write can have some very long column names and more importantly they contain letters that make it all but impossible to fully utilize dynamics abilities.  A sample column name may be something like [Measures].[Response Count].  The brackets and periods make it impossible to reference that field off of the returned object.

To combat this issue, I wrote an extension method to Query.

using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Data;

namespace DapperExtension
{
    public static partial class SqlMapper
    {
        /// <summary>
        /// Return a list of dynamic objects, reader is closed after the call
        /// </summary>
        public static IEnumerable<dynamic> Query(this IDbConnection cnn, IDictionary<string, string> columns, string sql, dynamic param = null, IDbTransaction transaction = null, bool buffered = true, int? commandTimeout = null, CommandType? commandType = null)
        {
            IEnumerable<dynamic> output = Query<FastExpando>(cnn, sql, param as object, transaction, buffered, commandTimeout, commandType);

            if (columns != null)
            {
                List<dynamic> modifiedOutput = new List<dynamic>();

                using (IEnumerator<dynamic> enumerator = output.GetEnumerator())
                {

                    while (enumerator.MoveNext())
                    {
                        IDictionary<string, object> currentRow = enumerator.Current as IDictionary<string, object>;
                        Dictionary<string, object> newRow = new Dictionary<string, object>();
                        if (null != currentRow)
                        {
                            foreach (string key in currentRow.Keys)
                            {
                                if (columns.ContainsKey(key))
                                {
                                    newRow.Add(columns[key], currentRow[key]);
                                }
                                else
                                {
                                    newRow.Add(key, currentRow[key]);
                                }
                            }
                        }
                        else
                        {
                            // exception
                        }

                        modifiedOutput.Add(FastExpando.Attach(newRow));
                    }
                }

                if (null != modifiedOutput && modifiedOutput.Count > 0)
                {
                    output = modifiedOutput;
                }
            }

            return output;
        }
    }
}

First thing you will notice is that I put this extension in the Dapper namespace. I purposely wrote an extension in a different file so that I didn’t have to touch the Dapper code. I know that it’s open source and many people would fork it, but an extension method serves me well. They can keep updating and I don’t have to try and maintain the fork. Also by putting this in the Dapper namespace, I have access to their private classes, of my interest to me is the FastExpando class.

The parameter that drives this logic is the Dictionary<string,string>.  What I am expecting is a dictionary that contains what the column name was from the MDX query and what you would like to call it.  From the previous example we would have key,value pair of [Measures].[Response Count] and ResponseCount.  The method goes through each item in the returned object and makes a new FastExpando (FastExpando is nothing more than an IDictionary<string, object>) and if the key to that item is in the column dictionary, then it adds the value as what was passed in, otherwise it uses what it had.  The method will also just return the original object.

One downside to this extension method is that we have to go through the result set after its already been created.  As I mentioned before, I didn’t want to mess with the core Dapper code to prevent this.  In following posts, you will see how I solved this problem by writing my  our own DBProvider.  Stay tuned.