The Data Warehouse - Strategy
Service profile for
dispositive informationprocessing Diplom-Informatiker Peter K. Albrecht
Head of the division Systems-Consult (Version: 01.05.1999)
Content
• Mummert + Partner Consultancy AG
• Why Data Warehouse Projects with
Mummert + Partner
• The Mummert + Partner - Service profile
1960 Dr. Olaf Mummert founded the Mummert consultancy in Karlsruhe on April 1st. 1996 Transformation of the consultancy Ltd. into a PLC.
We accompany our customers up to the target.
Entwicklung und Umsetzung von Branchenlösungen 42% Strategie-, Management und Organisationsberatung 39%
IV-Beratung 15% Sonstiges 4% Öffentlicher Sektor Sonstige Industrie 42% 39% 15% 4% 0% 5% 10% 15% 20% 25% 30% 35% 40% 45% Entwicklung und Umsetzung von
Branchenlösungen Strategie-, Management und
Organisationsberatung IV-Beratung Sonstiges revenue revenue 0 50 100 150 200 250 300 88 89 90 91 92 93 94 95 96 97 98 employees employees 0 100 200 300 400 500 600 700 800 900 1000 88 89 90 91 92 93 94 95 96 97 98
The target leading combination of business- and specialist
know-how.
Banks - Investment Banking - Retail Banking - Asset Management - Banking Services
Insurances - Customer Relation - Market/Product/Sales - Controlling - Insurance architecture
- Financial architecture - Asset Management - Business Solutions Federal and state
authorities
- Management and organizational consulting - Financial systems - Facility- and Assetmanagement
Public sector - Financial systems - Logistic - Facility- and
Assetmanagement Energy - Customer management - Logistic
- Finanzsysteme - Facility- und Assetmanagement Telecommunication - Customer Relation - Debitor management
Trade - Customer management – Logistic - Financial systems Industry - Customer management - Construction (CAX-Technologien)
- Logistic - Financial systems
- Data Warehousing - Document Management - CRM-Technology - Quality Management - Software Factory - Technology Management (Infrastructur) - Human Ressources Management
We are where our customers need us.
Berlin Frankfurt Hamburg Cologne Leipzig London Milan Munich Münster New York Vienna ZurichContent
• Mummert + Partner Consultancy AG
• Why Data Warehouse Projects with
Mummert + Partner
• The Mummert + Partner - Service profile
Extensive initial solutions ensure the project success
1. Specialized procedural model
2. Proven methodology
3. Certified product know how
4. Data Warehouse laboratory
5. Skilled and experienced employees
6. Successful customer relationship
On the basis of these 6 components, Mummert + Partner is
the competent partner for Data Warehouse projects.
1. The M+P procedural model for Data Warehouse projects
Operation/
maintenance
Helpdesk Automation Verification
Scalability
Capacity Tuning Backup
Change-
Manage-ment
Modeling
Metadata Data Marts WarehouseData Dataflow Frontend Architecture PrototypingAdvance study
definitionTarget Is - analysis External Know-howData Focus Economic viability
Project definition
ROLAP vs. MOLAP 3-TIER vs. 2-TIER vs. 1-TIER Planing Team staffing Product choice Server FAT-Client THIN-Client IntranetImplementation
Network Development Security /Usergroups Testing Education Propagation
Customizing
Data sources
2.The proven methodology is the key to success.
Frontend
Data Marts
Data Warehouse
Data sources
Meta-Data
Relational
Multidimensional
OLAP-CLIENT
OLAP-SERVER
RDBMS
ETL
4. The Data Warehouse Laboratory - for developing and prototyping
Backupserver für alle NT-Server und Clients IBM RS6000 F40 1 GB RAM 60 GB 40 x Windows NT WS 64 MB RAM IBM SP2 SilverNode AIX 4 Prozessoren 3 GB RAM 360 GB Fünf NT-Server Pentium II 512/256 MB RAM 18/9 GB
5. Professional technical and methodical know -how.
Data Warehouse - Team
Data Warehouse - Experts
System analysts
Graduates
Technology management
Producer
24 MA
6 MA
Business consultants
6 MA
Composition of the team
Goal: 60 employees up to
the middle of 1999
More than 140 personal years
of
Data Warehouse experience
Mummert + Partner
1 1 2
4
9 10
17
2121
2527
293131
33
36
0 5 10 15 20 25 30 35 40 Mar-98 Apr-98 M ay-98 Jun-98 Jul-98 Sep-98 Oct-98 Nov-98 Dec-98 Jan-99 Feb-99 Mar-99 Apr-99 M ay-99 Jun-99 Jul-99Content
• Mummert + Partner Consultancy AG
• Why Data Warehouse projects with
Mummert + Partner
• The Mummert + Partner - Service profile
Project handling or alternativly consulting single services
Operation/
maintenance
Modeling
Advance studies
Project definition
Implementation
Helpdesk Automation Verification
Scalability
Capacity Tuning Backup
Change- Manage-ment Data Warehouse Dataflow Data Marts
Metadata Frontend Architecture
Prototyping Target
definition Is - analysis Know-how
External Data Focus Economic viability ROLAP vs. MOLAP 3-TIER vs. 2-TIER vs. 1-TIER Planing Team staffing Product choice Server FAT-Client THIN-Client Intranet
Network Development Security /
Usergroups Testing Education Propagation
Customizing
Data sources
Targetdefinition
• Activities
• Evaluation of the strategic areas and their dimensions
• Definition of the expected targets from the economic point of view
• Definition of the necessary controlling instruments to reach the target
• Holding „strategy-workshops“ with the concerning departments
• Results
• Documentation of demand concerning the subject
• Model of a satisfactory information demand (high level)
• Prioritization of set targets
• Prioritization of controlling instruments that have to be created
• Hierarchical dimension of the business
IS- analysis
•
Activities· Analyzing existing operational systems
· Analyzing existing dispositive systems
· Analyzing the current user architecture • Results
· Grade of covering the information demand by data of existing systems
· Expanding the targets by the integration of existing dispositive systems and reporting
· Consolidation of necessary controlling instruments to reach the target • Productcriteria
• Accessibility to: Flat Files, VSAM, IMS, DB2, Oracle, Informix, Sybase, Adabas, Ingres, SAP-R/2, SAP-R/3, Tandem-NonStop-SQL, NCR Teradata, MS-SQL-Server, MS-Excel, MS-Access, OBDC
Covering - preceding Systems
External Data
Covering
- prec.Systeme - external sources
Know-how
Covering: - prec. Systems - external Sources - Know-how (System-Input)Advance study:
Advance study:
Focussing
Prototyping
Advance study:
Economic viability
Advance study:
ROLAP vs. MOLAP
Project definition:
• Activities
• Working out the necessary dimensions and their hirarchical order (drill down, slicing and dicing, aggregation)
• Evaluating the expected data volumina by regarding the sparsity • Discussing the MOLAP – attempt (multidimensional OLAP)
• Discussing the ROLAP – attempt (relational OLAP) • Discussing the HOLAP – attempt (mixed use)
• Results
• Basic decision about the usuable data architecture for the data marts and for the data warehouse which will be build up in later steps
• Productcriteria
• Support of MOLAP, ROLAP and/ or HOLAP • Support of personal OLAP
• Export possibility to MS-Office • Add-in functionality for MS-Office
MOLAP ROLAP HOLAP
Operative Data Data Warehouse Data Marts
3-TIER vs. 2-TIER vs. 1-TIER
DB-Server OLAP-Server Client
FAT-Client vs. THIN-Client / Intranet
Project definition:
Server
Project definition:
Product choice
Project definition:
• Activities
· Assessment of the existing technologies on the market regarding the necessary data-and C/S-arcitecture to reach the target
· Consideration of the company’s demands regarding the product choice
· Holding user-workshops to find out about the acceptance regarding possible front-end design
• Results
· Confirmation of the data- and C/S-arcitecture in case it had to be revised
· Making a principle decision about the products that will be used
· Making a principle decision about the sort of user-surface • Productcriteria
· Possibilities of combining the products between each other
· Integration of the products/modules over common metadata
· Maximum amount of transformed dimensions and hierarchical levels
Team staffing
Project definition:
•
Data supply Front-end
Design Technique Team of experts Sponsor customer Quality safeguarding producer Support Projectmanagement
Planning
Project definition:
Metadata
Modeling:
•
Activities• Determination/ fixing the repository to deposit metadata over datasources, data warehouse, data marts as well as the data flow between all levels (staging areas) • Determination of metadata with accessibility for the user
• Results
• A central active repository for every level of the system
• Productcriteria
• Interface for data modeling
• Storing of the transformation- aggregation and extraction rules • Storing of all the necessary operations
• Storing of information about the user and his authorization on the dimension level
Data Marts
Modeling:
• Activities
• Modeling of the Data Mart that will be developed in consideration to the agreed data arcitecture
• Consideration of aggregation and denormalization
• Agreeing precisely on the data model with the help of the specialists team by holding a navigation workshop
• Documentation of the data model by using metadata from the arranged repository • Working out the cardinality and rates of compressions of the dimensions
• Results
• Data model of the data mart that will be created in a denormalized form • Productcriteria
• Modeling of data in the way of ER with support of the denormalization and hierarchical dimensions (star scheme, snowflake)
• Generating the physical data storage • Automatic partition and intensification
Data sources
Modeling:
Data Warehouse
Modeling:
Data-flow
Modeling:
•
Activities• Modeling the dataflows all the way from the data-sources up into the data marts on field level • Documentation of aggregations, transformations, extractions and operations which are
necessary to fill the data marts via the data warehouse • Documentation of the conditions of integrity on field level
• Joint and detailed agreement upon the data flow modeling with the specialists team • Differentiation of additive, semi-additive and non-additive business figures
• Results
• Active repository to regulate the data supply
• Productcriteria
• Use of the modern structured analysis or similar methods • Possibility to deposit conditions of integrity on field level
Frontend
Modeling:
• Activities
• Modeling the frontend design with the necessary details
• Agreeing upon the navigation within the system on the basis of the modeled data mart • Documentation of the data flow from the data mart to the Frontend
• Agreeing upon the frontend design between the specialists team and chosen users • Considerating the possibilities of the chosen product
• Considerating the possibilities of intelligent software-agents • Results
• Confirmed model of the dialog that will be developed and used • Productcriteria
• Possibilities of navigation (drill down, drill up, slicing and dicing) • Maximum amount of on- and off spreadsheet dimensions
• Possibilities of exception-reporting (traffic lights function?, balanced scorecard, sw-agents) • Graphical opportunities (2D, 3D, geographic analysis)
• Flexibility in the layout of reports (standard-reporting, free navigation, EIS-functionality) • Flexibility by analyzing (ranking, comparison of different periods, cumulation)
• User friendliness (Power-user functionality) • Export- and impressor functionality
Architecture
Modeling:
Network
Implementation:
Customizing
Implementation:
• Activities
• Implementation of the chosen product in every level of the data warehouse which is supported by the product
• Using the specific procedures and technics which are given by the chosen product • Agreeing upon further creations of system components due to the development
• Considerating the adopted standards which have been worked out by the internal and external quality assurance
• Considerating the referential integrity which is visible by modeling • Results
• Implementation of the chosen product
• Identification of further components of the system that will have to be developed • Productcriteria
• effort for the development of surface and data supply • effort for the integration of changes
• effort for refresh, append and update of the data deposit • seperating surface and data supply
Development
Implementation:
• Activities
• Creating the data supply routine by using the information deposed on the repository dynamically • Programming the frontend that has to be developed
• Integration of data supply and surface by using standardized interfaces
• Considerating the adopted standards which have been worked out by the internal and external quality safeguarding
• Considerating the referential integrity which is visible by modeling • Results
• Completed system allowing nonrestricted access to all data • Productcriteria
• Effort For the development of surface and data supply • Effort for the integration of changes
• Effort for refresh, append and update of the data deposit • Seperating frontend and data supply
• Interfaces to higher programming languages over class librarys • Availability of an integrated software development location
Surface-Design
Data-supply
Security/ Usergroups
Implementation:
• Activities
• Defining all the dimensions which are relevant for security
• Defining the usergroups in collaboration with the specialists team • Implementation of security informaion in the repository
• Expanding the routines of data access by the consideration of security information in the repository
• Results
• Security administration system
• Completed system by considerating the aspects of security • Concept for different usergroups
• Productcriteria
• Considerating power-user as well as end-user
• Variable classification of users to different user groups
Testing
Implementation:
• Activities
• Definition of error categories (e.g. data supply, surface, technic)
• Definition of error classes /grades (e.g. 1= serious error...4= nice to have) • Defining equivalence classes to reach a high functional covering by the test • Carrying out the test to eliminate errors steadily
• Carrying out stress- and masstests above the equivalence classes
• Results
• Approving the system
• Productcriteria
• Debugging- functionality
Education
Implementation:
Propagation
Implementation:
Helpdesk
Operation/ maintenance:
Automation
Operation/ maintenance:
• Activities
• Creating automatical job-processes for the data supply of the data marts
• Creating automatical job-processes for the supply of data of the client-system (pull-principle) • Considerating available time frames
• Handing over the data supply routines to the productive system of the customer • Results
• Productive system
• Productcriteria
• Possibility of automation of the data supply- and data spreading routines • Considerating recommencement in case of an interruption of the data supply • Query automation of integrity conditions as well as free eligible filters
• Exception-handling by proactive presentation of critical raw data • Independent data supply of the clients
Change-Management
Operation/ maintenance:
Capacity
Operation/maintenance:
• Activities
• Finding out about the expected need of store capacity • Considerating given key figures due to the modeling like
-amount of dimensions as well as their cardinality
-amount of hierarchies per dimension as well as their rates of compression -amount of different features per hierarchy
-in case of MOLAP solutions, the amount of non usable spaces (sparsity) -in case of ROLAP solutions, length of a line of the central entity-type • Consideration of the additional requirement due to the indication
• Consideration of the additional need due to aggregation • Results
• Qualified projection of the required capacity of the system • Productcriteria
Tuning
Operation/maintenance:
• Activities
• Recommendation of aggregations for standard analysis • Recommendation of indices for frequent used analysis
• In case of higher data volumina (>100 GB) implementation of parallel architectures • Moving data from the data mart to the client ( subsetting, personal OLAP)
• Dismanteling and parallelising the raw data supply • Partitioning of the data deposit
• Results
• Improvement of the running system • Productcriteria
• Automated logging on statement level
• Automated recommendation of aggregations and index • Log-management-system to evaluate the use of the system • Possibility of using bitmap-index
