One of the consistent pieces of feedback from our Australian TechEd attendees is “the schedule builder is difficult to navigate”
Solution 1: Pivot
Viewing complex data sets, such as TechEd session data, in Pivot is just beautiful. And useful.
This Pivot was highlighted by Michael Kordahi in the TechEd keynote:
Solution 2: OData, the code way
In the week prior to TechEd, a magical URL appeared that exported a 3Mb XML file. This contained Session, Topic, Speaker and Room data all mashed up in XML. Only 25% of the data corresponded to real sessions. And there were other small pieces of clean up required.
My goal was to generate an OData feed of the raw data for others to use for their nefarious reasons. The easiest way would be to add an OData feed from the original source. As with most IT situations, it wasn’t that easy as the system contains PII data and the cost to implement outweighed the immediate benefit.
Onto data munging.
At this point I will have to make an admission. I like to code first. Research post the event showed that I could have used SQL Server SSIS to import the data via a schema. My attitude is that the less you deal with XML the better life is.
After downloading the XML, it is processing time.
- LINQ to SQL as described here, gather up the XML values into POCOs. The LINQ statement ended up being 112 lines long and a little difficult to debug. But it worked, and very quickly.
- This style of code checked for blank (or null) values:
Waitlisted = string.IsNullOrEmpty(ss.Element("waitlisted").Value) ? 0 : Convert.ToInt32(ss.Element("waitlisted").Value)
- Using Entity Framework 4.0, after creating a model; generated the database schema. Initially I mapped this to SQL Express 2008 for testing; and SQL Azure for final data upload.
- Using the same EF schema, created a new project that would expose the data as OData. Using WCF Data Services, a pre-existing schema – this process took 2 modifications to the generated C#.
- Hosting via Windows Azure; deployment directly from within Visual Studio 2010 – the server was deployed within minutes. And working.
For future work, a worker-role in Azure capturing and updating the data would have reduced personal intervention in updating the data.