Transcript 2006 Census

Business Intelligence
If you are an organisation that:
• analyses data
• or publishes data to intranet or web
The business intelligence solution I will present has the
ability to:
•
Enhance your collection of data
•
Simplify and speedup your data analysis
•
Improve the quality and quantity of your web publications
•
Allow you to benefit from our experience
•
Save you time and money
Presentation outline
1. Background to the projects
2. The challenge for 2006 Census
3. BI Solution
•
Collecting data via the web
•
Turning data into relevant knowledge
4. Generic potential
5. Lessons learnt
Statistics NZ
Strategic Priorities
• Mission: “Turning data into relevant knowledge efficiently”
• Organisational goal to improve quality, timeliness, and
accessibility of statistics for the public
• Aim to improve, streamline and standardise our end-to-end
business processes
• Increase amount of analytical work undertaken
• Enable open accessibility to official statistics: significantly
increasing statistical information published on the web
• Giving info back to NZ’rs to make informed decisions on all
aspects of economy and society
• E-govt network & Internet technologies will be integral to
delivery of government information and services
2006 Census- IT & Business challenges
• Provide on line web based data collection
• Provide solution to enable dissemination of products to web
• Automate as much as possible the output operations phase
• Provide an integrated authoring and web publishing tool
• Automate the application of the five census confidentiality rules
• Create an analytical environment for data
• Create a solution for capable of reuse in 2011 Census
• Produce a scalable solution for potential use with other Statistics
NZ datasets
Online census successful outcomes
Value to business
• One of the first successful censuses in the world to trial on
line collection
• Fulfilling e-government strategy – use of internet
• 7% of all collected forms were submitted online
• Improved public perception and confidence
• Reduced time and cost of processing census forms
Value to IT
• Maximised return on investment
• Fast easy deployment
• Scaleable
• Security and encryption highly successful
Turning data into relevant
knowledge
and
Getting it out there
using
Microsoft Office Business Applications
Looking for the right solution
• Technical investigation of existing technologies
• Concluded only solution to meet technical and business requirements
was Microsoft technology
•
•
•
•
SQL server
SQL Server Analysis Services (SSAS)
Office Web Components
Content Management Server (CMS)
• Proof of concept validated solution workable
• confirmed we needed SQL Server Analysis Services 2005 for rules
• Embarked on full end to end Microsoft development
2001 Census outputs
• Majority of output paper-based with limited web dissemination
• Tables created in 3rd party tools from unit record data and manually
validated
• Loaded to Excel for formatting and additional confidentialisation
• Time series – concordances built for each census spliced together in
Excel
• Excel tables supplied to publications unit for loading into the
publications system.
• Graphs created by publications in a special graphing package
• Highly manual process involving extensive checking and re-checking
Current Publication Process
IPE data
store (CMS)
Microsoft Content Management
Server (CMS)
IPE (Integrated Publishing
Environment)
Manually Input
Data
Extraction
Tools
KEY
= Confidential Data
MS Excel
Tables &
Graphs
F
I
R
E
W
A
L
L
Statistics NZ
public website
(CMS)
Webpages
Dissemination solution 2006
Product Creation
• The 2006 Census product mix based on an audience model
• Use pre-developed product CMS templates designed in-house
and built by Datacom
Authoring
• Content created by statistical analysts directly in the Integrated
Publishing Environment (CMS) using Office Web Components
(OWC)
• Can create tables, graphs, conditional text & data for products
• Total Placeholder Solution (TPS) – multi tiered application utilising
Microsoft OWC
• Data is automatically confidentialised
The 2006 solution - continued
Highly automated
• Analysts create the content for one regional publication and the
system automatically produces the remaining regions (100)
• QAAP RC/TA product created 6500 tables and graphs
• Uses aggregate, pre-validated cube data
• 2006 classifications mapped back to 2001, 1996
The cube allows multiple ‘clients’ to interrogate the data:
• Excel 2003 and 2007
• Office Web Components
• SAS Enterprise Guide
• Any OLAP query tool
2006 Census
Publication Process
SQL
census
data
warehouse
IPE Data
Store
(Confidential)
IPE (Integrated Publishing
Environment)
TPS
(Total PlaceHolder Solution)
Analysis Tools
R
u
Cube
l
e
s Layer
MS Excel
SAS
(or any other analysis tool)
KEY
= Raw Data
= Confidential Data
F
I
R
E
W
A
L
L
Statistics NZ
Public Website
Census Webpage
Creating Content
Instantiate & publish content
Cube for analysis
SQL
census
data
warehouse
TPS
(Total PlaceHolder Solution)
Analysis Tools
R
u
Cube
l
e
s Layer
KEY
= Raw Data
= Confidential Data
IPE Data
Store
(Confidential)
IPE (Integrated Publishing
Environment)
MS Excel
SAS
(or any other analysis tool)
F
I
R
E
W
A
L
L
Statistics NZ
Public Website
Census Webpage
Table Builder
Technical information – the cube
• Online Analytical Processing (OLAP) cube design uses Microsoft
Analysis Services 2005
• Typical MS cube has 12 dimensions, Census 2006 cube 180 with
240 hierarchies or variables
• Dwelling, Household, Family and Individual counts all linked
• Combined all Census Databases (1981-2006) into SQL database
• Data Warehouse, uses metadata and maps 2006 data to 1991
• Student Loans 30 dims but 130 million amount values, allows
longitudinal analysis
Cube tool builder- CubeToolz
• An automated OLAP cube tool builder: CubeToolz
• Used in place of Microsoft SQL Server Analysis Services cube
designer tool - for non developers
• Census cube tool builder uses metadata to automatically generate
the OLAP cube structure
• CubeToolz already being used with Student Loans dataset
• CubeToolz has a UI to allow developers to create their own cubes
• Automatic validator tool for cube data and concordances
• Significantly enhances ability to reproduce new cubes quickly &
iteratively with end user input
Confidentiality rules
• Confidentiality rules are server based, ‘client’ independent, can be
switched on and off
• Independent of any client browsers - no matter how the cube is
queried, the rules apply
• No modification needed to client tools
• Run quickly
Version 2.0
SQL
census
data
warehouse
IPE
Environment)
Canvas to create Product
R
u
Cube
l
e
s Layer
MS Excel
SAS
(or any other analysis tool)
KEY
= Raw Data
= Confidential Data
IPE Data
Store
(Confidential)
(Integrated Publishing
F
I
R
E
W
A
L
L
Statistics NZ
Public Website
Census Webpage
Table Builder
Constraints with current solution
• In OWC we could mimic much of Excel functionality via code but not all
• End users familiar with Excel wanted it’s functionality/flexibility
• Additional business requirements for area unit product – 2000 areas
• Navigation, volume and performance issues
• Visual Studio Tools for Office (VSTO) 2007 offered a way out of this
V2: “The Excelerator”
Designed enhanced authoring & web publishing tool using Excel
Creating a really innovative, scaleable & more generic solution
Excel 2007 linked to web page
Impact on Statistics NZ Business Processes
• Reduction in time and cost of data collection
• Allow more analysis and dissemination in same or less time
• Use of Excel/Pivot table reduces reliance on proprietary
software's to analyse and disseminate data
Original ‘As is’ Business Model
Need
Design/
Build
Collect
Process
Analyse Disseminate
Potential ‘To Be’ Business Model created by BI solution
Need
Design/
Build
Collect
Process
Analyse
Disseminate
Business Benefits
• Used well known Microsoft technologies familiar to end users &
developers
• 2006 Census collection & publishing systems delivered solutions that
met the original goals
• Provide reusable solutions for next census
• Scaleable solution allows deployment to wider organisation
• Have potential to produce significant savings in operating costs for
the organisation
IT Benefits
• Learnt a lot about OLAP cubes and how to build them
• Developed data warehouse with metadata/ variable mappings
• Built metadata driven automated cube development tool
• Leveraged off SSAS 2005 new features to automatic rules app on
server
• Future proofed with well supported Microsoft technologies
• Scaleable & reusable solution
Key lessons of the BI project
Managerial lessons

Key ingredient to successful innovation is a great TEAM

Recruit the right people, ensure they’re in the right roles, play to
their strengths

Make sure they understand what needs to be achieved- Vision

Reward great achievements & be amazed at how frequently
they occur

Energise and Inspire

Optimistic

Confidence & resilience

Self humility
Key lessons of the BI project
Other key lessons

Importance of formal change management of BI solution – especially
moving from a manual to automated process

Beginning of project identify BI information required

Subject matter experts are critical engage at the start

Prototyping the IT solution early is essential

Managing client expectations also crucial

Improving organisational IT literacy

BI solutions need on going invest in IT software and hardware for
successful outcomes

Use well known & trusted technologies like Microsoft

Use external support and consultation

Don’t underestimate the need to promote & sell success of BI solution
internally
Project Team
•
•
•
•
•
•
•
•
•
•
•
•
•
Peter Baker –senior developer/architect
Deane Landreth – senior developer/TPS architect
Del Robinson - senior developer/OLAP architect
Paul Chen – developer
Joanne Sharp – developer
Consultancy Support
Peter Quaid – business analyst
Pat Martin
Steffan van Soest – developer
Microsoft Consultancy Services
Rory White – developer
Wayne Carter - developer
Leigh Street – tester
James McGahey - developer
Linda Parkes – business analyst
Dave Stockman – developer