Data Warehousing 2

Data Warehousing 2

1. Slowly Changing Dimensions (SCD)

   • Type 2
     • Tracks changes. Type 2 is able to track as there are 4 supplementary attributes that is added to the dimension: SCD original ID (alt PK), SCD start/active date, SCD end date, and current SCD status (active/inactive)
       • Dimensions are normally static. But, a dimension can change if (e.g sales person moving to another sales territory, employee resigned/promoted)
   • Type 3
     • Does not track entire history. Tracks only current and original state. Cons: If change more than once, can't track. Type 3 is able to track as 2 additional attributes are added: SCD start date (date member became active) and SCD initial Value (original state of the changed attribute)
   • Type 1
     • Does not track changes of dimension members (show only). Thus, it overwrites prev dimension info w current dimension info, and we can't determine the dimension members past state. This results in inaccurate analysis.

2. Designing DW

   • 1. Choose a data mart: choose a subject matter for the DM, *first DM sold be most value for $, answers most imp biz qns, most accessible for data extraction. Customer invoices/month statements are good as it is easily accessible and has high quality.
   • 2. Choose the table granularity: decide what the fact table record represents *lowest level/detail (transaction level: thus usually is amnt). This affects the DM's dimension.
   • 3. Identify and conform the dimensions (select fact table dimensions): dimensions are platforms for browsing the allowable constraint values. Good dimensions = easily understandable. Conformed dimension = dimension used in 2 data marts (e.g customer and product dimension): It allows DM to be built correctly and independently, and allows them to merge coherently into an overall DW. An ideally conformed dimension of 2 data marts allow easy implementation of drill thru
   • 4. Choosing fact table's facts: data must be numeric and additive. Additional facts can be added anytime as long as they are consistent with the grain of the table
   • 5. Store recalculations in the fact table: (e.g net prices: list price minus net price). This prevents mistakes made by users.
   • 6. Rounding out dimension tables 7. Choose duration of database 8. Track SCDs

3. Data WH Analysis

   • OLAP (Online Analytical Processing)
     • OLAP is a system designed to address the need of info extortion by providing effective and efficient adhoc analysis of organisation's data. Main focus: effectiveness, accessibility, response time. It enables end users to perform data driven activities like generating queries using query tools, perform adhoc requests, and conduct statistical and other analysis to query online system and to conduct analysis
       • MOLAP
         • Multidimensional OLAP: Requires precomputation and preprocessing (storage of info). It is implemented via a multidimensional DB that summarises transactions into multidimensional views ahead of time.
       • ROLAP
         • Relational OLAP: Does not require pre computation and preprocessing. Accesses data in relational DB and generate SQL queries to calculate info required.
       • HOLAP
         • Hybrid OLAP: Combines ROLAP AND MOLAP > Greater scalability. Faster computation than MOLAP, stores data half in MOLAP, half in ROLAP. Suitable for cubes that require fast query response for summaries based on large amnt of data
       • OLAP OPERATIONS: SLICE (subset of a multidimensional array), DICE (slice on more than 2 dimension), DRILL DOWN/UP (navigation among levels of data ranging from the (down): most summarised to the most detailed one), ROLL UP (computing all data r/s for one/more dimension, PIVOT (change report's dimensional orientation or an ad hoc query page display), DILL THRU (Drilling thru the cube's bottom level to it's back end relational tables by using SQL)
   • OLTP (Online Transaction Processing)
     • OLTP is a system that is responsible for capturing and storing data related to day to day biz functions like ERP, CRM, SCM, POS. Main focus: efficiency of routined tasks.
   • OLAP vs OLTP
     • 1. Purpose (OLAP helps support decision making and provide answers to business queries, OLTP helps carry out day to day business functions) 2. Data source (OLAP's data comes from DW or DM, thus is non normalised and is focused on accuracy and entirety whereas OLTP's data comes from transaction database that is normalised and is focused on efficiency and consistency. 3. Reporting (OLAP's report are adhoc and multidimensional, and is broadly focused. OLTP's reports are routined, periodic, and narrowly focused) 4. Hardware requirements (OLAP requires databases that has large capacity, specialised, and has a multiprocessor. OLTP only requires an ordinary relational database) 5. Execution speed (OLAP's speed is slow as it contains resource intensive, complex, large scaled queries. OLTP's speed is fast as it merely records biz transactions and routine reports)

4. Benefits of DW

   • Provides faster connections, makes powerful solutions that provides growth and is affordable, frees up in-house system capacity and cash flow, meets storage needs for large volumes of data, enables users to access data remotely