Transcript Slide 1
Platinum Learn & Enjoy www.sqlbits.com Gold [Put your phone on Vibrate!] Group BY: [Food and Drink at Reading Bowl, see you there!] Silver Feedback Forms: [Voucher for £30 book on return of Form] Lunch Time Sessions: [Idera in Everest, Quest in Memphis, Grok in Chic 1 and 2] Ask The Experts [Sessions need to finish on time, take questions to the ATE area] Handling Early Arriving Facts Presented By Sutha Thiru Edenbrook ETL Capability Lead [email protected] 6th October 2007 Agenda • • • • • • • • Typical Warehouse Architecture Early Arriving Facts Handling early arriving facts Demo Two Fact Tables Per One Fact Table BISC Resources Q&A 06/10/2007 http://sqlblogcasts.com/blogs/tsutha/ 3 Typical Warehouse Architecture SQL Server Oracle ODS Stage Warehouse AS Cube DB2 CSV Files / Text Files 06/10/2007 Reporting Services Pro Clarity Performance Point Share Point Excel http://sqlblogcasts.com/blogs/tsutha/ 4 Early Arriving Facts • “An Early arriving fact takes place when the activity measurement arrives at the data warehouse without its full context. In other words, the statuses of the dimensions attached to the activity measurement are ambiguous or unknown for some period of time” Kimball Design Tip #57 06/10/2007 http://sqlblogcasts.com/blogs/tsutha/ 5 Early Arriving Facts (Contd.) SSIS SSIS Source Data Stage Fact Record – Joe Bloggs, £100 06/10/2007 http://sqlblogcasts.com/blogs/tsutha/ Warehouse Dimension – 1, John Smith 6 Early Arriving Facts (Contd.) • We are running a real-time business. • When will we get the rest of the data. • Example for early arriving fact, Sales Transaction for £100 for Customer “Joe Bloggs”. We do not have “Joe Bloggs” in our customer dimension table. 06/10/2007 http://sqlblogcasts.com/blogs/tsutha/ 7 Points To Be Aware • Late arriving facts vs early arriving facts. • Dimensions are “not accurate”. • Business Intelligence Service Centre (BISC) will get all the exception reports highlighting default dimensions as well as rejected facts. 06/10/2007 http://sqlblogcasts.com/blogs/tsutha/ 8 Handling Early Arriving Facts • 3 Options – Reject early arriving facts altogether. – Map early arriving facts to default Surrogate key. – Generate dimension on the fly and map the surrogate key to the fact record. 06/10/2007 http://sqlblogcasts.com/blogs/tsutha/ 9 Handling Early Arriving Facts DEMO 06/10/2007 http://sqlblogcasts.com/blogs/tsutha/ 10 2 Fact Tables • Why do we recommend two fact tables? – When default surrogate key (-1) is mapped we don’t load the main fact table. – We load them into separate fact tables. – BISC is responsible to fix these default surrogate key fact records. 06/10/2007 http://sqlblogcasts.com/blogs/tsutha/ 11 2 Fact Tables (Contd.) 06/10/2007 http://sqlblogcasts.com/blogs/tsutha/ 12 2 Fact Tables (Contd.) Why do we do this? 06/10/2007 http://sqlblogcasts.com/blogs/tsutha/ 13 Business Intelligence Service Centre (BISC) • The objective of the BISC is to support the business in deriving maximum value from the Business Intelligence solution as well as working with IT to ensure the solution evolves on a sound technical foundation • Key Personnel BISC Manager Data Manager BI Senior Analyst 06/10/2007 http://sqlblogcasts.com/blogs/tsutha/ 14 BISC BI Skills & Processes Business Data & Technology The Success of BISC dependant on Placing Business at the heart of BISC. 06/10/2007 http://sqlblogcasts.com/blogs/tsutha/ 15 BISC Responsible For • • • • • • Provides BI Roadmap Governance Facilitate communication Standardise internal and external reporting Support business users in self service reporting Provides a central unit for a data stewardship Validating specification for reports 06/10/2007 http://sqlblogcasts.com/blogs/tsutha/ 16 BISC Is Not Responsible For • Data not in the BI Framework • Cleaning the data • Authoring Reports 06/10/2007 http://sqlblogcasts.com/blogs/tsutha/ 17 BISC Roles • BISC Manager – Overall responsibility for BISC – BI Roadmap Management – Benefits Realisation • Data Manager – Meta Data Management – Organise Data Cleansing Process – Define Data Management approach 06/10/2007 http://sqlblogcasts.com/blogs/tsutha/ 18 BISC Roles (Contd.) • BI Senior Analyst – Gather requirements for future phases – Support business to create reports – Liaise with key users in the business – Intermediary role between IT and business – Perform advanced analysis where required 06/10/2007 http://sqlblogcasts.com/blogs/tsutha/ 19 Resources • • • • • • • http://www.rkimball.com/html/designtipsPDF/ http://sqlblogcasts.com/ http://www.sqlis.com/ http://blogs.conchango.com/jamiethomson/ http://cwebbbi.spaces.live.com/ http://www.sqlserverfaq.com/ http://forums.microsoft.com/msdn/showforum. aspx?forumid=80&siteid=1 06/10/2007 http://sqlblogcasts.com/blogs/tsutha/ 20 Thank You Q&A 06/10/2007 http://sqlblogcasts.com/blogs/tsutha/ 21 Platinum www.SQLBits.com www.sqlbits.com Gold [Conference Web site] www.SQLBlogCasts.com [Becoming the premier Blogging site for SQL professionals] Silver www.SQLServerFAQ.com [UK SQL Server Community Website] UK SQL Bloggers cwebbbi.spaces.live.com sqlblogcasts.com/blogs/simons sqlblogcasts.com/blogs/tonyrogerson Feedback Forms!!