Data Warehousing
- One or Two Dimensions
- Using a Data Warehouse for CRM
- A dimension with only one attribute
- Data Warehouse Data Modelling (SQLBits 4 Persentation)
- 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 28/1/12
- When is a number not a measure? 28/1/12
- Parent SK in the Fact Table 8/2/12
- City and Rank 15/2/12
- Effective and Expiry Dates in Type 2 Dimensions 15/2/12
- Adding a New Attribute and Measure 22/2/12
- Star Schema or Snowflake 13/3/12
- Name of Surrogate Key Columns 14/3/12
- 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
- Why do we need a data warehouse? (again) 21/4/16
- Rating Dimension 18/9/16
- Numerical Attributes 13/11/17
- Loading Late Arriving Dimension Rows 13/12/17
- Late Data Warehouse Loading 20/12/17
- Big Volume Data Warehouse 11/05/18
- Modern Data Warehouse 6/9/18
- Data Warehouse for Asset Management 25/9/18
- Junk Dimension Revisited 5/10/18
- Transactional Fact Table 20/2/19
- Day Measures 8/11/20
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 20/5/11
- Concat NULL Yields NULL 20/5/11
- Make yourself sysadmin on SQL 2008 Express 30/8/12
- Formatting Dates in SQL Server 2012 26/12/12
- Data Consistency in Oracle and SQL Server 29/11/13
- SQL Server 2014 Installation 23/11/14
- SQL Server Scripts Library 27/12/14
- Temporal Tables in SQL Server 2016 14/1/16
- Alt Key in SSMS 10/2/16
- U-SQL 7/5/16
- About NOLOCK 18/6/16
- Column Store Index 31/1/19
Oracle BI
- Comparing Oracle 11g OLAP Option and SSAS 25/12/10
- Introduction to Oracle BI for MS BI Developer 7/1/11
- Oracle ETL Tools 8/1/11
- Bit and Pieces on Oracle 23/12/12
- Difference Between CURRENT_DATE and SYSDATE 2/1/13
Business Knowledge
- Layering in Insurance
- Bank Data Model 23/4/12
- Credit Default Swap (CDS) 15/6/12
- Distribution Yield vs Underlying Yield 17/6/12
- Treasury in Investment Banking 15/3/13
- Off Balance Sheet Items 16/6/13
- Securitising Cash Positions 6/7/15
- Investment Banking Books for BAs and Developers 19/9/15
- Investment Banking 23/9/15
- Credit Risk and Market Risk 27/9/15
- Asset Management Business Processes and Systems 3/5/16
- Domain Knowledge 18/6/16
- Historical Portfolio Positioning 4/7/16
- Data Warehousing / Business Intelligence for Investment Banking 3/1/17
- Swaps and Options 21/10/17
- Performance Attribution 9/12/18
- Asset Management Companies in the UK 5/1/19
|
Data Science
- Andrew Ng’s Machine Learning Course 25/10/17
- What is Data Science? 28/10/2017
- What’s in Azure? 28/11/2017
- What Machine Learning Can Be Used For 27/12/2017
- Andrew Ng’s Deep Learning Course 5/1/2018
- Building a Neural Network 7/2/2018
- BI vs Data Science 12/4/2018
- Can Machine Learning replace BI? 27/6/2018
- Entropy and Information Gain in Decision Tree 30/5/2019
Python by example
- Turtle 10/9/20
- Python: String and Array 27/11/20
- Python: List, Tuple, Dictionary, Set 27/11/20
- Python: If and For 1/12/20
- Python: List comprehension 1/12/20
- Python: Function, Lambda, Map, Filter, Reduce, 2/12/20
Data Architecture
- Code Decode Table 23/4/15
- Responsibilities of Data Architects 31/5/15
- Varchar(255) 21/6/15
- Physical Data Modelling (PDM) 12/7/15
- Painful-to-Retrieve Data Structures 17/7/15
- Distinct-Attribute Rows in Dimension Tables 22/8/15
- 3 Things in Date Dimension 28/8/15
- EAV Fact Tables 12/9/15
- Data Types for Common Columns 16/9/15
- Data Interface (How to Manage a DW Project) 15/2/16
- The Problem with Data Quality 17/2/16
- Different Measures for Different Products 16/3/16
- Data Lake vs Data Warehouse 20/6/16
- Definition of Big Data and Data Warehousing 17/4/17
- Choosing between Big Data and Data Warehousing 24/4/17
- Hierarchy with Multiple Parents 18/10/17
- Relational vs Non Relational Databases 12/11/17
- Data Type 15/2/19
- Data Files – Delimiter and Qualifier 21/2/19
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 17/8/11
- SAP Hana, an in-Memory Database 8/7/12
- Business Objects Voyager (SAP BO Analysis) 10/7/12
- What is Big Data, Data Warehouse, Data Mining 14/7/12
- Tibco Spotfire 2/12/12
- Composite 17/12/12
- The 5 Stages of DWBI Journey 8/11/13
- iDashboard 16/7/14
- Using BI Tools as ETL Tools 1/12/14
- Data Scientist 7/2/15
- Power BI Q&A 15/7/18
- A few DAX functions 4/11/18
- DAX Studio and DAX Guide 4/11/18
- DAX: Percentage of Total and Filter on 2 Columns 4/11/18
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 26/5/11
- Many to Many: Which Dimension is Used? 28/5/11
- Processing ROLAP Cube and ROLAP Dimension 28/6/11
- SSAS Videos 6/10/11
- Duplicate Attribute Keys in SSAS 9/7/12
- The Trio Maestro’s SSAS 2012 Tabular Book 17/8/12
- Sherry Li and Tomislav’s 2012 MDX Cook Book 13/9/13
- Processing SSAS Cubes using PowerShell 27/10/13
- Can I Cluster SSAS? (Minimizing Downtime at Deployment) 27/10/13
- 5 Minutes SSAS DBA 31/5/14
- SSAS Stops Logging 16/6/14
- Process Update of a Dimension 15/7/14
- Analysis Services Tabular Model 10/5/19
MDX
- Sorting 21/6/10
- Filter 21/6/10
- Existing 25/6/10
- Except 27/7/10
- Filter on Dates Before Today 25/10/10
- Tomislav’s MDX Book 1/9/11
- Half Year To Date 19/12/11
- Setting Default Date to Last Available Date 10/08/14
- Holding History – MDX for “From Date” and “To Date” 18/6/15
Power BI
- Dynamic Difference
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
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 27/12/11
- SQL Server User Group Indonesia 24/6/14
- SQLBits 12 Telford 29/6/14
- SQLBits 15 Liverpool 4-7th May 2016 18/12/15
|
|
|
RSS feed for comments on this post.
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 |
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 |
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 |
Thank you for your comment Suda. Yes I created this page manually.
Comment by Vincent Rainardi — 17 May 2011 @ 7:15 am |
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 |
Very Nice collection about BI, help me alot … thanks
Comment by neeraj1982 — 1 October 2011 @ 6:10 pm |
Thanks for wonderfull collection
Comment by cemuney — 6 January 2012 @ 6:09 pm |
A great website!
Comment by Dez — 28 May 2012 @ 3:33 pm |
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 |
Very good set of articles and examples.
Thank you.
Comment by matteo montesi — 29 August 2012 @ 10:45 am |
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 |
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 |
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 |
Waw…
Comment by yvonne sumilat — 14 July 2018 @ 4:03 am |
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 |
Nice one.
Comment by Oriyomi — 25 December 2020 @ 9:48 am |