James Serra Data WarehouseBIMDM Architect James Serra 3gmail

James Serra – Data Warehouse/BI/MDM Architect James. Serra 3@gmail. com http: //James. Serra. com/

Agenda Ø Ø Ø Why use a data warehouse? Fast Track Data Warehouse (FTDW) Appliances Data Warehouse vs Data Mart Kimball vs Inmon (Normalized vs Dimensional) Populating a Data Warehouse ETL vs ELT Normalizing and Surrogate Keys SSAS Cubes SQL Server 2012 Tabular Model End-User Microsoft BI Tools

Legacy applications + data marts = chaos Production Control MRP Inventory Control Parts Management Logistics Shipping Finance Marketing Sales Accounting Management Reporting Engineering Raw Goods Actuarial Order Control Purchasing Human Resources Enterprise data warehouse = order Continuity Consolidation Control Compliance Collaboration Single version of the truth Enterprise Data Warehouse Every question = decision

Software: • SQL Server 2008 R 2 Enterprise • Windows Server 2008 Configuration guidelines: • Physical table structures • Indexes • Compression • SQL Server settings • Windows Server settings • Loading Hardware: • Tight specifications for servers, storage and networking • ‘Per core’ building block

Data Warehouse vs Data Mart • − − • −

Kimball vs Inmon Ø Normalized (Inmon) vs Dimensional (Kimball) Ø Normalized: Ø Normalization rules Ø Many tables using joins Ø Dimensional: Ø Facts and dimensions Ø Less tables having duplicate data (de-normalized) Ø Easier for user to understand

Kimball vs Inmon Ø Top-Down (Inmon) vs Bottom-Up (Kimball) Ø Bottom-Up: Ø Ø Data marts Logical data warehouse Decentralized Quick results, iterative approach Ø Top-Down: Ø Ø Enterprise data model Centralized Later create data marts More upfront work but less redo

Populating a Data Warehouse Ø Ø Frequency of data pull Full Extraction – All data Incremental Extraction – Only data changed from last run Determine data that has changed Ø Ø Ø Timestamp - Last Updated CDC Partitioning Triggers MERGE Ø Online Extraction – Data from source Ø Replication Ø Database Snapshot Ø Availability Groups Ø Offline Extraction – Data from flat file

ETL vs ELT Ø Ø Ø Uses staging tables Ø Processing done by target database engine (SSIS: Execute T-SQL Statement task instead of Data Flow Transform tasks) Ø Use for big volumes of data Ø Use when source and target databases are the same Ø Use with PDW ELT is better since database engine is more efficient than SSIS Database engine: Transformations SSIS: Data pipeline and workflow management

Normalizing and Surrogate Keys Ø Normalize to eliminate redundant data and setup table relationships Ø Surrogate Keys – Unique identifier not derived from source system Ø Ø Ø

SSAS Cubes Reasons to use instead of data warehouse: Ø Aggregating (Summarizing) the data for performance Ø Multidimensional analysis – slice, drilldown Ø Hierarchies Ø Advanced time-calculations – i. e. 12 -month rolling average Ø Easily use Excel to view data Ø Slowly Changing Dimensions (SCD)

Data Warehouse Architecture

SQL Server 2012 Tabular Model Ø Ø Ø New x. Velocity in-memory database in SSAS Build model in Power Pivot or SSDT Uses existing relational model No star schema, no extra SSIS Uses DAX Faster and easier to use than multidimensional model

End-User Microsoft BI Tools Ø Ø Ø Excel Pivot. Tables SQL Server Reporting Services (SSRS) Report Builder Power. Pivot Performance. Point Services (PPS) Power View

• • • • http: //bit. ly/Srz. X 1 x http: //bit. ly/Sr. Ai 4 p http: //bit. ly/Sr. AEYw http: //bit. ly/Srz. NHy http: //bit. ly/Sr. Awsj http: //bit. ly/Sr. AIrp http: //bit. ly/Sr. AHnc http: //bit. ly/Sr. AKQa http: //bit. ly/x. AZy 9 h http: //bit. ly/y 7 b. XY 5 http: //bit. ly/Sr. ARu. Z http: //bit. ly/YQIXz. M http: //bit. ly/Sr. BMLT http: //bit. ly/Sr. AZug