Data Integration: More Than the Sum of Its Parts

Every day, office workers problem solve, often using disparate systems that need to communicate with each other to accomplish the goal at hand. No industry is immune from these operational headaches and inefficiencies. It’s often the job and responsibility of internal teams to interface between archaic and disconnected systems and databases. 

This lack of modernization and foresight comes at a cost. According to the Harvard Business Review, some teams lose up to 60% of their working day on actions like extracting data from multiple systems only to enter it into another. This lost productivity contributes to “disconnection debt.” When you dig deeper, these are costs companies incur from lackluster results and missing the mark on key performance indicators.

This operational nightmare hit home for our team at 3Pillar. At one point, different departments were using six separate business systems (Salesforce, Workday, OpenAir, Lever, Netsuite, and Engage) to meet their functional and operational needs. Each system captured specific information related to their work and generated data in different formats. 

This workflow resulted in the formation of data silos, adversely affecting the efficiency and accuracy of our reporting process. Generating reports based on the information from different sources has historically been a tedious task to accomplish.

In contrast, 3Pillar’s leadership team envisioned a more expansive approach. The idea was to develop a centralized data platform designed to function as the singular source of truth for all reporting requirements throughout the organization. This move would set up a foundation to integrate data from all six systems, eliminating manual processes associated with Key Performance Indicator (KPI) generation.

Following 6 months of active development, we designed and implemented a cloud-based data platform that seamlessly ingests, transforms, and stores data coming from the six separate systems through a daily batch processing system. 

Here’s a look at some of the foundational elements of this solution:

Data Ingestion: Our team leveraged Azure Data Factory to ingest data from NetSuite and Azure Databricks to ingest data from five other sources. The ingestion process is an incremental loading process and is based on a field such as “last execution date,” to ensure efficient data retrieval and processing.

Storage: Data is stored initially in ADLS Gen2 Raw Container, ensuring secure AES 256-bit encryption by default.

Data Transformation: The Azure Databricks pipeline performs data cleansing and formatting tasks, subsequently transferring the processed data to the ADLS Gen2 Processed Container.

Data Loading: Tables are classified into two categories: those that maintain data change history and those that do not. Our team used Azure Databricks to manage data change history using Slowly Changing Dimension Type-2 and to perform additional transformations prior to loading into Azure PostgreSQL DB.

Insights Generation: Power BI enhances reporting reliability and efficiency by seamlessly integrating with Azure PostgreSQL DB. This synergy means swift and precise data accessibility.

Security: All stages prioritize security with Azure’s encryption standards and access controls, managed through Azure IAM integration. Azure Key Vault serves as a centralized repository for securely storing and managing sensitive credentials, including Azure Storage Account keys, passwords for the Data Warehouse (DWH), and source system access keys.

Governance: Role-based Access Control (RBAC) in the Data Warehouse (DWH) regulates data access with granular permissions based on user roles and responsibilities. Also, IP whitelisting in server firewalls is required to access the data in Data Warehouse (DWH).

On the surface, this cloud-based data platform made it possible to access data from multiple sources without any lag, support business processes, and provide relevant and real-time insights to inform decision makers. Since implementation, we’ve reported the following qualitative and quantitative results:

1. Centralized Data Integration: Consolidating data from six disparate systems into a single, reliable source of truth eliminates data silos and ensures consistency.

2. Efficient Reporting: Power BI, when employed with Azure PostgreSQL DB, makes for streamlined reporting and quick and accurate data access. This reduces manual effort of data preparation and shifts complex calculations to Azure PostgreSQL DB. Ultimately, this eliminates the need for additional joins that would have been required if different data sources like Excel and CRMs are directly connected into Power BI.

3. Enhanced Data Quality: Azure Databricks pipelines ensure data cleansing and formatting, improving data accuracy and consistency across reports and analyses.

4. Cost Efficiency: Cloud-based storage and automated data processes reduce operational costs compared to multiple manual processes and systems. Using the data platform, our team reported that development and refresh time were reduced by almost 40%. That, in turn, reduced the operational costs. It also opens up the possibility to build more complex cross-system reports in half of the time required.

5. Robust Security: Azure’s encryption standards, Role-based Access Control (RBAC), and Azure Key Vault for secure data handling make it possible to meet compliance and governance requirements effectively.

Overcoming these challenges means an opportunity in that we can help other organizations achieve similar results. However, finding a data integration partner is about more than outsourcing. It’s about truly gaining a strategic partner that ensures your investment of time and resources translates to bottom-line results. It’s high time to find efficiencies at scale through integration. 
Ready to get started? Talk to the 3Pillar Global team today.