Data Warehousing, BI and Data Science

Data Warehousing One or Two Dimensions Using a Data Warehouse for CRM A dimension with only one attribute Data Warehouse Data Modelling (SQLBits 4 Persentation) Storing Percentage Measure in the Fact Table Primary Key in Accumulating Snapshot Fact Tables Join Brings More Rows Normalised Data Warehouse (1) Normalised Data Warehouse (2) Disk Space in Data Warehousing Why a 400 GB Data Warehouse Requires 20 TB of Disks Reasons for Creating a Data Mart from a Data Warehouse Creating a Data Mart from a Data Warehouse: Four Questions Data type of Is_Current Flag column Transaction Dimension Data Warehousing and Business Intelligence SQL 2008 Data Warehousing Features SQL 2008 DW Features (Presentation at Microsoft) Parallel Data Warehouse (what a clever name) Change Tracking in SQL Server Data Warehousing Change Data Capture in SQL Server Data Warehousing Merge Statement in SQL Server Data Warehousing Bitmap Filter (Star Join Query Optimisation) Primary Key and Clustered Index on the Fact Table Impact of Clustering the Fact Key to Query Performance Standardising Entity and Column Names General Performance Considerations for “INSERT SELECT” Preserving the History of the Facts Data Warehousing Interview Questions (2) Data Warehousing Books Normalising a Fact Table Denormalising a Fact Table Mixed Grain Fact Table Non Aggregatable Measure A Measure or An Attribute Putting Higher Grain Attributes into a Separate Dimension Time Dimension The Primary Key of a Fact Table with the Grain the same as a Dimension When To Snowflake Dimensions with Multi Value Attributes The Word “Fact” – Terminology Clarification Measures on a Dimension Tables How To Deal With Status Linking 2 Fact Tables Data Warehousing Books Data Architect, Data Warehouse Architect and BI Solution Architect Storing History on 3rd Normal Form (3NF) Building a Data Warehouse at the Same Time as the Operational System Stored Procedure vs ETL Tool ETL and Data Integration IIS in Data Warehousing Where To Put an Attribute (DW) Reviewing a Dimensional Model Who’s Who in Data Warehousing Differences between Data Warehousing and BI Role of a DW Designer/Dimensional Modeller in the ETL Development Phase of a DW/BI Project Data Sourcing Slowly Changing Dimension (SCD) Type 0 Bridge Table with Multiple Instances Bridge Table with Date Populating Unknown Measure with a Zero or NULL Expiry Date column in SCD Type 2 Dimension Using Accumulated Snapshot Fact Table to Monitor Status SCD Type 2 – Initialising a New Attribute A Data Warehouse Must Always Reflect the Values in the Source System UNK in Data Warehousing Top 2 things I missed in SQL Server and Oracle Two Methods of Reporting Wherescape RED Testing Your Data Warehouse The Principle of “Do It Upstream” in Data Warehousing Updating Past Data 28/1/12 When is a number not a measure? 28/1/12 Parent SK in the Fact Table 8/2/12 City and Rank 15/2/12 Effective and Expiry Dates in Type 2 Dimensions 15/2/12 Adding a New Attribute and Measure 22/2/12 Star Schema or Snowflake 13/3/12 Name of Surrogate Key Columns 14/3/12 Data Type Changes in the Source System 10/4/12 A Data Warehouse with no Surrogate Keys 9/5/12 System Interfaces Contract 19/5/12 Month Attributes in the Date Dimension 28/5/12 Delete Takes A Long Time 22/6/12 Fact or Dimension 28/6/12 Not All Surrogate Keys Define the Fact Table Grain 30/6/12 Hadoop in Data Warehousing 3/7/2012 Data Warehouse vs Data Virtualisation 8/7/12 Delete All Rows in Dimension Table 12/7/12 The Main Weakness of Snowflake Schema 15/7/12 Initial Data Load 2/8/12 Why Do We Need a Data Warehouse? 3/12/12 Seven Methods of Data Integration 31/5/12 Building a Data Warehouse and BI on Low Budget 15/2/14 Dedup 15/2/14 Banding and Grouping in Data Warehousing 20/5/14 Connecting Fact Tables 19/6/14 Real Time Data Warehouse 14/20/14 Data Extraction Method 29/3/06 Populating Fact Tables 6/11/06 Estimating the Size of Dimension and Fact Tables 13/2/15 Indexing Fact Tables in SQL Server 5/6/15 Flip Flopping in Dimension Tables 21/8/15 Indexing Fact Tables 11/11/15 Measure or Attribute 18/12/15 DimMonth, DimQuarter and DimYear 29/12/15 The ABC of Data Warehousing 19/1/16 Accumulative Snapshot Fact Table 3/2/16 Six Dimension Types 11/2/16 Dimodelo 13/2/16 Effektor 15/2/16 Instrument Dimension 24/2/16 Investment Performance 26/2/16 Data Sourcing 1/4/16 Why do we need a data warehouse? (again) 21/4/16 Rating Dimension 18/9/16 Numerical Attributes 13/11/17 Loading Late Arriving Dimension Rows 13/12/17 Late Data Warehouse Loading 20/12/17 Big Volume Data Warehouse 11/05/18 Modern Data Warehouse 6/9/18 Data Warehouse for Asset Management 25/9/18 Junk Dimension Revisited 5/10/18 Transactional Fact Table 20/2/19 Day Measures 8/11/20 One or Two Fact Tables 21/02/21 SQL Server Find Which Partitioning Function Is Used by a Table Select count(*) For a Very Big Table SQL Server Editions Copy a Table Informatica Metadata Manager Can Now Read SSAS Cubes and SSRS Reports Metadata How Long Did It Run? Class Does Not Support Aggregation SQL Server 2008 Backup Compression The Database Does Not Have a Valid Owner Using Pivot for 2 Value Columns Q&A on: Resizing TempDB Editions and Prices of Visual Studio 2010 Creating Test Data on SQL Server 2008 Updating a Table Based on Itself Collation Conflict When Querying a Linked Server Inserting Multiple Rows in One SQL Statement T-SQL: Exists versus In T-SQL: Exists versus In – Performance Comparison Null in Date Column LINQ2SQL and TSQL and CLR 20/5/11 Concat NULL Yields NULL 20/5/11 Make yourself sysadmin on SQL 2008 Express 30/8/12 Formatting Dates in SQL Server 2012 26/12/12 Data Consistency in Oracle and SQL Server 29/11/13 SQL Server 2014 Installation 23/11/14 SQL Server Scripts Library 27/12/14 Temporal Tables in SQL Server 2016 14/1/16 Alt Key in SSMS 10/2/16 U-SQL 7/5/16 About NOLOCK 18/6/16 Column Store Index 31/1/19 Oracle BI Comparing Oracle 11g OLAP Option and SSAS 25/12/10 Introduction to Oracle BI for MS BI Developer 7/1/11 Oracle ETL Tools 8/1/11 Bit and Pieces on Oracle 23/12/12 Difference Between CURRENT_DATE and SYSDATE 2/1/13 Business Knowledge Layering in Insurance Bank Data Model 23/4/12 Credit Default Swap (CDS) 15/6/12 Distribution Yield vs Underlying Yield 17/6/12 Treasury in Investment Banking 15/3/13 Off Balance Sheet Items 16/6/13 Securitising Cash Positions 6/7/15 Investment Banking Books for BAs and Developers 19/9/15 Investment Banking 23/9/15 Credit Risk and Market Risk 27/9/15 Asset Management Business Processes and Systems 3/5/16 Domain Knowledge 18/6/16 Historical Portfolio Positioning 4/7/16 Data Warehousing / Business Intelligence for Investment Banking 3/1/17 Swaps and Options 21/10/17 Performance Attribution 9/12/18 Asset Management Companies in the UK 5/1/19

Data Science Andrew Ng’s Machine Learning Course 25/10/17 What is Data Science? 28/10/2017 What’s in Azure? 28/11/2017 What Machine Learning Can Be Used For 27/12/2017 Andrew Ng’s Deep Learning Course 5/1/2018 Building a Neural Network 7/2/2018 BI vs Data Science 12/4/2018 Can Machine Learning replace BI? 27/6/2018 Entropy and Information Gain in Decision Tree 30/5/2019 Python by example Turtle 10/9/20 Python: String and Array 27/11/20 Python: List, Tuple, Dictionary, Set 27/11/20 Python: If and For 1/12/20 Python: List comprehension 1/12/20 Python: Function, Lambda, Map, Filter, Reduce,  2/12/20 Data Architecture Code Decode Table 23/4/15 Responsibilities of Data Architects 31/5/15 Varchar(255) 21/6/15 Physical Data Modelling (PDM) 12/7/15 Painful-to-Retrieve Data Structures 17/7/15 Distinct-Attribute Rows in Dimension Tables 22/8/15 3 Things in Date Dimension 28/8/15 EAV Fact Tables 12/9/15 Data Types for Common Columns 16/9/15 Data Interface (How to Manage a DW Project) 15/2/16 The Problem with Data Quality 17/2/16 Different Measures for Different Products 16/3/16 Data Lake vs Data Warehouse 20/6/16 Definition of Big Data and Data Warehousing 17/4/17 Choosing between Big Data and Data Warehousing 24/4/17 Hierarchy with Multiple Parents 18/10/17 Relational vs Non Relational Databases 12/11/17 Data Type 15/2/19 Data Files – Delimiter and Qualifier 21/2/19 Business Intelligence Reporting and Analytics Current Trend in Business Intelligence Where To Store The Business Logic Microsoft BI Books Data Warehousing & BI Companies How to Choose the Right BI Technology to Suit Your Style 17/8/11 SAP Hana, an in-Memory Database 8/7/12 Business Objects Voyager (SAP BO Analysis) 10/7/12 What is Big Data, Data Warehouse, Data Mining 14/7/12 Tibco Spotfire 2/12/12 Composite 17/12/12 The 5 Stages of DWBI Journey 8/11/13 iDashboard 16/7/14 Using BI Tools as ETL Tools 1/12/14 Data Scientist 7/2/15 Power BI Q&A 15/7/18 A few DAX functions 4/11/18 DAX Studio and DAX Guide 4/11/18 DAX: Percentage of Total and Filter on 2 Columns 4/11/18 Analysis Services Parent Child Dimension Top 10 Tips: Building Cubes Top 10 Tips: Cube Testing The Most Useful SSAS Book Who’s Who in Analysis Services Building Cubes From Operational System (SQLBits presentation) The Unpopular SELECT Statement Date Dimension in Analysis Services (Part 1) Date Dimension in Analysis Services (Part 2) Many-to-Many in Attribute Relationship SSAS DMV (Dynamic Management View) SSAS DMV: Join Using DataSet SSAS DMV Nugget at SQL Server User Group Optimising Cube Query Performance and Processing Performance Creating Many Roles in SSAS Cubes Vertical Fact Table Creating a Dimension with Multiple Column Key Scheduled Deploy in SSAS AMO: Enumerating DSV Tables and Columns The 4.2 billion tuples limitation in SSAS Double Click Property Name Where To Put An Attribute Attempted to read or write protected memory Combining DW and ERP Data in SSAS Cubes Combining DW and ERP Data in SSAS Cubes – 5th Case Creating a dimension from a large table Concatenating attributes to form dimension key What is a cube? Cube developer What are cubes bad at? Using SSAS cubes for reconciliation Who Are Using The Cube Now? Who Uses What Cube and When Taking a cube offline Comparing Excel 2007 and ProClarity Qlikview vs PowerPivot: connecting to SSAS cube PowerPivot Multi Language Cubes Cube Translation in Excel 2007 Q&A on: Browsable Folder When Restoring Cubes Renaming Attribute & Dimension Aggregate Not Shown Ratio in SSAS Updating Partition Source SQL Parser: The query contains  … parameter, which is not declared Tuning Cube Processing Performance SSAS Developer Interview Questions Q&A on: Impersonation Parent Child Dimension: extra child node Ragged Hierarchy in SSAS Where To Put an Attribute (SSAS) Many to Many Is Not Always Right 26/5/11 Many to Many: Which Dimension is Used? 28/5/11 Processing ROLAP Cube and ROLAP Dimension 28/6/11 SSAS Videos 6/10/11 Duplicate Attribute Keys in SSAS 9/7/12 The Trio Maestro’s SSAS 2012 Tabular Book 17/8/12 Sherry Li and Tomislav’s 2012 MDX Cook Book 13/9/13 Processing SSAS Cubes using PowerShell 27/10/13 Can I Cluster SSAS? (Minimizing Downtime at Deployment) 27/10/13 5 Minutes SSAS DBA 31/5/14 SSAS Stops Logging 16/6/14 Process Update of a Dimension 15/7/14 Analysis Services Tabular Model 10/5/19 MDX Sorting 21/6/10 Filter 21/6/10 Existing 25/6/10 Except 27/7/10 Filter on Dates Before Today 25/10/10 Tomislav’s MDX Book 1/9/11 Half Year To Date 19/12/11 Setting Default Date to Last Available Date 10/08/14 Holding History – MDX for “From Date” and “To Date” 18/6/15 Power BI Dynamic Difference SSIS SSIS: Looping with ADO Enumerator SSIS: Debugging a Script Component SSIS: Importing Files – Read the First N Rows SSIS: Work Flow vs Stored Procedures SSIS: SQL Server Destination or OLE DB SSIS: Importing a File with Dynamic Columns Coming SSIS Articles SSIS: Export a Query Result to a File SSIS 2008 Data Profiler SSIS: Updating a Variable based on Database SSIS: Updating a Variable based on a File (Script Component) SSIS: Updating a Variable based on a File (Script Task) SSIS: Import/Export Files with Variable File Name Set at Run Time SSIS: True/False and 1/0 on Bit Columns 16/11/11 SSIS: Loading a Big File Fast 20/3/12 SSIS: How to Convert DateTime to Int 26/5/12 SSIS: How to Populate a Fact Table using SSIS (part 1) 26/5/12 SSIS: How to Populate a Fact Table using SSIS (part 2) 26/5/12 SSIS: SCD Wizard Performance Issue 19/6/12 SSIS: Automating DDL Changes 3/10/12 SSIS 2012: Converting Date from Excel for Lookup 28/12/12 Loading a Dimension Table using SSIS 9/9/15 Six Important Features of an ETL Tool 10/10/15 Choosing an ETL Tool 12/2/16 SSIS: Numeric Column Loaded as NULL 21/1/19 Memory for SSIS 3/8/19 Events SQLBits 4 Manchester (Data Warehouse Data Modelling) SQLBits 5 Wales (Building Cubes from Operational Systems) SQLBits 7 York 4 Sessions for SQLBits 8 Brighton SQLBits 8 Brighton (Advanced Dimensional Modelling) SQLBits 9 Liverpool SQLBits 10 London 27/12/11 SQL Server User Group Indonesia 24/6/14 SQLBits 12 Telford 29/6/14 SQLBits 15 Liverpool 4-7th May 2016 18/12/15 Project Management Team Setup for BI Project Don’t Worry About the Sign Off Blogging Why Write a Blog? Blogging Using Microsoft Word Blogging Using Windows Live Writer Back to Microsoft Word 4/6/11 Principles of Explaining Things 26/5/12 Starting a Post 27/1/19 Copy Paste Anchor Links from Microsoft Word to WordPress 11/9/20 Miscellaneous Development Operations (DevOps) 26/9/19