Data Warehousing, BI and Data Science

Content of This Blog

Data Warehousing

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

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 20/5/11
  21. Concat NULL Yields NULL 20/5/11
  22. Make yourself sysadmin on SQL 2008 Express 30/8/12
  23. Formatting Dates in SQL Server 2012 26/12/12
  24. Data Consistency in Oracle and SQL Server 29/11/13
  25. SQL Server 2014 Installation 23/11/14
  26. SQL Server Scripts Library 27/12/14
  27. Temporal Tables in SQL Server 2016 14/1/16
  28. Alt Key in SSMS 10/2/16
  29. U-SQL 7/5/16
  30. About NOLOCK 18/6/16
  31. Column Store Index 31/1/19

Oracle BI

  1. Comparing Oracle 11g OLAP Option and SSAS 25/12/10
  2. Introduction to Oracle BI for MS BI Developer 7/1/11
  3. Oracle ETL Tools 8/1/11
  4. Bit and Pieces on Oracle 23/12/12
  5. Difference Between CURRENT_DATE and SYSDATE 2/1/13

Business Knowledge

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

Data Science

  1. Andrew Ng’s Machine Learning Course 25/10/17
  2. What is Data Science? 28/10/2017
  3. What’s in Azure? 28/11/2017
  4. What Machine Learning Can Be Used For 27/12/2017
  5. Andrew Ng’s Deep Learning Course 5/1/2018
  6. Building a Neural Network 7/2/2018
  7. BI vs Data Science 12/4/2018
  8. Can Machine Learning replace BI? 27/6/2018
  9. Entropy and Information Gain in Decision Tree 30/5/2019

Python

  1. Turtle 10/9/20
  2. Python: String and Array 27/11/20
  3. Python: List, Tuple, Dictionary, Set 27/11/20

Data Architecture

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

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 17/8/11
  7. SAP Hana, an in-Memory Database 8/7/12
  8. Business Objects Voyager (SAP BO Analysis) 10/7/12
  9. What is Big Data, Data Warehouse, Data Mining 14/7/12
  10. Tibco Spotfire 2/12/12
  11. Composite 17/12/12
  12. The 5 Stages of DWBI Journey 8/11/13
  13. iDashboard 16/7/14
  14. Using BI Tools as ETL Tools 1/12/14
  15. Data Scientist 7/2/15
  16. Power BI Q&A 15/7/18
  17. A few DAX functions 4/11/18
  18. DAX Studio and DAX Guide 4/11/18
  19. DAX: Percentage of Total and Filter on 2 Columns 4/11/18

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 26/5/11
  53. Many to Many: Which Dimension is Used? 28/5/11
  54. Processing ROLAP Cube and ROLAP Dimension 28/6/11
  55. SSAS Videos 6/10/11
  56. Duplicate Attribute Keys in SSAS 9/7/12
  57. The Trio Maestro’s SSAS 2012 Tabular Book 17/8/12
  58. Sherry Li and Tomislav’s 2012 MDX Cook Book 13/9/13
  59. Processing SSAS Cubes using PowerShell 27/10/13
  60. Can I Cluster SSAS? (Minimizing Downtime at Deployment) 27/10/13
  61. 5 Minutes SSAS DBA 31/5/14
  62. SSAS Stops Logging 16/6/14
  63. Process Update of a Dimension 15/7/14
  64. Analysis Services Tabular Model 10/5/19

MDX

  1. Sorting 21/6/10
  2. Filter 21/6/10
  3. Existing 25/6/10
  4. Except 27/7/10
  5. Filter on Dates Before Today 25/10/10
  6. Tomislav’s MDX Book 1/9/11
  7. Half Year To Date 19/12/11
  8. Setting Default Date to Last Available Date 10/08/14
  9. Holding History – MDX for “From Date” and “To Date” 18/6/15

Power BI

  1. Dynamic Difference

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 16/11/11
  15. SSIS: Loading a Big File Fast 20/3/12
  16. SSIS: How to Convert DateTime to Int 26/5/12
  17. SSIS: How to Populate a Fact Table using SSIS (part 1) 26/5/12
  18. SSIS: How to Populate a Fact Table using SSIS (part 2) 26/5/12
  19. SSIS: SCD Wizard Performance Issue 19/6/12
  20. SSIS: Automating DDL Changes 3/10/12
  21. SSIS 2012: Converting Date from Excel for Lookup 28/12/12
  22. Loading a Dimension Table using SSIS 9/9/15
  23. Six Important Features of an ETL Tool 10/10/15
  24. Choosing an ETL Tool 12/2/16
  25. SSIS: Numeric Column Loaded as NULL 21/1/19
  26. Memory for SSIS 3/8/19

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 27/12/11
  8. SQL Server User Group Indonesia 24/6/14
  9. SQLBits 12 Telford 29/6/14
  10. SQLBits 15 Liverpool 4-7th May 2016 18/12/15

14 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


RSS feed for comments on this post.

Leave a Reply

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

Gravatar
WordPress.com Logo

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

Google photo

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

Twitter picture

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

Facebook photo

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

Cancel

Connecting to %s

Blog at WordPress.com.