Error 500 referencing VSEnterpriseHelper when running unit test of ASP.NET in VS2010

As noted previously, I want to move to test-driven development. My first step towards that end is to add unit tests to my team's existing projects. So, while working to retrofit a web dashboard project I co-authored to use my new DataFactories class, I added unit testing to it. Upon every attempt to run the first test, however, I was receiving the following error:

The test adapter 'WebHostAdapter' threw an exception while running test 'MyMethodTest'. The web site could not be configured correctly; getting ASP.NET process information failed. Requesting 'http://localhost:2212/VSEnterpriseHelper.axd' returned an error: The remote server returned an error: (500) Internal Server Error.
The remote server returned an error: (500) Internal Server Error.

It turns out that this can be caused by numerous things, including not having access to your data source for the project. Having confirmed that, though, you may still have the issue. I chased my tail on this for a while until I found a Microsoft Connect (bug submission site) post. Here's the jist: VS2010 only supports testing in DotNet 4.0. However, if you're running a 3.5 web site then testing will give you this error. There is an easy workaround, as follows:

1. Open Web.Config
2. Under the <runtime> element, *before* the <assemblyBinding appliesTo="v2.0.50727" xmlns="urn:schemas-microsoft-com:asm.v1"> element, add the following:

<assemblyBinding xmlns="urn:schemas-microsoft-com:asm.v1"/>

3. The <runtime> element should look something like this:

<runtime>
  <assemblyBinding xmlns="urn:schemas-microsoft-com:asm.v1"/>
  <assemblyBinding appliesTo="v2.0.50727" xmlns="urn:schemas-microsoft-com:asm.v1">
    <dependentAssembly>
      <assemblyIdentity name="System.Web.Extensions" publicKeyToken="31bf3856ad364e35"/>
      <bindingRedirect oldVersion="1.0.0.0-1.1.0.0" newVersion="3.5.0.0"/>
    </dependentAssembly>
    <dependentAssembly>
      <assemblyIdentity name="System.Web.Extensions.Design" publicKeyToken="31bf3856ad364e35"/>
      <bindingRedirect oldVersion="1.0.0.0-1.1.0.0" newVersion="3.5.0.0"/>
    </dependentAssembly>
  </assemblyBinding>
</runtime>

4. Save and close your Web.Config. If you don't, you'll get notifications that it's being changed externally, which is actually the debugger converting that section into something it can use.

My Adventure in Reinventing the Wheel

Why I Needed a New Wheel
Let me begin by saying that this is going to be a long article. It took me months to develop what I'm presenting and weeks to write the article. Hopefully, it takes you less time to see the value in my sharing the pains I took and my results.

In an earlier article, I mentioned that my team had settled on using a linked server methodology for accessing OLAP data via our SQL instance. While this does provide benefits, it isn't the quickest path to the data. Further, as I began developing more web applications, such as interactive data divers and operational dashboards, rather than Reporting Services reports, the impact of this double hop to the data became more noticeable. It was time to find a more direct path to the data.

My initial goal was to create a way to reference parameterized MDX queries with expected outputs of either a strongly typed DataTable or a DataSet as my use of these queries was always for presenting a table or graphical representation of key performance indicators.

Found a Unicycle
When I began this voyage, my skills were strong but my knowledge was sorely lacking. Thus, I began with the simple belief that I needed to jump right into ADOMD.NET, Microsoft's framework for working with Microsoft SQL Server Analysis Services. Before jumping into the deep end, I did what I've always done and sought out swim fins. Usually, I come up short on this as not many developers have documented their travails through these waters. This time, however, I found Sacha Tomey's blog entry, MDX Stored Procedures where he talks about a way to use a text file as a kind of stored procedure for SSAS, storing a parameterized MDX query and ensuring that you get a (optionally strongly typed) DataTable or DataSet back from the query. It's an old post from 2007 so it was heartening to see that someone had preceded me on my quest. It's a pretty straightforward implementation and he released the full source code in his Part 2 post.

Sacha's class expects that you know what your SSAS server, database, and cube will be at design time. Now, I may not be very knowledgeable but one thing I know is that this is not easily maintainable code. You see, I have a development platform, a QC platform, and a production platform. While they all have databases with the same names, the server names differ. That's why we never hard code server names. Rather, we code using connection names which are stored in our web.config file as connection strings and each server has its own web.config file modified appropriately. So, the first thing I knew I had to do was modify the code to allow the use of connection strings.

My First Wheel Resembled Swiss Cheese
Ok, so my wheel was round but I started seeing holes in the implementation immediately. As I created this modified implementation of Sacha's work (duly documenting from where I'd lifted the code), I thought to myself how cool it would be if his MDXHelper class were actually two classes, one for the connection and one for the command, you know, like System.Data.SqlClient has. So, I created a namespace called MdxClient and two classes, MdxConnection and MdxCommand and started moving Sacha's methods into the appropriate classes. As I was tearing apart his constructors to do my bidding, I didn't bother to implement constructors for the MdxConnection involving a server/database pair and implemented my connection string version instead.

About the time I finished with the MdxConnection and started moving on to the MdxCommand, I had an enlightened conversation with my mentor, a much more senior developer I talk to every blue moon. For the last year he's been telling me to study up on design patterns. Today, this is required coursework in many CS degree programs but I'm only vaguely aware of design patterns myself. Until recently, I was just a spaghetti programmer in "get 'er done" mode at my boss's insistence. This time, however, I had the specific goal of "get 'er done...faster" as the web pages were not meeting the responsiveness that our customer's expected. And to make things faster I needed to make things better, not just clean and well-documented with a minimum of repeated code.

My mentor floated out lots of ideas along with several pattern names, including Factory, Singleton, and Adapter. Two of those sounded familiar since I've used the SqlDataAdapter and seen, while investigating the DbConnection something called the DbProviderFactory. I really didn't know what each of these meant and, while my mentor tried to educate me in about an hour and a half, it was after midnight when this conversation began and excitement only carried me so far.

(My mentor also said I shouldn't continue any development without creating the tests first, AKA test-driven development but I'll talk about that in another post.)

My Second Wheel Resembled Emmental
So, I went back to my desk the next day and started opening up the definitions of the SqlConnection and SqlCommand to see what they contained. I was sleepy and figured this was a good place to start just to see if what I was doing with MdxConnection and MdxCommand had a similar footprint. After all, one thing that stood out from my conversation the night before was that my "how cool would it be if" moment when I started on this journey had merit and now I knew why. If I could make them have the same functionality, then it wouldn't matter if today we wanted the application to connect to SSAS to get its data and tomorrow we wanted the same application to query SQL instead.

While the footprints were similar, I found I had more work to do and got to doing it. It wasn't until the next day, after more sleep, that I realized that maybe the work I was doing wasn't achieving my goals. First off, it seemed like a lot of what I was doing was just reproducing functionality already in AdomdConnection and AdomdCommand. So, as I started deferring to their method implementations, I wondered if I was on the right track or not. It turned out that AdomdConnection implements a lot of the same methods as SqlConnection. That's because SqlConnection inherits from the abstract class DbConnection which implements the interface IDbConnection, the same interface which AdomdConnection implements. Similarly, SqlCommand inherits from DbCommand which implements IDbCommand, the same interface which AdomdCommand implements.

Then it dawned on me. I'm reinventing the wheel. Maybe it was time to stop developing a solution to my problem and start designing one. What were those design patterns again?

Off to the Wheel Factory
Recalling the DbProviderFactory as I was taking my first stab at my own MdxClient, I decided to take the advice often given and avoid reinventing the wheel. What I discovered pretty quickly, with great disappointment, was that ADOMD.NET didn't support DbProviderFactory. Robert Bouillon wrote an article stating as much but with a useful workaround, use OLE DB instead of ADOMD.NET.

While a very cool solution for some needs, alas it didn't suit my purposes. The problem is that OLAP is good about returning System.String objects for text when using ExecuteReader() but for numbers it returns System.Object objects. I wanted strongly typed objects so that I could perform calculations without having to perform casts first. From Sacha's article, I'd already seen that a solution to this was to use ExecuteXmlReader() and then use the details in the XML to create a strongly typed DataTable. Unfortunately, OLE DB doesn't support. I'll admit here that frustration was setting in.

Forget Wheels! Time to Build a Better Mouse Trap!
Ok, so I decided that it was time to stop hunting for wheels or attempting to invent my own. Instead, it was time to take existing ideas and designed something that suited my own vision. I was still sold on the Abstract Factory Pattern as it would allow me to switch between different data access methodologies as long as they all provided the same functionality, most importantly the support for parameterized scripts (aka stored procedures) and strongly typed output. My solution, the IDataFactory interface with concrete implementations of AdomdClientDataFactory and SqlClientDataFactory. The heavily commented implementation is available on CodePlex.

Are you a Considerate Coder?

Documentation is for dummies and "those" types of people!
Ok, so you are hot stuff when it comes to developing software and/or web applications. No one can possibly keep up with your "mad skillz", right? I know that I've heard that in my office a time or two. However, are you a considerate coder? "Oh, not another documentation Nazi!" I hear you saying. Maybe I am and maybe I'm not but let's explore this both from an altruistic and a selfish point of view.

Why do you care so much?
So call me selfish but I really like to understand the code I'm having to maintain. Oh, I hear you saying that your code is self-documenting. Then call me stupid, if that makes you feel better. It may be obvious to you what s = x != 0.0 ? Math.Sin(x)/x : 1.0; does but it can take me a minute to figure it out. That's a minute I could have back in my life if you would have had a one line comment above it saying "Approach limit; Test x to prevent divide by zero error". Ok, so that's a simple example but I've seen the ?: operator used in much more convoluted ways (in my own code) and that only alludes to the complexity that a code block can have. So, you think your code is easy to read because you named the variables well? Kudos to you if you do but I've seen a lot of code written by those less considerate than yourself which use the shortest variable names possible, as if Intellisense and auto-complete hadn't been invented yet.

And why should I care?
So, enough haranguing on the fact that code isn't easily digested by eyeballs. Let's revisit your statement that no one else on your team can keep up with your coding ability. That very statement underscores the need to document your code. It isn't just your grandmother's advice that you always wear clean shorts that has merit. Always document your code for the day that you end up unintentionally soiling those clean shorts due to the proverbial bus accident.
One final appeal to the innate, selfish nature of all animals, even the human species. You'll look good if your boss can understand what your code was supposed to do. And in the event that the code doesn't do exactly what you expected, it'll be far easier to find the spot where it's most likely needing special attention and that'll make your boss happy, too.

Right Answer Wrong Question Scenarios

As I mentioned in an earlier post, one way that we provide intelligence to the data, resulting in information, is to prevent Right Answer Wrong Question (RAWQ) scenarios.  A RAWQ scenario occurs when data is returned which seems sane on first glance but upon inspection it is found that the numbers don't match reality.  This happens in SQL with improper joins but is typically caught quickly by the developer.  However, when you expose your data for the public to consume in cubes, RAWQ happens altogether too frequently.

This is best shown via an example.  Consider a Public Libary cube which has three date dimensions, Date Checked In, Date Checked Out, and Date Held.  Similarly, it has three people dimensions, one for Held By, Checked Out By, and Checked In By.  (Ok, so the library doesn't know who checks the book in.  Go with it. ;-)  A typical MDX query might look like this:

SELECT 
{ 
   [Measures].[Count Books] 
} ON COLUMNS, 
NON EMPTY { 
   [Checked Out By].[Checked Out By].Members 
} ON ROWS 
FROM 
   [Books] 
WHERE 
( 
   [Date Held].[Date].&[2010-04-19T00:00:00] 
) 

Do you see the wrong question?  You will get an answer and it won't be the wrong one but you asked the cube to show you a count of books checked out by all patrons which was held on April 19th, 2010.  It's a good question, even a potentially useful one, but it would be rare that the library needs to know this information.  More likely they'd want to know either who held how many books on a date or who checked out how many books on a date.

We implemented a solution to this by creating Perspectives for cubes where such RAWQ scenarios exist.  Doing so where each perspective has matching dimensions, i.e. a [Checked Out Books] perspective with the  [Checked Out By] dimension matching the [Date Checked Out] dimension, while hiding the [Books] cube from public consuption.  Only developers have access to the "raw" [Books] cube.

One method to get a combined dataset of OLAP data

When I started with this team, about 3 years ago, they had just completed a business intelligence solution that allowed any (internal) user with a web browser to pull data from several SQL Server Analysis Services (SSAS) cubes.  As I mentioned in my introductory post, it seems that most BI is done in Excel one cube at a time.  Our STAR Award-winning solution, while still representing only one cube at a time, did not have this limitation.  Further, it has allowed us to provide views into the data which help to prevent Right Answer Wrong Question scenarios.

Once our users got used to having such easy access to their data, they suddenly wanted to mix up the data in the different cubes to see new, potential relationships.  They were already receiving some of this data in various reports and some rolled up in a summary report, all created using SQL.  However, if the SQL developer didn't use the appropriate joins and where clauses to match the filters and slicers that the user selected on our BI solution, the numbers wouldn't match up.  Further, the old SQL reports ran much slower than the BI solution.  Thus, it was decided that we could get speedier, more consistent results if we started using the OLAP cubes for our reports.  (Doing so sealed the deal on the STAR Award.)  However, doing so is not so easy in Reporting Services.  You have to get each cube's data separately and representing them on the report can get messy and is very design intensive due to the usage of several stacked tables.  It would be really nice if we could use one matrix (we were on SQL Server 2005 at the time) to dump all of the different cube's data, with the only commonality being that they all represented the same date range.

My coworker investigated and we decided to implement a linked server in SQL to SSAS.  With this, we could create a stored procedure that collated MDX query results against multiple cubes, delivering a table to the SSRS report and speeding up the resulting report.

This has functioned well for us for 2+ years.  However, recently we have been creating web applications, where query time is quickly noticed by the end-user, and have found that this has added overhead to the queries which we want to eliminate.  More on that in a future post.

Introduction of this blog and its author

If you've found this post, then you've arrived early to the action. My goal with this blog is to introduce myself and share what I've learned most recently in my growth as a Business Intelligence developer.

A bit about myself: I have worked in the IT industry, mainly in the support arena, for over 20 years. My first PC was a brand new Timex/Sinclair 1000 which my father and I modified to have an external keyboard. I fell in love with programming then when I found a listing which showed how to poke values into memory spaces. Doing so would cause interference patterns on a TV with it's stationed tuned slightly out of phase (now that ages me) resulting in it playing the Star Spangled Banner, quite a feat for a system that had no sound functionality.

I began my professional career in application and hardware support implementing CADD systems. I moved into desktop support, then network administration, and back to application support on Windows Servers. My education introduced me to several programming languages, including Basic, Fortran, Pascal, C++, and Java. I was also exposed to dBase IV, Microsoft Access, Microsoft SQL Server, and Oracle in the professional realm. I decided early on that, though I didn't have the formal education to be a developer, that's where I wanted to be. Thankfully, I found an opportunity to pursue that about 3 years ago and haven't looked back since.

Today, I work in a business analysis group for one of the largest security software companies in the world. Our focus in this group is to provide business intelligence to all levels of management in the software support organization. The tools we use include Microsoft SQL Servers 2005 & 2008, Reporting Services (SSRS), Analysis Services (SSAS), and ASP.Net 3.5. My focus is on utilizing SSAS as a platform for consistent reporting of metrics utilized to meet performance goals.

I plan to use this blog as a way to share with the community the things I've learned along the way. I've been finding it difficult to find information on exactly what I've been doing. I may be far off the mark but it seems that the BI community isn't doing quite what my organization is in implementing custom applications presenting information from data held in multiple cubes. That has been a particular challenge in SSRS, which seems designed to best handle one dataset per table/matrix/tablix. I've also found that Microsoft doesn't seem to treat OLAP access in ASP.Net development with the same level of ease as SQL access. Thus, I've had to search out solutions for that, as well.

It's as if the only way Microsoft really wants OLAP data to be available is via Excel, and there is a big difference between data and information. Giving someone a pivot table in Excel, while easy, does not help them make decisions. One must have some specific intelligence to turn that data into information, which is what I and my team strive to do. Perhaps something I post here will help you do the same.