How would it be if using standard Microsoft Office tools and SharePoint 2010 you could take a CAD drawing, drag-and-drop data from your property management database onto it, choose Excel style colour coding rules (e.g. traffic lights or shading) and then publish your diagram for all users to see on your Intranet (without them needing anything other than a browser? This post, part of my theme of “doing BI with technology you already have”, discusses how SharePoint 2010 and Visio 2010 avoid the following common problems in data visualization:
- Need for specialist and possibly licensed applications to see the data visualization;
- IT expertise required to connect data from a database to a Visio shape;
- Out-of-date data and need for manual intervention.
I’ll for sure do a more detailed post on this topic in the next few weeks, and will be able to show this to any of you attending Realcomm 2010 in Las Vegas early in June, but in my mind this is too good to wait any longer for!
SharePoint 2010 contains some quite radical generalizations of the service applications that existed in MOSS2007 (shared services and excel services). The new architecture allows service applications to be created for many purposes from search to Office Web Apps to Business Connectivity Services. If you’re interested in the new model, then you should take a look at Spencer Harbar’s great post SharePoint 2010: Service Applications Part One: Model Overview.
However, I want to pick out one particular service application and its application to visualizing real estate data on floor and site plans.
You may well already be a Visio user – perhaps for organization charts or swim-lane process diagrams. Maybe you’re already using it for floor and/or site plans. Visio is a powerful tool that can be used to create or edit a wide range of diagrams. It can also import AutoCAD drawings, although you may need to create a simplified version of your plan for this to be most effective (perhaps using just a few layers).
Once you’ve got the drawing in Visio the fun really starts. Using a standard Office Data Connection (the same you probably already use to get data into Excel for your own analysis) you can open a dataset – perhaps containing floor areas, rent psf/m, option or expiry information. You answer a few questions – for example which of the columns uniquely identifies each row (including the unusual but potentially useful option in certain other contexts to rely on the order) – and then you get the row of data in a nice table on the right and your drawing on the left.
Now for the part that has been so difficult in the past – relating the data to the shapes and choosing the rendering options. But now in Visio 2010 this could hardly be easier. You simply drag each row and drop it on the corresponding shape in the drawing!
Once the rows are linked to the shapes, there is a single icon in Visio that allows you to determine the interpretation of the data. You can colour the shape based on one of the values and add others as labels on each shape (with the usual control over positioning, size etc. The built in options for colouring are extensive – from shadings to traffic lights – and should meet your needs. Don’t be afraid to go back to whoever provided your Office Data Connection and ask for the data in a different format if you get stuck – that’s often the easiest way to work around the few limitation you might find.
So now we have a Visio drawing connected – live – to data from our property management system (say). How do we share it? We could save it on the network and allow others with Visio to open it – like we would with Excel – but that often leads to the only person using it being you. Wouldn’t it be better to publish it to the asset managers home page on the intranet, or a specific property extranet, where the drawing can be viewed in context with other information: perhaps documents, reports, some narrative.
Well, it couldn’t be easier. There’s a small technical decision process to go through about how you are going to login to the database to get the data once the drawing is published, but once that’s figured out, you can just upload your drawing to a SharePoint 2010 library, and then use the new Visio Web Access web part to drop the drawing onto any page. This web part uses Silverlight to render the drawing so it can be viewed by anyone even if they don’t have Visio installed. You can configure the refresh of the drawing directly in the web part – for example to refresh the data once each day.
And why stop there? You can take any drawing and apply this technique to it. For example, how about showing occupancy traffic lights by US state…?