6 - Advanced Dimensional Modeling Concepts Prof. Navneet...

Info iconThis preview shows pages 1–10. Sign up to view the full content.

View Full Document Right Arrow Icon

Info iconThis preview has intentionally blurred sections. Sign up to view the full version.

View Full DocumentRight Arrow Icon

Info iconThis preview has intentionally blurred sections. Sign up to view the full version.

View Full DocumentRight Arrow Icon

Info iconThis preview has intentionally blurred sections. Sign up to view the full version.

View Full DocumentRight Arrow Icon

Info iconThis preview has intentionally blurred sections. Sign up to view the full version.

View Full DocumentRight Arrow Icon

Info iconThis preview has intentionally blurred sections. Sign up to view the full version.

View Full DocumentRight Arrow Icon
This is the end of the preview. Sign up to access the rest of the document.

Unformatted text preview: Advanced Dimensional Modeling Concepts Prof. Navneet Goyal Department of Computer Science & Information Systems BITS, Pilani Topics Mini-Dimensions Out-Triggers Drill-Across Conformed Dimensions Time Dimension Multi-valued Dimensions Helper Tables Bridge tables Role Playing Dimensions Rapidly Changing Monster Dimensions Multi-million customer dimension present unique challenges that warrant special treatment: 1. Browsing or constraining takes too long 2. Type-II change not feasible 3. Business users want to track the myriad of customer attribute changes, eg, insurance companies want accurate information of customers at the time of approval of a policy or when a claim is made Mini-Dimensions Single technique to handle browsing- performance & change tracking problems Separate out frequently analyzed or frequently changing attributes into a separate dimension, called mini- dimension Mini-Dimensions Demographic Key AGE GENDER INCOME LEVEL 1 20-24 M < 20000 2 20-24 M 20K-24999 3 20-24 M 25K-29999 18 25-29 M 20K-24999 10 25-29 M 25K-29999 Mini-Dimensions Minidimension can not be itself allowed to grow very large 5 demographic attibutes Each attribute can take 10 distinct values How many rows in minidimension? 10,0000 Mini-Dimensions Separate out a package of demographic attributes into a demographic mini-dimension Age, gender, marital status, no. of children, income level, etc. One row in mini-dimension for each unique combination of these attibutes Dimension-focused Queries Standard OLAP queries are fact-focused Query touches one fact table and its associated dimensions Some types of analysis are dimension-focused Bring together data from different fact tables that have a dimension in common Common dimension used to coordinate facts Sometimes referred to as drilling across Drill-Across Example Example scenario: Sales fact with dimensions (Date, Customer, Product, Store) CustomerSupport fact with dimensions (Date, Customer, Product, ServiceRep) Question: How does frequency of support calls by California customers affect their purchases of Product X? Step 1: Query CustomerSupport fact Group by Customer SSN Filter on State = California Compute COUNT Query result has schema (Customer SSN, SupportCallCount) Step 2: Query Sales fact Group by Customer SSN Filter on State = California, Product Name = Product X Compute SUM(TotalSalesAmt) Query result has schema (Customer SSN, TotalSalesAmt) Step 3: Combine query results Join Result 1 and Result 2 based on Customer SSN Group by SupportCallCount Compute COUNT, AVG(TotalSalesAmt)...
View Full Document

Page1 / 66

6 - Advanced Dimensional Modeling Concepts Prof. Navneet...

This preview shows document pages 1 - 10. Sign up to view the full document.

View Full Document Right Arrow Icon
Ask a homework question - tutors are online