Data Warehousing and Data Science

Content of This Blog

Data Warehousing

  1. Data Warehouse on Data Lake
  2. Do We Still Need a Data Warehouse?
  3. Modern Data Warehouse
  4. Why Data Lake
  5. What is a Data Lake?
  6. Data Lakehouse
  7. Data Lake Architecture
  8. Data Lake in Azure: Databricks or Synapse?
  9. Data Lake on Oracle
  10. Data Lake for Asset Management
  11. Data Warehouse for Asset Management
  12. Data Warehouse Data Modelling
  13. NoSQL in Data Warehousing
  14. Difference between Extract and Ingest
  15. Share it, don’t move it
  16. Infrastructure in Data Warehousing
  17. Star Schema or Snowflake
  18. Using a Data Warehouse for CRM
  19. Why do we need a data warehouse?
  20. Data Warehousing Interview with Astera
  21. Azure Big Data Analytics
  22. Using Temporal Tables in Data Warehousing
  23. Loading Late Arriving Dimension Rows
  24. Late Data Warehouse Loading
  25. Foreign Keys in Fact Tables
  26. Big Volume Data Warehouse
  27. Junk Dimension Revisited
  28. Transactional Fact Table
  29. Rating Dimension
  30. Numerical Attributes
  31. Day Measures
  32. One or Two Fact Tables
  33. One or Two Dimensions
  34. A dimension with only one attribute
  35. Storing Percentage Measure in the Fact Table
  36. Primary Key in Accumulating Snapshot Fact Tables
  37. Join Brings More Rows
  38. Normalised Data Warehouse (1)
  39. Normalised Data Warehouse (2)
  40. Disk Space in Data Warehousing
  41. Why a 400 GB Data Warehouse Requires 20 TB of Disks
  42. Reasons for Creating a Data Mart from a Data Warehouse
  43. Creating a Data Mart from a Data Warehouse: Four Questions
  44. Data type of Is_Current Flag column
  45. Transaction Dimension
  46. Data Warehousing and Business Intelligence
  47. SQL 2008 Data Warehousing Features
  48. SQL 2008 DW Features (Presentation at Microsoft)
  49. Parallel Data Warehouse (what a clever name)
  50. Change Tracking in SQL Server Data Warehousing
  51. Change Data Capture in SQL Server Data Warehousing
  52. Merge Statement in SQL Server Data Warehousing
  53. Bitmap Filter (Star Join Query Optimisation)
  54. Primary Key and Clustered Index on the Fact Table
  55. Impact of Clustering the Fact Key to Query Performance
  56. Standardising Entity and Column Names
  57. General Performance Considerations for “INSERT SELECT”
  58. Preserving the History of the Facts
  59. Data Warehousing Interview Questions (2)
  60. Data Warehousing Books
  61. Normalising a Fact Table
  62. Denormalising a Fact Table
  63. Mixed Grain Fact Table
  64. Non Aggregatable Measure
  65. A Measure or An Attribute
  66. Putting Higher Grain Attributes into a Separate Dimension
  67. Time Dimension
  68. The Primary Key of a Fact Table with the Grain the same as a Dimension
  69. When To Snowflake
  70. Dimensions with Multi Value Attributes
  71. The Word “Fact” – Terminology Clarification
  72. Measures on a Dimension Tables
  73. How To Deal With Status
  74. Linking 2 Fact Tables
  75. Data Warehousing Books
  76. Data Architect, Data Warehouse Architect and BI Solution Architect
  77. Storing History on 3rd Normal Form (3NF)
  78. Building a Data Warehouse at the Same Time as the Operational System
  79. Stored Procedure vs ETL Tool
  80. ETL and Data Integration
  81. IIS in Data Warehousing
  82. Where To Put an Attribute (DW)
  83. Reviewing a Dimensional Model
  84. Who’s Who in Data Warehousing
  85. Differences between Data Warehousing and BI
  86. Role of a DW Designer/Dimensional Modeller in the ETL Development Phase of a DW/BI Project
  87. Data Sourcing
  88. Slowly Changing Dimension (SCD) Type 0
  89. Bridge Table with Multiple Instances
  90. Bridge Table with Date
  91. Populating Unknown Measure with a Zero or NULL
  92. Expiry Date column in SCD Type 2 Dimension
  93. Using Accumulated Snapshot Fact Table to Monitor Status
  94. SCD Type 2 – Initialising a New Attribute
  95. A Data Warehouse Must Always Reflect the Values in the Source System
  96. UNK in Data Warehousing
  97. Top 2 things I missed in SQL Server and Oracle
  98. Two Methods of Reporting
  99. Wherescape RED
  100. Testing Your Data Warehouse
  101. The Principle of “Do It Upstream” in Data Warehousing
  102. Updating Past Data
  103. When is a number not a measure?
  104. Parent SK in the Fact Table
  105. City and Rank
  106. Effective and Expiry Dates in Type 2 Dimensions
  107. Adding a New Attribute and Measure
  108. Name of Surrogate Key Columns
  109. Data Type Changes in the Source System
  110. A Data Warehouse with no Surrogate Keys
  111. System Interfaces Contract
  112. Month Attributes in the Date Dimension
  113. Delete Takes A Long Time
  114. Fact or Dimension
  115. Not All Surrogate Keys Define the Fact Table Grain
  116. Hadoop in Data Warehousing
  117. Data Warehouse vs Data Virtualisation
  118. Delete All Rows in Dimension Table
  119. The Main Weakness of Snowflake Schema
  120. Initial Data Load
  121. Why Do We Need a Data Warehouse?
  122. Seven Methods of Data Integration
  123. Building a Data Warehouse and BI on Low Budget
  124. Dedup
  125. Banding and Grouping in Data Warehousing
  126. Connecting Fact Tables
  127. Real Time Data Warehouse
  128. Data Extraction Method
  129. Populating Fact Tables
  130. Estimating the Size of Dimension and Fact Tables
  131. Indexing Fact Tables in SQL Server
  132. Flip Flopping in Dimension Tables
  133. Indexing Fact Tables
  134. Measure or Attribute
  135. DimMonth, DimQuarter and DimYear
  136. The ABC of Data Warehousing
  137. Accumulative Snapshot Fact Table
  138. Six Dimension Types
  139. Dimodelo
  140. Effektor
  141. Instrument Dimension
  142. Investment Performance
  143. Data Sourcing
  144. Watermark in Data Warehousing

Data Architecture

  1. What is Data Architecture?
  2. Elements of Data Architecture
  3. Code Decode Table
  4. Responsibilities of Data Architects
  5. Varchar(255)
  6. Physical Data Modelling (PDM)
  7. Painful-to-Retrieve Data Structures
  8. Distinct-Attribute Rows in Dimension Tables
  9. 3 Things in Date Dimension
  10. EAV Fact Tables
  11. Data Types for Common Columns
  12. Data Interface (How to Manage a DW Project)
  13. The Problem with Data Quality
  14. Different Measures for Different Products
  15. Data Lake vs Data Warehouse
  16. Definition of Big Data and Data Warehousing
  17. Choosing between Big Data and Data Warehousing
  18. Hierarchy with Multiple Parents
  19. Relational vs Non Relational Databases
  20. Data Type
  21. Data Files – Delimiter and Qualifier

SQL Server

  1. Find Which Partitioning Function Is Used by a Table
  2. Select count(*) For a Very Big Table
  3. SQL Server Editions
  4. Copy a Table
  5. Informatica Metadata Manager Can Now Read SSAS Cubes and SSRS Reports Metadata
  6. How Long Did It Run?
  7. Class Does Not Support Aggregation
  8. SQL Server 2008 Backup Compression
  9. The Database Does Not Have a Valid Owner
  10. Using Pivot for 2 Value Columns
  11. Q&A on: Resizing TempDB
  12. Editions and Prices of Visual Studio 2010
  13. Creating Test Data on SQL Server 2008
  14. Updating a Table Based on Itself
  15. Collation Conflict When Querying a Linked Server
  16. Inserting Multiple Rows in One SQL Statement
  17. T-SQL: Exists versus In
  18. T-SQL: Exists versus In – Performance Comparison
  19. Null in Date Column
  20. LINQ2SQL and TSQL and CLR
  21. Concat NULL Yields NULL
  22. Make yourself sysadmin on SQL 2008 Express
  23. Formatting Dates in SQL Server 2012
  24. Data Consistency in Oracle and SQL Server
  25. SQL Server 2014 Installation
  26. SQL Server Scripts Library
  27. Temporal Tables in SQL Server 2016
  28. Alt Key in SSMS
  29. U-SQL
  30. About NOLOCK
  31. Column Store Index
  32. What’s in Azure?

Business Intelligence

  1. Reporting and Analytics
  2. Current Trend in Business Intelligence
  3. Where To Store The Business Logic
  4. Microsoft BI Books
  5. Data Warehousing & BI Companies
  6. How to Choose the Right BI Technology to Suit Your Style
  7. SAP Hana, an in-Memory Database
  8. Business Objects Voyager (SAP BO Analysis)
  9. What is Big Data, Data Warehouse, Data Mining
  10. Tibco Spotfire
  11. Composite
  12. The 5 Stages of DWBI Journey
  13. iDashboard
  14. Using BI Tools as ETL Tools

Oracle BI

  1. Comparing Oracle 11g OLAP Option and SSAS
  2. Introduction to Oracle BI for MS BI Developer
  3. Oracle ETL Tools
  4. Bit and Pieces on Oracle
  5. Difference Between CURRENT_DATE and SYSDATE

Data Science

  1. Machine Learning or Data Science?
  2. How to do AI without Machine Learning?
  3. Forecasting time series: using statistics vs machine learning
  4. Managing Investment Portfolios using Machine Learning
  5. Using Reinforcement Learning to Manage Portfolio Allocation
  6. Forecasting Stock Prices using LSTM
  7. Using CNN for Stock Prediction
  8. Stock Price Forecasting using XGBoost
  9. Tuning XGBoost Models
  10. MCC Formula for Multiclass Classification
  11. Logistic Regression with PCA in Python
  12. Linear Regression in Python
  13. Handling Class Imbalance
  14. Natural Language Processing (NLP)
  15. What is Convolution?
  16. What is Convolutional Neural Network (CNN)?
  17. What is CNN? (Part 2)
  18. Recurrent Neural Network (RNN) and LSTM
  19. RNN Applications
  20. Reinforcement Learning
  21. Ensembles – Odd and Even
  22. SVM with RBF Kernel
  23. Which Machine Learning Algorithm Should I Use?
  24. Interview Questions for Data Scientists
  25. Why do we use Python in Machine Learning?
  26. The Trick in Understanding Human Language
  27. Feature Importance
  28. Automating Machine Learning using Azure ML
  29. Why Linear Regression is so hard
  30. Google Colab
  31. Learning Machine Learning with Upgrad
  32. Tokeniser
  33. Entropy and Information Gain in Decision Tree
  34. Can Machine Learning replace BI?
  35. Building a Neural Network
  36. Andrew Ng’s Machine Learning Course
  37. Andrew Ng’s Deep Learning Course
  38. What Machine Learning Can Be Used For
  39. What is Data Science?
  40. Data Scientist
  41. BI vs Data Science

Python by example

  1. Turtle
  2. Python: String and Array
  3. Python: List, Tuple, Dictionary, Set
  4. Python: If and For
  5. Python: List comprehension
  6. Python: Function, Lambda, Map, Filter, Reduce
  7. Pandas: Column, data type, group
  8. Plot: Boxplot, barplot, pairplot, heatmap

Analysis Services

  1. Parent Child Dimension
  2. Top 10 Tips: Building Cubes
  3. Top 10 Tips: Cube Testing
  4. The Most Useful SSAS Book
  5. Who’s Who in Analysis Services
  6. Building Cubes From Operational System (SQLBits presentation)
  7. The Unpopular SELECT Statement
  8. Date Dimension in Analysis Services (Part 1)
  9. Date Dimension in Analysis Services (Part 2)
  10. Many-to-Many in Attribute Relationship
  11. SSAS DMV (Dynamic Management View)
  12. SSAS DMV: Join Using DataSet
  13. SSAS DMV Nugget at SQL Server User Group
  14. Optimising Cube Query Performance and Processing Performance
  15. Creating Many Roles in SSAS Cubes
  16. Vertical Fact Table
  17. Creating a Dimension with Multiple Column Key
  18. Scheduled Deploy in SSAS
  19. AMO: Enumerating DSV Tables and Columns
  20. The 4.2 billion tuples limitation in SSAS
  21. Double Click Property Name
  22. Where To Put An Attribute
  23. Attempted to read or write protected memory
  24. Combining DW and ERP Data in SSAS Cubes
  25. Combining DW and ERP Data in SSAS Cubes – 5th Case
  26. Creating a dimension from a large table
  27. Concatenating attributes to form dimension key
  28. What is a cube?
  29. Cube developer
  30. What are cubes bad at?
  31. Using SSAS cubes for reconciliation
  32. Who Are Using The Cube Now?
  33. Who Uses What Cube and When
  34. Taking a cube offline
  35. Comparing Excel 2007 and ProClarity
  36. Qlikview vs PowerPivot: connecting to SSAS cube
  37. PowerPivot
  38. Multi Language Cubes
  39. Cube Translation in Excel 2007
  40. Q&A on: Browsable Folder When Restoring Cubes
  41. Renaming Attribute & Dimension
  42. Aggregate Not Shown
  43. Ratio in SSAS
  44. Updating Partition Source SQL
  45. Parser: The query contains  … parameter, which is not declared
  46. Tuning Cube Processing Performance
  47. SSAS Developer Interview Questions
  48. Q&A on: Impersonation
  49. Parent Child Dimension: extra child node
  50. Ragged Hierarchy in SSAS
  51. Where To Put an Attribute (SSAS)
  52. Many to Many Is Not Always Right
  53. Many to Many: Which Dimension is Used?
  54. Processing ROLAP Cube and ROLAP Dimension
  55. SSAS Videos
  56. Duplicate Attribute Keys in SSAS
  57. The Trio Maestro’s SSAS 2012 Tabular Book
  58. Processing SSAS Cubes using PowerShell
  59. Can I Cluster SSAS? (Minimizing Downtime at Deployment)
  60. 5 Minutes SSAS DBA
  61. SSAS Stops Logging
  62. Process Update of a Dimension
  63. Analysis Services Tabular Model

MDX

  1. Sorting
  2. Filter
  3. Existing
  4. Except
  5. Filter on Dates Before Today
  6. Tomislav’s MDX Book
  7. Half Year To Date
  8. Setting Default Date to Last Available Date
  9. Holding History – MDX for “From Date” and “To Date”
  10. Sherry Li and Tomislav’s 2012 MDX Cook Book

Power BI

  1. Power BI Datamart
  2. Power BI Q&A
  3. Dynamic Difference
  4. DAX Studio and DAX Guide
  5. DAX: Percentage of Total and Filter on 2 Columns
  6. A few DAX functions

SSIS

  1. SSIS: Looping with ADO Enumerator
  2. SSIS: Debugging a Script Component
  3. SSIS: Importing Files – Read the First N Rows
  4. SSIS: Work Flow vs Stored Procedures
  5. SSIS: SQL Server Destination or OLE DB
  6. SSIS: Importing a File with Dynamic Columns
  7. Coming SSIS Articles
  8. SSIS: Export a Query Result to a File
  9. SSIS 2008 Data Profiler
  10. SSIS: Updating a Variable based on Database
  11. SSIS: Updating a Variable based on a File (Script Component)
  12. SSIS: Updating a Variable based on a File (Script Task)
  13. SSIS: Import/Export Files with Variable File Name Set at Run Time
  14. SSIS: True/False and 1/0 on Bit Columns
  15. SSIS: Loading a Big File Fast
  16. SSIS: How to Convert DateTime to Int
  17. SSIS: How to Populate a Fact Table using SSIS (part 1)
  18. SSIS: How to Populate a Fact Table using SSIS (part 2)
  19. SSIS: SCD Wizard Performance Issue
  20. SSIS: Automating DDL Changes
  21. SSIS 2012: Converting Date from Excel for Lookup
  22. Loading a Dimension Table using SSIS
  23. Six Important Features of an ETL Tool
  24. Choosing an ETL Tool
  25. SSIS: Numeric Column Loaded as NULL
  26. Memory for SSIS
  27. Download files from Azure Blob Storage using Control-M 

Business Knowledge

  1. Layering in Insurance
  2. Bank Data Model
  3. Credit Default Swap (CDS)
  4. Distribution Yield vs Underlying Yield
  5. Treasury in Investment Banking
  6. Off Balance Sheet Items
  7. Securitising Cash Positions
  8. Investment Banking Books for BAs and Developers
  9. Investment Banking
  10. Credit Risk and Market Risk
  11. Asset Management Business Processes and Systems
  12. Domain Knowledge
  13. Historical Portfolio Positioning
  14. Data Warehousing / Business Intelligence for Investment Banking
  15. Swaps and Options
  16. Performance Attribution
  17. Asset Management Companies in the UK

Events

  1. SQLBits 4 Manchester (Data Warehouse Data Modelling)
  2. SQLBits 5 Wales (Building Cubes from Operational Systems)
  3. SQLBits 7 York
  4. 4 Sessions for SQLBits 8 Brighton
  5. SQLBits 8 Brighton (Advanced Dimensional Modelling)
  6. SQLBits 9 Liverpool
  7. SQLBits 10 London
  8. SQL Server User Group Indonesia
  9. SQLBits 12 Telford
  10. SQLBits 15 Liverpool 4-7th May 2016

19 Comments »

  1. Wooow.
    What a perfect blog about DW/BI.
    I will check it each month for new changes.
    Good luck.

    Comment by khashayar jamsahar — 29 December 2010 @ 11:21 am | Reply

  2. Hiya! I simply want to give a huge thumbs up for the great info you have got here on this post. I might be coming back to your blog for extra soon.

    Comment by Catheryn Wanczyk — 11 April 2011 @ 6:37 pm | Reply

  3. You have got a really informative blog here… thanks and keep writing…
    I was wondering how did you create this page… manually?

    Comment by theSuda — 17 May 2011 @ 6:41 am | Reply

  4. What an amazing repository for DW/BI issues!! I can’t tell you how grateful I am for you putting all this up for everyone. I will definitely be back for updates. I’m especially interested in using AMO with Powershell and your great examples of SSAS DMVs. I had never heard about it before. Thanks so much again.

    Comment by Reiner — 6 September 2011 @ 1:25 pm | Reply

  5. Very Nice collection about BI, help me alot … thanks

    Comment by neeraj1982 — 1 October 2011 @ 6:10 pm | Reply

  6. Thanks for wonderfull collection

    Comment by cemuney — 6 January 2012 @ 6:09 pm | Reply

  7. A great website!

    Comment by Dez — 28 May 2012 @ 3:33 pm | Reply

  8. I have read your book Dataware house with SQL Server. It realy super!. Thanks for sharing the information

    Comment by Uma — 23 August 2012 @ 5:34 am | Reply

  9. Very good set of articles and examples.
    Thank you.

    Comment by matteo montesi — 29 August 2012 @ 10:45 am | Reply

  10. For details DWH & BI concepts and DWH & sql interview related materials please visit on this link http://datawearhousebiworld.blogspot.in/2015/01/hello-all.html

    Comment by bidevloper — 25 January 2015 @ 12:38 pm | Reply

  11. There is lot information available in this blog about BI and data warehousing. Would follow this blog regularly for any update on Oracle BI side.

    Comment by sourishbanerjee — 31 July 2015 @ 8:27 am | Reply

  12. Always a pleasure to read your blog! Very useful information. Please continue your excellent blog many years…

    Comment by Hennie de Nooijer — 16 January 2017 @ 7:59 am | Reply

  13. Waw…

    Comment by yvonne sumilat — 14 July 2018 @ 4:03 am | Reply

  14. Wow, such a great blog! The blog explained best practice loading DW so clear. Really appreciate your efforts and time for writing this blog!

    Comment by Rose — 15 December 2020 @ 6:04 am | Reply

  15. Nice one.

    Comment by Oriyomi — 25 December 2020 @ 9:48 am | Reply

  16. Hi Vincent! This is probably the best nuts-and-bolts data warehousing blog out there, keep up with great work! I do have one question. In the “Populating Fact Tables” post (https://dwbi1.wordpress.com/2015/02/09/populating-fact-tables/) you linked to loading dimension tables blog post but the link is broken (https://www.sqlservercentral.com/columnists/vRainardi/). Do you have a working link, or a backup of the blog post?

    Comment by CuriousOne — 14 August 2021 @ 8:50 pm | Reply

  17. Hi.. Nice informative blog. Best Data Warehousing and Business Intelligence Services Provider in USA

    Comment by Mani.DWP — 12 September 2022 @ 8:46 am | Reply


RSS feed for comments on this post.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

Blog at WordPress.com.