- amazon s3 · • we have experienced the good, the bad, the ugly • we have documented the data...

www.solverglobal.com


Cloud Integration Paths (WTS2)

Using Sql Connector and Data Gateway


[email protected]

Alan RittbergerTechnical Service Lead, Solver


Session Objectives

• Today we will examine:• Sql Connector and Data Gateway connectors for client side data access• Client side objects consumable by Cloud Integrations• Variations of client side objects• Pros and Cons of multiple data provisioning paths


Session Agenda

• Connectors and their usability, Sql vs Gateway• Views versus Tables• SSIS in the mix• Extending data sourcing for FX Rates and control tables• Rules are your silver bullet• Leveraging existing infrastructure


Connectors

• All data consumption in the Cloud is thru a connector• We will concentrate on 2 of the most flexible

• Sql Connector• Data Gateway


Sql Connector


Sql Connection Info


What does the Sql Connector see

• Tables• Views• Note view to table is not 1 to 1• Views can contain logic

• For mappings• For filtering• For ????


Sql Connector Pros & Cons

• Pros• Standard technology that just works• Flexibility of exposed objects• Supported on many data sources• Builds on MSSQL skill sets

• Cons• Not encrypted• Connection from internet is not recommended

• Caveat• BI360 Cloud is built on Azure SQL. Any Data source on Azure SQL(SaaS) is

easily accessible.


Data Gateway Connector


Gateway Setup

• Looks familiar because it is• Built on the Sql connector• Supports same objects

• Tables• Views

• Added layer for security• Allows connections thru firewalls

• SSL connection from client• Data push rather than pull

• Request is separate from data• Data is sent on secure connection


Gateway Pros & Cons

• Pros• Based on standard technology (Sql Connector)• Secured data transmission• Flexible object support• Builds on MSSQL skill sets

• Cons• Data set size limitations• Requires constant server\client ping communication• Installed as a service that needs some management


Data Source Requirements

• Data Source Definition• How many sources• How many locations• Remote or local• Data access security• Data Source Definition

• Data Source Transformations• COA consistency• Calendar consistency• Mapping needs• Any other transform that could require complex logic


Cloud & Client Integration touch points

• How do we reconcile what the Cloud wants with what the client side provides

• We really cannot change much in how the cloud gets the data• Basic drag and drop functionality• Some transformations• Little dynamic structure• No logic application or looping type structures


Cloud Integration


Where are we now in the process?

• We know what the Cloud integrations want and need to be effective• We have experienced the good, the bad, the ugly

• We have documented the data sources• Documentation can never be discounted

• We have defined the transformations and other needs• A functional specification document becomes a decision tool


Two paths to the same end

• Views on ERP tables • SQL Server Data Tools


How to decide – Define an exit strategy

• Is this client a long term management prospect?• Is this client a one and done prospect?• How complex is the integration?• What are your skill sets?• What are the client skill sets?


Scenario #1- It doesn’t get any simpler

• Great Plains integration• 1 entity• Single calendar• Low transactional data count• No currency conversion• Key item: Client will allow to build your source objects on the ERP

Server


Scenario #1 Solution

• Views with 2 part naming (included with Cloud tenant)

• <dbname>.dbo.GL10110• Easy to implement• Performance is good• Little need for external logic• Simple to maintain• All stored on 1 SQL Server• Easy for client to backup• Easy for client to monitor• ETL is run in off hours for minimal

impact on ERP processes


Scenario #2

• 3 Distinctly different data sources• Multiple calendars that need merged• Multiple COA’s that are mapped to an authority• All data sources are remote with nothing but read access• Currency conversion from multiple sources• Must load conversion rates from one ERP source• Other complex interactions and rules for translating source data to a

common design



• Much more complexity• Interim steps and transforms needed• Data sourcing for a Cloud feature there is not direct integration to• Ready made for an SSDT backend populating staging tables

• Easy to implement from familiar templates• Extended logging and error handling capabilities• Common tools and skill sets• Easy to manage once implemented


Scenario #2 Solution Caveats

• Does require client provisioning a staging server• Extra cost for implementation• Extra management for IT


Scenario #2 Solution – Dimensions


Scenario #2 Solution – Fact Sets


Scenario #2 Solution – Conversion Rates


BI360DW as a data source

• Ready made source for cloud views• ETL is already in place and data cleaned• Quick way to migrate to the cloud


BI360 Dim Source for view


BI360 Fact source for view


Bonus Content – Period Control

• In a cloud integration how do we control the drop\load process• We can map and integration and select periods

• Those period selection are static• There is no dynamic “Between Current Period – 2 and Current Period”

• We can write rules to do deletes• Rule still need a source of how many periods to delete• Temp tables like PH implements are not there


Bonus Content – Period Control as a dim


Bonus Content – More……..

• Dimensions can be stand alone, do not need to associate to a module• Can easily be managed in the portal or updated via integrations

• Dimensions make excellent staging tables for dynamic content• Rules are a powerful tool to achieve complex ends

• Learn the rule syntax and experiment

• Jobs can wrapper integration steps and rules• Dimensions + Rules + Jobs = Successful processes in the Cloud


Key Takeaways & Resources

• No single solution• Know your clients• Be creative


Your feedback is valuable:

www.solverglobal.comwww.solverglobal.com

THANK YOU!• Alan Rittberger, Technical Service Lead, Solver• Toby Price, Senior Integration Engineer, Solver

- amazon s3 · • we have experienced the good, the bad, the ugly • we have documented the data...

Documents