Transcript presented the project

Strategies for Successful Reporting
in a Data Warehouse Environment
with Actuate
Christian Donner
Systems Consultant
[email protected]
The Guiding Principle
Any intelligent fool can make things
bigger, more complex and more violent.
It takes a touch of genius - and a lot of
courage to move in the opposite direction.
-- Albert Einstein
Sun Life of Canada

Global financial services firm

Assets under management $181 billion

Corporate headquarters in Toronto, Canada

US headquarters in Wellesley, MA

Going public in 2000
The US Group Division

Offers life and disability insurance to
companies with 10 to 5000 employees

Generates yearly revenues of approx. $400
million

In 1997 launched a project to reengineer its
core business processes and replace
systems

The data warehouse is part of this system.

We call it “Sun Link”
How We Sell Group Insurance
Broker
Sales Rep
Sponsor
Underwriter
Process Improvement Goals

Minimize proposal turnaround time

Minimize time spent with declines

Automate as much as possible

Measure turnaround time

Count declined and sold cases

Make information available to everyone
 Data Warehouse
Definitions
According to Ralph Kimball,

A Data Warehouse is
the queryable source of data in the
enterprise

A Data Mart is
a logical subset of the complete data
warehouse
Data Warehouse Project Goals

Provide a single source of management
information for the whole division

Provide current up-to-date data

Provide a data model that is easy to use
and understandable for end-users

Allow users to view canned reports, run
reports with parameters or create their own
report

Replace existing reports from different
systems
Strategy: Many Small Parts

The first release in February 99 included
prospect measures and reports.

The second release went into production in
June this year and added workflow and
performance measures.

The third part in March 2000 will include
sales data and reports.
The Platform

NT 4.0 Server

Windows 95 field clients

NT 4.0 Workstation developer clients

SQL Server 6.5

Actuate 3.2.1

approx. 150 users (20 concurrent)

Currently 40 reports in production

32 reports in development

74 database tables
Our Strategies

Use a dimensional data model for reports

Drive the data model from the report
information requirements

Design the reports with the business users
and show them early what it will look like

Rebuild database on a nightly basis

Use Actuate to generate and distribute
reports
The Operational Data Model
Territory
ZipCodeAssignment
MailingAddress
PartyAddress
TerritoryAssignment
Employment
EmploymentRole
CaseSalesRepresentative
Party
OrganizationAssignment
Case
OrganizationAssociation
CaseSponsor
The Dimensional Model



Better performance,
since only 3 tables in
join (as opposed to 13)
Easier to read - faster
learning for end users
Easier and faster to use
- higher productivity
SalesOrg anizationDimen sion
CaseFact
SponsorDimension
The Database Load Process
Full load via
bulk copy. Views are
used to extract
a subset of the data.
Stored procedures
build dimension, fact and
aggregate tables.
2 copies of the
database, one is always
online!
Data
Warehouse
Staging Area
Operational
Database
Users view pregenerated reports
using the viewer
Data
Warehouse
Report
Viewer
Report
Encyclopedia
Reports are run daily
after completion of the
database build
Why Actuate?
+
Server-based architecture
+
Conventional and Web-based reports
+
Moderate cost
-
Ad-hoc query capability
-
MS Office integration
-
Metadata-driven
Our Strategies With Actuate

Get the right people

Design for change and build a class library

Encapsulate data access in stored
procedures

Establish an informal process and allow
developers to bring in design ideas

Find creative solutions for limitations of the
product
The People Search
Crystal Reports
Actuate
Entry Level
Master Level
hours
days
days
months

Several days of senior level consulting helped us
with a jump-start.

We found a talented consultant with some Actuate
experience to lead the design of the class library
and the development process.

We assigned one of our own developers to the
project who now is the only person to maintain
existing and develop new reports.
The Class Library Structure
AFC
base
classes
Foundation Classes
ABS
abstract
classes
Abstract Sun Life Classes,
not accessible for developers
COM
Group
reusable
collections
library
classes
Low level class library
in some cases instantiated directly
Compound classes library
to allow reuse of complex structures
ROD
report
classes
ROD
Objects
Object layer
Local layer of abstraction
Report elements can be customized in one place
Why Stored Procedures?

Encapsulating database dependencies
 No
need to recompile reports
 Changes
can be made fast and easy

Performance advantage (in some cases)

Business logic is all in one place
 Centralized
 Can
code maintenance
be cross referenced
 Plain
text files can be used with source code
management tools
The Development Process
Work assignment interactively in meetings
 Report development with

 Database
coding
 Class library maintenance
 Test data generation
 Function test
 Reviews
 Version tracking
 Change management
Final tests by test team
 Deployment

Creative Solutions

Reports wait for database

Environments - database access controlled
by user id

Dynamic help file location

Registry access

Static and dynamic hyperlinks
(C DLL source code can be downloaded from
www.denormalize.com)
Multiple Environments
Admin
Desktop
Viewer
Report
Generation
Dev
User ID
Tst
Help
HTML
Registry
Prod
Client
Actuate Server
Database Server
Report Demonstration
Did we reach our goals?

Provide a single source of management
information for the whole division

Provide current data

Provide a data model that is easy to use and
understandable for end-users

Users can view canned reports, run reports
with parameters or create their own report

Replace existing reports from various
systems with reports from one source!
The Key Advantages of Actuate

OO concepts facilitate the design for reuse

Reuse helps to achieve consistent look and
feel with minimal development effort

Actuate provides a framework for simple
and efficient report distribution throughout
the company (and beyond)
Still In Need Of A Test Strategy

The report layout has changed; fields were
added or removed

The report structure has changed; a
grouping level was added or a similar
change has been made

The business rules have changed that
define the data in the database, but the data
is the same

The data has changed, but all components
remained unchanged
Lessons Learned

We underestimated complexity and project duration

Users don’t know what they need beforehand.

We started with the development of the class library
without verifying the design.

It is impossible to freeze the data model at any time.

Feedback is only available if people use the reports.
People use the reports if they need them to run their
business.

Data integrity problems are guaranteed.

Report and warehouse administration required more
time than we expected.
The Next Steps

Open database for end users

Actuate 4 and web delivery

Home page and “personalized contents”

Develop a test strategy for reports

Extend and enhance the data warehouse
Thank you!