One of the coolest parts of my job is meeting with exceptionally smart people in the Life Science industry. Last week I had an opportunity to talk with James Averback of Life Science Integration Partners about Models and the integration of this important concept in the InfoMesa fabric. James is an expert in Regulated Documents, the acknowledge gems and output of the R&D process. As we talked and I demoed InfoMesa to him, we struck on the notion of models as a grouping mechanism.

There are a million definitions of a model, but in this case let's define it as how you do something, who does it, and what you need to accomplish the task. It's important to note that virtually all the things we have in InfoMesa and all the things we do during the work day can fit into a larger grouping of things.

Ideas, Hypothesis, Experiments, Protocols, Procedures, Stages, Departments - they could all be considered models that contain various elements - People, documents, paragraphs, definitions, molecules, atoms, viewpoints, etc.

We've heard a number of times that people enjoy the freeform nature of the InfoMesa Whiteboard, but the question of how to establish law & order in the wild west of free space is a concern.

We think that models are the ticket. Here's how they work...let's look at a freeform assembly of data first...

What are the tasks being done, who is doing the work, when does something happen before something else (precedence)?...models are the key...

These visuals are only to help communicate the basic idea (which we are still working through), the end result will likely be a bit difference.

In general Models are containers - miniature whiteboards (or perhaps better stated: branded whiteboards).

The characteristics of these containers are as follows:

• These containers have type (Idea, Hypothesis, Protocol, Department, etc.)
• These containers can contain any grouping of elements (molecule viewers, images, documents, sticky notes, video annotations, etc.)
• Containers can link to other containers (just as whiteboards link to other whiteboards)
• The linkage of containers can indicate concurrent process, serial process or sequence. Now we have a way to show how things need to progress - sketch out a flow and fill in the blanks.
• A container can have membership of People - like a department or a committee.
• A container can be tagged, just as Elements.
• Voting and Ranking of Type. A container can have attributes of the social environment.  How many people like this idea or hypothesis or how promising is a new idea?
• Models can have linkages to Human Amplification Methods - namely web services and workflows that can work on a researchers behalf-not AI, just processes.

We don't yet know everything about this new approach but here's a high-level graphic to show a basic idea...

And here's an idea of linkage and progression...

These Models need to easily created, assembled, grouped and maintained - I see them as higher level than a Tool, which is a custom application. Anyone should be able to wire together a new model. This has real estate implications and I think that the Services and Tools tabs will combine and a new tab, Models, will be placed on the Toolbar.

James will be providing guidance as we move through these waters and I welcome his input and yours. What are your thoughts about this direction?

Happy New Year!

This week I had a few extra moments to dive into the strange world of Cloud Database and Data Storage. I've been a database and visualization programmer for virtually my entire professional carrier with lots of different databases (DB2, Access, SQL Server (of course) and Oracle and Sybase), but I had not looked at the hosted data storage that is now beginning to be popular with Microsoft Azure (SQL Data Services), Amazon and Google.

I looked at Microsoft Azure and signed up for all the dev tools and within 45 minutes had Rooms and Whiteboards being created in the cloud. I have to be honest, I didn't think it would be this easy. It's so easy, in fact, that I think that the entire database world will be rocked to it's core by this development. That's not to say that there aren't learning's of what goes where/when. There will always be the need to store proprietary data in the organization, but for many other things, cloud data seems like the perfect ticket.

Security is a big deal for InfoMesa and I'm going to place that on the "Parking Lot" until next week. Let's talk about how SQL Data Services works!

Terminology Changes

Let's get one thing cleared up immediately: SQL Data Services is not SQL Server and how you query and store things is not SQL (language specific).

When we talk about databases we usually thing in terms of Servers (the physical machines the database engine and data files reside on), Engine instances (the database engine or instances), and the Data Files which are seen logically as Tables and Views. These Tables and Views are then further broken into Tables that contain Rows and Columns. We use SQL to describe what we want back or what we want to update, insert of delete and in the case of a select statement get back a recordset that we can traverse or bind to various objects for visualization or manipulation.

Why the remedial database? Well, for decades we have spent time normalizing (arranging) the data into 3rd, 4th and even 5th normal form--increasingly moving away from the giant flat file of the 1970's (and 1960s). The 1980's and 1990's were the decade of the relational database coming into it's own and being the de facto way that business (and science) stores data.

SQL Data Services (SDS from here on out) does not act this way and it's the biggest obstacle to overcome mentally. SDS uses something called an Authority - this is the physical server that the data will be stored on. Inside on an Authority is a container - containers are similar to databases (or complexes in db2). Once we get to containers, lots of things are different. Entities are now the main thing.

Entities are stored inside a container. You can store many kinds of entities in a container - so think of entities as a table of tables - they use the term heterogeneous containment to describe this idea. In fact, in order to perform a join between entities they have to live in the same container - no cross container joining. I was a little bummed about this until I started playing around with the code.

Making a Connection

There are no ODBC/OLEDB drivers and no ODBC database connection - that's a big change. Here's how to connect to the SDS service (in C#):

First, you add the System.ServiceModel as a Reference and then add ssdsClient as a Service Reference, here's how...

1. Click Add Service Reference from the project menu.
2. Specify address https://database.windows.net/soap/v1/.
3. Change the default value (ServiceReference1) for Namespace to ssdsClient.
4. Some of the samples use generic List (not Array). You will need to update the service reference settings. Click Advanced.
5. In the Service Reference Settings dialog box, in Collection Type, click System.Collection.Generic.List, and then click OK.
6. Enter the code, and then build and run the application.

Second, here's the code to wire up a connection:

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Windows;
using System.Windows.Controls;
using System.Windows.Data;
using System.Windows.Documents;
using System.Windows.Input;
using System.Windows.Media;
using System.Windows.Media.Imaging;
using System.Windows.Navigation;
using System.Windows.Shapes;
using System.ServiceModel;
using WPF_SSDS.ssdsClient;

namespace WPF_SSDS
{
    /// <summary>
    /// Interaction logic for Window1.xaml
    /// </summary>
    public partial class Window1 : Window
    {
        private static string userName;
        private static string password;
        private const string authorityId = "infomesa";
        private const string containerId = "core";
        private static SitkaSoapServiceClient proxy;

        public Window1()
        {
            InitializeComponent();
            userName = "InfoMesaRepository";
            password = "USE YOUR OWN PASSWORD";

                proxy.ClientCredentials.UserName.UserName = userName;
                proxy.ClientCredentials.UserName.Password = password;

                try
                {
                    // 1) First create a sample entity (to update later)
                    // Set scope to the container where you want to create the sample entity
                    Scope myContainerScope = new Scope();
                    myContainerScope.AuthorityId = authorityId;
                    myContainerScope.ContainerId = containerId;

                 }

Fairly simple and no SQL Connect String that was such a hangup for so many people.

So, let's post some records to the container...

Room first...I use a GUID for the ID, because AutoID is a pretty difficult thing to pull off in a concurrent environment. Pay special attention to the Kind MetaData Tag - this is what separates the apples and oranges in the container.

private void CreateRoomEntity(string ROOM_Name, string ROOM_Desc, string ROOM_Author, string ROOM_PubDate, bool ROOM_Visible, string ROOM_Type, string ROOM_Parent)
        {
            string GUID;
            GUID = System.Guid.NewGuid().ToString("N");

            using (proxy = new SitkaSoapServiceClient("BasicAuthEndpoint"))
            {
                proxy.ClientCredentials.UserName.UserName = userName;
                proxy.ClientCredentials.UserName.Password = password;
                try
                {
                    // 1) First create a sample entity (to update later)
                    // Set scope to the container where you want to create the sample entity
                    Scope myContainerScope = new Scope();
                    myContainerScope.AuthorityId = authorityId;
                    myContainerScope.ContainerId = containerId;

                    // Add a whiteboard
                    Entity e1 = new Entity();
                    // Set requied metadata property
                    e1.Id = GUID;
                    // Set optional metadata property
                    e1.Kind = "Room";

                    // Set flex properties
                    e1.Properties = new Dictionary<string, object>();
                    e1.Properties["RoomName"] = ROOM_Name;
                    e1.Properties["RoomDesc"] = ROOM_Desc;
                    e1.Properties["Author"] = ROOM_Author;
                    e1.Properties["Visibility"] = ROOM_Visible;
                    e1.Properties["RoomType"] = ROOM_Type;
                    e1.Properties["PublicationDate"] = DateTime.Parse(ROOM_PubDate);

                    proxy.Create(myContainerScope, e1);
                    MessageBox.Show("Room Created!");

                }
                catch (Exception e)
                {
                    MessageBox.Show(e.Message);
                }
            }

Reading data from SDS

Getting data out is just as simple. Here's an execution of getting the Rooms out of the container...

        private void ExecuteQuery()
        {
            string sampleQuery = @"from e in entities where e.Kind==""Room"" select e";
            string output = "";
            using (proxy = new SitkaSoapServiceClient("BasicAuthEndpoint"))
            {
                proxy.ClientCredentials.UserName.UserName = userName;
                proxy.ClientCredentials.UserName.Password = password;
                try
                {
                    // Identify scope. When dealing with containers, authority must be in scope.
                    Scope myContainerScope = new Scope();
                    myContainerScope.AuthorityId = authorityId;
                    myContainerScope.ContainerId = containerId;

                    List<Entity> myQuery = proxy.Query(myContainerScope, sampleQuery);
                    if (myQuery.Count == 0)
                    {
                        MessageBox.Show("No Items found!");
                    }
                    else
                    {
                        foreach (Entity e in myQuery)
                        {
                            output = output + "\r\nID:" + e.Id.ToString();
                            output = output + "\r\nKind:" + e.Kind.ToString();
                            output = output + "\r\nProperties:";
                            foreach (KeyValuePair<string, object> p in e.Properties)
                            {
                                output = output + p.Key.ToString() + " = " + p.Value.ToString() + "\n";

                            }

                        }
                    }

                    MessageBox.Show(output);
                }
                catch (Exception e)
                {
                }
            }
        }
     

Some Thoughts

You can add properties to any entity and they don't have to exist in all the other ones - think of a nullable field and, again, the thought of giant flat files come to mind. From a performance perspective I haven't tested hundreds or thousands of items, but just in these cases, performance seems to be on par with the local Jet database we use for InfoMesa now.

I'll post some more thoughts next week, but I'm pretty happy with what I'm finding.