SQLBI Methodology

1. SQLBI Methodology Alberto Ferrari – alberto.ferrari@sqlbi.com Marco Russo – marco.russo@sqlbi.com

2. Who we are BI03 - SQLBI Methodology Spaghetti English!  (we’re from Italy) Founders of SQLBI.COM Book authors

3. Topics in this session BI03 - SQLBI Methodology A methodology is far too complex for a simple session, nevertheless, we will make a simple introduction. Draft papers can be found onwww.sqlbi.com/sqlbimethodology.aspx • Brief description of Inmon and Kimball methodologies • Our vision of the Data Warehouse • New layers in the architecture • The usage of views as an interface between layers

4. OLTP System(s) OLAP Data Warehouse Client Data Mart OLAP Data Mart 1 3 4 5 2 Retrieve Data Populate Populate Query Transform Data Data Warehouse DM / OLAP Data Architecture of a BI solution BI03 - SQLBI Methodology

5. Data Mart Data Mart Inmon Data Warehouse The Data Warehouse is the corporate data model Other systems “will be derived” from the DWH OLTP System(s) OLAP Data Warehouse OLAP BI03 - SQLBI Methodology

6. Data Mart Data Mart Kimball Data Warehouse The Data Warehouse is the sum of all theData Marts OLTP System(s) OLAP STAGING DATABASE Data Warehouse OLAP BI03 - SQLBI Methodology

7. The common choice BI03 - SQLBI Methodology Since Kimball’s methodology: • Is faster both to project and develop • Is very well documented • Leads to faster ETL, because there are fewer steps • Does not need months of analysis It is the winner, in almost all Data Warehouses we encountered in our day to day work

8. Data Mart Star Schema The structure of the Data Marts need to be a simple star schema, made up of only facts and dimensions. Simple to manage huge amount of data quickly BI03 - SQLBI Methodology

9. Data Marts interactions Sales Person Customers Vendors Purchase Sales Department Time Production Products Employee We need to update products… Hist Prod. BI03 - SQLBI Methodology

10. Departmental views of data… Sales Person Sales: we want a different description for products Customers Vendors Purchase Purchase: we want to filter out products too old Sales Department Time Sales: our time dimension is slightly different… Production Products Production: we want to add more SCD attributes to the products dimension Employee Hist Prod. BI03 - SQLBI Methodology

11. SQLBI Methodology

12. Data Mart Data Mart Complete vision of Data Warehouse The Data Warehouse is a view of both the corporate data model and the Data Marts OLTP System(s) OLAP Data Warehouse OLAP BI03 - SQLBI Methodology

13. Data Mart Data Mart Overview of the structure Entity / Relationship database All relationships are held with natural keys Holds the complete data model Kimball Data Marts Fact Tables Dimension Tables Surrogate Keys Slowly Changing Dimensions OLAP Cubes Dimensions Attributes Measure Groups Details of presentation Details of computation Data Warehouse OLAP OLAP BI03 - SQLBI Methodology

14. SQLBI Methodology CONFIGURATION DB Source OLTP Mirror OLTP Staging Area ODS Data Warehouse Operational Data Store Data Marts Custom Reports OLAP Cubes Client Tools OtherSystems Excel, Proclarity, … BI03 - SQLBI Methodology Customers

15. SQLBI Methodology CONFIGURATION DB Source OLTP Mirror OLTP Staging Area ODS Data Warehouse Operational Data Store Data Marts Custom Reports OLAP Cubes Client Tools OtherSystems Excel, Proclarity, … BI03 - SQLBI Methodology Customers

16. SQLBI Methodology CONFIGURATION DB Source OLTP Mirror OLTP Staging Area ODS Data Warehouse Operational Data Store Data Marts Custom Reports OLAP Cubes Client Tools OtherSystems Excel, Proclarity, … BI03 - SQLBI Methodology Customers

17. SQLBI Methodology CONFIGURATION DB Source OLTP Mirror OLTP Staging Area ODS Data Warehouse Operational Data Store Data Marts Custom Reports OLAP Cubes Client Tools OtherSystems Excel, Proclarity, … BI03 - SQLBI Methodology Customers

18. SQLBI Methodology CONFIGURATION DB Source OLTP Mirror OLTP Staging Area ODS Data Warehouse Operational Data Store Data Marts Custom Reports OLAP Cubes Client Tools OtherSystems Excel, Proclarity, … BI03 - SQLBI Methodology Customers

19. Who holds the truth? BI03 - SQLBI Methodology

20. Some details of SqlBI BI03 - SQLBI Methodology Let us expand on the details of our model • How do we build the Data Warehouse? • How do we build the Data Marts? • Which methodology should each database adopt? • How do we manage to always have the ability to make updates at any level?

21. Why the OLTP Mirror is flat? BI03 - SQLBI Methodology

22. SQLBI Main Features BI03 - SQLBI Methodology

23. Example of SQLBI Methodology Modeling Data flow of “Products”

24. Products in the OLTP Productmodeldescriptionisstored in a separate table List Price History and ProductCost are kept in separate tables Finishedgoodsflagsisstoredas a simple bit, no descriptionisprovided Severaltechnicalfields are requiredforbookkeeping BI03 - SQLBI Methodology

25. Products in the Data Warehouse A single producthistorytableholdsall the variations We can trackdifferenthistoricalattributes, notstored in the OLTP Finishedgoodsflagsislinkedto a tablethatprovides a description Modelnamehasbeende-normalized BI03 - SQLBI Methodology

26. Products in the DataMart Productis a slowlychangingdimension, the attributes are deducedfrom the historical and currenttables Allattributes are denormalized, asrequired in the Kimball Methodology Building this dimension from the Data Warehouse is much easier than doing the same directly from the OLTP model. Adding attributes or completely rebuilding the dimension is an easy task. BI03 - SQLBI Methodology

27. Why use natural keys in the DWH? The first version of our model stores products with the current version only Then, after some time, historical tracking of some attributes is needed. All the existing data is still valid, we will only need to change some views to feed the Data Marts with historical data BI03 - SQLBI Methodology

28. From Data Warehouse To Data Mart Data Warehouse Data Mart From DWH to Data Mart BI03 - SQLBI Methodology

29. Divide (et impera) ETL steps Extract, Transform and Load: it is a very complex process that needs to be correctly formalized and understood BI03 - SQLBI Methodology

30. Interfaces between levels • Each step is a different level in the architecture • Each step reside in a different database • Between each step there are VIEWS INTERFACES Views Views Views Views BI03 - SQLBI Methodology

31. VIEWS: 1° class citizens in DWH BI03 - SQLBI Methodology

32. SSIS and Views BI03 - SQLBI Methodology

33. Usage of views to simplify SSIS packages The SSIS ETL Code

34. SSAS and Views BI03 - SQLBI Methodology

35. Usage of views to replace DSV Views in the SSAS Data Source View

36. SQLBI Methodology BI03 - SQLBI Methodology

37. Links • Download the SQLBI Methodology paper fromwww.sqlbi.com/sqlbimethodology.aspx • Write us for any feedback and/or question marco.russo@sqlbi.com alberto.ferrari@sqlbi.com

38. Complete the Evaluation Form & Win! Sponsored by Dell • You could win a Dell Mini Netbook– every day – just for handing in your completed form! Each session form is another chance to win! Pick up your Evaluation Form: • Within each presentation room • At the PASS Booth near registration area Drop off your completed Form: • Near the exit of each presentation room • At the PASS Booth near registration area

39. Thank you for attending this session and the 2009 PASS Summit in Seattle