- new
Microsoft MDS end of life: your options before 2028
Microsoft removed Master Data Services from SQL Server 2025, and SQL Server 2022 mainstream support ends on 11 January 2028. What that means for your MDS models, and how the alternatives compare.
- new
Faster AS400 loads into SQL Server: one column per row
How we cut the fetch of 361,899 IBM i rows from 487 seconds to 57 to 95 seconds by packing 76 columns into one string, and how we kept it safe.
- 6.3k
How to Truncate All Tables in a Database While Preserving Foreign Keys: A Complete Script
When testing a data warehouse , a common challenge is managing large datasets effectively. Often, you need to reset tables to a clean state, ensuring consistent testing environments. The most ...
- 3.7k
Understanding Lakehouse, Warehouse, and Datamart: The Differences Between the Three Fabric Objects
In the world of data management, there are several data storage and processing models that organizations use to handle their ever-growing data needs. Three of these models, which have risen to ...
- 4.4k
The Correspondence Between Excel Formulas and DAX Formulas: Why Financial Analysts Should Learn DAX
Excel, a widely used tool in the world of finance, has been the go-to platform for data analysis for decades. The formula-based structure of Excel allows analysts to manipulate, aggregate, and ...
- 11.2k
SSIS Maximum insert commit size
The default value of the Maximum insert commit size is 2147483647, the largest value a 4 byte INT will hold. This value is default set in SQL 2012+ but you need to set it manually in versions lower ...
- 18.2k
Data Warehouse - Why do you need one?
The word "Data Warehouse" sounds quite exciting! It suggests something complicated, a kind of warehouse or collection of data. In fact a data warehouse is a database ...
- 6.4k
SSAS OLAP is returning wrong results
Like the title says; my SSAS OLAP cube (SQL 2008R2) was giving me wrong results when selecting a combination of a measure and a fact. I was struggling for days with this issue. I called old ...
- 539.9k
How to implement Dimension Security in SSAS
I know it was not difficult to implement dimension security in Analysis Services. While it's not difficult, why did it cost me a lot of days to get this to work? Well, its because you have to do a ...
- 11.9k
SSAS: How to format a dimension attribute?
I was looking for a method of formatting a dimension attrbibute in SSAS2008R2 to, for example a currency symbol €. In search of this question, I found out this is not possible within the default SSAS ...
- 4.7k
Powerpivot architecture
Today I came across a nice page on the Microsoft site about the architecture of Microsoft Powerpivot.
- 9.7k
Datawarehouse and Datamart - Empty Strings or NULL Values?
I was wondering what's the best method to store empty fields from source systems in a datawarehouse. For example;a customer record without a first name stored in the database. When loading this ...
- 9k
How do I create a Period Dimension?
This blog covers the period dimension. It looks a lot like the date dimension discussed earlier (and indispensable in every data warehouse), but its function is a little different. The power of ...
- 7.5k
Data Vault AdventureWorks Example
There are many articles on the internet about the Data Vault. It is a method/data model to design a data warehouse. What strikes me is that there are no good examples (yet) of the ...
- 4.8k
One Single Version of the Truth does not exist
This week I went to a seminar at Logica called Guru4Pro. The subject was: “Best Practices in Data Vault”, and the Godfather of Data Vault, Dan Linstedt, was one of the speakers.
- 7.8k
Data conversion performance in SSIS
Today I came across a very good article by the SQL CAT team about the performance differences between the different ways to convert data types in SSIS.
- 7.6k
Controlling ETL jobs in a Data Warehouse
There are several ways to start the ETL jobs of a data warehouse. You can create an SSIS package that calls all underlying ETL packages. You can also create an SSIS package that ...
- 6.2k
Metadata – Table growth in data warehouses
While working out how to manage metadata (stored in a metadata database) for our data warehouse, the topic ‘table growth’ came up. In a data warehouse it is useful to know ...
- 7.3k
ETL Tool or SQL
In an earlier article I wrote about the advantage of handling changes in a dimension with the Kimball Slowly Changing Dimension Type 2 principle, creating a new ...
- 8.7k
Updating a dimension in a data warehouse
In an earlier article I wrote about the benefit of handling changes in a dimension with the Kimball Slowly Changing Dimension Type 2 principle: creating a new ...
- 5.9k
Historical Staging Area HSA in a DWH project
In Data Warehouse projects there is always the question of which architecture works best. Every architecture has its own pros and cons. What I find important in an architecture ...
- 4.9k
Why not just Type 2?
In a Slowly Changing Dimension scenario you normally decide per field whether it is a type 1, type 2 or type 3 field. With a type 1 the record is overwritten with the new value, with ...
- 4.1k
Dimension lookup optimization
To update the dimension/fact, you must find out if a record already exists. Normally you do this by looking at the business key (natural key) in the dimension. If it is not ...
- 4k
SSIS Package Configurations
Working with SSIS packages can cause problems when you have to develop across different environments. Dougbert.com links to some SQL MVPs in a short blog post ...
- 3.7k
MSc Business Intelligence
In 2010 DNV Academy CIBIT will be the first Dutch training institute to start an official Master of Science programme in Business Intelligence. On 12 March there was an information evening about it ...
- 3.8k
Checkpoints in SSIS
The Control Flow in Integration Services gives you control and an overview of the tasks that run in your package. But when one of these tasks fails, you often do not want ...
- 3.9k
Exporting SSIS data to Excel 2007
Below is a short description of how to connect to Excel 2007 from SSIS.
- 4k
SQLBI Methodology
This week I came across an interesting piece of information about a methodology for BI and data warehouse solutions. As most people know, the biggest theories in the BI market ...
- 3.5k
Excel connectors and 64-bit
The SSIS Excel connector is a special connector that does not have many configuration options, but does exactly what it should do: read data from an Excel tab and write it back into ...
- 3.7k
Using a variable in an OLE-DB Source (SSIS)
In Integration Services you may want to build the SQL in your Data Flow Task dynamically by using a variable. To get this done, you need a trick.
- 3.3k
Finding the value of a variable in SSIS
SSIS has a number of debugging tools. The one I could not find myself was a tool to trace variable values at runtime. A quick search on the internet did wonders.
- 3.6k
A tool to help you find KPIs
A colleague of mine recently gave me a tip for a website where you can find KPIs (Key Performance Indicators) for important business processes.
- 3.8k
Custom Logging in SSIS
Logging in SSIS (Integration Services) is a useful method that you should build into every package by default. With logging you can always look up (if needed) what time packages were ...
- 3.9k
BIDS Helper
The Business Intelligence Development Studio (BIDS for short) is normally used to develop Microsoft BI software. There is room for improvement everywhere, and in this case the BIDS ...
- 4.3k
Analysis Services Enterprise vs Standard
When a Microsoft Analysis environment has several servers with different editions, you always have to weigh how you keep the servers in sync with each other.
- 4k
Analysis Services, Reporting and Excel 2007
I am currently working on a project that implements SQL Server 2005 (64-bit), Analysis & Reporting Services plus Excel 2007. The combination of Analysis and Excel offers very good options ...
- 4.3k
Starting and stopping SQL Server Services
Jorg Klein found a smart way to stop and restart all SQL Server Services in 1 step. This saves a lot of time, because you no longer have to do it by hand!
- 4.5k
Business Rules in the Data Warehouse
As the name says, Business Rules are rules that apply to a company. Anyone with common sense can come up with Business Rules. In Data Warehousing you also deal with Business ...
- 8.4k
Query Active Directory
It can be useful to include data from your Active Directory (AD) in your data warehouse. In my case it was useful because the customer wanted to see whether a user who had left the company still ...
- 3.9k
Hierarchies in Analysis Services 2005
Everyone knows them: hierarchies in Analysis Services 2005. With a hierarchy you can show certain data to the end user at different levels. A well-known example is: Year -> Quarter -> ...
- 3.4k
SSIS error reporting with Event Handlers
When an error occurs in your ETL package, as a data warehouse administrator you also want to know what went wrong and when. When you use an event handler, an e-mail can for example ...
(41 rows affected)
Completion time: