Services
Platform and Technology Capabilities
Home / Insights / Case Study / Azure SQL Data Warehouse and Power BI Helps a Marketing Solutions Provider Improve Data Culture, Deepen Data Insights, and Make Better Business Decisions
Services: Business Intelligence
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.
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:
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
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.
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.