Implementing Data Warehouse Methodology in Statistics Iceland

1
Implementing Data Warehouse Methodology in
Statistics Iceland

• Introduction to the DW project
• By
• Kjartan Sigurdsson

2
Intro

• Running one OS and DB environment from 1996 i.e.
  MS Windows and MS SQL
• Stowe pipe production model
• Want better model for collection, processing and
  dissemination of statistics
• First in 1999 begin to plan for DW
• Not enough capability for major development
  projects
• If you cant beat them, join them!

3
Hardware and OS

• PC/LAN 1-GHz/100-MHz net
• 7 Servers with MS Windows 2000
• 126 Work Stations with MS Windows XP
• SAN Storage with total ca. 1Tb
• Backup Library connected to SAN

4
Software

• MS Windows xp
• MS Office xp (Access, Excel, Project ...)
• MS Outlook
• MS SQL 2000 RDBMS
• MS Analysis Services (OLAP)
• MS Data Analyzer ---- NEW
• MS BI Accelarator ---- NEW

5
Today

• Most of the data in MS SQL database
• Still some Data in Access databases
• Homemade software for subject areas
• LiSA Web Management System
• PC-Axis/PX-web
• Varedeklarationer

6
Classification DB

• No special system running today
• Looking at Stabas from DK/NOR
• Shall run on MS SQL2000 DB

7
The Data Warehouse Project

• Project planning
• Requirement definition
• Technical architecture
• Dimensional modeling and design
• Application specification design
• Deployment
• Maintenance and growth

8
(No Transcript)
9
Work done so far..

• Planning and definitions
• Pilot Project for testing
• Presentation and kick off ...
• Requirement definition for all subject areas
• Started Application spec. and testing with COGNOS
• Logical design for the first subject areas

10
Microsoft Data Warehousing Framework

• Integrated framework that includes
• DTS Extract/Transform/Load
• Data storage - Relational/Multidimensional
• Data Access via APIs
• System Management tools
• Repository for shared metadata (OIM)
• Open architecture for easy integration

11
Microsoft Data Warehousing Framework
12
(No Transcript)
13
BI Accelerator

• New tool from Microsoft
• Built on the foundation of MS DWF
• Helps in building analytic applications
• Automatically builds a solid infrastructure
• Saves a lot of time

14
BI Accelerator Components
15
BI Accelerator Scope
16
Developing the analytical application development
includes the following tasks

• Understanding and selecting a data model
• Developing a project plan
• Gathering business requirements
• Developing your customized data model
• Configuring the data model
• Creating the analytical application
• Evaluating and revising the design
• Choosing clients and configuring views
• Loading and processing a data subset
• Evaluating the analytical application
• Configuring and extending the analytical
  application
• Scaling and deploying the analytical application

17
Analytical Application Dvelopment Process
18
DTS - Master Update package
19
Dimension Update subpackage for a dimension with
a single hierarchy
20
The Data Mart MatrixComformed dimensions and
conformed facts
21
Data Mart Dimensions
22
Date Dimension
23
Dimensional Model Dimensions, Fact and
Mesures(Star schema)
24
Pilot Project for testing

• The project was carried out in following tasks
• Requirements and collection of data
• Designing the Dimensional Data Model
• Designing the Staging Area
• Designing Error Handle and Audit Procedures
• Populating Default Data and Static Dimensions
• Integrating with Analysis Services
• Designing the ETL Workflow (DTS)
• Verifying the result to ÚTVEG 2000

25
Catch and catch value of Icelandic vessels by
fishing areas and species year 2000Tables 5.1 -
5.2 - Microsoft Data Analyzer
26
Catch and catch value of Icelandic vessels by
fishing areas and Demersal catch year 2000Tables
5.1 - 5.2 - Microsoft Data Analyzer
27
Útvegur 2000 Table 5.1
28
Útvegur 2000 Table 5.2
29
The End