| Client : | University of Bridgeport |
| Category : | Education |
| Date : | 21 March, 2016 |
| Status : | In Process |
| Location | USA |
| Tags : | Education, university, bridgeport |
Company Overview
University of Bridgeport (UB) is a private, independent university which provides diversified courses for Under Graduate and Post-Graduate programs for the students and is ranked among top 20 University in USA. Every year they give admission to students from across the globe for various courses like Engineering, Management and so on. For managing students they are primarily using ERP which stores information like students, subjects, courses, grades, demographics etc.
University of Bridgeport has many student applications every year as it is one of the top 20 Universities in the US. This huge number of applications made their work difficult to manage. The data was coming from ERP and excel format data source and it was difficult to manage and control the entire procedure.
The University used CRM and ERP software to store the data. The data was stored in the following manner:
Inquiry data was stored in CRM, application data in both CRM & ERP and the rest in similar fashion as shown above. We received this data in the form of .CSV and .XLS. This data was then moved to a Data Warehouse using Pentaho Data Integration Tool (by executing ETL). The data in the Warehouse is then used to develop Business Analytics Reports.
Work done in detail:-
- We helped the organisation to fix issues at the source level which include, fixing the data in flat files like column shifting, date column related issues, etc.
- The above problems were fixed by performing Data cleansing which involves writing scripts using Pentaho Data integrator ETL tool.
- Developed ETL jobs to move source data to the warehouse by using appropriate business logic. These jobs included processes like error handling, logging, scheduling & alerting via E-mail.
- After running these ETL scripts and loading into Data Warehouse, a proper data validation was done.
- Using Pentaho, we developed Business analytical reports like Crosstab reports, Tabular reports; Pictorial charts (Pie Charts, Candlestick Charts, Bar Charts, etc.)
- Apart from the above work done, we also helped them in setting up the BI Server which had software like Pentaho Data Integration, Pentaho Business Analytics Suite.
Operating System : Windows server 2008
ETL Tool : Pentaho data integration version: 4.8
BI Server : Pentaho Business Analytics suite
Report Designer : Pentaho report designer version: 5.0
Database : SQL Server 2008
Connectivity : Open VPN
- Challenge
-
University of Bridgeport has many student applications every year as it is one of the top 20 Universities in the US. This huge number of applications made their work difficult to manage. The data was coming from ERP and excel format data source and it was difficult to manage and control the entire procedure.
- Solution
-
The University used CRM and ERP software to store the data. The data was stored in the following manner:
Inquiry data was stored in CRM, application data in both CRM & ERP and the rest in similar fashion as shown above. We received this data in the form of .CSV and .XLS. This data was then moved to a Data Warehouse using Pentaho Data Integration Tool (by executing ETL). The data in the Warehouse is then used to develop Business Analytics Reports.
- Architecture
-
Work done in detail:-
- We helped the organisation to fix issues at the source level which include, fixing the data in flat files like column shifting, date column related issues, etc.
- The above problems were fixed by performing Data cleansing which involves writing scripts using Pentaho Data integrator ETL tool.
- Developed ETL jobs to move source data to the warehouse by using appropriate business logic. These jobs included processes like error handling, logging, scheduling & alerting via E-mail.
- After running these ETL scripts and loading into Data Warehouse, a proper data validation was done.
- Using Pentaho, we developed Business analytical reports like Crosstab reports, Tabular reports; Pictorial charts (Pie Charts, Candlestick Charts, Bar Charts, etc.)
- Apart from the above work done, we also helped them in setting up the BI Server which had software like Pentaho Data Integration, Pentaho Business Analytics Suite.
- Tools and Technologies
-
Operating System : Windows server 2008
ETL Tool : Pentaho data integration version: 4.8
BI Server : Pentaho Business Analytics suite
Report Designer : Pentaho report designer version: 5.0
Database : SQL Server 2008
Connectivity : Open VPN
