Dimensional Modeling By Divya Manduva Pratima Surapaneni Contents
Dimensional Modeling By Divya Manduva Pratima Surapaneni
Contents Introduction to Dimensional Modeling u ER Vs Dimensional Modeling u CIF Relational Modeling u Kimball’s Dimensional Modeling u Comparison u Conclusion u Discussion u
Introduction u Dimensional Modeling • • • u Drill up Slice and dice Business Focused and Accepted Accessible Information Input for Decision Support Consistent Presentation ER to Dimensional Modeling • An Illustration u Relational Vs Dimensional
Relational Modeling Dimensional Modeling Data is stored in RDBMS or Multidimensional databases Tables are units of storage Cubes are units of storage Data is normalized and used for OLTP. Optimized for OLTP processing Data is de normalized and used in data warehouse and data mart. Optimized for OLAP Several tables and chains of relationships among them Few tables and fact tables are connected to dimensional tables Volatile (several updates) and time variant Non volatile and time invariant Detailed level of transactional data Summary of bulky transactional data (Aggregates and Measures) used in business decisions SQL is used to manipulate data MDX is used to manipulate data Normal Reports User friendly, interactive, drag and drop multidimensional OLAP Reports
Corporate Information Factory
Kimball’s Dimensional Modeling Architecture
Dimensional Tables u u u u Represent objects of business Contain text descriptions of business Small # of rows, Large # of columns Serve as Primary source for “Query by”/ “Report by” constraints Highly De normalized Represent 10% of total data Depth and Quality Determine Data Warehouse Usefulness ( Level of Granularity)
SDLC Approach to Design a Data Warehouse
Compare and Contrast Approach (Scope) u Perspective u Data Flow u Functionality/ Flexibility u Results u
Conclusion u Suggesting a Hybrid Model
References u u u A method for developing Dimensional Data Marts, Tim Chenoweth, David Schuff, Robert St. Louis, Communications of ACM, Volume 46, Issue 12 December 2003 Dimensional Modeling: In a Business Intelligence Environment, Chuck Ballard, Daniel M. Farrell, Amit Gupta, Carlos Manuela, Stanislaw Venice http: //www. redbooks. ibm. com/redbooks/pdfs/sg 247138. pdf IBM Informix Dynamic Server Enterprise and Workgroup Edition, v 10. 00. x. C 3; IBM Informix Dynamic Server Express Edition, v 10. 00. x. C 3 E; and IBM Informix Client Software Developer's Kit, v 2. 90. x. C 3. http: //publib. boulder. ibm. com/infocenter/idshelp/v 10/index. jsp? topic=/com. ibm. ddi. doc/ddi 222. htm http: //www. wilshireconferences. com/EDF 2002/analytics-sessions. htm http: //www. learndatamodeling. com/diff_r_d. htm http: //blogs. ittoolbox. com/dw/design/archives/dimensional-modelingfundamentals-7712
References (contd. . ) u u u u u A method for developing Dimensional Data Marts, Tim Chenoweth, David Schuff, Robert St. Louis, Communications of ACM December, Volume 46, Issue 12 2003 Mastering data warehouse design : relational and dimensional techniques / Claudia Imhoff, Nicholas Galemmo, Jonathan G. Geiger http: //search. barnesandnoble. com/booksearch/isbn. Inquiry. asp? z=y&endeca=1&isb n=0471324213&itm=9 http: //www. dbmsmag. com/9510 d 05. html http: //www. casact. org/newsletter/index. cfm? fa=viewart&id=5349 http: //www. b-eye-network. com/view/410 http: //www. dkms. com/papers/cifckf. pdf Daniel L. Moody, Mark A. R. Kortink, “From Enterprise to Dimension Models: A Methodology for Data Warehouse and Data Mart Design”, Proceedings of the International Workshop on Design and Management of Data Warehouses (DMDW'2000), Stockholm, Sweden, June 5 -6, 2000. (http: //ssdi. fct. unl. pt/mei/bddw/material_apoio/artigos/files/2000 -Moody. pdf ) http: //www. atlantamdf. com/Presentations/Atlanta. MDF_091106. pdf http: //www. intelligententerprise. com/show. Article. jhtml; jsessionid=NKBOH 2 L 3 S 2 BMMQSNDLRCKH 0 CJUNN 2 JVN? article. ID=17800088&pgno=2 Dimensional Modeling: A whirlwind Tour of How and Why, Wayne Little, October 2006
Discussion
Thank you !
- Slides: 15