Connecting to External Data On-Premises in Office 365 SharePoint Online

BRETT LONSDALEOWNER – LIGHTNING TOOLS

SPONSORS

About Me• Brett Lonsdale• Formed Lightning Tools in July 2007• Founder of UK Community Day• Authored & Co-Authored Many Books• Follow Me On Twitter: @brettlonsdale• Linked-In: linkedin.com/brettlonsdale

What do we hope to achieve?• Islands of data joined

together to build powerful solutions• Minimal training

requirements• Adoption of SharePoint• Make data more

accessible

How do we hope to achieve it?• Power BI

• Data Management Gateway• Oracle, SQL, Salesforce

• Microsoft Excel• Power Query• Power Pivot• Power Q&A• Power Map

• Custom App• CRUD OData App

• Business Connectivity Services• Web Parts• External List• External Data Column• Office Integration

• External List (Site Scoped ECT APP)• Third Party App

BCS in SharePoint On-Premises• External Data in SharePoint• External Data in the Client• Authentication Options• Secure Store• Windows Authentication• Revert To Self• Username & Password

• Multiple Connectors• SQL• WCF• .NET Assembly• OData

BCS in SharePoint Online• No BCS Search• No Profile Pages?• Connectivity is different• OData – BDC Model File• SPD & Azure & Secure Store• Visual Studio Add-in

• Hybrid BCS

External List• Like an internal list, but

with external data• CRUD functionality• Views, Filters, Sorting• No item permissions• Export to Excel• External System doing

Paging, Sorting & Filtering

Web Parts• Read Only• Ideal for Dashboards• Formatting via XSLT• Web Part Connections• Browser Customization

External Data Column Type• Lookup your meta data• Still no Content Type

Support• Great Office integration• Powerful solutions

Office Client Integration• Excel• Word (Quickparts)• Outlook (Office Types)

External Content Types• Created through

SharePoint Designer 2013• Created using Visual

Studio• 3rd Party Tool

External Content Types• Methods

• Finder (Read List)• Specific Finder (Read Item)• Creator (Create)• Updater (Update)• Deleter (Delete)• ID Enumerator (Index)

• Properties• Identifiers• Filters• Actions• Office Item Type• Title Column

Expose an OData End Point• WCF Data Service• ADO.NET Entity Data Model

OData• Expose data by exposing it as OData• Automatic ECT creation in Visual Studio for OData.

Deploy ECT’s as an App• No longer need to be Farm Solutions/BCS Model• Easier deployment• Better development environment• External List Only

Hybrid BCS• SharePoint 2013/2016• Uses OData Service

EndPoint

Sync with SQL Azure• Several Tools Available• Sync On Premises

Database with SQL Azure• SharePoint Designer/VS to

create ECT

Permissions• Permissions set at the Meta Data Store• Can be propagated to the LOB System to the ECT• Can be set at the ECT

Secure Store

BCS and Performance Considerations• Limit Filters• Wildcard Filters• Stored Procedures and Views

Resources• Configuring Hybrid BCS (ADFS/Azure AD for

Authentication)• http://channel9.msdn.com/Events/SharePoint-Conference/2014

/SPC319• Deploy a Business Connectivity Services Hybrid Solution • http://

technet.microsoft.com/en-us/library/dn197239%28v=office.15%29.aspx

• BCS Meta Man• www.lightningtools.com

thank youquestions?

live ratingsLIGHTNINGTOOLS.COM/OUR-BLOGS@BRETTLONSDAL

E

spca.biz/9BSE