Introduction to Data Warehousing in Organizations

1. Lecture 1 Themes in this session • An introduction to the data warehouse • Decision making in organisations • The use of data and information in organisations

2. An introduction to the data warehouse

3. The Data Warehouse Definition:” A single, complete and consistent source of data obtained from a variety of sources and made available to end users in a way that they can understand and use in a business context” - Barry Devlin The data warehouse is an architecture for the provision of information ! The data contained in the data warehouse is... • subject oriented • integrated • non-volatile • time variant • transformed • accessible

4. Subject-oriented Operational Systems Informational Systems Sales System Customer Data Payroll System Employee Data Purchasing System Vendor Data

5. Integrated Customer Data Operational Systems Informational Systems Marketing System Order System Billing System

6. Time variant Customer Data Operational Systems Informational Systems Order System 60-90 days 5-10 years

7. Non-volatile Operational Systems Create Update Delete Order System Customer Data Insert Informational Systems Load Access

8. The Data Related Problems Behind the Data Warehouse Data in organisations often has the following characteristics... • massive volume • dispersed • difficult to access • badly integrated • complex data structures • not suitable for high level business queries

9. The Information Needs Behind the Data Warehouse Organisations need information which is... • more holistic in its coverage of the business • selected and enriched • easily accessible • more easily understandable • of a high quality • directly applicable to the decision situation

10. The integration problem DBMS DBMS DBMS DBMS DB DB DB DB End user application A End user application B End user application C

11. The integrated data warehouse DW DBMS DB DB End user application A DB Extraction DB End user application B DB End user application C External DB

12. Characteristics of the integrated data warehouse • Contained data represents a holistic view of the organisation • All contained data is for use in ”knowledge discovery” • current data • historical data • aggregated data • Data usually time-stamped • Data often pre-processed for expected queries • Does not burden operational systems • Own database management environment

13. Some important terminology • granularity - the level of detail of data contained in the data warehouse • aggregation- a summation of detailed data to a less detailed level • de-normalisation - the abandonment of the rules of normalisation and allowance of repetitive storage of data • dimension - a business perspective from which data is looked upon

14. A generic five-tier data warehouse architecture DW DBMS DB DB Data Flow Extraction Middle- ware DB DB DB Legacy Layer Extraction Layer Database Layer Middleware Layer Applications Layer

15. The legacy layer DB DB DB DB DB DB Common legacy layer characteristics... • online source data often in OLTP systems • often dispersed • discrepancies between different data sources • often contains inconsistencies • ”twinkling databases” • already taxed by the normal operations of the organisation • number of different types of data structures present

16. The extraction layer Extraction DB DB DB DB DB DB Often the most complex layer in the architecture, involves... • data selection • retrieval and conversion of data • data cleansing and data scrubbing • data enrichment • data aggregation

17. The database layer DW DBMS DB The database layer is characterised by... • storage of data • processing of queries • data warehouse DBMS • relational database management system (RDBMS) • modified relational database management system • multi-dimensional database management system (MDDBMS) • object database management system (ODBMS) • the storage of metadata

18. The middleware layer DW DBMS DB A technological platform for accessing the database, involves the distribution and control of enquiries and resulting data flows... • client-server • LAN • WAN • Intranet Middle- ware

19. The application layer Interaction with system users through a number of decision support applications... • standard reporting • ad-hoc query processing • statistical analysis • simulations - ”what if” • OLAP • data mining Middle- ware

20. The data mart issue A small-subject oriented warehouse • provides information for a specific sub-group of the enterprise’s business analyses • far narrower scope than an enterprise wide data warehouse • far smaller data volumes • more manageable • faster and easier to fine tune the design of the data mart • allows a “piecemeal” approach to some of the enormous integration problems involved in creating an enterprise wide data model

21. Data mart evolution Operational Systems Sales System Data Mart Payroll System Marketing System Customer Data Purchasing System Order System Billing System

22. Data marts based on an enterprise wide data warehouse Data Warehouse Data Mart Operational Systems Sales System Payroll System Marketing System Purchasing System Order System Billing System

23. Avoid the data mart explosion Data Mart Data Mart Data Mart

24. Alternative DBMS’s for the database layer DW DBMS DB A closer look at the three of the four major DBMS alternatives, namely... • relational database management system (RDBMS) • modified relational database management system • multi-dimensional database management system (MDDBMS)

25. The relational database management system (RDBMS) DW DBMS DB • based on standard, normalised relational tables • known technology, many supporting applications, portable • standard query interface (SQL) • supports easy summations and calculations • can support very large databases • can be slow when processing complex queries • established suppliers

26. The modified RDBMS DW DBMS DB • uses star-join schema based data structure • expanded SQL, good for business queries • provides a more readily understandable interface • specially designed for quick access and fast calculations • highly indexed • often used in data marts (ie, dw with a limited subject area) • good market reputation • demands good knowledge of users information needs

27. Star-join schema in a modified RDBMS

28. The Multi-Dimensional DBMS (MDDBMS) DW DBMS DB • Uses a ”metacube” as standard data structure • data stored as an array with any number of possible dimension • Optimised for OLAP applications • often only compatible with proprietary systems • no branch standards • demands new competence • can have bad performance with large data volumes

29. Metadata Data about data Main functions are to give... • data definitions • the origin of data • the structure of data • rules for the selection and transfer of data • qualitative and quantitative data about data

30. The metadata repository An integrated complete source of metadata • is at the heart of the data warehouse architecture • supports the information needs of... • system developers • data administrators • system administrators • users • applications on the data warehouse • very complex data structure • must contain full version history • must always be up to date

31. Decision making in organisations

32. The basics of organisational decision making • decisions involve choices between alternative courses of action in a given decision situation • decision making relates to organisational purposes and should serve the interests of the organisation • decisions are made on behalf of the organisation by a decision making unit, these can be anything from an individual to an entire organisation • decision making occurs in a specific environment which has a great impact on the decision making process and the choices made

33. Decision making models • Provide a framework for interpreting decision making in an organisation • help articulate the organisational aspects of the environment in which decision making occurs • can help suggest what form of decision support is best suited to an organisation

34. 4 organisational decision making models Rational Political competitive Garbage can Program • The rational model • The political/competitive model • The garbage can model • The program model

35. The rational model Rational Political competitive Garbage can Program • Involves organisational units using information rationally in order to make choices on behalf of the organisation • decisions based on sound analysis of possible outcomes and their expected value to the organisation • decision unit chooses those options that lead to the outcome with the maximum expected value • the model is widely spread but there is some scepticism as to the applicability of the model in complex, organisational decision situations

36. The political/competitive model • Decision making occurs in an environment where decisions are consequences of strategies and tactics of decision units trying to influence decisions so that they result in choices favourable to themselves • different stakeholders in the same decision process have different goals • decision units promote the choice of options that they believe will result in an outcome which best serves their own purposes • the interests of the organisation are often subordinate to the interests of the decision units involved in the process Rational Political competitive Garbage can Program

37. The garbage can model • Organisational decisions are consequences of intersections of problems looking for solutions, solutions looking for problems, and opportunities for making decisions • participants in the decision making process look for opportunities to apply tried and tested solutions to problems they are confronted with • the organisational memory records which solutions are available and to what sorts of problems they were applicable • this model highlights the role of chance and timing in organisational decision making and is quite different from the rational model Rational Political competitive Garbage can Program

38. The program model • A model based on the presumption that decision makers are to some extent incapable and unwilling to take decisions • decision making is affected by standard operating procedures, group norms, and action-constraining organisational programs • these standard practices result in a “programming” of decision makers • decision making behaviour at time t will probably be the same as decision making behaviour at time t-1 Rational Political competitive Garbage can Program

39. Problems with organisational decision making • limited rationality in the choice of alternatives • inconsistent preferences for decision outcomes • chaotic, fragmented, and coincidental decision processes • decision making as a sacred ritual • conflict of interest

40. The organisational decision making environment • Available knowledge - more and increasing • knowledge will become more available and more proliferous • Complexity - more and increasing • specialization and diversity in systems accompanied by increasing interdependence • Turbulence - more and increasing • the rate of change in the environment is continually increasing

41. The difficulties of managerial problem solving • managerial problems are by nature indefinite • not easily identified and in some cases not even possible to agree on what the problem is • most managerial problems are interconnected • acting towards the solution of one problem may adversely affect another problem, the sub-optimisation problem • managerial problems come in an infinite variety of shapes and sizes • managerial problems most often have no clear cut solution • the factors which affect managerial problems are often unbounded

42. The use of data and information in organisations

43. Retrieving information • Scanning (exploration) • general browsing of several data sources in order to spot variations or find a specific piece of information • Focused search (exploitation) • searching for data in order to answer a specific question or solve a given problem

44. The rational use of information • Information’s main role is reducing uncertainty • ignorance about future consequences of current actions • ignorance of the knowledge possessed by others and their probable actions • The cost of information should never exceed the value of the benefits it delivers • this can /theoretically) be achieved by comparing the situation with a specific piece of information as compared to the situation without that same information

45. The real (ab)use of information • Information overload and the distribution of worthless information • Information is used for ex-ante substantiation of decisions • Information is power and power is rarely shared • Information seeking is a ritual where the organisations decision making history decided which information is “right” • Information can be imbued with multiple meanings depending on the intentions of the person citing the information • Soft is best

46. Some of the problems of providing information • What is the organisations “culture” of information use? • What decision situations are going to be supported by the information and which factors are “relevant” to these decisions? • How will the decision situations evolve and how will this affect the information requirements? • How can the information be presented so as to be minimally unambiguous and maximally accessible ?