| Customer: A well-known data-driven marketing solutions provider | Profile: The client serves its customers by providing insights into specific market segments, and targeted platforms to engage with those audiences.
Services: Business Intelligence |
| Size: 201-500 employees | |
| Region: Tuscaloosa, Alabama | |
| Industry: Marketing and Media |
Business Need
Offering strategic data, media, and marketing services to over 4,000 clients, the solutions provider’s unique data assets and highly customizable digital marketing applications enable businesses to identify and target sales, marketing, and recruiting prospects. They serve the industrial end markets including heavy duty trucking, construction and agricultural equipment, and machine tools.
The solutions provider had implemented Microsoft Dynamics CRM Online to manage customer data collected from several sources (such as their website, customer service, marketing data, and social media), with goals to support growth, and enable sales representatives to track and close deals quickly.
With information being captured, processed, and entered from a multitude of sources, the solutions provider faced the issue of data quality. They wanted to clean, append, and refine their CRM Online data. In addition, the solutions provider required a business intelligence and analytics platform that was user friendly and supported CRM Online as a data source – they wanted to combine CRM Online with another tool for data visualization purposes.
The solutions provider identified that capturing massive and varied amounts of their CRM Online data using Azure SQL Data Warehouse, and transforming it into valuable business insights using Power BI were the answers to all of their requirements.
Solution and Approach
The solutions provider engaged with Synoptek as the preferred information technology partner to implement Azure SQL Data Warehouse project and Power BI integration.
Azure SQL Data Warehouse Implementation
The implementation involved three key phases:
Phase 1: Real-time data synchronization between Dynamics CRM Online and Azure SQL. This included:
- Configuring the Data Export Service to replicate Dynamics CRM Online data to an Azure SQL Database
- Replicating all the relevant entities such as accounts, leads, opportunities, and contacts
Phase 2: ETL process: This process converted raw data from Azure SQL Database to a normalized data model in the Azure SQL Data Warehouse. This is where the data is stored in a star schema (fact/dim tables with column store indexes), using the ETL (Extract, Transform, and Load) process, which was executed through Azure Data Factory pipelines and SSIS packages.
Phase 3: SSAS Tabular Model Creation: This process encapsulated all of the information, stored in Azure SQL Data Warehouse, into a semantic model using Azure Analysis Services, and creating an SSAS Tabular Model.
With SSAS Tabular Model, the data to be consumed for Power BI visuals was made readily available.
Power BI Integration
Synoptek (formerly Indusa) team successfully integrated the solutions provider’s CRM Online system with Power BI. This stage of the project involved two key phases:
Phase 1: Dashboard Setup/Roll-out
- Consuming data from SSAS Tabular Model
- Building visualizations using Power BI
- Setting up a deployment process and training the solutions provider’s staff to deploy new dashboards as they are developed
Phase 2: CRM Online Dashboard Embed: This process embedded the appropriate Power BI dashboards in the CRM dashboard pane.
Synoptek (formerly Indusa) configured Power BI so that the solutions provider was able to slice or aggregate the data by: client, product, product family, industry, lead source, CRM stage, and more.
Business Results
With the implementation of Azure SQL Data Warehouse, Synoptek helped the solutions provider ingest, store, and prepare their CRM Online data for analysis – all the data that they take-in on a daily basis is ready for analytics at whatever scale they require.
Business leadership no longer has to make decisions based on inconsistent information/duplicate data, so the decisions that affect strategy and operations are based on data-driven facts and supported by relevant information that the solutions provider has gathered over time.
ETL consolidates data from multiple sources and transforms it into a useful format. This allows users to easily access data from one interface.
With the integration of CRM Online and Power BI, the solutions provider is able to gain rich sales insights via interactive map-based reports. Pipeline analysis provides them with an array of data, including opportunity and account analysis and revenue spread figures. They can also track sales performance by lead size, lead age and uncover emerging trends.
Both the Azure SQL Data Warehouse implementation and Power BI integration were successfully performed in the span of just 18 weeks. The project involved four BI experts, one business analyst, and one quality assurance specialist.
Working with Synoptek has changed the way the solutions provider makes business decisions. They now have full control over what analytics they want to see – whether on desktop computer, tablet or smartphone.
Download PDF