Dw design hierarchies_7
?
0 likes?248 views
1. Hierarchies are logical structures that organize data into ordered levels, with each level value having a parent value in the level above and children values in the level below. 2. Dimensions can contain multiple hierarchies to group data at different granularities, such as grouping time data by day, week, month, quarter, and year. 3. Levels range from general to specific, with the root level being the most general. Hierarchies define the parent-child relationships between levels that enable drilling down into data.
Dw design hierarchies_7
- 1. DATA WAREHOUSING Multi Dimensional Data Modeling Hierarchies and Levels
- 2. 2
- 3. product prodId name price store storeId city p1 bolt 10 c1 nyc p2 nut 5 c2 sfo c3 la sale oderId date custId prodId storeId qty amt o100 1/7/97 53 p1 c1 1 12 o102 2/7/97 53 p2 c1 2 11 105 3/8/97 111 p1 c3 5 50 customer custId name address city 53 joe 10 main sfo 81 fred 12 main sfo 111 sally 80 willow la 3 3
- 4. Group data within dimensions: SalesRep ? Region ? SubRegion ? Country ? Customer 4 4
- 5. ? Hierarchies are logical structures that use ordered levels as a means of organizing data. ? For a particular level value, a value at the next higher level is its parent, and values at the next lower level are its children 5 5
- 6. ? Hierarchies impose a family structure on dimension values. For a particular level value, a value at the next higher level is its parent, and values at the next lower level are its children. ? These familial relationships enable analysts to access data quickly. 6 6
- 7. A dimension can be composed of more than one hierarchy. years weeks months time day week month quarter year 1 1 1 1 2000 2 1 1 1 2000 quarters 3 1 1 1 2000 days weeks 4 1 1 1 2000 5 1 1 1 2000 6 1 1 1 2000 7 1 1 1 2000 months 8 2 1 1 2000 7 7
- 8. ? Query tools use hierarchies to enable you to drill down into your data to view different levels of granularity. ? This is one of the key benefits of a data warehouse. 8 8
- 9. ? When designing hierarchies, you must consider the relationships in business structures. ? For example, a divisional multilevel sales organization. 9 9
- 10. ? A level represents a position in a hierarchy. For example, a time dimension might have a hierarchy that represents data at the month, quarter, and year levels. ? Within a hierarchy, each level is logically connected to the levels above and below it. years time day week month quarter year 1 1 1 1 2000 2 1 1 1 2000 3 1 1 1 2000 quarters 4 1 1 1 2000 5 1 1 1 2000 6 1 1 1 2000 7 1 1 1 2000 months 8 2 1 1 2000 10
- 11. ? The levels in a dimension are organized into one or more hierarchies. years months quarters weeks months 11
- 12. ? Levels range from general to specific, with the root level as the highest or most general level. all years weeks quarters months days 12
- 13. ? Level relationships specify top-to-bottom ordering of levels from most general (the root) to most specific information. ? They define the parent-child relationship between the levels in a hierarchy. 13
- 14. store city region sType tId size location t1 small downtown store storeId cityId tId mgr t2 large suburbs s5 sfo t1 joe s7 sfo t2 fred city cityId pop regId s9 la t1 nancy sfo 1M north la 5M south region regId name north cold region south warm region 14
- 15. ? The Data Warehouse Toolkit.Second Edition.The Complete Guide to Dimensional Modeling.Ralph Kimball.Margy Ross