Download code from GitHub
From the very beginning, Visio was responsible for introducing the visual data paradigm for business information reporting. A key concept of Visio from the outset was smart shapes that could respond to information changes. This chapter reviews the evolution of data within Visio, from the introduction of a modifiable ShapeSheet in the very first version (v1.0) in 1992, through to the Quick Import feature in Visio 2016. It is important to understand the important enhancements in Visio's evolution. It will empower the prospective power user and developer with the knowledge of why some code is written a certain way, how it can be more efficient, and which solutions are potential dead-ends. It is always more productive to create any solution on top of a core product because the object model provides documented properties, methods, and events.
Visio has a number of add-ons that utilize the core Visio application object model, and that are presented as different templates or diagram types. Although some of these are very popular, such as the Organization Chart add-on, extending either the code or the associated master shapes can be problematic. However, some are still useful, so I will describe these in more detail.
When looking at extracts of code from other books, Internet pages, or even colleagues, it can be important to understand that the code may have been efficient for its time but could be better now. I have been guilty of this myself because I have offered code that worked well for many years, while a student has seen the potential of newer enhancements to the object model and proposed a better, more efficient solution.
All of the screenshots in this book come from Microsoft Visio Professional 2016, which was run in developer mode. The fact that screenshots of the latest version can still be used to describe core parts of the engine that have been enhanced since the first version demonstrates how the product has been built on firm foundations.
Tip
Running Visio in Developer Mode
Either tick the Developer tab in Customize Ribbon or Run in developer mode in File | Options | Advanced | General.
In this chapter, we shall cover the following topics:
Why choose Visio for data diagramming?
The evolution of data in Visio prior to the Microsoft acquisition
The evolution of data in Microsoft Visio
Reviewing the significant current OOTB add-ons
Shedding a tear for the sadly missed OOTB add-ons
Microsoft Visio first appeared as Visio from a company called ShapeWare in 1992. From the very beginning, it was designed as a smart diagramming system. Before long, the company changed its name to Visio Corporation and a new information graphics paradigm was born.
At that time, I was working as an implementation consultant for a Unix CAD (Computer Aided Design) system that had a link to a Unify database. I was using this system to provide personnel desk locations, space chargeback, and cable management to merchant banks in the city of London. In those days, you could not buy just the software and install it on your own PC or Mac; instead, you had to buy the hardware too. So, each workstation would cost about £20k ($30k). This is quite an investment, and the skills required were quite specialized, thus spending extra for a consultant to actually use it did not seem so expensive. The work for the merchant banks took me over to New York, and the cable management application even took me to NASA in Alabama.
However, the merchant banks that I worked for began to demand that any reports were formatted to an exacting standard. They had become used to the WYSIWYG interface in new Windows applications such as Word. "Unfortunately, this was not available directly in UNIX, so, I invested in Microsoft Access for reporting, via FTP. They soon also demanded better printed graphics than was possible in CAD, so I had to seriously reconsider my toolset. The consulting company that I worked for also sold a Windows CAD system that could not produce acceptable graphics either. It was also very difficult to automate, so I surveyed the available alternatives.
In the days before easy downloads from the Web were available, every computer magazine had a cover disk (a 3.5" stiffy, not a CD) with a few trial versions of programs on it, and I had previously tried one called Visio 2. I had been impressed with its parametric behavior and the provided ability to automate it using Object Linking and Embedding (OLE), so I decided to find out more about the current version at that time, which was version 4. I was excited to find that the technical edition had now brought the ability to import some types of CAD files, which meant that I would be able to utilize some of the drawings that I had been using for years. It also introduced the ability to link to databases via OLE.
I began to provide solutions using Visio Technical Edition linking to data in Sybase, Oracle, SQL Server, Access, and Excel using the database connectivity support that was introduced in Visio 4.
For example, I was linking 600 trader desks per floor on a single Visio page to the corporate Sybase database, and with a single macro I was able to refresh the text and color fill for each desk with the up-to-date occupant details. These floor plans were used by the help desk on these large open-plan floors to find traders who reported something amiss in their workstation. At first, I had to automate Visio from an external application, which I did with Microsoft Access or Excel as they already had Visual Basic for Applications (VBA) built in. I also wrote some code in Visual Basic (VB) as executables, but all these methods ran code across application boundaries, which slowed them down. I did manage to wrap VB DLLs with C++ to get them running within Visio as add-ons, but the coding time was increased by too much. Then, Visio itself introduced built-in VBA, so the code could execute far quicker within the Visio environment, and the time taken to write tactical solutions was reduced.
The parametric capability of Visio shapes enabled me to construct a single monitor SmartShape that changed size and appearance depending on one of the 33 different combinations of manufacturer and screen size that I entered into the Custom Properties of the shape.
Note
A case study is available at http://bvisual.net/Case_Studies/ChaseManhattanBank.aspx.
I was totally sold on the Visio paradigm and started a business based on providing Visio-based solutions shortly before Microsoft acquired Visio Corporation at the start of the year 2000.
Microsoft took over an extremely large amount of code and began the process of assimilating the application into the extended Microsoft Office family. This has had many challenges since the original Visio developers had no access to Windows or Microsoft Office code and practices. The "Big Three" Office applications (Word, Excel, and PowerPoint) have always blazed the trail as far as user interface design and file format are concerned, and Visio has followed behind at a respectful distance.
So, after the acquisition, Microsoft reviewed the breadth of features available within the many different editions of Visio (Standard, Technical, Professional, and Enterprise) that they had inherited and began to consolidate them. Over time, a large number of add-ons were added to the base product, and the Visio Corporation voraciously acquired many products that were using the Visio system (for example, IntelliCAD for CAD, InfoModeler for database modelling, and Kaspia for network discovery). Some of the products and code were incompatible with Microsoft's vision for Visio, so they were deprecated.
Note
For a more complete history of Visio, take a look at http://visio.mvps.org/History, which is maintained by the longest-serving Microsoft MVP for Visio, John Marshall.
However, the core engine of Visio has matured and expanded over many years of production use, with very little of it being removed. Therefore, skills learned around Visio shape development or automation have not been a waste of time, and most of the old documentation about these subjects is still relevant.
Note
An oldie, but a goody, Developing Microsoft Visio Solutions can be found at https://msdn.microsoft.com/en-us/library/aa245244(v=office.10).aspx.
There are three available editions of Visio 2013 and 2016: Standard, Professional, and Pro for Office365. In fact, the last two are exactly the same apart from the licensing method. This book is not about the Standard edition because it does not contain all of the data capabilities.
The period between the years 1992 and 2000 saw Visio burst onto the scene and rapidly grow in size, acquiring almost every other product that used its drawing system. The first sales target was to out-sell the best-selling flowcharting tool of the time, ABC Flowcharter. This was done within 18 months, and Visio was on its way.
Beyond the Standard Edition was the Technical Edition, which contained CAD and engineering add-ons, and the Professional Edition, which contained database modelling and network diagramming. Eventually, there was also the Enterprise Edition with network discovery.
Note
Visit http://www.visiocorp.info/boxes.aspx for more information on the Visio Corporation.
The timeline displays the most relevant milestones of data diagramming, and a short explanation of each follows. They are relevant because these features still exist, and there are still code examples out there that use them.
The intelligence in Visio comes from its unique modifiable ShapeSheet, which is reputedly modeled on an Excel worksheet. Every single shape in Visio has one, as does each page and even the document itself. So, it is important to understand it. It can be opened from the Developer ribbon by the Shape Design | Show ShapeSheet button. The following screenshot shows an example of a current ShapeSheet, and it also shows the Shape Name dialog:
Note the Data 1, 2, and 3 boxes on the Shape Name dialog can actually hold 64,000 characters, but only use it with caution because there are some old add-ons out there that use them. Initially, they were the only way to persist data in the shape. The ShapeSheet on the right of the screenshot shows how it is broken down into sections, rows, and cells. We will learn more about this in the next chapter. In the first version of Visio, there was no User-defined Cells or Shape Data section because it was introduced in version 4. The pre-cursor to User-Defined Cells was the Scratch section. Both of these sections are optional because they can be created, and have new rows inserted, as required. This is in contrast to the fixed, mandatory sections such as Shape Transform because every shape needs to have a location and rotation in the page that it is on.
In the center of the screenshot is the Drawing Explorer window, which displays the document, pages, shapes, and so on. We will learn more about that too in the next chapter.
OLE2 and the published object model allowed Visio to be controlled by programming languages such as Visual Basic 3. In fact, Visio was the first non-Microsoft product to have OLE2 compliance.
Custom Properties, later renamed as Shape Data in 2007, not only provides a method of storing typed data for each shape, but also provides a dialog to view and edit them. The following screenshot shows that there is now a Shape Data popup dialog and a Shape Data window that was added years later:
There will be much more about Shape Data in Chapter 2, Understanding How Data Is Stored within Visio.
Properties Reports, now called Shape Reports, provide a method to create simple tabular reports from data in Visio shapes. The following screenshot shows the Reports dialog, and there will be more about this feature in Chapter 8, Validating and Extracting Information:
The OLE link to databases provides a method to read and write data to/from not only Custom Properties/Shape Data, but also from the newly provided User-defined Cells, and in fact, almost all the ShapeSheet cells and even the position of the shape in the page. The following screenshot shows the Link to Database dialog that can be opened from the View | Macros | Add-Ons | Visio Extras ribbon menu:
User-Defined Cells were an important addition in the ShapeSheet. They have just two columns: Value and Prompt. This means that formulae can be entered in the Value column with a description of what they are there for in the Prompt column. Before that, developers would use the Scratch section for formulae and descriptions of their purpose. This can make it difficult to understand the ShapeSheet code; however, the Scratch section does remain important for geometric calculations because of the capabilities of the X, Y, and A to D columns.
This wizard provides an easy-to-use interface for data connectivity, and the following screenshot shows how it can be started from the View | Macros | Add-Ons | Visio Extras ribbon menu:
All of the data related add-ons under the View | Macros | Add-Ons | Visio Extras ribbon menu are part of one large add-on, and they are therefore not part of the core engine and object model in Visio. I have covered their functionality in an earlier book of mine (take a look at http://www.visualizinginformation.com), so it is not covered in this book.
This version saw improvements in the database connectivity and ODBC compliance, and the inclusion of built-in VBA vastly increased the speed of scripts.
The speed improvements that I could make on automating updates utilizing the database connectivity add-on with VBA were really good. The time taken to update nearly 600 data-linked desks was reduced from 6 minutes to less than 60 seconds. This was a great improvement, but it was still relying on an add-on using ODBC. Although the add-on is capable of two-way connectivity (it can update the data source as well as refresh from the data source), it still has issues. For example, only one link is possible to a single shape. This is restricting in the corporate world, where for example the facilities, IT, and HR departments loathe merging their databases at source.
Post-acquisition, Visio has seen its challenges, not only for the new custodians of the code base, but also for the Visio community. Microsoft has had to undertake some rationalization and integration with the extended Microsoft Office family. This has meant, for example, that developers have had to change the whole Visio user interface to use common Office elements. More latterly, this has meant changing the file structure of Visio documents to use the Open Packaging Convention (OPC) file format. The Visio community saw little improvement as far as data was concerned until Visio 2007, when the core engine was expanded at last. These features have been further enhanced since then, but they remain the cornerstone of data solutions in Visio 2016.
Some improvements to the database add-on kept us going for a while, but it uses older connectivity methodology and, as an add-on, it is difficult to extend, as explained earlier.
This add-on does have the ability to read and write data though, which can be useful for power-users and developers. For example, the position of a shape in a page, called its PinX and PinY, or its fill and line patterns and colors, could be captured from Visio and sent back directly to a table in the data source.
It is old, though, and other important information is unavailable, such as the containers that a shape is within, which call-out shapes are associated, or even which other shapes it is connected to. Chapter 8, Validating and Extracting Information, will cover extracting all types of information from a Visio diagram.
Most Visio users do not realize that there is a sample MS Access database called DBSample.mdb installed in the Program Files | Office | lcid folder. It is named Visio Sample Database in the View | Add-Ons | Visio Extras | Link to Database | Name drop-down list, and it contains a few tables to play with. The Qualifier box displays the installed location. However, I would recommend copying it to a writable folder on your local or network drive before updating it.
In Visio 2007, there was a plethora of new data features added to the core engine. This means that the Visio object model was extended for Professional edition users at the very least. A new property called DataFeaturesEnabled appeared on the Application object, and a DataRecordsets collection appeared on the Document object. Each normal DataRecordset appears in the new External Data window so that rows of data can be linked to shapes. Importantly, this data can be refreshed, either manually or after a set time period, while the document is open, or by code opening and refreshing each document. This provides extensibility for developers who can now create data automation code on firm foundations.
The new pivot diagram add-on also creates DataRecordsets, but these are not displayed in the External Data window.
Tip
Detailed steps to download the code bundle are mentioned in the Preface of this book. Have a look.
The code bundle for the book is also hosted on GitHub at https://github.com/PacktPublishing/Mastering-Data-Visualization-with-Microsoft-Visio-Professional-2016. We also have other code bundles from our rich catalog of books and videos available at https://github.com/PacktPublishing/. Check them out!
The following screenshot from Visio 2016 shows two buttons (Custom Import and Linked Data) that replace the one called Link Data to Shapes, present in the earlier versions. There is no change to the foundation though:
The Quick Import button, Data Graphics group, and Data Graphic Fields checkbox were introduced in Visio 2016, and they will be explained in the next chapter.
The Display Data | Insert Legend button was not introduced until Visio 2010.
Further integration with SharePoint was introduced in two ways.
First, Visio could be used to create simple SharePoint Workflows, which could then be exported to SharePoint Designer for enhancement. Since it would be dangerous to export badly formed data to SharePoint, Visio 2010 was given a validation rules engine so that the structure of diagrams could be validated.
Second, a Visio web part was provided in SharePoint that could not only display Visio documents faithfully, but also be partly refreshed from a suitable linked data source.
Validation and Visio services were originally only available in the Visio 2010 Premium edition, but Microsoft decided to offer all of the Premium content in the Professional edition from Visio 2013.
Note
My previous book, Microsoft Visio 2013 Business Process Diagramming and Validation, covers this topic in great detail. Take a look at http://www.visiorules.com for more information.
Container and callout shapes were introduced as part of a structured diagram concept, thus making it easier to construct and navigate diagrams.
Validation and structured diagrams are part of the object model and are discussed in more depth in Chapter 8, Validating and Extracting Information.
The Visio file format has remained unchanged since Visio 2003, but it was time for Visio to join the rest of the Office applications and embrace the OPC file format. This is a zipped up document with many XML parts inside it; because it follows the OPC standard, it becomes accessible to some standard coding techniques.
In addition, this version saw the addition of Business Connectivity Services in SharePoint as a refreshable data source for Visio diagrams, and the need to publish documents to SharePoint was removed with native support of the new Visio file format by the Visio web part. Visio files can now be utilized in SharePoint web pages on any modern device, in any modern browser.
The Quick Link button provides some automatic analysis of data in Excel worksheets, and some improvements were made to data graphics, such as the inclusion of icons in text callout items. These features are discussed in more detail in Chapter 3, Linking Data to Shapes.
There are a few add-ons that use data to generate diagrams, and they even provide the ability to export data. A couple of these utilize more specialist data such as Schedule | Gantt Chart and Schedule | Timeline for Microsoft Project. However, these three add-ons use data to create the layout of a diagram, which can then be enhanced with linked, refreshable data.
First introduced in Visio 4.0, this popular wizard and its supporting add-on provide the ability to create hierarchical organization charts, and as the following screenshot shows, it can use Microsoft Exchange, Excel, text files, or an ODBC compliant data source:
The imported data is used to create a hierarchical structure, but there is currently no refresh functionality. However, it can be overlaid with refreshable data using the Data | Custom Import feature that is covered in Chapter 3, Linking Data to Shapes. There is also the ability to compare two Visio documents in order to check what the differences are. It also has an export feature that outputs the shape data and hierarchical relationship to an Excel workbook, text, or CSV file.
This add-on has some other good features that some Visio users love. For example, it has the ability to insert images into the shape easily. This is all done using a non-extendable add-on, which makes it difficult to develop with.
First introduced in Visio 2003, this add-on provides the ability to import and, as the following screenshot shows, use Microsoft Exchange, Excel, Active Directory, or an ODBC-compliant data source.
The imported data is displayed in the Space Explorer window, and it can be refreshed from the ribbon. Unless you need to directly import data from Active Directory or Exchange Server, or particularly like the Space Explorer tree view display or the ability to automatically add shapes onto other shapes (such as Person or Asset shapes onto Space shapes), then I recommend using the Data | Custom Import feature that is covered in Chapter 3, Linking Data to Shapes.
This add-on uses the same Data Selector as the Data | Custom Import feature but with the extra capability of using SQL Server Analysis Services as a data source, as the following screenshot shows:
This add-on also utilizes the DataRecordsets collection that is covered in the next chapter, but DataRecordsets are hidden and numerous. This add-on, though, is worthy of more explanation and is covered in Chapter 5, Using the Pivot Diagram Add-On, since you can overlay with refreshable data by using the Data | Custom Import feature that is covered in Chapter 3, Linking Data to Shapes.
There are a number of add-ons that have disappeared from Visio over the years. This could be for very good reasons, but we oldies still miss some of their capabilities. So, I mention them not only to warn you that some documentation you may find on the web is out-of-date, but also possibly to encourage reincarnations.
For example, the Flowchart-TQM Wizard and the Network Diagram Wizard were deprecated in Visio 2003, but they provided a method to not only add shapes with data, but also to connect them together. The data linking and structured diagram concepts have been added to Visio since then, so a reincarnation would need to account for these.
The network discovery tool was last seen back in the Visio 2000 Enterprise Edition, and it had many devoted users for many years after that. This add-on would discover the connectivity of items in a network, create a database, and then diagram parts of it on demand in a Visio diagram. There are several third-party applications that are now available that can generate Visio diagrams of your network.
What all of the afore mentioned deprecated add-ons have in common is that they were not part of the core engine, and therefore, were more difficult to maintain and almost impossible to extend with third-party code.
There is also one deprecated add-on that is due to make a return by popular demand. This was and is the database modelling engine that originated in the code from the Infomodeler acquisition. It could be used to not only reverse-engineer databases, but also to forward-engineer and create reports. This eventually disappeared completely in Visio 2013, after first losing its forward engineering and reporting capability in earlier versions. It did have its limitations, but it was still very useful and is missed by quite a few users who need to understand, or explain to others, the complexity of a relational database structure. It will be returning in a limited form some time in 2016.
You have learned about the evolution of data within Visio over the years, and how the core engine has been extended since Visio 2007 to include linking to refreshable data sources with enhanced graphics to represent these values.
The quest is to become proficient in creating refreshable data-linked graphics that accurately represent business intelligence. However, first we must look underneath the hood to see what makes Visio tick, which we will do in the next chapter.
