Integrated Analytics on data of SAP & Salesforce CRM for esteemed Energy Conservative Company
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.
- 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 integrate SAP and Salesforce 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 .
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.
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)
- 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.
|Database||SSQL Server 2012 Enterprise|
|Third Party toolkit||CMORE/Connect by PMONE, Theobald toolkit|
|Frameworks||.Net Framework 3.5|
|Job Schedule||SQL Server Agent|