Chapt 4 5 Modeling of Features of Dimensions

Chapt. 4. 5 Modeling of Features of Dimensions Within a dimension hierarchy, elements at the same level may have different attributes (properties). Consider the Product Groups (PG) Car: (cc, PS, cyl, fuel) Camera: (reflex, digital, focal length) TV: (screensize, audio, color, flat) etc. Such specific properties are called Features in DWH If all elements have the same attributes, modelling is trivial, within a common relation. Prof. Bayer, DWH, CH. 4. 5, SS 2000 1

Partial Schema for Features n P# Products n 1 belongs PG# Prod-Group n shows possesses P#, FN, F-Value m Prof. Bayer, DWH, CH. 4. 5, SS 2000 m Feature FN, Unit 2

Auxiliary Query for Query Formulation to see the features of the product-group ‚Car‘: select FN, Unit from Prod-Group G, shows s, Feature F where G. name = ‚Car‘ and G. PG# = s. PG# and s. FN = F. FN i. e. semantic help for follow-up query Prof. Bayer, DWH, CH. 4. 5, SS 2000 3

„P# of engines with 2800 cc“ select P# from possesses pos where pos. FN = ‚cc‘ and pos. F-Value = ‚ 2800‘ Prof. Bayer, DWH, CH. 4. 5, SS 2000 4

Problem: F-values have specific Units and therefore types, e. g. cc, PS, audio, screensize, … How to code, represent F-Value? Ex: Cities belong to countries, which show features like: currency political system VAT-Rate Steuer-Hebesatz Exercise: construct detailed schema for this Prof. Bayer, DWH, CH. 4. 5, SS 2000 5

Month M# belongs_T D# Time Facts shows_T P# Prod-Group Feature-T FN, unit Posesses_T D#, FN, F-Value Prof. Bayer, DWH, CH. 4. 5, SS 2000 Posesses_T P#, FN, F-Value 6

Facts . . . Region Prod-Group PG# n belongs_P P# Prod-Group n shows_P m Posesses_T P#, FN, F-Value Prof. Bayer, DWH, CH. 4. 5, SS 2000 m Feature-P FN, Unit 7

Typical features for months: avg temperature avg sunshine avg beer consumption on holidays Prof. Bayer, DWH, CH. 4. 5, SS 2000 Production planing for beer and icecream 8

Typical Query S Sales where screensize = 17 Brand = Sony ( Year = 1999 Month = Dec Year = 2000 Month = Jan) screensize restriction on Product-Hierarchy Brand restriction on Manufacture-Hierarchy or additional restriction on Product-Hierarchy Combine all restrictions to get one or a few Query-Boxes on Base-Cube. Retrieve Query-Boxes only from Base-Cube, perform 1. 2. 1. Post-filtering 2. aggregation 3. Problem: Restrictions, computations based on F-Values? 4. Be careful with type Incompatibilities! Prof. Bayer, DWH, CH. 4. 5, SS 2000 9