Integrated Analytics on data of SAP & Salesforce CRM for esteemed Energy Conservative Company

Client

The Client is an Energy Conservation Company based in the state of Texas, United States who has for three decades revolutionized how their clients view, consume and use energy nationwide.


Industry

Technology, E-commerce


Business Challenge and Approach

  • Our US based client uses SAP as their ERP system (Finance (GL, AP, AR), Inventory etc.)and Salesforce (Lead, Opportunity, Deal, Customer, Products etc.) as CRM system. They approached Volga with a requirement which aimed to integrateSAP andSalesforce data and analyse it to take key business decisions,
  • Volga’s Team of experts identified a mechanism for extracting SAP and Salesforce data from source system on schedule bases.
  • SAP database tables had column names in German language , Over team resolved them and created Source to Target mapping for various tables.
  • Staging database was modeled for both SAP and Salesforce.
  • Created ETL packages using third party tool along with SSIS for daily data extraction from both the system.
  • Scheduled Jobs to extract data on daily scheduled time period.
  • Accurate Logging and Auditing mechanism created to keep track of ETL activity and data transfer.
  • Data warehouse modelled for integration of both the systems as one, Common dimensions created between both the system to view integrated data.
  • Analytical Solution created using SQL Server Analysis Services (SSAS) to resolve complex queries and allow quick analysis on data.
  • Custom calculation, KPI, User level data security implemented in OLAP solution.
  • To send daily reports on schedule bases, we used SSRS for creation of reports and scheduled them to send reports automatically on daily, weekly and monthly schedule.
  • Interactive Dashboard created for visualization of data in Tableau.
  • Self BI capability given using Power Pivot , so client can analyse their data on their own.
  • User Security Implemented allow users of a particular group to visualize and analyse only authorized data .

Benefits

  • Daily automatic loading of SAP and Salesforce data to SQL Staging database.
  • Daily automatic & incremental loading of data to data warehouse.
  • Daily automatic Processing of date in OLAP Cube
  • Integrated view of SAP and Salesforce data for better analyses.
  • Restricted access to data as per defined user role.
  • Easy comparison of data over various time periods. I.e. week on week, month on month, year on year comparison.
  • Quick answer to Decision makers to allow them to take accurate decisions.
  • Daily receipt of automatic reports in email to various user groups.
  • Self BI capability using power pivot.
  • Attractive, Interactive and informative dashboards using Tableau.

Solution

Extracting SAP data to SQL Staging


We used CMORE/Connect tool offered by PMONE as a solution for extracting data from SAP to SQL Staging. Our team has expertise on configuring CMORE/CONNECT for data extraction. CONNECT generated SSIS ETL Packages automatically using metadata of SAP after proper configuration of the tool.ETL generated were used to extract SAP data to SQL staging.



CMORE/CONNECT for SAP is intelligent software for connecting SAP and Microsoft technology. CONNECT eliminates hours of tedious work by creating infrastructure automatically to make content (dimensions, hierarchies, characteristics and key figures) based on data and structures from the SAP environment available in a Microsoft SQL Server environment.

This way, Microsoft technology with all of its flexibility, ease of use and good response times, can be used in conjunction with a SAP environment. The necessary licenses for software infrastructure (Microsoft SQL Server, Microsoft Office) already exist in many companies, so this enables reuse of existing investments.


Automatic Creation of SQL Server Integration Service Packages for SAP data Extraction.




Introduction cMORE/Connect-PMONE


Many companies rely on SAP ECC (R/3) as a central ERP system. At the same time, many companies are using software infrastructure from Microsoft, especially SQL Server, Excel and SharePoint. These products are commonly used because Microsoft technology is regarded as an innovative and cost-effective solution for Business Intelligence and Big Data. cMORE/Connect for SAP combines SAP and Microsoft technology to provide a solution for reporting and analysis which takes advantage of existing IT infrastructure and enables companies to reuse existing investments.


Jump to start your implementation with cMORE/Connect

  • This innovative product uses key building blocks to speed up implementation
  • Access to actual SAP metadata for easier integration of standard and custom data sources and fields
  • Data loading process management creates robust infrastructure
  • Automatic data synchronization between SAP and Microsoft BI based on best practices
  • Fast and flexible connectivity to SAP data sources
  • Ability to perform full and incremental data loads
  • Predefined business content (optional, available for a variety of subject areas)

Benefits

  • Daily Automatic data synchronization from SAP system to SQL staging database.
  • Easy and quick generation of ETL to extract data of SAP using its metadata.
  • No need to worry about German language interpretation to understand database table and its columns.
  • Easy schedule of jobs in SQL agent using Connect itself.
  • Easy monitoring of audit data using SSRS reports given with Connect tool.

Technology Used


Languages C#
Database SSQL Server 2012 Enterprise
ETL Tool SSIS
Third Party toolkit CMORE/Connect by PMONE, Theobald toolkit
Frameworks .Net Framework 3.5
Source Control TFS
Job Schedule SQL Server Agent

Extracting Salesforce CRM data to SQL Staging


We used third party tool kit CozyRoc along with SSIS and build ETL solution for extracting daily data on incremental bases from Salesforce CRMto SQL server Staging database using Salesforce web service.

ETL Job Scheduled to extract daily data from live Salesforce CRM to Offline staging database.




Benefits

  • Daily Automatic data synchronization from Live Salesforce to Offline SQL staging database.
  • Offline Backup of Salesforce data.
  • Data will be with you if you discontinue salesforce subscription.

Technology Used


Languages C#
Database SSQL Server 2012 Enterprise
ETL Tool SSIS
Third Party toolkit Cozyroc
Frameworks .Net Framework 3.5
Source Control TFS
Job Schedule SQL Server Agent

Data modelling for Integration of data in Data warehouse


  • For Integrated view of data and analytics of the data, our expert team identified queries to be answered by the new system and came up with a data ware house design
  • Our Team identified common dimensions between the two systems
  • Designed ETL Package for delta loading of Dimensions and Fact tables of data warehouse.
  • Scheduled the ETL Job to perform the data loading operation on regular time interval.
  • Error Logging and Auditing implementation for Accuracy of data.
  • Fail over mechanism implemented in case of any failure.


Benefits

  • Integrated, time variant view of data stored from two different system in common data model.
  • Preserving Historical data changes for later analysis.
  • Time variant and multidimensional analysis on your historical data.

Database SQL Server 2012 Enterprise
Source Control TFS
ETL Tool SSIS
Job Schedule SQL Agent

OLAP Solution designed on Data warehouse to support complex data analytics


  • Our team of experts used SQL Server Analysis servicesfor development of OLAP Cube.
  • Dimensions and Fact tables created using SSAS (SQL server analysis services).
  • Performance Optimization done for quick processing of OLAP cube and Quick response to complex User Queries.
  • Security Implementation done to restrict cell level data to various user groups.
  • Custom calculations and KPI created in SSAS to support complex analyses quickly.
  • Automatic Cube processing create using SSIS to process data in OLAP cube and scheduled Data will be with you if you discontinue salesforce subscription.


Benefits


  • All the benefits of OLAP Cube inherited in this design against huge data growth.
  • Custom calculations, aggregations, MOLAP storage offered benefit against quick and complex queries of user groups.
  • Cell level security Offered benefit of restricting user groups to access authorized data only.

Database SQL Server 2012 Enterprise
Source Control TFS
ETL Tool SSIS
Job Schedule SQL Server Agent
SQL Service SQL Server Analysis Services
SQL Tool SSDT

Visualization given using Power Pivot, SSRS and Tableau


  • SSRS Reports created and scheduled to send as attachment in email for Visualizing data.
  • Dashboards created in Tableau for interactive analysis on OLAP cube.
  • Power Pivot interface created as per user group to do self service analysis as per user permission.


Benefits


  • SSRS Reports created and scheduled for time based automatic delivery.
  • Report Model created to allow Ad Hoc report creation by user on their own.
  • Various templates created in Power Pivot to view integrated data of SAP and Salesforce from OLAP Cube as per authority of user login.
  • Tableau dashboards created for better visualization, interaction and information of data.

Database SQL Server 2012 Enterprise
Reporting Tool SSRS
Source Control TFS
Dashboard Tableau
Excel Services Power Pivot
SQL Tool SSDT

Integrated Analytics of SAP & Salesforce CRM Data



Few sample Queries can be answered by above BI Solution


  • How many opportunities are there for an Existing Customer?
  • What is the Payment Trend of a customer?
  • What is the Open Revenue by Geographical Locations?
  • Who are the customers with higher Open Transaction counts?
  • What is the Open revenue for Existing customers?
  • How many deals were closed by existing customers?
  • How many leads were associated with existing customers?
  • Which locations has the maximum customers?
  • Which geographical location gets the maximum revenue?

Why Volga Infotech?

  • High-quality and cost-effective services
  • High-end technology and best-of-breed infrastructure
  • Skilled, talented and experienced professionals
  • Daily updates on the progress of work
  • Direct contact with the Team
  • Save on time, effort and infrastructure by outsourcing
  • Maximize revenue and minimize expenses
  • Quick turnaround time
  • Latest software and technologies