dimensional modeling by divya manduva divya manduva pratima surapaneni
Post on 22-Dec-2015
241 Views
Preview:
TRANSCRIPT
Dimensional Modeling Dimensional Modeling
ByBy
Divya ManduvaDivya Manduva
Pratima SurapaneniPratima Surapaneni
ContentsContents
Introduction to Dimensional ModelingIntroduction to Dimensional Modeling ER Vs Dimensional ModelingER Vs Dimensional Modeling CIF Relational ModelingCIF Relational Modeling Kimball’s Dimensional ModelingKimball’s Dimensional Modeling ComparisonComparison ConclusionConclusion DiscussionDiscussion
IntroductionIntroduction
Dimensional ModelingDimensional Modeling• Drill upDrill up• Slice and diceSlice and dice• Business Focused and AcceptedBusiness Focused and Accepted• Accessible InformationAccessible Information• Input for Decision SupportInput for Decision Support• Consistent PresentationConsistent Presentation
ER to Dimensional ModelingER to Dimensional Modeling• An IllustrationAn Illustration
Relational Vs Dimensional Relational Vs Dimensional
Relational Modeling Dimensional Modeling
Data is stored in RDBMS 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 FactoryCorporate Information Factory
Kimball’s Dimensional Modeling ArchitectureKimball’s Dimensional Modeling Architecture
Dimensional TablesDimensional Tables
Represent objects of businessRepresent objects of business Contain text descriptions of businessContain text descriptions of business Small # of rows, Large # of columnsSmall # of rows, Large # of columns Serve as Primary source for “Query by”/ Serve as Primary source for “Query by”/
“Report by” constraints“Report by” constraints Highly De normalizedHighly De normalized Represent 10% of total dataRepresent 10% of total data Depth and Quality Determine Data Depth and Quality Determine Data
Warehouse Usefulness ( Level of Warehouse Usefulness ( Level of Granularity)Granularity)
SDLC Approach to Design a Data SDLC Approach to Design a Data WarehouseWarehouse
Compare and ContrastCompare and Contrast
Approach (Scope)Approach (Scope) PerspectivePerspective Data FlowData Flow Functionality/ FlexibilityFunctionality/ Flexibility ResultsResults
ConclusionConclusion
Suggesting a Hybrid ModelSuggesting a Hybrid Model
ReferencesReferences A method for developing Dimensional Data Marts, Tim Chenoweth, David A method for developing Dimensional Data Marts, Tim Chenoweth, David
Schuff, Robert St. Louis, Communications of ACM, Volume 46, Issue 12 Schuff, Robert St. Louis, Communications of ACM, Volume 46, Issue 12 December 2003December 2003
Dimensional Modeling: In a Business Intelligence Environment, Chuck Ballard, Dimensional Modeling: In a Business Intelligence Environment, Chuck Ballard, Daniel M. Farrell, Amit Gupta, Carlos Manuela, Stanislaw Venice Daniel M. Farrell, Amit Gupta, Carlos Manuela, Stanislaw Venice http://www.redbooks.ibm.com/redbooks/pdfs/sg247138.pdfhttp://www.redbooks.ibm.com/redbooks/pdfs/sg247138.pdf
IBM Informix Dynamic Server Enterprise and Workgroup Edition, v10.00.xC3; IBM Informix Dynamic Server Enterprise and Workgroup Edition, v10.00.xC3; IBM Informix Dynamic Server Express Edition, v10.00.xC3E; and IBM IBM Informix Dynamic Server Express Edition, v10.00.xC3E; and IBM Informix Client Software Developer's Kit, v2.90.xC3. Informix Client Software Developer's Kit, v2.90.xC3. http://publib.boulder.ibm.com/infocenter/idshelp/v10/index.jsp?topic=/com.ibm.http://publib.boulder.ibm.com/infocenter/idshelp/v10/index.jsp?topic=/com.ibm.ddi.doc/ddi222.htmddi.doc/ddi222.htm
http://www.wilshireconferences.com/EDF2002/analytics-sessions.htmhttp://www.wilshireconferences.com/EDF2002/analytics-sessions.htm http://www.learndatamodeling.com/diff_r_d.htmhttp://www.learndatamodeling.com/diff_r_d.htm http://blogs.ittoolbox.com/dw/design/archives/dimensional-modeling-http://blogs.ittoolbox.com/dw/design/archives/dimensional-modeling-
fundamentals-7712fundamentals-7712
References (contd..)References (contd..) A method for developing Dimensional Data Marts, Tim Chenoweth, David Schuff, A method for developing Dimensional Data Marts, Tim Chenoweth, David Schuff,
Robert St. Louis, Communications of ACM December, Volume 46, Issue 12 2003Robert St. Louis, Communications of ACM December, Volume 46, Issue 12 2003 Mastering data warehouse design : relational and dimensional techniques / Claudia Mastering data warehouse design : relational and dimensional techniques / Claudia
Imhoff, Nicholas Galemmo, Jonathan G. Geiger Imhoff, Nicholas Galemmo, Jonathan G. Geiger http://http://search.barnesandnoble.com/booksearch/isbnInquiry.asp?zsearch.barnesandnoble.com/booksearch/isbnInquiry.asp?z==y&endecay&endeca=1&isbn=0471324213&itm=9=1&isbn=0471324213&itm=9
http://www.dbmsmag.com/9510d05.htmlhttp://www.dbmsmag.com/9510d05.html http://http://www.casact.org/newsletter/index.cfm?fawww.casact.org/newsletter/index.cfm?fa==viewart&idviewart&id=5349=5349 http://www.b-eye-network.com/view/410http://www.b-eye-network.com/view/410 http://http://www.dkms.com/papers/cifckf.pdfwww.dkms.com/papers/cifckf.pdf Daniel L. Moody, Mark A.R. Kortink, “Daniel L. Moody, Mark A.R. Kortink, “From Enterprise to Dimension Models: From Enterprise to Dimension Models:
A Methodology for Data Warehouse and Data Mart DesignA Methodology for Data Warehouse and Data Mart Design”, Proceedings of ”, Proceedings of the International Workshop on Design and Management of Data Warehouses the International Workshop on Design and Management of Data Warehouses (DMDW'2000), Stockholm, Sweden, June 5-6, 2000. (DMDW'2000), Stockholm, Sweden, June 5-6, 2000. (http://ssdi.di.fct.unl.pt/mei/bddw/material_apoio/artigos/files/2000-Moody.pdf) (http://ssdi.di.fct.unl.pt/mei/bddw/material_apoio/artigos/files/2000-Moody.pdf)
http://www.atlantamdf.com/Presentations/AtlantaMDF_091106.pdfhttp://www.atlantamdf.com/Presentations/AtlantaMDF_091106.pdf http://www.intelligententerprise.com/http://www.intelligententerprise.com/
showArticle.jhtml;jsessionid=NKBOH2L3S2BMMQSNDLRCKH0CJUNN2JVN?showArticle.jhtml;jsessionid=NKBOH2L3S2BMMQSNDLRCKH0CJUNN2JVN?articleID=17800088&pgno=2articleID=17800088&pgno=2
Dimensional Modeling: A whirlwind Tour of How and Why, Wayne Little, October 2006Dimensional Modeling: A whirlwind Tour of How and Why, Wayne Little, October 2006
DiscussionDiscussion
Thank you !Thank you !
top related