1 / 30

Business Intelligence

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

tam
Download Presentation

Business Intelligence

An Image/Link below is provided (as is) to download presentation Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author. Content is provided to you AS IS for your information and personal use only. Download presentation by click this link. While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server. During download, if you can't get a presentation, the file might be deleted by the publisher.

E N D

Presentation Transcript

  1. Business Intelligence

  2. 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 yourcollection of data • Simplify and speedup yourdata analysis • Improve the quality and quantity of your web publications • Allow you to benefit from our experience • Save you time and money

  3. Presentation outline • Background to the projects • The challenge for 2006 Census • BI Solution • Collecting data via the web • Turning data into relevant knowledge • Generic potential • Lessons learnt

  4. 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

  5. 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

  6. 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

  7. Turning data into relevant knowledge and Getting it out there using Microsoft Office Business Applications

  8. 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

  9. 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

  10. FI REWALL MS Excel Tables & Graphs KEY = Confidential Data Current Publication Process IPE data store (CMS) Microsoft Content Management Server (CMS) IPE (Integrated Publishing Environment) Manually Input Statistics NZ public website (CMS) Data Extraction Tools Webpages

  11. 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

  12. 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

  13. 2006 Census Publication Process SQL census data warehouse IPE Data Store (Confidential) IPE (Integrated Publishing Environment) TPS (Total PlaceHolder Solution) FI REWALL Analysis Tools Cube Rules MS Excel Statistics NZ Public Website Layer SAS (or any other analysis tool) Census Webpage KEY = Raw Data = Confidential Data

  14. Creating Content

  15. Instantiate & publish content

  16. Cube for analysis SQL census data warehouse IPE Data Store (Confidential) IPE (Integrated Publishing Environment) TPS (Total PlaceHolder Solution) FI REWALL Analysis Tools MS Excel Cube Statistics NZ Public Website Rules SAS (or any other analysis tool) Census Webpage Layer Table Builder KEY = Raw Data = Confidential Data

  17. 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

  18. 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

  19. 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

  20. Version 2.0 SQL census data warehouse IPE (Integrated Publishing Environment) IPE Data Store (Confidential) FI REWALL Canvas to create Product Cube Rules MS Excel Statistics NZ Public Website Layer SAS (or any other analysis tool) Census Webpage Table Builder KEY = Raw Data = Confidential Data

  21. 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

  22. V2: “The Excelerator” • Designed enhanced authoring & web publishing tool using Excel • Creating a really innovative, scaleable & more generic solution

  23. Excel 2007 linked to web page

  24. Need Design/ Build Collect Process Analyse Disseminate Original ‘As is’ Business Model Potential ‘To Be’ Business Model created by BI solution Disseminate Analyse Need Design/ Build Collect Process 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

  25. 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

  26. 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

  27. 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

  28. 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

  29. 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 • Peter Quaid – business analyst • Steffan van Soest – developer • Rory White – developer • Wayne Carter - developer • Leigh Street – tester • James McGahey - developer • Linda Parkes – business analyst • Dave Stockman – developer Consultancy Support Pat Martin Microsoft Consultancy Services

More Related