Data Platform and Data Science

Data Platform History of Data Warehousing Not a Data Warehouse, but a Data Platform Enterprise Data Platform (EDP) Customer Data Platform (CDP) 3 Things We Need on Data Platform Components of Data Platform Data Mesh – JP Morgan Chase and Fannie Mae Data Product Cloud Data Warehouse Good Bye Data Centre – It must be in the Cloud Do We Still Need a Data Warehouse? Modern Data Warehouse Why Data Lake What is a Data Lake? What is a Data Lake, physically? Data Lake vs Data Warehouse Data Warehouse on Data Lake Data Lake Architecture Data Lake on Azure: Databricks or Synapse? Data Lake on Oracle Data Lake on Snowflake Data Lake for Asset Management Data Lakehouse Data Lakehouse vs Data Lake+Warehouse Fact & dim tables in Data Lakehouse Building Dimensional Model on Data Lakehouse Data Fabric Microsoft Fabric as a Data Fabric Platform Microsoft Fabric as a Data Warehouse Platform Microsoft Fabric for Machine Learning Microsoft Fabric vs Databricks Microsoft Fabric: AI-powered transforms & new Dataflow Data Sharing Share it, don’t move it Azure Big Data Analytics What about Hadoop? It’s Data Engineer, not ETL Developer Data Engineering Data Engineer Snowflake SQL dbt Definition of Big Data and Data Warehousing Choosing between Big Data and Data Warehousing Difference between Extract and Ingest Productionalisation What’s in Azure? Column Name in Google BigQuery Three Modes of Synapse Analytics ETL vs ELT (and Ingestion) Using Informatica to load into Snowflake Data Migration vs Data Warehousing Four Biggest Lessons Learned in 2023 Data tables, but not SQL Buy don’t build Snowflake for AI: Prediction for a bank Matillion: SaaS ETL Tool Data Vault Data Warehousing 12 Reasons for Using Dimensional Model Data Warehouse for Asset Management Why do we need a data warehouse? Data Warehousing Interview with Astera Data Warehouse Data Modelling Infrastructure in Data Warehousing Star Schema or Snowflake Using a Data Warehouse for CRM NoSQL in Data Warehousing Using Temporal Tables in Data Warehousing Loading Late Arriving Dimension Rows Late Data Warehouse Loading Foreign Keys in Fact Tables 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 A Data Warehouse with no Surrogate Keys System Interfaces Contract Month Attributes in the Date Dimension Delete Takes A Long Time Fact or Dimension Not All Surrogate Keys Define the Fact Table Grain Hadoop in Data Warehousing Data Warehouse vs Data Virtualisation Delete All Rows in Dimension Table The Main Weakness of Snowflake Schema Initial Data Load Why Do We Need a Data Warehouse? Seven Methods of Data Integration Building a Data Warehouse and BI on Low Budget Dedup Banding and Grouping in Data Warehousing Connecting Fact Tables Real Time Data Warehouse Data Extraction Method Populating Fact Tables Estimating the Size of Dimension and Fact Tables Indexing Fact Tables in SQL Server Flip Flopping in Dimension Tables Indexing Fact Tables Measure or Attribute DimMonth, DimQuarter and DimYear The ABC of Data Warehousing Accumulative Snapshot Fact Table Six Dimension Types Dimodelo Effektor Instrument Dimension Investment Performance Data Sourcing Watermark in Data Warehousing Snowflaking on Security Dimension Position Fact Table – IBOR vs ABOR Distinct-Attribute Rows in Dimension Tables 3 Things in Date Dimension EAV Fact Tables Different Measures for Different Products Data Interface (How to Manage a DW Project) Surrogate Key in Snowflake, Databricks and DbSchema Metadata Driven ETL Using Production Data in Dev & Test Environment Data Architecture What is Data Architecture? Elements of Data Architecture Code Decode Table Responsibilities of Data Architects Data Architect jobs in UK Varchar(255) Physical Data Modelling (PDM) Painful-to-Retrieve Data Structures Data Types for Common Columns The Problem with Data Quality Hierarchy with Multiple Parents Relational vs Non Relational Databases Data Type Data Files – Delimiter and Qualifier Why they are called FLAT files DbSchema Architecture Review Board 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 Project Management Team Setup for BI Project Don’t Worry About the Sign Off Development Operations (DevOps) Building Prod & Dev Environment

Data Science Learning AI – Where to start? Learning Machine Learning with Upgrad Andrew Ng’s Machine Learning Course What Machine Learning Can Be Used For Talking Avatars Salesforce Einstein Can Machine Learning replace BI? Machine Learning for Asset Management Machine Learning or Data Science? What is Data Science? Interview Questions for Data Scientists Data Scientist BI vs Data Science Why do we use Python in Machine Learning? Which Machine Learning Algorithm Should I Use? How to do AI without Machine Learning? Automating Machine Learning using Azure ML MLOps (Machine Learning Operations) Logistic Regression with PCA in Python Linear Regression in Python Why Linear Regression is so hard Feature Importance Handling Class Imbalance MCC Formula for Multiclass Classification Google Colab Ensembles – Odd and Even Entropy and Information Gain in Decision Tree SVM with RBF Kernel Tuning XGBoost Models The Trick in Understanding Human Language Tokeniser Natural Language Processing (NLP) What is Convolution? What is Convolutional Neural Network (CNN)? What is CNN? (Part 2) Recurrent Neural Network (RNN) and LSTM RNN Applications Reinforcement Learning Building a Neural Network Andrew Ng’s Deep Learning Course Forecasting time series: using statistics vs machine learning Stock Price Forecasting using LSTM Stock Price Forecasting Using CNN Stock Price Forecasting using XGBoost Managing Investment Portfolios using Machine Learning Using Reinforcement Learning to Manage Portfolio Allocation Python 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 Business Knowledge Asset Management vs Wealth Management Buy Side and Sell Side 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 ABOR and IBOR Business Intelligence It’s not BI any more, it’s Analytics! BI Developer or Analytics Engineer? 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 Power BI Power BI Datamart Power BI Q&A Dynamic Difference DAX Studio and DAX Guide DAX: Percentage of Total and Filter on 2 Columns A few DAX functions 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 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 SSIS: Loading a Big File Fast SSIS: How to Convert DateTime to Int SSIS: How to Populate a Fact Table using SSIS (part 1) SSIS: How to Populate a Fact Table using SSIS (part 2) SSIS: SCD Wizard Performance Issue SSIS: Automating DDL Changes SSIS 2012: Converting Date from Excel for Lookup Loading a Dimension Table using SSIS Six Important Features of an ETL Tool Choosing an ETL Tool SSIS: Numeric Column Loaded as NULL Memory for SSIS Download files from Azure Blob Storage using Control-M  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 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 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 Miscellaneous Career in Data Contracting