|
Business Situation:
The original environment in which WSI was
hired was for converting a 16-bit DOS reporting environment to a
32-bit environment while ensuring Y2K compliancy. After this
process was begun, it was determined that Wells Fargo Card
Services (WFCS) wanted to enhance their intranet capabilities to
help streamline daily operations. Specifically, WFCS wanted to
provide better, faster and easier company-wide access to
information, specifically 400 static reports that were hand
delivered to some 500 users. To further complicate the
situation, Norwest and WFCS reporting systems needed to be
merged into one system due to the merger between the two
companies. In addition, the static reports were originally
created by manual intervention where the creator of the report
had to start the process to create the report each morning.
Major Goals:
- Develop one common reporting system to
replace the current systems of Paradox, Access and SAS that
were in place
- Automate the creation and delivery of
the reports.
- Give power users the necessary
reporting tools to effectively do their job.
Solution:
In conjunction with Omnitec Consulting (a
partner technology firm with whom WSI has performed other jobs),
WSI set out to create an environment that would allow WFCS to
accomplish the goals that they wished to achieve. As the lead
architects for this transformation, WSI had a large say in the
solution that was created throughout the entire process.
It was determined early on in the design
process that the best solution to accomplish a majority of the
goals would involve the development of an intranet reporting
system. As the Card Services Department didn’t have an intranet
system in place, this was going to require the creation of the
entire site from scratch.
The site was set up to allow the users to
register right on the site. A manager would then approve or deny
the request for access and grant the user access to the areas of
reports that they needed access to. This allowed a user to only
see reports that were pertinent to them.
The next item that needed to be tackled
was the automation of the reports that the user would access on
the intranet system. It was decided that the best solution for
web reporting would be in a PDF format as this would be easier
to support in different browsers.
However, before reports could be developed
and produced, the data had to be processed and stored in a
common reporting database. To accomplish this, a “Smart” Visual
Basic application was developed that runs continuously
throughout the day looking for processes that it needs to run.
When it finds a process that needs to run, it kicks it off and
at the end of the process generates the necessary reports for
the process and sends those out to the intranet reporting system
for the end user to print or view. To help automate the process
even further, users could request an e-mail notification when a
process was completed. This allowed the user to know when a
process was complete and literally have access to the reports
seconds after they were produced. Users were also allowed to
request a report in an Excel format. This worked in the same
manner as the e-mail notification, but instead of receiving just
an e-mail, they would also receive an Excel worksheet in the
format of the report.
Due to the volume of reports and magnitude
of data in the system, certain power users began requesting more
sophisticated reporting tools and options. To help with this, we
developed and implemented three SQL Server OLAP applications
with retrieval of the data via the Intranet: 1) Segmentation and
Vintage analysis on consumer credit card, business card, and
lines and loans portfolios, 2) Scorecard performance analysis on
consumer credit card applications data, and 3) merging key
information on the applications data with the accounts in the
portfolio to allow analysis on the portfolio based on data when
the account was approved.
Benefits:
Automated all processes resulting in the
elimination/re-deployment of 4 full-time staff members while at
the same time providing a system for the end user that was
faster and more efficient to help them in analyzing data in a
timely manner and therefore being more prepared to make the
correct business decisions faster.
Technical Information:
Smart Visual Basic Application – This
application was developed using Microsoft Visual Basic 6.0 using
ADO as the data access method. As a number of data-sources sent
the data to the system in a text format of some sort, various
SQL Server DTS packages were developed to help automate the
loading of data.
Report Development – All reports were
built in an Access Database. When the VB application got to the
reporting phase, it would launch out and start a version of
Access through remote automation and print the necessary report.
Instead of printing to the printer though, it would print to the
PDF spooler which allowed for the report to be saved out to the
intranet reporting system.
Database – All data was stored in one or
more SQL Server 7.0 databases. Data of roughly 10GB is loaded
into the databases in a typical month.
Intranet System – Running on an IIS
Server, all pages were developed using Microsoft Visual
InterDev. In addition, custom COM components were developed for
use on the web to enhance certain features of the site.
|
