Data Warehousing and Data Science

Data Warehousing Data Warehouse on Data Lake Do We Still Need a Data Warehouse? Modern Data Warehouse Why Data Lake What is a Data Lake? Data Lakehouse Data Lake Architecture Data Lake in Azure: Databricks or Synapse? Data Lake on Oracle Data Lake for Asset Management Data Warehouse for Asset Management Data Warehouse Data Modelling NoSQL in Data Warehousing Difference between Extract and Ingest Share it, don’t move it Infrastructure in Data Warehousing Star Schema or Snowflake Using a Data Warehouse for CRM Why do we need a data warehouse? Data Warehousing Interview with Astera Azure Big Data Analytics 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 Data Architecture What is Data Architecture? Elements of 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? 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 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

Data Science Machine Learning or Data Science? How to do AI without Machine Learning? Forecasting time series: using statistics vs machine learning Managing Investment Portfolios using Machine Learning Using Reinforcement Learning to Manage Portfolio Allocation Forecasting Stock Prices using LSTM Using CNN for Stock Prediction Stock Price Forecasting using XGBoost Tuning XGBoost Models MCC Formula for Multiclass Classification Logistic Regression with PCA in Python Linear Regression in Python Handling Class Imbalance 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 Ensembles – Odd and Even SVM with RBF Kernel Which Machine Learning Algorithm Should I Use? Interview Questions for Data Scientists Why do we use Python in Machine Learning? The Trick in Understanding Human Language Feature Importance Automating Machine Learning using Azure ML Why Linear Regression is so hard Google Colab Learning Machine Learning with Upgrad Tokeniser 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 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 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  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) Building Prod & Dev Environment 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