Discussions on SQL Server Manageability and Development.

PDC 2009 Session Recording – Building Location-Aware Application with SQL Server Spatial Library is now posted.

You can find a recording of the session Ed, Torsten and I gave at PDC on building location-aware applications using the SQL Server Spatial Library here:

http://microsoftpdc.com/Sessions/SVR33

Have a great weekend,

Olivier

Introducing the Excel Spatial Spreadsheet – Building an Excel Add-In for doing Spatial Analysis and Spatial Operations in .NET.

Good Evening,

I sit down to write this blog article on the plane on my way to LA in preparation for tomorrow’s presentation at PDC on the same subject.   In my last article I described the ability to leverage the hard work that the SQL Server team put into building a spatial library for your own .NET applications.    In this article I will continue to describe how the SQL Server Spatial Library can be leveraged in many different types of applications, and in this case for use with Microsoft Office, and more specifically Microsoft Excel.

It will likely take a couple blog articles to cover the entire scope of this project, but this first article will show all the necessary steps to get the base functionality built and we will expand on it over the coming two articles.

Let’s get started building the ES2 (Excel Spatial Spreadsheet) add-in…

Pre-Requisites

In order to follow along you will need Visual Studio 2008, Microsoft Excel 2007 and you will also need to download the SQL Server Option Pack which contains the SQL Server types assemblies (or Install SQL Server 2008 on your development machine as it also includes the same assemblies).

Office Add-In Technologies

Microsoft Office offers a wide variety of options for developers to extend the core office suite.   There is of course the built in Visual Basic for Applications which allows developers to build applications hosted by Office.   You can also build different kinds of add-ins that plug into Office.   Some add-ins are more “GUI” oriented, allowing you to add task panes, new ribbons while others focus on providing more “backend” services like new formulas for Excel.    

There are also a few options for building add-ins for office.   Visual Studio includes the VSTO project types and you can also go with the more traditional and complex native extensions written in C / C++.

What the heck is a Spatial Function for Excel?

For this blog article, our focus is to build new spatial operators that can be used within Excel as if they were native Excel functions.  We want to enable an Excel User (Information Worker) who wants to find out the area of a state or zip code to type in something like:

=AREA(ZipCodeShape)

or to find out if a customer is located within a kilometer of a store location:

=IF(DISTANCE(CustomerLocation, StoreLocation)<1000,”YES”,”NO”)

it would also be nice if an Excel user could easily turn a user’s existing data like addresses  into spatial objects for analysis by calling out to web services offered by Bing maps for example:

=PARSEANDLOCATE(“One Microsoft Way, Redmond WA”)

In fact we’d like to enable someone who is not an expert in spatial analysis to just “try and learn” spatial operators quickly and painlessly, which is exactly what Microsoft Excel enables (you can always Undo and try out different scenarios by pasting a new formula in a different cell).

We want “The Power of SQL Spatial with the Simplicity of Excel”

To achieve that goal I’ll  be leveraging a type of add-in called an Automation Add-In.   For those who remember the days of COM / DCOM programming (yes I’m that old) and terms like late binding, IDispatch and automation interfaces then you’ll be right at home, for those who were able to skip that time period, don’t stress out, .NET makes it pretty easy to create COM Automation classes.  

Implementing the base Spatial Automation Add-In

Start Visual Studio 2008 or 2010 as Administrator (more on this later) and create a new .NET 3.5 C# Class Library Project and name it something interesting like MySpatialFunctions.

Once the newly created project is loaded in Visual Studio, right click on the project to bring up the project properties dialog.  

Click the [Assembly…] button on the Application Tab of the project properties and make sure the “Make Assembly COM-Visible” is checked.

We also need to ensure that the “Register for COM Interop” on the Build tab is checked.  This step tells Visual Studio to register any COM components in the project every time the project is rebuilt.  The alternative is to manually register the assembly using the regasm.exe .NET utility.

Now rename the default class name to something a little nicer than Class1, for example MySpatialFunctions.  Please note that the name you pick for the class will show up in Excel’s drop down list of functions as the Category name for your functions (example shown below):

Following the instructions from the previous article we need to add a reference to the SQL Server Type library which includes the Spatial data types.  Right click on the references folder in your newly created project and add a reference to the SQL Server Type assembly.

You will also need to add:

using Microsoft.SqlServer.Types;

to the beginning of your class file to bring in the .NET definitions for the spatial data types. 

Now let’s make sure your class can act as a COM object by bringing in the .NET COM interop support and decorating your class with the necessary attributes [ClassInterface(ClassInterfaceType.AutoDual)].  Without getting too deep into COM terminology, this tells .NET to expose our class as supporting both COM late-binding (IDispatch / Automation) and early binding clients.

Your class file should now look something like the following: 

   1:  using System;

   2:  using System.Collections.Generic;

   3:  using System.Linq;

   4:  using System.Text;

   5:   

   6:  using Microsoft.SqlServer.Types;

   7:  using System.Runtime.InteropServices;

   8:  using Microsoft.Win32;

   9:   

  10:  namespace MySpatialFunctions

  11:  {

  12:      [ClassInterface(ClassInterfaceType.AutoDual)]

  13:      public class MySpatialFunctions

  14:      {

  15:   

  16:          [ComRegisterFunctionAttribute]

  17:          public static void RegisterFunction(System.Type t)

  18:          {

  19:              Microsoft.Win32.Registry.ClassesRoot.CreateSubKey

  20:                  ("CLSID\\{" + t.GUID.ToString().ToUpper() + "}\\Programmable");

  21:          }

  22:   

  23:          [ComUnregisterFunctionAttribute]

  24:          public static void UnregisterFunction(System.Type t)

  25:          {

  26:              Microsoft.Win32.Registry.ClassesRoot.DeleteSubKey

  27:                  ("CLSID\\{" + t.GUID.ToString().ToUpper() + "}\\Programmable");

  28:          }

  29:      }

  30:  }

The above code will expose your C# class as a COM automation class and will expose all public methods as automation callable methods.   The RegisterFunction, decorated [ComRegisterFunctionAttribute] is called by Visual Studio as part of building the project.  These functions are used register the MySpatialFunctions class with the Windows Registry.  You can also use the .NET regasm.exe utility to achieve same result.

Now that the plumbing is in place, let’s implement the SPATIALAREA function which will take in the description of a Geography object in WKT (well known text) format from Excel and return the area of that geographic object.   WKT is really just text or a string, so the input parameter to the function will be a String and the COM interop between Excel and our automation add-in will make sure that whatever Excel is passing in will appear to us as a .NET String.   We will return the area as a double.

   1:  namespace MySpatialFunctions

   2:  {

   3:      [ClassInterface(ClassInterfaceType.AutoDual)]

   4:      public class MySpatialFunctions

   5:      {

   6:          public double SPATIALAREA(String Shape)

   7:          {

   8:              double fReturnValue = -1;

   9:   

  10:              // Convert the String representation of

  11:              // geographic object into a SqlGeography

  12:              SqlGeography geo = SqlGeography.Parse(Shape);

  13:   

  14:              // We now have a valid Geography, let's call

  15:              // it's area function and convert the results

  16:              // from SqlDouble to .NET Double.

  17:              fReturnValue = geo.STArea().Value;

  18:   

  19:              // Return the results

  20:              return fReturnValue;

  21:          }

  22:         

  23:          // ....

  24:      }

  25:  }

The above code receives a String from Excel and converts the string representation into a SqlGeography object using using the class method Parse() which expects a WKT encoded string.   Once the string is successfully parsed, we call the OGC standard STArea() method to return the shape’s area in square meters.   The SqlGeography and SqlGeometry objects return SQL types (SqlDouble, etc.), so we use the .Value method to return the underlying double value.  The area is then returned to Excel as a double.

I’ll stop with this one function for now to test that we have all the necessary pieces in place, but you could easily add additional public functions that expose additional methods from the SqlGeography class.   For example you could add a SPATIALDISTANCE and a SPATIALINTERSECTS method to return the distance between two spatial objects or to test if two spatial objects intersect.    I’ll in fact do that in the next blog article to demonstrate some more advanced Excel methods that operate on ranges of cells.

Now Build the Project (F5).  This will compile the class assembly and register it as a COM class library.   You need to run Visual Studio as an administrator in order for Visual Studio to correctly register the library as a COM Class Library.   When the add-in is packaged for distribution we can create an installer that will take care of registration, for local development and small distributions, it is very convenient to have VS do the registration.   Trust me… COM registration setup programs are an acquired taste and can be painful to get right.

Once the project is built and the assembly registered with Windows we can switch gears and launch Excel to bring in the new automation add-in we just created.

Manually Enabling the Automation Add-In within Excel 2007

Lanch Excel 2007 and click on the Office button (upper left round button):

Select [Excel Options…] on the bottom right of the dialog box and from the Options dialog box, select the [Add-Ins] tab.

Choose Manage: Excel Add-ins and click [Go…], on the dialog box that follows click [Automation….] and find your class MySpatialFunctions.MySpatialFunctions in the list, select it and click OK.  This will register your automation add-in with Excel and make its functions available to Excel user as Formulas.

Let’s now make sure the method is available within Excel.   In Excel, select a cell and click the Formula button (Fx) and in the Category drop down list, find your MySpatialFunctions.

You have now completed the basic process for registering a custom automation add-in with Excel.   In the next blog article I will show how to automate this process using a setup program for deployment to end-users.

This is really just a glimpse into what is feasible in terms of spatial analysis now that this plumbing is in place.

The next blog post will include example of functionality to operate over a range of cells.  These aggregate functions can act on multiple geographic shapes, and include UNION, INTERSECTION and a function to calculate the CONVEXHULL of a shape, etc. 

Testing the base Spatial Add-In

So we’ve built an Add-In that currently supports one spatial function, the SPATIALAREA function, which when given a WKT encoded string representing a shape will return its area.   So how do you get a set of WKT data to try this out?    WKT is a standard format for geospatial data and there are different tools and data sources on the internet that let you convert data to / from WKT.  Another such text representation is called GML and it is relatively easy to modify our Add-In to support both formats (and auto-detect what format).  

For now here is an example WKT object, which represents the country of Morocco, all the pairs of numbers are Latitude and Longitude values which form the vertices of the country’s border:

You can copy the above text into a Cell within a blank Excel Spreadsheet, for example copy the text into cell A1.  You can “truncate” the text by putting a space / blank in cell B2.

Now let’s try out our fancy SPATIALAREA function by typing =SPATIALAREA(A1) in cell A2 and pressing Enter.

 

And we should get the answer in square meters, format the cell to show 2 decimal places (unless you really like scientific notation):

 

Achieving A More Perfect Spatial Add-In

One of the issues with using the simple approach (Auto Dual Class Interface) to create and automation add-in is that some of the non-spatial related base Object methods are then exposed to Excel as callable methods and visible in the Function Picker in Excel.    You can see some of those unintended functions above as “Equals”, “GetType” and “ToString”  

It would be preferable from a user experience and ease of use perspective if we could hide those methods and only expose spatial methods within Excel.   In order to achieve finer control over the exposed methods it is necessary to get a tiny bit more involved with the COM interop support in .NET.   

With some refactoring we can in fact hide those base Object public methods and have finer control over the methods that are exposed to Excel.  

I will cover this in detail in the next blog article, but here is the approach:

1.   Define an Inteface in C# that represents all the methods to be exposed to Excel by the automation class. 

•  Decorate that interface with [ComVisible(true)] attribute and specify a unique Guid for that interface [Guid(“unique-guid-here”)].

2.   Modify the MyFunctions class:

o   Derive / implements interface from step #1.

o   Add [ComVisible(true)] decoration to the class

o   Add [Guid(“unique-guid-for-the-class”)] decoration to the class.

o   Add [ProgId(“MySpatialFunctions.MySpatialFunctions”)]

o   Modify [ClassInterface] Attribute to reflect [ClassInterface(“ClassInterfaceType.None”)]

1. Modify the RegisterFunction and UnRegisterFunction to reflect both the Interface and the Class.

In addition to “cleaning” up some of the unintended methods from above, It would also useful to get a reference to the Excel application to enable more communication back and forth between the add-in and Excel.    You can get a reference to the Excel Application object by implementing the Extensibility.IDTExtensibility2 interface as part of your class.   Once you have access to the Excel Application object, your add-in can actually start communicating and controlling Excel, automatically formatting cells for example that include a spatial formula.

What’s Next?

In the next article I will build on this base add-in and expand on the functions it exposes as well as describe the process of building a VSTO add-in to provide a Spatial Ribbon within Excel.  I’ll also cover how to build some spatial UI componentry for an Excel spreadsheet to allow the embedding of a visualizing / mapping spatial control within a spreadsheet.  Finally I also plan to cover the creation of a setup project to install your add-ins (VSTO and Automation) on end-user workstations.   

So some cool stuff is coming.   Here is a small teaser to show you what you can expect:

Hope to see you at PDC tomorrow and let me know if you find this Excel add-in helpful in learning the SQL Spatial operators and in performing simple spatial analysis without having to leave Excel.

Sincerely,

Olivier

PDC 2009 is next week - I'll be Building Location Aware Applications with the SQL Server Spatial Library at Session SVR33 - Tuesday

I will be presenting at PDC next week with Ed Katibah and Torsten Grabs on uses of the SQL Server Spatial library (SQLTypes).   The session will be on Tuesday at 12:30pm.   

Title:
Building Location Aware Applications with the SQL Server Spatial Library

Abstract:
Did you know that the new SQL Server 2008 Spatial Types are available in a redistributable library?  This library can be used to build interesting location aware functionality in applications that don’t even connect to a database.  See us build an Excel Add-In for spatial analysis and an application that demonstrates how to spatially-enable the new StreamInsight product.  Hear about what the Spatial Library delivers today and what is coming in future releases.

The session will largely be demos and code walkthroughs with minimal powerpoint slides.

Please stop by if you are attending PDC and share your experiences building location-aware applications using SQL Server 2008.

Olivier

Watch out for gotcha with missing VC++ runtime dlls when deploying .NET applications that leverage the new SQL Server Spatial Types libraries.

In my previous blog post I discussed the ability to leverage the SQL Server type library in your own .NET applications.  The article showed how to create a simple console application that calculates some areas for different shapes and how to create these shapes either from a text representation like WKT or “by hand” using the builder classes in the spatial library.

I did not spend much time on discussing the details of deploying your spatially-enabled .NET application to other machines (client or server) since I expected it to be like any other .NET application: Just copy the EXE along with any dependent assemblies to the target machine and run, of if motivated, create a simple setup project, or possibly use Publish to create a click-once installer.

The best laid plans of…

It turns out that deployment of a spatially-enabled .NET application onto a “clean” target machine has a potential *gotcha*.

Taking the example of the simple .NET console application from the previous blog, there are several expected pre-requisites necessary on the target machine in order for the application to run successfully:

• .NET Framework: that one is pretty obvious given my console application was a .NET application.
• SQL Server Types: Running this installer will install and register the SQL Types assembly in the GAC.  Alternatively you could place the SQL types assembly as a side-by-side assembly to your application.

It turns out that you will also need to make sure the Visual C++ 2008 C runtime libraries are also installed on that target machine.

The spatial data types make use of both managed code (.NET) and native code to speed up spatial calculations and as a result the spatial types make calls to some C runtime library functions. 

If these C runtime libraries are not installed on your target machine you will get the following exception when you run your application:

You can find the 32bit version of the VC++ redistributables here and the 64bit version here.  Run the appropriate version for your target environment and that should resolve the problem. 

Alternatively build an installer for your application that installs the C runtime libraries as part of the installation of your application (note that I did not try this, so let me know if you run into any problems).

You can read more about this scenario on the Microsoft support site here.

Happy coding.

Olivier

Why I should have paid more attention in geometry class: Embedding the new SQL Server Spatial CLR types and functionality directly into your own .NET applications.

In SQL Server 2008 we introduced the ability to store spatial objects as columns within a database through the Geometry and Geography types as well as the ability to perform efficient query operations against those columns through the use of a new Spatial index.

These data types were implemented as CLR (.NET) types hosted by SQL Server and a great benefit of this approach is that enabled the SQL team to make these same .NET assemblies are available for download and distribution “outside of SQL Server” for use in any .NET application.  

You can download the SQL CLR Types here.

Once you have downloaded and installed the SQL types on your development machine, you can make them available to your .NET applications by adding a reference to the assembly within Visual Studio your project.

Let’s try it with a simple “hello world” console application that will create a couple of simple geometric shapes and calculate some simple geometric operations like area and intersections.

From Visual Studio, select File->New Project and select the Console Application project type.

Once your new project is loaded, you will need to add a reference to the SQL Server types assembly.  Right click on the References node in your Solution Explorer and Choose Add New Reference.

Find the Microsoft.SqlServer.Types .NET component and add it to your project. 

Now that the project is set up to use the Spatial data types, let’s try out a simple set of spatial operations.   

   1:  using Microsoft.SqlServer.Types;

   2:   

   3:  class Program

   4:  {

   5:      static void Main(string[] args)

   6:      {

   7:          // Build a geometry object from Well Known TextSqlGeometry

   8:           a = SqlGeometry.Parse("POLYGON ((0 0, 10 0, 10 10, 0 0))");

   9:   

  10:          // Calculate and display its area

  11:          System.Console.WriteLine("Area of A = " + a.STArea().Value.ToString());

  12:   

  13:          // Create a buffered version of the original

  14:          SqlGeometry b = a.STBuffer(1);

  15:   

  16:          // Calculate and display its area

  17:          System.Console.WriteLine("Area of B = " + b.STArea().Value.ToString());

  18:   

  19:          // Create the shape that represents the difference of the two shapes

  20:          SqlGeometry c = b.STDifference(a);

  21:   

  22:          // Show its area

  23:          System.Console.WriteLine("Area of B-A = " + c.STArea().Value.ToString());Console.ReadLine();

  24:      }

  25:  }

We start by creating a very simple shape, a triangle (line 8) by converting a text representation of the shape into a SqlGeometry object.   The text representation used by the SQL Spatial type library is WKT (well known text).

Next we buffer the triangle (line 14) by one unit all around, which means “grow” the shape around its border by one unit.  Buffering can be useful when used in conjunction with other operators to find out if shapes border each other or to create more complex shapes that represent shapes with holes.

Now we create a more complex shape, by geometrically “subtracting” the original triangle from the buffered triangle (line 20), resulting in a shape that represents the one unit border around the original shape.

We can now verify that these operations resulted in shapes that are in fact complements of each other by calculating the areas of the individual shapes and comparing the area of the complex border shape with the areas of the original triangle and its buffered version (lines 11, 17 and 23).

A question you may be asking is can one start from a non-textual representation of a shape?  Can I build the shapes in code?

Yes you can in fact create shapes “by hand” using builder objects (SqlGeometryBuilder and SqlGeographyBuilder) exposed by both the SqlGeometry and the SqlGeography data types.

Here is a simple example that builds a triangle by hand:

   1:     class Program

   2:      {

   3:          static void Main(string[] args)

   4:          {

   5:              // Create instance of a geometry builder

   6:              SqlGeometryBuilder geoBuilder = new SqlGeometryBuilder();

   7:   

   8:              // Spatial objects have a reference ID / reference system

   9:              // which identifies the type of projection (or lack of)

  10:              // used, for example WGS84 - SRID = 4326 

  11:              geoBuilder.SetSrid(4326);

  12:   

  13:              // Building a simple polygon

  14:              geoBuilder.BeginGeometry(OpenGisGeometryType.Polygon);

  15:   

  16:              // Start the shape at 0,0 and add each line segment for the

  17:              // triangle (0,0 - 0,10) - (0,10 - 10,10) - (10,10 - 0,0)

  18:              geoBuilder.BeginFigure(0, 0);

  19:              geoBuilder.AddLine(0, 10);

  20:              geoBuilder.AddLine(10, 10);

  21:              geoBuilder.AddLine(0, 0);

  22:              geoBuilder.EndFigure();

  23:   

  24:              // We're done, this polygon has one figure.

  25:              geoBuilder.EndGeometry();

  26:   

  27:              // retrieve the constructed geometry

  28:              SqlGeometry geo = geoBuilder.ConstructedGeometry;

  29:   

  30:              Console.WriteLine("The new shape's WKT:" + geo.ToString());

  31:              Console.WriteLine("and it's area: " + geo.STArea().Value.ToString());

  32:   

  33:              // Wait

  34:              Console.ReadLine();

  35:          }

You are probably thinking to yourself, that’s neat, it had been a long time since I had thought about geometry, but Olivier how do I use this in my day job?  

Agreed, these were very simple examples intended to show you how to get started. 

You now have at your disposal a set of industry standard operations as well as extension methods that allow for complex calculations and operations against complex geometric shapes and a similar set of operations for geographic shapes. 

So while calculating the area of a triangle seems pretty simple, what if that shape was a state and you were asked to calculate the area of a state, excluding all water areas?   Remember that Minnesota is the land of 10,000 lakes.  

The spatial library can handle complex shapes made up of 100’s of 1000’s of vertices, including shapes that have “holes”.

What if you were collecting the locations for the current instances of the bird flu reported across different regions and wanted to quickly calculate an aggregate shape that encompasses those points for clustering analysis (ConvexHull)? 

What if you wanted to efficiently compare the current location for trucks within your fleet against a known “good” path for a route to better track fuel utilization or compliance (STIntersects)?

These are some of the types of problems that the SQL Server Spatial types can assist with.

Can I extend the existing SqlGeometry and SqlGeography types?

The classes are sealed, but you can use the new .NET extension methods to make it look like you have extended the existing data types.

In my next blog posting I’ll show some examples of using this extension mechanism to provide integration with Bing Maps Web Services as well as provide ways to aggregate and disaggregate complex shapes.

So what can I do with this new knowledge?

With the basics out of the way there are a series of business problems that you could look to leverage the libraries and SQL Server for:

• Location based services like asset tracking and employee tracking.  For example having mobile devices or laptops report their location through posts against a .NET web service or Windows Azure Web Service application and using the library to quickly calculate the proximity or relationships between assets and employees.
• Combine the SQL CLR types with other Microsoft services like Bing Maps and Mappoint web services to perform Geocoding and analysis within your .NETapplication.
• Analysis of real estate opportunities by combining in-house data, commercial data and census or government data.
• Building extensions and utilities to make working with spatial data easier and more efficient. 
• Many of the spatial goodies and tools that have been made available on CodePlex were written using these builder APIs and the .NET types.   You can download and try these add-ons from CodePlex here.
• Modeling and analysis of floor plans
• <Your App Here> – Let me know what unique applications you find for using the new SQL Server Spatial Types in your own application.

Have a great week,

Olivier

Calling all web developers: The SQL Server Team needs your help.

The Microsoft SQL Server product team is undertaking a short survey to help guide potential new features and capabilities for web developers, please take a few minutes to take the survey and help shape the future of SQL Server. 

Click this link to take the survey.

Thank you for your time and feedback.

Olivier

Videos from Tech-Ed: Building Location-Aware Services with SQL Server 2008 Spatial - WiE Demo and Code Walkthroughs

It has been way too long since I’ve blogged.   I have no (good) excuse.

I had the opportunity to attend and present at last week’s Tech-Ed conference and had a great time meeting customers and getting feedback and suggestions on SQL server and our developer tools.

Our session on WiE (Building location-aware services using SQL Server 2008)  was recorded and is available on the Tech-Ed web site for viewing by those who attended Tech-Ed.  Please visit the Tech-Ed Online to view any of the sessions from this year’s Tech-Ed.

Unfortunately I am not allowed to post the entire recording of the session directly on my blog.  That said, like any good database guy, I have a backup plan.. I had made some recordings of the demos before the show as a backup in case the demo machine failed during the show.   These are posted as part of this blog article (hoping that the video quality survives the posting process). 

The videos will provide some additional help and context for those folks who are reading the previous blog articles on WiE and downloading the source code from our CodePlex project site.

The Demo

Demo

Code Walkthrough of Mobile Client

Code walkthrough of mobile client.

Code Walkthrough of Spatial Trigger

Code Walkthrough of Spatial Trigger.

Please let me know if you find these types of videos useful, if so I’ll try to keep building some to accompany future blog posts.

In my next blog post I will be showing some examples of using the SQL Server spatial types “outside” of SQL Server.  The spatial geography and geometry types are in fact .NET CLR types and are available as a redistributable component for you to use in your own .NET applications.

In the coming months I also plan to start a series of articles on using our new Velocity data caching technology and using it to help build scalable web applications.

Sincerely,

Olivier

Happy New Year!

Just a quick post to wish folks a happy new year.  

Next week will mark my first year at Microsoft.   While I still need to work on my resolutions for 2009 (healthy eating, exercise,... you know the stuff that usually only lasts a week or so), I do have some initial areas of focus and topics for the blog next year:

• SQL Server Spatial and Mobile Development
• Fostering CodePlex project for WiE and taking WiE to 2.0
• ORM and related developments
• Developer productivity topics

Do you have any topics you'd like me to cover for the 2009 edition of the blog?  Please post comment or e-mail.

Have a fun and safe new year's eve and a great new year.

See you in 2009.

Olivier

WiE project and source code posted to CodePlex!

Good Afternoon,

I’ve just completed posting the WiE community project to CodePlex.  As this is my first CodePlex project I expect that I may have forgotten some steps so please let me know of any issues you experience and I will work to resolve them quickly.

You can find the project at: http://www.codeplex.com/wie

In the coming weeks I will put together some documentation for developers interested in and willing to contribute to the CodePlex project and turn this sample into a full fledged location-based social networking application.  

Until then you can refer to previous blog articles for a review of the design and implementation of the current WiE release:

• Part 1: Introduction
• Part 2: The WiE Mobile Client
• Part 3: Implementing the SQL Server Compact Model for Mobile Client
• Part 4: Implementing the SQL Data Services data Model for the Mobile Client
• Part 5: Synchronizing from Compact to SQL Data Services
• Part 6: Visualizing location information using new Virtual Earth ASP.Net Server Control
• Part 7: Using SQL Server 2008 Spatial to implement the rules for Location-base service
• Part 8: Using SSIS to Synchronize between SQL Data Services and SQL Server 2008

Here is a very high level architecture diagram of the components and underlying technologies that make up the current release of WiE.

Have a great holiday and a wonderful new year.

Olivier

PASS Summit 2008 WiE PPT presentation is now posted.

Just a quick post to let you know that you can now access and download the WiE Powerpoint presentation from the PASS Summit 2008 web site.

http://www.softconference.com/pass/sessionDetail.asp?SID=135520

I believe SQLPass will also post the actual recording of our session at this same link in the coming weeks. 

Olivier

Part VIII: WiE uses SQL Server Integration Services (SSIS) to synchronize SQL Data Services (SDS) with SQL Server 2008.

In the previous article we covered using the use of INSERT triggers along with the new Spatial data-type support in SQL Server 2008 to implement “spatial rules” for our location-based service.  All that remained was to implement a mechanism to make all the location data hosted by SQL Data Services visible SQL Server 2008.  While there are several options available, including building a custom application to synchronize the two data stores, I chose to leverage SSIS and the SSIS providers for SQL Data Services found on CodePlex.  This article will walk you through the process of creating an SSIS package and using the SDS Providers for SSIS.

Our Synchronization Requirements

Our requirements are relatively simple:  We would like to retrieve all the “recently” collected location records from SQL Data Services and  have them inserted (in first-in first-out order) into our SQL Server 2008 tabDeviceLocationHistoryTable.  The inserts will cause the evaluation of an INSERT trigger that implements our location-based service logic.

The definition of “recently” is interesting and I chose to implement this rule as anything-newer-than-the-most-recently-synchronized-location-record.   It is a valid concern that timestamps have issues when determining what has not yet been synchronized and you could add some buffer to minimize the risks.  Keep in mind that our timestamps are acquired from the GPS data which hopefully is somewhat more accurate than the individual clocks on each device.

Install the SSIS Providers for SDS

There is a CodePlex project that provides a set of SQL Data Services components for SSIS including an SDS Source, SDS Target and SDS Connection provider.  These are the key components required for data flow tasks in SSIS.   To download these components visit the project site.

Note: In the process of implementing the solution we found a bug in the SSIS provider around conversion of floating point numbers, this should now be fixed and new version uploaded by the time you read this article.  Another enhancement made to the original SSIS Source provider was to add the ability to specify a filter (where clause) for the retrieval of entities from the container.  This modification should also be available from CodePlex by the time you read this article.

Creating the WiESyncSSDS Package

Launch Visual Studio (VS) or Business Intelligence Development Studio (BIDS) and create a new Integration Services Project. 

Adding connection managers to the project:

Our SSIS Package will contain flows that take data from SQL Data Services (Source) and convert and store that data in SQL Server (Target).   We need to define connection managers to our Source and Targets.

Add a connection manager for SDS

1.       Right click the [Connection Managers] area of the SSIS Package designer and choose “New Connection…”   

2.       From the list select [SSDS Connection manager].

3.       Name your connection “SSDS WiE Connection” and specify your SDS credentials and the name of your SDS authority.

Add a connection manager for SQL Server

1.       Right click the [Connection Managers] area of the SSIS Package designer and choose “New OLEDB Connection”.  

2.       Specify the connection information to your SQL Server 2008 server and the SQL WiE Database.

3.       Name your connection “SQL WiE Connection”

Defining User Variables:

Our package will need a variable to hold the date and time of the most recent location record in the SQL Server database.

1.       Right click the control flow designer surface and select “Variables” from the menu.

2.       A Variables list will appear showing system and user defined variables.

3.       Click the [Add Variable] button (x with a gold star)

4.       Name the new variable dtMostRecentLocation and set its type to DateTime.

Defining the Control Flow:

An SSIS Package is made up of a control flow made up of one or more control flow items.  SSIS supports a variety of control flow items including running scripts, transferring files with FTP and most importantly Data Flow tasks that facilitate the movement of data from a source to a destination.

Our control flow will consist of a series of data flow tasks:

1.       A data flow to synchronize the member information from SDS to SQL

2.       A data flow to synchronize the device information from SDS to SQL

3.       A data flow to synchronize the location information from SDS to SQL

We also want to make sure to synchronize the member and device information before the location information to ensure we do not violate any possible referential integrity rules we have relating members to devices and devices to locations.

Create flow for synchronizing members

1.       Drop a new [Sequence Container] onto your control flow designer surface and name it “Synchronize Member Information”

2.       Within the sequence container, drop a Data Flow Task and name it “Import Member Information”

Create flow for synchronizing devices

1.       Drop a new [Sequence Container] onto your control flow designer surface and name it “Synchronize Device Information”

2.       Within the sequence container, drop a new [Data Flow Task] and name it “Import Device Information”.

3.       Drag line from the [Synchronize Member Information] sequence container to the [Synchronize Device Information] sequence container and set its precedence constraint to “Completion” to ensure we synchronize members before synchronizing devices.

Create flow for synchronizing locations

1.       Drop a new [Sequence Container] onto your control flow designer surface and name it “Synchronize Location History”.

2.       Drag line from the [Synchronize Device Information] sequence container to the [Synchronize Location Information] sequence container and set its precedence constraint to “Completion” to ensure we synchronize devices before synchronizing locations.

3.       Drop a new [Execute SQL Task] within the sequence container and name it “Lookup the most recently loaded location record”. 

This task will be used to query the database for the most recent location from SQL and will set the [dtMostRecentLocation] variable so it can be used by the data flows to query SDS appropriately.

4.       Drop a new [Data Flow Task] within the sequence container and name it “Import Location History”. 

5.       Drag line from the [Execute SQL Task] to the [Data Flow Task] and set its precedence constraint to “Completion”.  This will ensure that the data flow task is only executed after the query for the most recent location record.

Implement the [Look up the most recently loaded location record] Execute SQL Task

1.       Double click the [Look up the most recently loaded location record] task you created above.

2.       Set its SQL Statement to “SELECT MAX(dtTimeCollected) AS dtMostRecentLocation FROM tabDeviceLocationHistory”

3.       Add a new Result to the Result Set tab and set the variable name to “User::dtMostRecentLocation”

The completed control flow should look like the following picture:

Defining the Data Flows:

Now that we have the shell for the control flow defined, we need to fill in the details of each data flow task.   Each data flow will have a SDS Source item used to query the SDS container for the appropriate entities, a series of transformations and an OLE DB Task used to call the appropriate stored procedure to store each record.

Create Import Member Information Data Flow

1.       Double click the Import Member Information data flow on the control flow designer.  This will switch the design surface to a data flow designer.

2.       Drop an SSDS Source data source on the data flow designer surface.

Note: You may need to add the SSDS Source to your toolbox.  Right click on the Data Flow Sources header in the toolbox and choose “Choose Items…” Click the [SSIS Data Flow Items] tab and select the SSDS Destination and SSDS Source items.

3.       Set the connection manager for the SSDS Source to the [SSDS WiE Connection] connection manager you created earlier.

4.       Under [Component Properties] set the ContainerID to “wie”, set the EntityKind to “member” and set the preview count to”1”.

Note: SDS doesn’t have the concept of a schema since it supports flexible entities.  The SSIS provider uses the preview count to retrieve some entities and infer a schema based on the entities that were returned.  It is important to set the preview count to a number that will result in enough entities to get the fullest picture of the properties associated with a Kind of entity.

5.       Add a [Derived Column Transformation] task to the data flow.  The SDS Source provider will return MemberID as a string and we will want to convert it to a GUID.  Unfortunately SSIS doesn’t recognize GUID strings that are missing the “{ }” symbols and so we use the derived column transformation to add the brackets to the string.

6.       Add a [Data Conversion Transformation] task to the data flow to convert the GUID strings to GUID data type.

7.       Finally add an [OLE DB Command] task to the data flow which will be used to call the dbo.sp_SaveMember stored procedure to insert or update member information in SQL Server for each member entity from the data flow.  Set the SqlCommand component property to “dbo.sp_SaveMember ?,?,?,?,?,?,?” and map the columns to the parameters for the stored procedure:

Create Import Device Information Data Flow

1.       Repeat steps 1 to 4 from above but set the EntityKind for the SSDS Source to “device”.

2.       Repeat step 5 to 6 but apply the brackets to the “DeviceID” column.

3.       Repeat step 7 but call the dbo.sp_SaveDevice stored procedure.

Create Import Location Information Data Flow

1.       Repeat steps 1 to 4 from above, but set the EntityKind for the SSDS Source to “location”.

2.       Repeat steps 5 to 6 but apply the brackets logic to three columns: “MemberID”, “DeviceID” and “LocationID”.

3.       We need to ensure that the data gets inserted in “first in first out” order, we do this by Adding a [Sort] task to the data flow, sorting on the DateCollected field in Ascending order.

4.       We now need to set up the Query parameter for our SSDS Source to ensure we only query for the “newly added” location entities, that is those location entities whose DateCollected is newer than the most recent location record in SQL Server database:

a.       Right click the “Import Location History” task from the Control Flow and choose “Properties”

b.      Scroll the properties down to “Expressions” and click the […] button.

c.       Set the property for [SDS Location Data].[Query] to the following:          

“e[\”DateCollected\”]>DateTime(\”” + (DT_WSTR,25) @[user::dtMostRecentLocation] + “\”)

5.       Repeat step 7 from above but call the dbo.sp_SaveDeviceLocationHistory stored procedure.

Deploy and Schedule the WiESyncSSDS Package to SQL Server

Once you have tested and debugged the SSIS package within Visual Studio you are ready to deploy the SSIS package to your SQL Server and to schedule the package as a job.  With the SSIS Package open within Visual Studio, choose File->Save As a Copy… which will present you with a dialog box to choose the destination for the copy.   Select the SQL Server or file system location where you would like to store the package for deployment and choose the level of protection you would like for the username and passwords associated with your connections.

Once you have deployed the package to your server, create a new SQL Agent job with a single SSIS step to execute the package.  Schedule that job so that it runs every few minutes.  

Note: You may run into some security issues depending on the security account used for SQL Agent.  Make sure that the job runs under credentials that have access permissions to your SQL Server instance and the WiE Database if using Windows Authentication.

WiE was unveiled at PASS Conference last week

I will post out PASS presentation online as well as all the code for the WiE Project on our CodePlex site in the coming week.     I look forward to your feedback, modifications and your use of the code for your own projects.

Sincerely,

Olivier

Part VII - WiE Goes Spatial: Using SQL Server 2008 as a Spatial Rules Engine for Location-based Services.

In this second to last chapter in the series I’ll discuss leveraging SQL Server 2008’s support for spatial data types to implement the “spatial rules” for the WiE Community.    Specifically, this article will implement 2 examples of simple proximity rules that drive most location-based service scenarios:

-          Proximity to a Point of Interest (fixed)

-          Proximity to other members (dynamic)

I’ll also touch on an approach to synchronizing data to and from SQL Data Services (SDS – Formerly known as SSDS) using SQL Server Integration Services (SSIS).

Don’t PASS this up!

Ed Katibah and I will be presenting SQL Server Spatial and WiE in a couple weeks at this year’s PASS Conference .  We look forward to seeing you there, please stop by and share your experiences with Spatial, SQL Compact, and Mobile development.

An Introduction to SQL Server Spatial Data types

There are two related data types associated with the new spatial support in SQL Server 2008.  These are the GEOMETRY and GEOGRAPHY data types.  The GEOMETRY data type maps data on a two dimensional plane (x & y coordinates) while the GEOGRAPHY data type deals with the fact that the earth is not flat (nor is it a perfect sphere) by dealing with data on a geodetic plane.

The most visible difference is in specifying positions.  The GEOGRAPHY data type expects positions specified using latitudes and longitudes while with the GEOMETRY data type specifies them as X, Y coordinates.  

The Shape of Things…

The SQL Server spatial data types allow you to represent any shape that can live on a plane.  These shapes include POINTs, MULTIPOINTs (a collection of points), LINESTRINGs (continuous line), POLYGONs and GEOMETRYCOLLECTIONs (collection of shapes). 

SQL Server stores these shapes internally as binary data but they are represented using a well known text (WKT) representation.  You can learn more about WKT at http://www.opengeospatial.org.

Both the GEOMETRY and the GEOGRAPHY data types offer the same functions for operating over spatial data:

You’re in my personal bubble: Buffers…

One important concept that we will leverage is the ability to create buffers (STBuffer) around shapes (buffers in turn become shapes).  Buffers allow you to create borders of a certain distance around a shape.  This capability allows you to ask questions akin to “is anything within 1 mile of my current location?”

To learn more about Spatial...

For a more substantive introduction to Spatial data types please refer to Isaac Kunen’s blog and to Ed Katibah’s blog.

Implementing the SQL Server Device Location History Table

Now that we have the basic concepts for spatial down, we’ll go ahead and create a new SQL Server database that will “mirror” the data we collect from the WiE Mobile Client and store in SQL Data Services.   We need to bring the data stored in SQL Data Services over to SQL Server in order to apply our spatial rules. 

The key tables are the similar to those we implemented for the SQL Compact database (See Part III) and for the entities in SQL Data Services (See Part IV):

One key change will be the use of the GEOGRAPHY POINT type in tabDeviceLocationHistory table to represent the member device’s location rather than using two floats to keep track of longitude and latitude.

I mentioned earlier that we will look at two examples, the first of these rules being finding the proximity of a member’s current location to a list of well known points of interests.  For this example we leveraged a database of known points of interest (unfortunately I can’t share that database).  For your own solutions you would likely have a table or collection of POI specific to your application or enterprise.  

Here is a subset of the schema for a POI table:

As an optimization for querying the most recent location of a member we augment the tabDevice table to store the “most recent” location for a device and we will keep this information up to date using a trigger on the tabDeviceLocationHistory table. 

For completeness here is the tabMember table:

SQL Server 2008 Triggers + Spatial = Rules Engine

Now that we have our tables defined we need some event to wake us up to evaluate our spatial rules.  I presume that most of you are already familiar with creating triggers on tables for the purposes of evaluating validation logic or simple data cleansing.  I’ll be leveraging triggers to implement our “business logic” for spatial rules.

I am only interested in looking at newly inserted location records, so we’ll create an INSERT trigger on the tabDeviceLocationHistory table:

We now have the “plumbing” in place to evaluate some rules whenever a new location record is inserted into the tabDeviceLocationHistory table.

Rule #1: Let me know of Points of Interest near my current location

In Spatial-Speak: Create a buffer around my current location of 1,000 meters and tell me all the points of interest that intersect my buffer, select the first

Rule #2: Let me know if other members are near my current location

This rule is very similar to rule #1, there is however one additional complexity in that while POI are fixed other members tend to move around, so it becomes important to take the “current time” into account when comparing location between members.

In Spatial-Speak: Create a buffer around my current location of 1,000 meters and query the location history table for all locations from other members that occurred around the same time as this location that intersect the buffer.

We now leverage the INSERT trigger to update the most recent location information before evaluating the rule.

With this change we can be fairly certain that the tabDevice table will have the most recent location information for each device and we can go ahead and use that table in our rule implementation.

Notifying Members of their Spatial Results

Now that we have implemented the rules and have retrieved the POI and closest member we use DBMail to generate an e-mail alert.   The member table holds the e-mail address for each member,

Using SSIS to synchronize data from SDS to SQL Server

We’ve already covered one synchronization mechanism in Part V of the series.  We could have used a similar approach to synchronize data from SQL Data Services (SDS) to SQL Server by building an IWiEModel implementation that “speaks SQL” and synchronizing between the SDS Model implementation and the SQL Server implementation. 

I however chose a slightly different approach in order to illustrate some of the flexibility and options available from the Microsoft stack and hopefully add a couple tools to your toolbox.  I chose to attempt to build a simple synchronization workflow using SSIS.  SQL Server Integration Services provides a very complete and powerful environment for building ETL processes and in some ways synchronization is really an ETL process.   

SQL Data Services Providers for SSIS

There is a CodePlex project that provides a set of SQL Data Services components for SSIS including an SDS Source, SDS Target and SDS Connection provider.  These are the key components required for data flow tasks in SSIS.   To download these components visit the project site.

What’s Next

In next week’s article I’ll cover the implementation of the SSIS package that leverages the SDS SSIS providers to build a simple synchronization mechanism between SDS and SQL Server.  The SSIS package will query SDS, retrieve all newly inserted locations from SDS and then insert them into the SQL Server databases (in turn triggering the spatial rules discussed in this week's article). 

Have a great week, and see you at PASS.

Olivier

Part VI – Visualizing WiE location information using the new server-side Virtual Earth ASP.Net Map Control

In the previous article of this series I mentioned that we would next discuss how to visualize the GPS information we’ve been collecting and storing in SSDS.   This article looks at using Virtual Earth to visualize the location history for members. 

For this implementation I chose to try out the brand new ASP.Net Virtual Earth server-side control.  In the past developers looking to integrate with Virtual Earth had to be pretty knowledgeable about JavaScript.  While JavaScript is pretty powerful and it does let you do some pretty cool things on the client side it can be a bear to work with and debug.    The Virtual Earth ASP.Net control lets ASP.Net developers work in c# on the server side without the need to resort to JavaScript.  That said, the use of the server-side control does not prevent the use of client side interactions.  You can in fact use the server-side map control in conjunction with the client side interactivity options afforded by the JavaScript based Virtual Earth SDK.

Download and install the new ASP.Net Map Control here: http://dev.live.com/tools/

Quick Refresh about SSDS

In Part IV of the series we discussed the new SSDS (SQL Server Data Services) and the approach for querying and storing entities into an SSDS container.   That article focused on accessing SSDS from the mobile client, but the steps for accessing SSDS from an ASP.Net application are very similar.  You add a web service reference to your web project and generate a web service proxy that you will use to make calls against the SSDS service.   Please refer to Part IV for details.

Note: One area of difference is that you are able to use WCF for connecting to the web service in an ASP.Net application whereas the mobile platform has limited support for WCF.

Assuming we have infrastructure in place to make calls to the SSDS web service, we now need to add and implement a set of methods on our Model (data access layer) that will allow the retrieval of lists of members and the location history for a member.

Querying SSDS for List of Members

For simplicity this will retrieve the entire membership.  In reality, we would want to restrict a web site visitor to only seeing his or her “friends” rather than the entire membership.  

Note: In the current beta release of SSDS, joins are not directly supported, so I’ve deferred implementing the “friends” logic until later (although it is fairly simple to simulate a join and I will likely do this in a later article.)

GetMembers()

Returns all the members of the community as a list of WiEMemberDataObjects.

Querying SSDS for the Location History of a Member

QueryLocationHistoryForMemberBetween(memberID,startDateTimeUTC,endDateTimeUTC)

Queries the SSDS container for all the location history for the specified member that occurred between the start and end times and returns a list of WiELocationDataObjects sorted by DateCollected in descending order (most recent location first).

Note: The current beta release of SSDS does not yet support sorting, so we implement sorting using the sort capabilities included in the .Net collection classes.  Here is what the comparison function for sorting by date collected on a WiELocationDataObject looks like:

QueryForLocationHistory(queryString,sortComparisonFunction)

This is a helper method that supports all the GetLocationHistoryXX() methods.  It takes a properly formatted SSDS query along with a comparison function to sort the results of the query.

Using the Virtual Earth ASP.Net Map Control

I’m going to assume you already know how to create a new ASP.Net web project using Visual Studio and have installed the Virtual Earth ASP.Net Map control referenced earlier in the article.   I hope that is a fair assumption, please let me know if you’d like me to detail the process of creating a web application.

After creating a new web project, open the Default.aspx page and place an instance of the Map Control and name it m_mainMap.   The ASP.Net VE Map Control relies on the script manager component and you will need to place a ScriptManager control on the page as well.

In addition to the map and script manager controls I will also add a couple more controls: a radio button list to allow the user to pick a timeframe to display (current, last 15 minutes or last 24 hours) and a checkbox list to allow the user to pick one or more members to display.   Finally I add a button [Refresh] with a server side handler for the button press to trigger the handling of plotting the members’ location history.

Note: I wrap the selection and button controls within an ASP.Net UpdatePanel to enable partial rendering of the page and minimize the visual effect of refreshing the entire page.

Here is a subset of the page:

In the code behind class for Default.aspx we will implement a method to plot a member’s location history on the map.  

Virtual Earth supports adding “Shapes” to a map.  There are three types of shapes supported: PushPins (point), PolyLines (open) and Polygons (closed).  I will use PushPins to highlight a single location or the final point in a segment and will use a polyline to show the “trail” or history of locations for a segment of time.

The code to add a new shape to a map is pretty simple:

Adding a PushPin to a Map:

The PushPin shape is useful to visualize a single point or known location.   You specify the location of the PushPin using longitude and latitude through an VE object called LatLongWithAltitude.   Also VE allows you to specify your own icons rather than use the standard PushPin but for now I will stick with the built in PushPin icon.

Adding a PolyLine to a Map:

A PolyLine is initialized by passing in a list of points specified by LatLongWithAltitude objects.   PolyLines can also have a pushpin associated with them and the placement of the PushPin can be controlled using the IconAnchor property.  

Descriptions for PushPins

The description associated with a PushPin can be any valid HTML and is shown when the user places the mouse cursor over the PushPin on a map.   I use the description to show some aggregate and status information about the member, for example the peak speed during the period and the member’s name and contact information.  

The following function builds an HTML table to show summary information for the member and the member’s location history:

BuildMemberLocationHistoryDescription(member,locationHistory)

Putting it all together

Now that we have the basics covered, let’s put them together to implement a full “historical” visualization for a member, showing the history as line segments and pushpins for end points.

To make the visualization more interesting, I break up the history into segments to capture the times when a member may have stopped tracking his or her location and also show some interesting tidbits of information like the peak speed during that period of time.  Of course you could do a number of other interesting things like generate a graph of the speed the member travelled over the journey, or highlight points of interests that were nearby (future article).

Here is the current implementation of showing the location history for a selected member.  

AddFriendToMap(memberID)

What’s Next?

In the next set of articles we will start implementing the “spatial rules engine” using SQL Server 2008 that will notify members of the proximity of other friends and points of interests.

Have a great week,

Olivier

A blue shirt in a sea of red and black shirts... my trip to Oracle Open World.

Good morning,

It has been much too long since I last wrote to the blog.   Things here have been busy, but that's no excuse. I hope to get back on track with writing regularly to the blog.  I will be continuing the series on the WiE community and will be presenting some of the concepts from WiE at the upcoming PASS conference in November.

I just returned from two weeks of travel, the first week was spent visiting customers in Michigan and I spent last week at the Oracle Open World conference.   Many of our customers run both Oracle and Microsoft products together and it was really valuable to hear directly from them on the strengths and weaknesses that they found in each product offering and the areas for improvement around integration of the two stacks.

On the subject of Oracle Open World: Microsoft had a great location at this year's show. Our booth was located right next to the Oracle "Demo Grounds" where Oracle demoed their database wares including Spatial, new Modeling tools, and Oracle Enterprise Manager.   The Oracle folks wore black and red polo shirts and the Microsoft folks wore blue dressed shirts, so it was pretty noticeable when I would go into the Oracle demo grounds area to learn more about the new offerings from Oracle.   That said, the Oracle team members were very nice and it was great to have discussions with our counterparts.

Oracle made several announcements at last week's show, but the "biggest" announcement was made by Larry Ellison and that was that Oracle was entering the hardware market by shipping a "database appliance" in partnership with HP.   This an interesting announcement, and the box did look cool.   The approach Oracle is taking is pretty interesting, by moving the database query processing "closer" to the disk storage they expect to achieve significant improvements in query processing time.   I wonder how and if that decision will impact Oracle's partnerships with SAN vendors, other server manufacturers and their customers who have already made investments in storage infrastructure.   Microsoft had exciting news of its own in this space a few weeks earlier with the acquisition of DATAllegro.     I'd love to hear if any of you have started investigating either of these solutions, please share your experiences around these products and get your thoughts about buying "Oracle Hardware".

Oracle also announced a new collaboration and messaging server named "Beehive".  Of course they played off that name for most of the show, dressing their engineers in yellow and black striped shirts and calling the management tool beekeeper.   I did not get a chance to take a look at the product, but it appeared to have good integration with Microsoft Outlook, Microsoft Exchange as well as integration with digital rights management. 

Oracle demonstrated an upcoming modeling tool targeted at Database Architects and Developers.  The tool lets architects model at the conceptual level and then hand off the conceptual models to developers and DBAs to develop the relational and the physical models.   The tools seemed well thought out and enabled DDL generation to target different environments (i.e. different physical models), including SQL Server.     We have some exciting technologies of our own related to conceptual modeling with our Entity Data Model and Entity Framework.   If you have not checked these out I strongly recommend that you do so.    Also check out ADO.Net Data Services which will turn your Entity Data Model into a RESTful web service with just a few clicks of a mouse.

I spent some time learning more about Oracle Enterprise Manager at the conference as it is the main tool used by DBAs running Oracle environments.  There were no major new announcements about OEM at the show but there were some interesting things and hints about OEM and OEM Grid Control.  OEM Grid Control is Oracle's offering for end to end management: from the application to the bare metal (they called it the "red stack") of multiple servers.  This is an area where have gotten feedback from customers but I could definitely use more of it.  

I am curious if any of you are currently using OEM and OEM Grid Control.  Have you used it in conjunction with SQL Server (Oracle offers a plug in for managing SQL Server through OEM) and what has your experience been?  What features would you like to see in SQL Server's own management tools based on your experience?  Let me know how can we better support DBAs that manage both environments.

I genuinely enjoyed attending the show and the customer visits from the previous week.  It provided a fuller picture of the heterogeneous environments our customers and their DBAs have to manage and the tools and options they have to choose from, manage and integrate together.  

Have a great week.

Olivier

Part V: Keeping WiE Mobile Client in sync with the Cloud, a first pass at synchronization.

The Implementation of WiEModelWithLocalCache

As we discussed in the previous articles, the data layer for the WiE Mobile Client is made up of “providers” that each implement the IWiEModel interface and work together to implement support for the occasionally disconnected mobile scenario.  Over the past two weeks article we covered the implementation of the local data store: WiELocalModel and of the remote data store WiERemoteModelSSDSForMobile.   In this article we combine both along with a simple synchronization mechanism to implement the complete occasionally connected data store.

As a refresher, the IWiEModel interface is shown below:

Implementing the WiEModelWithLocalCache class

The WiEModelWithLocalCache “front ends” the two previously discussed implementations of the model interface and routes calls to the appropriate provider, which in most cases is the Local Provider, relying on a separate synchronization agent to forward those requests at a later time to the remote provider. 

Initialize()

The Initialize() method is called by the application to initialize its model.  The WiEModelWithLocalCache leverages that call to create instances of the local and remote providers and the sync provider.

The class is then responsible for implementing the various SaveXXX() and GetXXX() methods expected from a model, while I won’t show every method I will highlight a couple so you an idea of the logic. 

The model attempts to perform operations against what it considers the “master” for a piece of data, if that source is unavailable it falls back to the secondary store.   The definition of “master” depends of the type of data: For location records, the local store is queried first; for member information, the remote store is attempted first.

GetLocation(Guid p_guidLocationID)

SaveLocation(WiELocationDataObject p_locationDataObject)

SaveLocation saves location records to the local model (SQL Compact based data store) and expects the synchronization mechanism to move those records to the remote model when appropriate.

GetMember(Guid p_guidMemberID)

GetMember assumes that the “best source” for information related to members is the remote model ( SSDS data store) and attempts to query it for that data and falls back to using the local version if the remote version is unavailable.

The WiEModelSynch class

The current implementation of WiEModelSynch implements a very limited set of synchronizations:   specifically the synchronization of the locally stored Location History with the remote data model. 

In the very near future the client will need to implement bi-directional synchronization and synchronization of additional data types.  I plan to implement this upcoming full implementation using the Microsoft Sync Framework and sync services for mobile devices.  I was hoping to have that implementation completed for this article but it still needs a little TLC and I am waiting for the next CTP of the mobile sync framework.  The net result is that the current implementation does not yet leverage the Sync Framework.

The WiEModelSynch class exposes a LocalModel and a RemoteModel property that it expects to be set by the application (in our case, this is done by the WiEModelWithLocalCache).   The Sync Agent registers with the models to be notified of the various ChangedEvents raised by the model and uses those notifications to “wake up” the worker thread.

OnLocationHistoryChangedEvent(object sender,LocationHistoryChangedArgs e)

 BackgroundWorker()

The WiEModelSynch implementation consists of a background worker thread that queries the local model for all available Location data objects and then attempts to save the retrieved location data objects with the remote model.

PerformSync()

Putting all the pieces together…

The mobile client application’s main form creates an instance of the WiEModelWithLocalCache class and uses it for all its data storage operations.