Data Warehouse VIP Tour
- 1. T h e D a t a W a r e h o u s e T o u r George Pond Sr. Business Analyst / Data Warehouse Developer http://www.gpond.com 1
- 2. W e lc o m e t o C y p r e s s C a y M a r in a H o w w o u ld y o u b u ild a d a t a w a r e h o u s e ? W h a t w o u ld it d o f o r y o u r b u s in e s s ? T h e a n s w e r s a r e in s id e . L e t ¡¯s b e g in y o u r t o u r w it h f u n c t io n a l r e q u ir e m e n t s . ...Each night, a closing report is prepared by Managers at Baracles (restaurant/pub), Dockside Gifts & Sundries, and at the boat rental kiosk... ...The proposed solution must bring in this information. Cypress Cay Managers must be able to quickly and easily retrieve daily scorecards and drill-down reports for trend analysis... 2
- 3. W e f in d c r it ic a l r e q u ir e m e n t s t h a t c a ll f o r O n - L in e A n a ly t ic a l P r o c e s s in g ...The proposed solution must bring in this information. Cypress Cay Managers must be able to quickly and easily retrieve daily scorecards and drill-down reports for trend analysis... O L A P c u b e s e n a b le s c o r e c a r d s , d r ill- d o w n r e p o r t s a n d t r e n d a n a ly s is . 3
- 4. W e d is c u s s d a t a r e q u ir e m e n t s w it h C y p r e s s C a y M a n a g e m e n t A L o g ic a l D a t a M o d e l id e n t if ie s w h a t t h e c u b e m u s t c o n t a in . 4
- 5. A D im e n s io n a l M o d e l f o r w a r d -e n g in e e r s in t o a P h y s i c a l D a t a b a s e . B o t h r e f le c t t h e L o g ic a l M o d e l. 5
- 6. O u r t o u r p a u s e s a t t h e n e w , e m p t y d a t a b a s e C y p r e s s C a y k e e p s P o in t O f S a le t a b le s , f la t f ile s a n d s p r e a d s h e e t s . E a c h Microsoft SQL SSIS m u s t b e m o v e d in t o s t a g i n g t a b l e s . Server SSAS SSRS 6
- 7. S Q L S e r v e r I n t e g r a t io n S e r v ic e s ( S S I S ) m o v e s d a t a in t o s t a g in g t a b le s Microsoft SQL SSIS Server SSAS SSRS 7
- 8. A f t e r s t a g in g , d a t a is m o v e d a g a i n S Q L S e r v e r A n a ly s is Microsoft SQL SSIS S e r v ic e s ( S S A S ) Server b u ild s 4 f a c t t a b le s a n d 5 d im e n s io n s SSAS SSRS o f C y p r e s s C a y d a t a 8
- 9. H ie r a r c h ie s p r o v id e d r ill- d o w n p a t h s in t o t h e O L A P c u b e Microsoft SQL SSIS Server Hierarchy. SSAS SSRS 9
- 10. W it h in t h e d a t a w a r e h o u s e , w e s e e t r e n d a n a ly s is a d d e d t o t h e c u b e Microsoft SQL SSIS Server SSAS SSRS S S A S in c lu d e s f o r m s f o r a d d in g c a lc u la t io n s a n d K e y P e r f o r m a n c e I n d ic a t o r s ( K P I ) . 10
- 11. M u lt i- d im e n s io n a l c u b e s r e q u ir e M u lt i- D im e n s io n a l e X p r e s s io n s : M D X This MDX query builds a matrix of dates and food sales trends. 11
- 12. M ic r o s o f t E x c e l p i v o t t a b l e s c a n p u b lis h a m a t r i x in g r a p h ic a l f o r m a t N o w o u r m a t r ix h a s f ilt e r s , d r i ll- d o w n c a p a b ilit y a n d s t a t u s in d ic a t o r s . 12
- 13. W it h S Q L S e r v e r R e p o r t in g S e r v ic e s , C y p r e s s C a y s e e s b e y o n d d r y f a c t s . Microsoft SQL SSIS Server P a t t e r n s e m e r g e SSAS SSRS a n d p e r f o r m a n c e is t r a c k e d t h r o u g h s lic e , d ic e a n d d r ill- d o w n . 13
- 14. T h is c o n c lu d e s y o u r D a t a W a r e h o u s e t o u r Y o u w o n ¡¯t f in d C y p r e s s C a y M a r in a o n a n y m a p . I t is a c o m p o s it e o f h o s p it a lit y b u s in e s s e s in t h e O r la n d o a r e a . For your tour today, I generated randomized data and built an OLAP cube with four fact tables and five dimensions. Thank you for taking the tour! George Pond http://www.gpond.com 14