Data Warehousing and Machine Learning

Data Warehousing Modern Data Warehouse Data Warehouse for Asset Management Data Warehouse Data Modelling Star Schema or Snowflake Using a Data Warehouse for CRM Why do we need a data warehouse? Data Warehousing Interview with Astera Loading Late Arriving Dimension Rows Late Data Warehouse Loading Big Volume Data Warehouse Junk Dimension Revisited Transactional Fact Table Rating Dimension Numerical Attributes Day Measures One or Two Fact Tables One or Two Dimensions A dimension with only one attribute 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 When is a number not a measure? Parent SK in the Fact Table City and Rank Effective and Expiry Dates in Type 2 Dimensions Adding a New Attribute and Measure Name of Surrogate Key Columns 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 Data Architecture Code Decode Table Responsibilities of Data Architects Varchar(255) Physical Data Modelling (PDM) Painful-to-Retrieve Data Structures Distinct-Attribute Rows in Dimension Tables 3 Things in Date Dimension EAV Fact Tables Data Types for Common Columns Data Interface (How to Manage a DW Project) The Problem with Data Quality Different Measures for Different Products Data Lake vs Data Warehouse Definition of Big Data and Data Warehousing Choosing between Big Data and Data Warehousing Hierarchy with Multiple Parents Relational vs Non Relational Databases Data Type Data Files – Delimiter and Qualifier 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 Concat NULL Yields NULL Make yourself sysadmin on SQL 2008 Express Formatting Dates in SQL Server 2012 Data Consistency in Oracle and SQL Server SQL Server 2014 Installation SQL Server Scripts Library Temporal Tables in SQL Server 2016 Alt Key in SSMS U-SQL About NOLOCK Column Store Index What’s in Azure? Oracle BI Comparing Oracle 11g OLAP Option and SSAS Introduction to Oracle BI for MS BI Developer Oracle ETL Tools Bit and Pieces on Oracle Difference Between CURRENT_DATE and SYSDATE 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 SAP Hana, an in-Memory Database Business Objects Voyager (SAP BO Analysis) What is Big Data, Data Warehouse, Data Mining Tibco Spotfire Composite The 5 Stages of DWBI Journey iDashboard Using BI Tools as ETL Tools

Machine Learning Which Machine Learning Algorithm Should I Use? The Trick in Understanding Human Language Why Linear Regression is so hard Ensembles – Odd and Even SVM with RBF Kernel Tokeniser Logistic Regression with PCA in Python Linear Regression in Python Handling Class Imbalance Natural Language Processing (NLP) Entropy and Information Gain in Decision Tree Can Machine Learning replace BI? Building a Neural Network Andrew Ng’s Machine Learning Course Andrew Ng’s Deep Learning Course What Machine Learning Can Be Used For What is Data Science? Data Scientist BI vs Data Science Python by example Turtle Python: String and Array Python: List, Tuple, Dictionary, Set Python: If and For Python: List comprehension Python: Function, Lambda, Map, Filter, Reduce Pandas: Column, data type, group Plot: Boxplot, barplot, pairplot, heatmap 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 Many to Many: Which Dimension is Used? Processing ROLAP Cube and ROLAP Dimension SSAS Videos Duplicate Attribute Keys in SSAS The Trio Maestro’s SSAS 2012 Tabular Book Processing SSAS Cubes using PowerShell Can I Cluster SSAS? (Minimizing Downtime at Deployment) 5 Minutes SSAS DBA SSAS Stops Logging Process Update of a Dimension Analysis Services Tabular Model MDX Sorting Filter Existing Except Filter on Dates Before Today Tomislav’s MDX Book Half Year To Date Setting Default Date to Last Available Date Holding History – MDX for “From Date” and “To Date” Sherry Li and Tomislav’s 2012 MDX Cook Book Power BI Power BI Q&A Dynamic Difference DAX Studio and DAX Guide DAX: Percentage of Total and Filter on 2 Columns A few DAX functions 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 Business Knowledge Layering in Insurance Bank Data Model Credit Default Swap (CDS) Distribution Yield vs Underlying Yield Treasury in Investment Banking Off Balance Sheet Items Securitising Cash Positions Investment Banking Books for BAs and Developers Investment Banking Credit Risk and Market Risk Asset Management Business Processes and Systems Domain Knowledge Historical Portfolio Positioning Data Warehousing / Business Intelligence for Investment Banking Swaps and Options Performance Attribution Asset Management Companies in the UK 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 SQL Server User Group Indonesia SQLBits 12 Telford SQLBits 15 Liverpool 4-7th May 2016 Project Management Team Setup for BI Project Don’t Worry About the Sign Off Development Operations (DevOps) Blogging Why Write a Blog? Blogging Using Microsoft Word Blogging Using Windows Live Writer Back to Microsoft Word Principles of Explaining Things Starting a Post Copy Paste Anchor Links from Microsoft Word to WordPress