Dimensional Modeling

1. Dimensional Modeling By Divya Manduva Pratima Surapaneni

2. Contents • Introduction to Dimensional Modeling • ER Vs Dimensional Modeling • CIF Relational Modeling • Kimball’s Dimensional Modeling • Comparison • Conclusion • Discussion

3. Introduction • Dimensional Modeling • Drill up • Slice and dice • Business Focused and Accepted • Accessible Information • Input for Decision Support • Consistent Presentation • ER to Dimensional Modeling • An Illustration • Relational Vs Dimensional

5. Corporate Information Factory

6. Kimball’s Dimensional Modeling Architecture

8. Dimensional Tables • 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)

9. SDLC Approach to Design a Data Warehouse

10. Compare and Contrast • Approach (Scope) • Perspective • Data Flow • Functionality/ Flexibility • Results

11. Conclusion • Suggesting a Hybrid Model

12. References • 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/sg247138.pdf • IBM Informix Dynamic Server Enterprise and Workgroup Edition, v10.00.xC3; IBM Informix Dynamic Server Express Edition, v10.00.xC3E; and IBM Informix Client Software Developer's Kit, v2.90.xC3. http://publib.boulder.ibm.com/infocenter/idshelp/v10/index.jsp?topic=/com.ibm.ddi.doc/ddi222.htm • http://www.wilshireconferences.com/EDF2002/analytics-sessions.htm • http://www.learndatamodeling.com/diff_r_d.htm • http://blogs.ittoolbox.com/dw/design/archives/dimensional-modeling-fundamentals-7712

13. References (contd..) • 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/isbnInquiry.asp?z=y&endeca=1&isbn=0471324213&itm=9 • http://www.dbmsmag.com/9510d05.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.di.fct.unl.pt/mei/bddw/material_apoio/artigos/files/2000-Moody.pdf) • http://www.atlantamdf.com/Presentations/AtlantaMDF_091106.pdf • http://www.intelligententerprise.com/showArticle.jhtml;jsessionid=NKBOH2L3S2BMMQSNDLRCKH0CJUNN2JVN?articleID=17800088&pgno=2 • Dimensional Modeling: A whirlwind Tour of How and Why, Wayne Little, October 2006

14. Discussion

15. Thank you !