SQLBI Methodology Alberto Ferrari alberto ferrarisqlbi com Marco
SQLBI Methodology Alberto Ferrari – alberto. ferrari@sqlbi. com Marco Russo – marco. russo@sqlbi. com
Who we are • Spaghetti English! (we’re from Italy) • Founders of SQLBI. COM • Book authors BI 03 - SQLBI Methodology
Topics in this session A methodology is far too complex for a simple session, nevertheless, we will make a simple introduction. Draft papers can be found on www. 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 BI 03 - SQLBI Methodology
Architecture of a BI solution OLTP System(s) Data Mart OLAP Data Mart Client Data Warehouse OLAP 1 3 Retrieve Data 2 Transform Data BI 03 - SQLBI Methodology 4 Populate Data Warehouse Populate DM / OLAP 5 Query Data
Inmon Data Warehouse OLTP System(s) Data Mart Data Warehouse The Data Warehouse is the corporate data model BI 03 - SQLBI Methodology OLAP Data Mart OLAP Other systems “will be derived” from the DWH
Kimball Data Warehouse OLTP System(s) OLAP Data Mart AT AB AS E Data Mart ST AG IN GD Data Warehouse BI 03 - SQLBI Methodology The Data Warehouse is the sum of all the Data Marts OLAP
The common choice 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 BI 03 - SQLBI Methodology
Data Mart Star Schema Dim Date Dim Vendors Dim Customers Sales 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 Dim Products BI 03 - SQLBI Methodology Dim Sales Persons
Data Marts interactions Sales Person Vendors Customers Purchase Department Sales Time Products Production Employee Hist Prod. We need to update products… BI 03 - SQLBI Methodology
Departmental views of data… Sales Person Vendors Customers Sales: we want a different description for products Purchase: we want to. Department filter out products too old Sales Time Sales: our time dimension is slightly different… Hist Prod. BI 03 - SQLBI Methodology Products Production: we want to add more SCD attributes to the products dimension Employee
SQLBI METHODOLOGY
Complete vision of Data Warehouse OLTP System(s) Data Mart Data Warehouse OLAP Data Mart OLAP The Data Warehouse is a view of both the corporate data model and the Data Marts BI 03 - SQLBI Methodology
Overview of the structure Data Mart OLAP Data Warehouse OLAP Data Mart Entity / Relationship database All relationships are held with natural keys Holds the complete data model BI 03 - SQLBI Methodology Kimball Data Marts Fact Tables Dimension Tables Surrogate Keys Slowly Changing Dimensions OLAP Cubes Dimensions Attributes Measure Groups Details of presentation Details of computation
SQLBI Methodology CONFIGURATION DB Source OLTP Mirror OLTP Staging Area ODS Operational Data Store Data Warehouse Data Marts OLAP Cubes Custom Reports Client Tools Other Systems Excel, Proclarity, … BI 03 - SQLBI Methodology Customers
SQLBI Methodology CONFIGURATION DB Source OLTP Mirror OLTP Staging Area ODS Operational Data Store Data Warehouse Data Marts OLAP Cubes Custom Reports Client Tools Other Systems Excel, Proclarity, … BI 03 - SQLBI Methodology Customers
SQLBI Methodology CONFIGURATION DB Source OLTP Mirror OLTP Staging Area ODS Operational Data Store Data Warehouse Data Marts OLAP Cubes Custom Reports Client Tools Other Systems Excel, Proclarity, … BI 03 - SQLBI Methodology Customers
SQLBI Methodology CONFIGURATION DB Source OLTP Mirror OLTP Staging Area ODS Operational Data Store Data Warehouse Data Marts OLAP Cubes Custom Reports Client Tools Other Systems Excel, Proclarity, … BI 03 - SQLBI Methodology Customers
SQLBI Methodology CONFIGURATION DB Source OLTP Mirror OLTP Staging Area ODS Operational Data Store Data Warehouse Data Marts OLAP Cubes Custom Reports Client Tools Other Systems Excel, Proclarity, … BI 03 - SQLBI Methodology Customers
Who holds the truth? OLTP Mirror • Holds the current truth about data • Holds the various versions in time of the current truth from OLTP Data warehouse • Organizes data from the storage and contains the complete truth Data Marts • Reads data from the Data Warehouse and contain the departmental truth OLAP BI 03 - SQLBI Methodology • Shows data to the customer, contains no new truth
Some details of Sql. BI 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? BI 03 - SQLBI Methodology
Why the OLTP Mirror is flat? Relationships Bad Data Safety BI 03 - SQLBI Methodology • No need to enforce relationships in the mirror • Bad data saved “as is” • No transformations No Errors! • ETL will be done later, in order to preserve the integrity of our “last parachute”
SQLBI Main Features Incremental DWH Different Data Marts BI 03 - SQLBI Methodology • The OLTP Mirror guarantees incremental building of the DWH • It does not add a great complexity to the overall structure • The DWH makes it easier to build different data marts • Each data mart shows data from the DWH and contains only one truth • Completely independent from each other • Handle future variations gracefully
Example of SQLBI Methodology Modeling DATA FLOW OF “PRODUCTS”
Products in the OLTP Product model description is stored in a separate table Finished goods flags is stored as a simple bit, no description is provided BI 03 - SQLBI Methodology List Price History and Product Cost are kept in separate tables Several technical fields are required for bookkeeping
Products in the Data Warehouse A single product history table holds all the variations We can track different historical attributes, not stored in the OLTP Finished goods flags is linked to a table that provides a description BI 03 - SQLBI Methodology Model name has been de-normalized
Products in the Data. Mart Product is a slowly changing dimension, the attributes are deduced from the historical and current tables All attributes are denormalized, as required 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. BI 03 - SQLBI Methodology
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 BI 03 - SQLBI Methodology
From Data Warehouse To Data Mart Data Warehouse From DWH to Data Mart BI 03 - SQLBI Methodology Data Mart
Divide (et impera) ETL steps Extract, Transform and Load: it is a very complex process that needs to be correctly formalized and understood OLTP Mirror DWH DATA MART CUBES • COPY • SELECT • RENAME • INTEGRATION • CLEANSING • STANDARDIZATIO N • SELECTION • FILTERS • JOINS • AGGREGATION • PRESENTATION • USER INTERFACE BI 03 - SQLBI Methodology
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 OLTP Mirror DWH DATA MART CUBES • COPY • SELECT • RENAME • INTEGRATION • CLEANSING • STANDARDIZATIO N • SELECTION • FILTERS • JOINS • AGGREGATION • PRESENTATION • USER INTERFACE Views BI 03 - SQLBI Methodology Views
VIEWS: 1° class citizens in DWH Easy Structure Optimizations BI 03 - SQLBI Methodology • Views can be modified by anyone, even out of BIDS • Views can provide default values when needed • Simple computation may be carried out by views • Renaming fields leads to better understanding of the flow • Present a star schema, even if the underlying structure is much more complex • Views can be analyzed by third party tools to get dependency tracking • Views can be optimized without ever opening BIDS
SSIS and Views Documentation • Simpler code inside SSIS packages • No need to open the package to understand what it is reading Debugging • Easily query the database for debugging purposes Optimizations • Query optimizations can be carried out separately BI 03 - SQLBI Methodology
Usage of views to simplify SSIS packages THE SSIS ETL CODE
SSAS and Views Documentation Optimizations Structure BI 03 - SQLBI Methodology • Renaming database columns to SSAS attributes • Clearly exposing all the transformation to DBA • Simplifying handling of fast variations • Full control on JOINs sent to SQL Server • Exposing a star schema, even if the underlying structure is not a simple star schema
Usage of views to replace DSV VIEWS IN THE SSAS DATA SOURCE VIEW
SQLBI Methodology Modeling • Mix between Inmon and Kimball • The users browse a Kimball structure • Internally, the DWH is an Inmon database Durability • Built to last for a long time • Building the blocks leaves space for updates, even if they are not used at first Optimization • Optimized for the SQL Server and Analysis Services features • The DWH is built incrementally • Better time to market BI 03 - SQLBI Methodology
Links • Download the SQLBI Methodology paper from www. sqlbi. com/sqlbimethodology. aspx • Write us for any feedback and/or question marco. russo@sqlbi. com alberto. ferrari@sqlbi. com
Complete the Evaluation Form & Win! • 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 Sponsored by Dell
Thank you for attending this session and the 2009 PASS Summit in Seattle
- Slides: 39