Visio Services 2013 Advanced Data Linking

Visio Services 2013 Advanced Data Linking Chris Hopkins Senior Consultant - Premier Developer Practice Microsoft

Agenda Data Graphics & Visualizations Goals & Challenges Data Linking Capabilities Data LinkingBest Practices

Visio Services supports data refreshable diagramsrendered by the server Easily share diagrams No client necessary Not static snapshots Visualize data in unique /custom ways Refreshable not editable Meant for consumers Client for Authors Client = Dashboard Designer What is Visio Services?

Data Linking –> Shape Data, a 3 step process • Import record set(s) from an external data source(s) • Multiple data record sets supported within a single diagram • Link records to shape • Link 1 record to N number of shapes • Link 1 record from each record set • Configure Data Graphics • Client refresh • Data cached in the diagram • Shape Data properties updated from linked records • Server refresh • Data cached for each user request • Cached diagram rendered with visual updates Static until linked

Data Visualization using Data Graphics Icon Sets • Surface Shape Data values • Visual version of ‘Insert Field’ • Dynamic shape behaviors • Icon Sets • Text Callouts • Data Bars • Color by Value • Build your own • These are just special Visio shapes Text Callouts Data Bars Color By Value

Data Visualization using shape behavior • Build behavior into your shapes which respondto data changes • Change formatting • Change text • Change geometry • Change size and position • Change visibility • Visio Services evaluates all shapes for rendering • Visio Services in 2010 was limited to Data Graphics

Aggregate data from refresh • Shape Data supports formulas • Create a new property for your shapes that performs calculations based on the other linked Shape Data properties • Data Graphics also support formulas • {field name} • Otherwise useShapeSheetsyntax • Data Source • Calculated at the source • Views, Store Procedures, PowerPivot, …

Demo

Advanced BI starts with the client Hyperlink to detail pages

Advanced data scenarios • Visio Services refreshes from a number of data sources • Excel Services • Ranges and Tables • SharePoint Lists • Native Lists • External Lists (BCS) • SQL • SQL Server • SQL Azure Excel Services SP Lists BCS + Custom OLEDB ODBC .NET

Excel Services Excel Services • Requirements for refreshing from a published workbook • User needs access to the diagram and workbook locations • Refreshed using current user credentials • VSDX/VDW and XLSX published on the same farm • No network shares, must reside in a document library • No old XLS formats • Link and Refresh from • Sheets / Tables / Named Ranges / Custom Range • Refreshed using OLEDB driver • ConnectionString = …Provider=Microsoft.ACE.OLEDB.12.0;User ID=Admin;Data Source=http://chhopkin-spdev/Shared Documents/Process…

Excel Services refreshing from SQL • Visio Services refreshes data from the published XLSX • Best practice = Refresh directly from the ‘root’ data source • Manually edit and upload or edit in browser

Excel Services refreshing from PowerPivot • PowerPivot Gallery - scheduled workbook refresh Every 5 minutes Workbook content updated

SharePoint List SP Lists • SharePoint Lists are an easy yet powerful data source • Same farm as the Visio diagram • User needs at minimum read access to the list • Get your data in a list and VGS can refresh from it • Manual Edit • SharePoint Workflow • Timer Job • Web Service • PowerShell Script • Applications or Services

External List - BCS BCS • External Lists are fully supported in Visio 2013 • This is new from the 2010 release • BCS add-in for 2010 • BCS allows you to configure external content types for connecting to • External SQL DBs • SQL On Premises • SQL Azure • WCF Services • Custom .NET providers

Make the most of SQL • Visio & Visio Services support SQL Server 7.0 –> 2012 • No Analysis Services or Reporting Services – at least not directly • Tables & Views – provided via Visio Data Linking dialog • Refresh from Production DB? Probably not! • Refresh from alternate “Visio refresh database” • SSIS package scheduled to migrate data as needed • Custom Application / Service that populates your tables • Can we refresh from Stored Procedures? Yes! • Not available in the Visio client UI for the SQL data source option • Configure BCS to execute the stored procedure – connect Visio via External List • Setup multiple lists with default parameters for stored procedures that accept parameters

Custom data sources Custom • Server Code • .NET assembly deployed to the GAC and registered as a Trusted Data Provider for the Visio Services service application • Implements AddonDataHandler • Returns a .NET DataSet object to Visio Services • Client Code • Create and maintain the record set • Set the ConnectionString so Visio Services knows to call your data provider • Example: Visio add-in for System Center 2012 Operations Manager

Professional Diagrams Quickly System Center • PowerView is not a data source but a visualization tool for PowerPivot • Data connect Visio diagrams to the pivot tables in PowerPivot • Incorporate PowerView in a Mashup with Visio Services connected to the same data source PowerView SAP DynamicsWeb Service

Professional Diagrams Quickly System Center • System Center contains a wealth of information • Visio add-in for System Center 2012 Operations Manager • BCS • PowerShell • Solution code PowerView SAP DynamicsWeb Service

Professional Diagrams Quickly System Center • SAP • Dynamics • Web Services • XML, JSON • BCS • PowerShell • Solution code PowerView SAP DynamicsWeb Service

In summary • Data Linking best practices • Utilize OOB data sources • Nothing wrong with Custom but limited to Visio Services refresh • Schema Changes – none • Were not just talking about Data Types, no missing rows or columns • Configure views or queries or filters that give you predictable results • Multiple data sources, multiple shapes • Drill downs and Pivots • Just hyperlinking and page navigation

Thank You

Visio Services 2013 Advanced Data Linking