SAS BI Reporting solution

Client: University

Technologies Utilized:
SAS Tools: SAS 9.4, SAS DI Studio, SAS Management Console, SAS Enterprise Guide, SAS Information Map Studio, SAS OLAP Cube Studio, SAS Web Report Studio, SAS Stored Process,  SAS BI Dashboard
Platform: Linux, Windows
Databases: Oracle

Project Narrative

A large state university was looking for a real-time reporting solution to monitor their student enrollment data which would enable their staff tobetter manage a variety of administrative tasks. The requirements were narrowed down to a list of 50 specific reports. These reports would help the university’s administrative office in making decisions on topics such as class timing, student’s schedules, enrollments, costs, classroom allocations, and the generation of marketing lists.
It was crucial for administrative purposes to have all of these reports easily accessible at any time of the year. Just as important was the ability to automate the reports so that there was no manual work involved when generating or storing data. Lastly, they needed the ability to slice and dice the available data in order to make prudent and informed decisions.

Client Challenges

The existing infrastructure for these types of reports was manual labor intensive. Analysts were actually using excel spreadsheets to come up with answers to their business questions. This was obviously quite inconvenient, as many solutions were dependent on the specific personnel who knew how to get a particular list being available to do so. This system was also highly prone to errorswith no proper checks instituted. This caused a lot of headaches every semester because ofthe constant errors.
There was no centralized outlet anyone in management could go to reach all of the needed reports.The primary reporting was executed through excel sheets attached toemailswith different iterations. This was very cumbersome and highly inefficient. There also weren’t any methods in place to sort and review the data chronologically,nor by any other dimension. This is because there was no data model followed or any history stored, apart from collection of excel spreadsheets.
They had purchased a SAS EBI platform and were eager to use it to its full capacity to assist in data integration as well as create OLAP Cubes and Web reports. Unfortunately, there was virtually zero knowledge of building such a system among the university’s staff.

Stratacent Solutions

Stratacent brought highly experienced development team to the project with a wide range of experience in data warehousing as well as reporting. Our consultants gathered the reporting requirements and mapped the current processes by working with the university’s analysts in order to design an overall solution in SAS. All the source data that was required to build the reports was mapped and a relational model was designed to support the reports. To fully facilitate the university’s reporting needs, our consultants built ETL flows to create this data warehouse in oracle using SAS DI studio. The flows were rigorously tested and automated to populate the data mart from a variety of source systems.
Simultaneously, consultants worked on developing OLAP cubes and information maps which would go toward developing real-time web reports and listings using the knowledge gathered by the analysts and the reporting requirements. For every requirement, a report was built on top of an OLAP cube and information map. The advantages were obvious right away. In this model, all historical information and slices could be stored at the data mart and projected in the reporting structure via the OLAP cubes.

Results

The final result was an automated process to create a data mart for all the reporting needs, automated processes to populate the OLAP cubes and web reports. These reports replaced all the manual tasks that they had been performing for years. On top of having these reports readily available as real-time data, additional functionalities, such as the ability to look up any dimensional data with the slice and dice model based on the OLAP cubes, were also delivered. This greatly enhanced the entire administrative team’s productivity and accuracy. It also resulted in a healthy reduction of costs.
These reports enriched the staff’s ability to make better marketing decisions, leading to revenue growth. The end-to-end SAS BI solution was developed to be a robust and scheduled solution so there was no need for additional resources to maintain its efficacy.

Talk to us on how we can help your technology needs at

+1.201.669.3875