1 / 22

Platinum

Platinum. Learn & Enjoy [Put your phone on Vibrate!]. www.sqlbits.com. Group BY: [Food and Drink at Reading Bowl, see you there!]. Gold. Feedback Forms: [Voucher for £30 book on return of Form]. Silver. Lunch Time Sessions: [Idera in Everest, Quest in Memphis, Grok in Chic 1 and 2].

maida
Download Presentation

Platinum

An Image/Link below is provided (as is) to download presentation Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author. Content is provided to you AS IS for your information and personal use only. Download presentation by click this link. While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server. During download, if you can't get a presentation, the file might be deleted by the publisher.

E N D

Presentation Transcript

  1. Platinum Learn & Enjoy [Put your phone on Vibrate!] www.sqlbits.com Group BY: [Food and Drink at Reading Bowl, see you there!] Gold Feedback Forms: [Voucher for £30 book on return of Form] Silver 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]

  2. HandlingEarly Arriving Facts Presented By Sutha Thiru Edenbrook ETL Capability Lead sutha@suthathiru.com 6th October 2007

  3. Agenda • Typical Warehouse Architecture • Early Arriving Facts • Handling early arriving facts • Demo • Two Fact Tables Per One Fact Table • BISC • Resources • Q & A http://sqlblogcasts.com/blogs/tsutha/

  4. Typical Warehouse Architecture SQL Server AS Cube Oracle ODS Stage Warehouse DB2 CSV Files / Text Files Reporting Services Pro Clarity Performance Point Share Point Excel http://sqlblogcasts.com/blogs/tsutha/

  5. 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 http://sqlblogcasts.com/blogs/tsutha/

  6. Early Arriving Facts (Contd.) Warehouse Source Data Stage SSIS SSIS Fact Record – Joe Bloggs, £100 Dimension – 1, John Smith http://sqlblogcasts.com/blogs/tsutha/

  7. 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. http://sqlblogcasts.com/blogs/tsutha/

  8. 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. http://sqlblogcasts.com/blogs/tsutha/

  9. 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. http://sqlblogcasts.com/blogs/tsutha/

  10. Handling Early Arriving Facts DEMO http://sqlblogcasts.com/blogs/tsutha/

  11. 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. http://sqlblogcasts.com/blogs/tsutha/

  12. 2 Fact Tables (Contd.) http://sqlblogcasts.com/blogs/tsutha/

  13. 2 Fact Tables (Contd.) Why do we do this? http://sqlblogcasts.com/blogs/tsutha/

  14. 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 http://sqlblogcasts.com/blogs/tsutha/

  15. BISC BI Skills & Processes Business Data & Technology The Success of BISC dependant on Placing Business at the heart of BISC. http://sqlblogcasts.com/blogs/tsutha/

  16. 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 http://sqlblogcasts.com/blogs/tsutha/

  17. BISC Is Not Responsible For • Data not in the BI Framework • Cleaning the data • Authoring Reports http://sqlblogcasts.com/blogs/tsutha/

  18. 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 http://sqlblogcasts.com/blogs/tsutha/

  19. 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 http://sqlblogcasts.com/blogs/tsutha/

  20. 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 http://sqlblogcasts.com/blogs/tsutha/

  21. Thank YouQ & A http://sqlblogcasts.com/blogs/tsutha/

  22. Platinum www.SQLBits.com[Conference Web site] www.sqlbits.com www.SQLBlogCasts.com[Becoming the premier Blogging site for SQL professionals] Gold www.SQLServerFAQ.com[UK SQL Server Community Website] Silver UK SQL Bloggers cwebbbi.spaces.live.comsqlblogcasts.com/blogs/simonssqlblogcasts.com/blogs/tonyrogerson Feedback Forms!!

More Related