By: Jeremy Kadlec | Updated: 2023-07-20 | Comments | Related: > Functions System
Problem
As organizations rapidly grow around the globe, data also continues to grow at unprecedented rates for both new and existing applications. Unfortunately, this has led to numerous complexities for both technology teams and organizational leadership.
Technology teams are facing challenges related to:
- Growth - Exponential data growth rates
- Silos - Data is stored in numerous databases (relational, Big Data, open source, Data Lakes, IOT, etc.) and formats leading to data isolation
- Limitations - New business dependent applications are now hosted in the cloud with limited data access and rapid changes
- Nuances - Time consuming and error prone data integration with characteristics unique to each platform
- Limited Resources - Severely limited development and integration resources
- Chaos - Hundreds of Extract-Transform-Load (ETL) processes moving data among databases, cloud data sources, data warehouses, partners, etc. with duplicate data updated on numerous schedules, varying calculations, excessive storage and more
- Higher Costs - Real time data access is challenging, so data is propagated resulting in outdated and duplicated data sets driving up costs for expensive storage devices
- Outdated Data - Reporting from Power BI or Microsoft SQL Server Reporting Services (SSRS) requires intermediary data ETL steps complicating reporting resulting in the inability to have real-time data from different sources for decision making
- Data Protection - No data backups from cloud-based applications to prevent unexpected failure resulting in data loss
Organizational leadership teams are facing challenges related to:
- Competition - Ever increasing pressure from global competitors
- Inaccurate Decisions - Limited data analytics and outdated data for decision making
- Integration - Poor data integration solutions among dependent business and partner processes
- Fiscal - Customer delays, missed contractual agreements and financial loss
- Time - Long lead times to integrate business critical applications impeding the customer experience
With limited technical resources, the technology team is severely constrained to meet core business needs. These constraints are exacerbated by the need to constantly build and maintain data integration code among numerous technologies and vendors. These constraints severely impact organizational leadership, which is highly dependent on the technology team for data access.
How can the technology team have a consistent interface to access data across the enterprise without detracting from core business initiatives?
Solution
Universal real-time data integration is a serious challenge that supersedes the capabilities of native SQL Server relational engine, SQL Server Integration Services (SSIS), Azure Data Factory (ADF) and Azure Synapse Analytics technologies. With so many organizations dependent on third party ERP (SAP, Acumatica, Sage, etc.), CRM (Salesforce, Service Now, Dynamics, etc.) and cloud solutions (Azure, AWS, Snowflake, Databricks, etc.) for real-time data access within internal custom-built applications and data warehouses, the challenges are momentous.
CData has recognized these technical challenges and organizational impacts, and has delivered a universal data access solution via drivers for on-premises, custom, ERP, CRM and cloud applications. The CData platform is a consistent relational \ tabular interface on top of the data source without the need for any knowledge of the underlying data structures or programming language. With CData software installed in the environment, Developers are able to seamlessly access data across 240+ applications and database platforms with simplicity through the SQL language. The time savings is unprecedented for constrained technology teams enabling them to meet the ever-increasing organizational leadership demands without having to be an expert on hundreds of applications.
The real value of CData’s platform to SQL Server Developers, Business Intelligence Professionals and DBAs is access to all 240+ applications CData supports via SQL code that we all know and love. There is no learning curve. CData’s Connectors are SQL-92 Compliant. Data Professionals can use the same SELECT statements, JOINs, control flow logic, etc. that are used daily in SQL Server Management Studio or Azure Data Studio. Just install the correct driver on the SQL Server and reference the other applications via a four part name (Server.Database.Schema.Object) in the FROM clause in SQL code. Under the covers, CData will translate the SQL logic into an API call to the source system. The time, energy and effort involved with monolithic ETL (data extract, transform, load) processes becomes obsolete and so does the notion of not having real-time data available to the business leaders.
Let’s explore what CData can do for both the business and technology professionals.
How does CData support the technology teams and organizational leadership?
CData is a universal data integration platform that enables technology teams to connect organization leadership with critical data across the organization in real time as seen below:
Let’s outline the functionality of each CData data connectivity product.
CData Connect
CData Connect enables Developers, Business Intelligence Professionals, Database Administrators and Power Users to build applications with real time data access to 250+ on-premises and cloud applications. Think of this product as a suite of drivers that is your gateway to your data from disparate sources in real time with options to install CData Connect on-premises or as a SaaS cloud platform. With CData Connect, DBAs will setup individual users with credentials to control data access (SELECT, INSERT, UPDATE and DELETE) and object access (tables and views). Whether writing SQL Server stored procedures, .NET code or self-service business intelligence solutions with Power BI, Excel, Tableau, QlikView, etc. CData Connect will enable access to your data without the need for intermediary ETL \ ELT steps, ultimately saving time, computing resources, storage and outdated data.
Check out this article to see how Koen Verbeeck of MSSQLTips.com integrates Snowflake and SSAS to meet business intelligence needs - Analyze Live Data from Snowflake with SSAS and the CData ADO.NET Adaptor.
For more information visit – CData Connect and this video.
CData Sync
CData Sync enables intelligent data replication among databases for synchronization, reporting, incremental replication as well as backup and data archive between on-premises and cloud databases. This is accomplished by setting up a Job to perform the data replication. CData Sync enables table level replication between different platforms with the ability to replicate only particular columns and filter the rows being replicated. Further, with the incremental replication functionality, only deltas (data changes) are being replicated, not the entire data set.
For more information visit – CData Sync and this video.
CData ArcESB
CData ArcESB is a visual workflow designer with access to the CData suite of drivers with mapping, transformation and automation capabilities. Within a single CData ArcESB workflow, data can be integrated from numerous applications (ERP, CRM, Marketing, Collaboration, Financial, etc.) with output to a database or flat file based on your needs.
For more information visit – CData ArcESB.
CData API Server
CData API Server is a solution for Developers to generate APIs (REST, ODATA, JSON, SOAP, CSV/TSV) rapidly from relational databases, NoSQL platforms and flat files that are fully managed (security, connectivity, monitoring, logging, etc.) for internal and cloud-based applications. Within minutes, Developers can start working with the API from .NET, Java, PHP, Power BI, Excel and more.
For more information visit – CData API Server.
CData DBAmp for SQL Server and Salesforce
CData DBAmp is a bi-directional Salesforce data access pipeline enabling business intelligence, integration and backups built exclusively for SQL Server. For all of the details (installation, configuration, querying, building a dimensional model for business analytics, etc.) on this product to seamlessly enable data access from SQL Server to Salesforce, read this article from John Miner on MSSQLTips.com - Adding Salesforce Connectivity to SQL Server.
For more information visit – DBAmp: Salesforce to SQL Integration.
CData SSIS Integration
CData knows that SQL Server Integration Services (SSIS) and Azure Data Factory (ADF) are part of the data infrastructure at many organizations. ETL processes are necessary to exchange data among organizations and partners as well as a transitional step before moving to real time data access with CData Connect. Often times SSIS faces data access limitations. CData can help support those SSIS processes with 200+ Data Flow components and 30+ Control Flow components that are not natively available.
Check out this article from John Miner on MSSQLTips.com - Salesforce and QuickBooks Integration to Snowflake via CData SSIS Connectors.
For more information visit – CData SSIS Components and Azure Integration.
CData PowerShell
CData PowerShell is designed with the scripting savvy Database \ IT Administrator in mind. After a simple installation, Administrators are able to access 225+ data sources for automated importing, exporting, data cleansing and more all via SQL code.
For more information visit – CData PowerShell Cmdlets.
Where is CData’s technology implemented?
CData’s technology is just not used by organizations around the globe, but many of the tools you use today could have CData Drivers already embedded in them. CData is trusted by:
|
Business Intelligence and Analytics
Data Integration
Data Governance
Data Warehousing
|
Artificial Intelligence and Machine Learning
Data Preparation
Data Storage
Data Virtualization
|
Perhaps you are already using CData technologies in your organization today.
Why is CData appealing to SQL Server Developers, Business Intelligence Pros and DBAs?
Rather than running ETL processes on a regular interval, Log Shipping or even full backup and restore, CData provides a real time interface to the data directly from SQL Server with SQL code that we already use currently. There is no learning curve to using CData–simply access the data via SQL code. This means that the technology team can stop managing ETL chaos with multiple processes pulling the same data, maintaining SQL Servers and licensing for ETL \ reporting, rising SAN storage costs and contending with out-of-date data.
In John Miner’s article on MSSQLTips.com, he details the CData wizard-based driver installation and simple Linked Server configuration to enable enterprise data access between SQL Server and Salesforce. Below we see the three screens to configure a SQL Server Linked Server in order to access the Salesforce data.
Linked Server General Configuration – Source
Linked Server Security Configuration – Source
Linked Server Options Configuration – Source
With the CData Driver installed and SQL Server Linked Server configured, Developers and DBAs are able to access remote objects via a 4 part name (Server.Database.Schema.Object) in SELECT, INSERT, UPDATE and DELETE statements as shown below.
Accessing Remote Data via CData – Source
How do I get started with CData?
- Identify data integration challenges and use cases impeding your organization
- Organizational Leadership
- Lack of insight into the data across the enterprise
- Poor decision making
- Poor planning
- Data out of sync
- Not 1 version of the truth
- Technology Group
- Siloed data across the organization and data management challenges
- Time to build and maintain data infrastructure via ETL code \ Full Backup and Restore \ Data Extracts and Uploads to support decision making
- Lack of development resources
- Storage costs for duplicate data
- Licensing costs for ETL \ Reporting SQL Servers
- Organizational Leadership
- Contact CData ([email protected] | (919) 928-5214) to setup a time to see their products in action, get your questions answered and see how CData can help with build a unified view of your data.
- Download the correct CData solution to address your data integration needs
- Report the findings to your team and take the next steps
Next Steps
- Download
- Contact Sales
- [email protected]
- (919) 928-5214
- Additional Resources
MSSQLTips.com Product Spotlight sponsored by CData.
About the author
Jeremy Kadlec is a Co-Founder, Editor and Author at MSSQLTips.com with more than 300 contributions. He is also the CTO @ Edgewood Solutions and a six-time SQL Server MVP. Jeremy brings 20+ years of SQL Server DBA and Developer experience to the community after earning a bachelor's degree from SSU and master's from UMBC.This author pledges the content of this article is based on professional experience and not AI generated.
View all my tips
Article Last Updated: 2023-07-20
