Business Intelligence Statistics NZ

1
Business Intelligence Statistics NZ

• A quick tour of projects such as Census / Student
  Loans and the technology behind them

2
BI Technology Platform Migration

• Older approaches
• Sybase databases
• SAS for data analysis and manipulation
• SuperCross for Pivot like user interface /
  reporting
• Table Builder limited user ad-hoc querying
• Excel manual editing
• Newer approaches
• SQL 2000/2005 Databases 2005 Analysis Server
• SAS or Excel cube for data analysis
• Excel 2007 Custom AddIn cube for reporting
• Some use of SSIS
• Cubes developed
• LEED (Analysis Server 2000) employer/employee
• Census 2006
• Student Loans Allowances
• Local Insight (regional statistics)

3
What is significant / different about BI projects
at Stats

• Many Varied datasets
• Social surveys / business surveys / census / data
  feeds from other departments eg IRD / MOE
• Heavily focused on publication of reports to the
  web
• Scheduled release dates sets timeframes
• Census in particular intended to release bulk
  reports (detailed geography)
• Specific presentation / classification
• Arrangement / hierarchy / naming are important
• Multi Response data eg ethnicity
• Accuracy
• High Level of validation required
• Confidentiality
• All data presented to the public is
  confidentialised to some degree (from rounding to
  complete removal)

4
Example 1 Census 2006 - Layout
5
Example 2 Census 2006 - Classifications
6
Example 3 Census 2006 Multi Response
7
Example 4 Student Loans Allowances -
Confidentiality
8
Streamlined .NET Solution

• Single data store for all reporting
  classifications,
• User tools to manage hierarchies, naming etc
• Tools to create cube definitions from metadata
• User tools to map dimensions to source data
• XMLA generation from metadata and XSLT
• Confidentiality rules built as SSAS C assemblies
  into cube
• Every client gets confidentialised results
• Automated validation / testing
• User tools to allow regression testing after
  changes to data / structure / rules
• Repeatable Business Approach
• Fast prototype and user feedback

9
(No Transcript)
10
Cube creation tools used

• Visual Studio
• Huge improvement over Analysis Server 2000!
• Investigation / Protoyping / troubleshooting
• Large designs are very cumbersome. Especially M2M
  
• Programmatic ADOMD
• Object model is easy to use within .NET. Lots of
  samples. Was used for early cube building and
  aggregation design tools.
• Requires direct server connection and permissions
• XMLA query based
• XML metadata (FOR XML) and transformation based
  approach. Was used for database, cube, and
  dimension generation.
• Simpler in a lot of respects you can see what
  you need to do
• Allows a lot of options for pre/post transforms
• Scripts can be generated at one time and deployed
  elsewhere

11
Confidentiality rules

• Confidentiality rules are server based, client
  independent, can be switched on and off

12
Pros / Cons of Server-Side Rules

• Advantages

• Disadvantages

• Independent of any client browsers - no matter
  how the cube is queried, the rules apply
• No modification needed to client tools
• The rules assembly module has direct access to
  the cube and its metadata
• Are written in C, load directly to the server
  from development machines
• Remotely debugging
• Sometimes run quickly

• There is no begin query / end query event in the
  SSAS object model difficult to pick up context
  changes
• There is no query context in relation to the
  calling query (What was the calling query, which
  dimensions apply to the current query that are
  not set to All)
• Needs mechanism to quickly identify specific
  dimensions/hierarchies
• Some calls are very slow (i.e. getting the cube
  name, or accessing the hierarchy tree when you
  have a large number of dimensions).
• Documentation on MDX is hard to find if it exists
  at all
• Not many people have done it before..

13
Publishing Excel 2007 Based Report Designer

• A .NET add-in to Excel provides a reporting
  canvas area (shown on the right). This is a 2.0
  browser control with some coded features added
• We utilise a com interoperability layer
  components provided by VSTO 2007 which allows two
  way communication between the standard Excel 2007
  grid area (shown on the left) canvas. The
  standard grid allows the user to get data from
  any source
• Web publication content in Excel 2007/canvas is
  converted to well formed XML stored in a secure
  database replicated via the firewall to Microsoft
  Content Management Server served up as HTML

14
What was achieved?

• Reduced barriers to high quality bulk publishing
• Quick, flexible design tools using guaranteed
  data
• Streamlined process for successive projects
• Reuse of classifications and rules
• Some SAS analysis can be done via cubes
• Allows easier access to research teams
• Allows quick what-if cloning, branching of cube
  design to test different approaches
• For example, can generate a cloned, cut down
  section of existing cube design in minutes with
  tailored dimensions

15
Project Team (Census Others)

• Chris Toohey Project Manager
• Peter Baker senior developer/architect
• Deane Landreth developer/TPS architect
• Del Robinson developer
• Joanne Sharp developer / analyst
• Peter Quaid business analyst
• Steffan van Soest tester/developer
• Leigh Street tester
• Rory White - developer
• Paul Chen - developer
• James McGahey - developer
• Linda Parkes business analyst
• Dave Stockman developer
• Ed Havelaar developer
• Shane Amataiti tester
• Eugenie Garnier business analyst

16
Links

• Census Stats
• http//www.stats.govt.nz/census/census-outputs/qui
  ckstats/aboutAPlace.htm
• Student Loans
• http//www.stats.govt.nz/products-and-services/inf
  o-releases/student-loans-allowances.htm