MS BI (SSRS/SSIS/SSAS) & POWER BI

As a Business Intelligence Solutions Delivery Leader, RPL continues to deliver MS BI / Power BI analytics and reporting for building achieving valuable insights from structured, and unstructured data. RPL’s BI team will select, optimize and deploy an array of custom and pre-built analytics and reporting tools to help you understand your valuable insights for your business decisions and growth. Our BI Services includes:
  • Strategic Planning, BI Assessments, BI Roadmaps
  • BI Tool Selection and purchase Assistance
  • Prototyping and Proof-of-Concept Exercises
  • Data Migration, and Data Integration
  • BI Architecture Design and Optimization
  • Analytics Reporting Automation, and Dashboard Development
  • MDM and Data Governance Guidance

RPL team understands how to efficiently extract information collected from multiple sources, including financial data, sales and marketing data, ERP data, and other data that could impact your enterprise and from there develop reporting, dashboards and frameworks for presenting analytics and deliver actionable insight for your leadership team..

MS BI (SSRS/SSIS/SSAS)
SQL Server Reporting Services (SSRS) - SSRS is used to create and manage web-enabled reporting service. It is used to create and generate reports, to retrieve data from various data sources, and to publish reports in various formats.

SQL Server Integration Services (SSIS) - SSIS has the ability to gather data from various resources in different kinds of formats, process this data, transform it and convert the processed data into any form that you can use in your daily business as well as for data-mining and data warehouse applications.

SQL Server Analysis Services (SSAS) - It stores, processes and secures data. It delivers OLAP (Online Analytical Processing) and data mining functionalities for applications. It is used to design, create and manage multi-dimensional structures that contain data, aggregated from other data sources.

POWER BI
Power BI is a cloud based business analytics service, allowing anyone to visualize and analyze data with greater speed, efficiency and understanding.

Benefits
  • Extremely low start-up costs
  • Easy to build & use Dashboards
  • Built – in Content Packs and Dashboards
  • Integrated into Office 365
  • Great Interactive Reports
  • Visualize & Analyze Everything in One Place
    • Power BI Desktop
    • Power BI Mobile
    • Power BI Gateway

Power BI ships out of the box with over 60 content packs for all the major applications such as:
  • Development
  • Dynamics CRM
  • Salesforce.com
  • Google Analytics
  • Adobe Analytics
  • Team Foundation Server
  • And many more ….




MS BI CASE STUDY

Client
Aspen Marketing Services: Aspen Marketing Services is an integrated marketing services company, specializing in serving the telecom and automotive industries. Some of its clients include General Motors, AT&T and AutoNation. This project is mainly concerned with the sales and services provided by the dealers of General Motors to their customers on a daily basis.

Requirement
ASPEN Reporting Portal is an easy to use, clutter free portal designed for ASPEN Marketing Services, to help them evaluate the effectiveness of Smart Touch 2.0 program. The portal provides a set of reports categorized into Marketing, Services and Sales verticals. These reports are generated both at the corporate and individual dealer levels. The reporting features include filtered search and drill- down on data at different levels and dimensions. The portal is designed to have a secure and privileged access to data built on SQL server reporting, analysis and integration services. These reports are provided with significant flexibility and scalability.

Requirements can be summarized as:
  • Downloading the Files from FTP.
  • Importing the data from the files into database and processing them.
  • Showing customer information for each dealer according to the 'sales' or 'service' category
  • Sending emails to customers based on the subscription intervals.
  • Generating the Sales ROI.
  • Role based Reports Accessing.

Dealer Marketing Programs:
Aspen Portal is a groundbreaking all-inclusive web interface for automotive dealers and features Aspen's leading proprietary tools including: e-Strike, Dealer Advantage, Business Central Advantage, Quick Strike, INP and Recall programs. Combining all these components into one empowers the dealers to access today's most complete, user-friendly and state-of-the-art technology solution for security, enrollment and reporting in the automotive industry.

Aspen's CRM platform allows dealers to create email, mail and personalized telephone service campaigns for both sales and services which ensures that customers are always able to reach a representative to schedule services such as sales, maintenance appointments and oil changes. Within the Aspen Portal, these tools combine to help dealers foster successful and longstanding relationships with clients while realizing legitimate ROI goals on marketing dollars spent.

Some of the main requirements include:
  • Downloading the Files from FTP.
  • Importing the data from files into the database and processing them.
  • Dynamically generating the tables based on the campaign being processed.
  • Generating the production files and uploading them to Client FTP site.
  • Reports for showing the Production file counts for each dealer.
  • Reports for showing the Manifest counts for each dealer.

Solution
ARS Project:

Downloading the files from FTP:
For downloading the files from FTP, we have written a service to monitor the FTP and fetch the file from source FTP location to the destination local system and process them.

Automated Daily Feed Processing:
The daily data feed process has been automated using the SSIS packages which includes ETL, staging and processing master data for the reports.

High Performance Reporting:
The reports are developed using SSRS and SSAS which has enabled faster data retrieval and easy access to data.

Report Subscription:
Report subscription facility enables privileged users to configure for periodic subscriptions of various reports and be able to receive up-to-date report information in the format selected, on a regular basis through e-mails.

Analyzing the data from Database:
Using SSAS we create cubes, based on the requirement, like viewing the number of sales for a dealer in a particular region during a year, or number of sales during a week, in a day etc.,

Role Based Report Accessing:
Using SSRS, we can provide role based access to the users. In other words, we can restrict the user from viewing reports which have secure information. Such reports will be viewable to users based on the role assigned to them.





Dealer Advantage and Business Central Advantage:
DA & BCA are two programs designed to get enrollment information of the dealer on to the portal. The dealer undergoes 5 enrollment steps to get enrolled in a campaign. Once the dealer gets enrolled, he can update the information about the total number of mailings to customers in that particular campaign.

Automating the Production Files Generation Process:
  • Downloading the FTP Files using FTP File Watcher Service.
  • Using SSIS Packages to load the data to ETL Database.
  • Moving the data from ETL Database to the ProductionTables generating these tables dynamically.
  • Generating the Production Files and uploading them to the Client FTP location using SSIS Packages.
  • Creating Stored procedures and functions (as per Requirement).
  • Generating the SSRS Reports for the Production Table data.