SSRS stands for SQL Server Reporting Services. 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.
SSIS stands for SQL Server Integration Services. 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.
SSAS stands for SQL Server Analysis Services. 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.
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.
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.
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 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.