GPS and Fleet Tracking Data Correlation Provides Holistic View For Sales & Operations  

A national plant wholesaler that supplies retail stores throughout the United States re-engaged Imaginet to work on a data correlation project. The client has greenhouses and production facilities across the country and a network of trucks to deliver products to various stores. The trucks have been outfitted with J.J. Keller ELDs and use the J.J. Keller Encompass Fleet Management Platform to monitor their GPS locations and other key metrics (e.g., engine RPMs, speed, and braking). In the Encompass system, each retail store has a defined geofence that shows the boundaries of that location, and it can track when a truck enters and leaves a store’s geofence. The Encompass data helped the client address safety concerns, schedule shipments, and improve staff efficiency, but they lacked a holistic view of their trucking fleet. They needed to bring the Encompass data into their data warehouse to determine where shrinkage may be occurring.

Imaginet built a utility in Azure Functions, using C# and the J.J. Keller REST API, to extract data (JSON files) from Encompass. The data was brought into Azure Storage, transformed through Azure Data Factory, and placed into the data warehouse (built in Azure SQL Database). We thoroughly reviewed, interpreted, and mapped the data from Encompass and, with Power BI, plotted the coordinates of the trucking fleet (organized by details such as speed incidents, locations where trucks are prone to speeding, and how long trucks spend at each stop). We used Agile methodology and Azure DevOps to create and track work items.

Imaginet helped the client connect their data source to their data warehouse to provide a clearer view of sales and operations, helping them make crucial business decisions, and optimize their processes to eliminate product waste and increase profits.

Technologies used:

• Agile
• Azure Data Factory
• Azure DevOps
• Azure Functions
• Azure SQL Database
• Azure SQL Server
• Azure Storage
• C#
• DAX
• J.J. Keller ELDs and Encompass ELD Mobile App
• J.J. Keller ELogs
• J.J. Keller Encompass Fleet Management Platform
• J.J. Keller Encompass Vehicle Tracking & Portal
• JSON
• Microsoft Dynamics 365
• Power BI
• QlikView
• REST API
• SQL Server 2016
• Transact-SQL

Data Centralization & Reporting Automation Improve Business Efficiency  

A prominent operator of retirement communities and long-term care homes was looking for a partner to help them clean their data, improve data flows, and centralize data sources. Their Power BI development had become complex, challenging, and time-consuming to load Microsoft Excel/CSV files every month to produce standard reports. To solve the issue, Imaginet constructed a centralized, organized data warehouse, imported the Excel files into tables, and created a Power BI model with improved performance.

We used automated processes to bring files from Google Drive into Azure Storage, then used Azure Data Factory to run scripts, load, and transform the data from source files into the data warehouse (built in Azure SQL Database). We used Azure DevOps to create and track work items, manage source code, and deploy code automatically to test and production environments using release pipelines.

Our code review procedures acted as a quality gate and offered opportunities for feedback and learning for the client. We provided extensive mentoring and training through Microsoft Teams to give our client the knowledge and confidence to maintain the data warehouse with internal resources.

Imaginet helped the client move away from siloed data and develop a more cohesive data strategy. By consolidating data from multiple sources and automating a complex reporting system, we drastically reduced the time and effort needed to generate monthly reports, creating a clearer view of the business and improving ROI.

Technologies used:

• Azure Data Factory
• Azure DevOps
• Azure SQL Database
• Azure Storage
• Microsoft Excel/CSV
• Microsoft Teams
• Power BI
• SQL Server Data Tools

Cloud-Based System & Custom Reporting Delivers Comprehensive View of The Customer

A well-known consumer packaged goods (CPG) organization specializing in pet food approached Imaginet to implement a new cloud-based system for their consumer affairs office. Their existing on-premises environment had a local database, server, and data model that had been in place for over 15 years, and they planned to decommission the legacy server. The customer service system collected data daily from the client’s contact centre (from phone calls, emails, and the consumer web portal). Imaginet imported the call centre data, including images submitted by consumers to support their complaints, and created a user-friendly model that allows the client to produce reports.

We sourced data from SharePoint Document Library files and XML files (from an SFTP Server hosted by the third-party contact centre), leveraging Azure Data Factory pipelines to store the files in Azure Storage and import the data into an Azure SQL Database. We identified reports to migrate from Microsoft Excel and earlier Power BI workspaces that used the on-premises SQL Server database and analysis model. In Power BI, we assembled dataflows and datasets – utilizing them to compose a robust set of reports that the client can easily pull or customize to suit their needs.

Our comprehensive reporting validation phase included various business teams to compare different versions of the reports and ensure accuracy. As a result of regular mentoring with blended team members, we provided training and knowledge transfer to the client’s development team.

Imaginet built a cloud-based customer service analytics model and reports so the client could retire their legacy server on time. Now, they have a complete picture of their data and can gather, organize, and interpret consumer feedback, save time and effort generating reports, and gain insights from their quality indicators.

Technologies used:

• Azure Data Factory
• Azure DevOps
• Azure SQL Database
• Azure Storage
• Microsoft Excel
• Power BI
• Power BI Desktop
• SFTP Server
• SharePoint Document Library
• Visual Studio
• XML

Robust Data Warehouse & Training Improve Data Management & Processes

The fundraising division of a Canadian university was having issues building reports from their donor management system and needed assistance. Complex data structures from Raiser’s Edge made it difficult for them to build a foundation to move forward and adapt over time as the business changed and software updates took place. The client approached Imaginet to develop a new data warehouse and ETL solution to integrate better with Power BI and lay the groundwork for future updates.

While the Raiser’s Edge on-premises database was the primary data source, we used cloud resources like Azure Data Factory to orchestrate the data movements from the donor management system into the new warehouse. We produced a Power BI model to enable business users to easily create their own dashboards and reports, used Azure DevOps to track work items, and composed automated build and release mechanisms that the client could continue to use going forward. We offered additional training in Power BI and replicated existing reports to link the solution from beginning to end. We continue to provide ongoing mentoring and design advice, as well as demonstration videos to the client for training and coaching purposes.

In a short amount of time, Imaginet improved the client’s data structure, storage, and consumption, while increasing efficiency. Our thorough implementation and development processes enabled their team to learn as we completed the work, giving the client a better understanding of their data warehouse system and Power BI.

Technologies:

• Azure Data Factory
• Azure DevOps
• Power BI
• Raiser’s Edge
• SQL Server (on-premises)

Refined Data Quality and Process Enhancement Streamline Reporting

A global investment manager, asset manager, and service provider contacted Imaginet to help them enhance their data quality and processes. They had numerous data quality issues and relied on a manual approach (using an on-premises system and Microsoft Excel) to clean, check, move, and report on their data. Sometimes it could take up to three weeks to generate a single report, causing a strain on time and resources.

Our team used Azure Data Factory to move and transform the data from several sources in their on-premises databases into a cleaner and more digestible data warehouse (using Visual Studio to create and Git to store database schemas) and tabular model. We gave unstructured data a home using Microsoft SQL Server Master Data Services (MDS) and integrated it with the data warehouses using automated builds and release pipelines in Azure DevOps. Finally, we used SSRS to store and produce reports.

The project gave the client a reason to review business rules and brought errors and inefficiencies to light, especially relating to reporting. By moving the data to the cloud and taking advantage of Azure DevOps release pipelines, we improved and refined the condition of the client’s data and practices, by which they develop reports based on their data. Putting everything in source control ensured that any team member could support and maintain the system. While it used to take multiple weeks to create a single report, now the client can deliver almost 70 monthly reports in just a few simple steps; for a much more automated, user-friendly, and streamlined reporting system.

Technologies used:

• Azure Data Factory
• Azure DevOps
• Azure SQL
• Git
• SQL Server Analysis Services (SSAS)
• SQL Server Master Data Services (MDS)
• SQL Server Reporting Services (SSRS)
• Visual Studio

Data Consolidation Elevates Reporting Structure & Business Analysis

The largest oil producer in Manitoba (and long-time partner) contacted Imaginet to implement a unified data warehouse. They needed to report on data from several sources, including PVR (oil production volume reporting system), InterSystems IRIS (financial data system), P2 Qbyte (financial data system), AccuMap (oil and gas mapping software), and WellView (well information management system). While the client had many valuable data points for current and historical perspectives, it was disjointed, time-consuming, and difficult for them to report across the different systems and see the insights that correlated data could reveal.

Imaginet used SQL Server Integration Services to load and transform the data into a data warehouse hosted on SQL Server (on-premises). We created an analysis cube using SQL Server Analysis Services that loads data from the data warehouse. The client connects to the cube from Microsoft Excel to build pivot tables and charts or, with the help of Power BI, can easily compile robust reports. We have also helped the client start the transition to cloud-based services. When they switched from IRIS to Qbyte for their financial tracking, we used Azure Data Factory to coordinate the data movements and replaced their Essbase cube with Power BI. The introduction of Azure DevOps helped us increase efficiency and collaboration in creating and tracking work items for the project.

Imaginet successfully connected the data from multiple systems so the client could get a better idea of production, broken down to each oil well. With the new system, weekly reporting is simplified and expedited. Up-to-date information is readily available, so the client can review and adjust production times and volumes as needed.

Technologies:

• AccuMap from IHS Markit
• Azure Data Factory
• Azure DevOps
• Azure SQL Database
• Azure Storage
• InterSystems IRIS
• Microsoft Excel
• Microsoft SQL Server (on-premises)
• Oracle Essbase
• Power BI
• PVR
• P2 Qbyte
• SQL Server Analysis Services (SSAS)
• SQL Server Integration Services (SSIS)
• TIBCO Spotfire
• WellView from Peloton

Increased Report and Analytics Efficiency

USA’s #1 grower of vegetable and herb plants needed to gather data from Microsoft Dynamics and improve speed and efficiency when accessing reports. Previously, they refreshed large amounts of data on a daily basis to feed QlikView reports resulting in delays and stalls. To increase analytics efficiency, Imaginet designed and built a data warehouse hosted in Azure to integrate operational data. Multiple pipelines in Azure Data Factory were created to fetch data from various domains like finance, inventory, production, and payroll. Data were sourced from different on-premise SQL Server databases then loaded into their data warehouse. SQL scripts were developed to create data factory task queries, merge stored procedures, and views to accommodate the columns for the dimensional model. Lastly, Imaginet deployed a data model to Power BI that allowed them to get instant insights into their production and create their own reports and visualizations in a proof of concept workspace. DevOps pipelines were created to accommodate deployments for different environments.

Technologies:

• Microsoft BI Stack
  • Azure Data Factory
  • Power BI
• Microsoft Languages
  • DAX
  • T-SQL
  • RDBMS
  • SQL Server 2016
  • Azure SQL Server
• Other Tools
  • Microsoft Dynamics
  • QlikView

Gained Holistic and Accurate View of Mortgage Data

A nationally recognized mortgage lender needed to analyze and report on mortgage data on a daily basis. However, data were stored in multiple disparate systems making it difficult to get an accurate, holistic view of their data. Encompass (an information management system) contained loan information, Velocify managed lead information., and Nextiva had historical data related to calls. In order to gain clarity and make better business decisions, data needed to be extracted across systems and loaded into a data warehouse to be useable in Power BI. Imaginet built an ETL (extract, transform, load) process and multiple pipelines in Azure Data Factory to fetch data from HTTP endpoints and raw files (csv / xml) to the source system. We then transformed the data in a relational form and loaded it into their data warehouse. SQL scripts were developed to create data factory task queries, merge stored procedures, and views to accommodate the columns for the dimensional model. Lastly, we developed a Power BI model sourcing data from the data warehouse allowing them to visualize and analyze data from a single source of truth, and create highly accurate and visually stunning reports and dashboards.

Technologies:

• Microsoft BI Stack
  • Azure Data Factory
  • Power BI
• Microsoft Languages
  • T-SQL
  • DAX
  • DBMS
  • Azure SQL Server
  • Snowflake
  • Rest APIs

Modernized Data Storage and Reporting

A crown corporation engaged Imaginet to design a storage structure of data from a retiring IBM UniData system, extract the data, prepare it for storage, replicate the ODBC connection functionality in SQL Server and Excel, replicate reports in a reporting tool, and develop trust in the data. Data was extracted to a SQL Server database using SSIS pipelines and C# custom applications. SQL objects were created to prepare the database as a source for analytics tools. Reports were built using Microsoft SSRS and ASP.NET. The data was also provided to power users through an Excel connection.

Technologies:

• Microsoft BI Stack
  • SSIS
  • SSRS
• Microsoft Languages
  • C#
  • ASP.NET
  • T-SQL

WellView Peloton API Extraction

An oil and gas subsidiary commissioned Imaginet to extract data from their WellView information management system into a data warehouse to be used with other data consumers. A custom C# app was developed in order to extract data periodically from WellView using Peloton Rest APIs and store the output data to JSON files in a server machine. A pipeline in Azure Data Factory was created to fetch data from the files, transform them in a relational form, and load raw data to their SQL data warehouse. Some SQL development was done to create new tables and views to select all required table columns. Finally, a WellView SSIS package was incorporated into a current SSIS project to load WellView data to join with other database tables. Views, packages, and tables were added or updated to support a data mart. The existing tabular model was updated with the new data so users can consume the data from Excel.

Technologies:

• Microsoft BI Stack
  • SSIS
  • Azure Data Factory
  • SSAS
• Microsoft Languages
  • C#
  • T-SQL
  • RDBMS
  • SQL Server 2016
  • REST APIs