visio services 2013 advanced data linking

Copyright© 2012 Microsoft Corporation

Visio Services 2013Advanced Data LinkingChris HopkinsSenior Consultant - Premier Developer PracticeMicrosoft


Agenda

Goals & Challenges

Data LinkingCapabilities

Data LinkingBest Practices

Data Graphics & Visualizations


• 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

1. Import record set(s) from an external data source(s)• Multiple data record sets supported within a single diagram

2. Link records to shape• Link 1 record to N number of shapes• Link 1 record from each record set

3. 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

RUNNING LOW

L

S

XL

Current Stock29

Static until linked


Data Visualization using Data Graphics

• 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

Icon Sets

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

NEED TO RESTOCK

L

S

XL L

S

XL


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 use

ShapeSheetsyntax

• Data Source• Calculated at the source

• Views, Store Procedures, PowerPivot, …


Demo


Advanced BI starts with the clientCombine Data Graphic Items

Use Formulas to calculate properties

Link a record to multiple shapesHyperlink 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

.NETOLEDBODBC

SP Lists

Excel Servic

es

+

BCS

Custom


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 Servic

es


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

http://www.bing.com/url?source=images&rch=C1U2mzOmUF5SwWO0WguXxE_RWl0zgeE&url=http://bluekeypi.com/images/powerpivot%20logo.png&urltarget=_blank&q=powerpivot+logo&view=detail&&id=B2BE5D006BFCF288CFF6BDA5B0E40BCCCAB66FD2&first=0&FORM=IDFRIR&ssIG=d55890d1e8d04489a0006a690aa066b2


SharePoint List

• 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

SP Lists


External List - 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

BCS


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

• 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

Custom


PowerView System Center

SAPDynamicsWeb Service

Professional Diagrams Quickly• 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




Professional Diagrams Quickly• System Center contains a wealth

of information

• Visio add-in for System Center 2012 Operations Manager

• BCS• PowerShell• Solution code




Professional Diagrams Quickly• SAP

• Dynamics

• Web Services• XML, JSON

• BCS• PowerShell• Solution code


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


© 2012 Microsoft Corporation. All rights reserved. Microsoft, Windows, Windows Vista and other product names are or may be registered trademarks and/or trademarks in the U.S. and/or other countries.The information herein is for informational purposes only and represents the current view of Microsoft Corporation as of the date of this presentation. Because Microsoft must respond to changing market conditions, it should not be interpreted to be a commitment on the part of Microsoft, and Microsoft cannot guarantee the accuracy of any information provided after the date of this presentation. MICROSOFT MAKES NO WARRANTIES, EXPRESS, IMPLIED OR STATUTORY, AS TO THE INFORMATION IN THIS PRESENTATION.