Data Warehousing Best Practices for SQL Server
In this article, we look at things you should consider when building or enhancing a data warehouse to ensure you follow standards and best practices.
Dinesh Asanka holds a Bachelor of Science in Electrical Engineering, an MBA in Information Technology, a Master of Science in Artificial Intelligence, and an MPhil in Data Warehousing from the University of Moratuwa. With more than 30 years of industry experience, he has successfully bridged the gap between theoretical knowledge and practical application in the fields of data management and analytics. Currently serving as a Senior Lecturer at the University of Kelaniya, he teaches subjects including Data Science, Big Data Analytics, and Database Administration. His research interests focus on Educational Analytics, Health Analytics, Data Science, and Artificial Intelligence-driven solutions for decision-making and learning enhancement. In addition to his academic and research contributions, Dinesh is also an active columnist and presenter who regularly contributes to professional forums, conferences, workshops, and public discussions related to technology, analytics, and digital transformation.
- MSSQLTips Awards: Rookie of the Year Contender - 2018
What is a Data Warehouse?
Learn about data warehousing and the differences between a data warehouse and a relational database to meet your analytical needs.
SQL Server Attach and Detach Database Examples
In this article we look at how to detach and attach a SQL Server database using the SSMS GUI and using T-SQL commands.
Migrate On-Premises SQL Server Business Intelligence Solution to Azure
In this article we look at things to consider when migrating your on-premises business intelligence environment to Azure.
Monitoring SSAS with Extended Events
In this tip we look at how you can use Extended Events to monitor SQL Server Analysis Services.
SQL Server Resource Governor Configuration with T-SQL and SSMS
In this tip we look at how to configure the SQL Server Resource Governor to set limits on resources that can be utilized.
Create KPI in a SSAS Cube
In this tip we look at how to create KPIs for a SQL Server Analysis Service Cube.
SQL Server Maintenance Plan Index Rebuild and Reorganize Tasks
In this tip we cover the different options for SQL Sever maintenance plans Index Rebuild and Index Reorganize tasks.
SSAS Hardware Configuration Recommendations
In this tip we look at some SQL Server Analysis Services (SSAS) configuration settings to think about for memory, processors, storage and performance counters to monitor.
SSIS CDC Tasks for Incremental Data Loading
In this tip we look at how to use the CDC tasks in an SSIS package for incremental data loading.
Infrastructure Planning for a SQL Server Data Warehouse
In this tip we look at some things you should think about when planning for a SQL Server data warehouse.
Retry SSIS Control Flow Tasks
Learn how to build a process to retry tasks that fail within a SSIS package. Instead of just failing the SSIS package, setup a delay and retry the process again.
SQL Server Temporal Tables Overview
In this tip we look at what temporal tables are in SQL Server and how to use temporal tables.
Script to Create and Update Missing SQL Server Columnstore Indexes
In this tip we look at a SQL Server script you can run to generate new columnstore indexes and also generate scripts to update existing columnstore indexes.
SQL Server Clustered Index Behavior Explained via Execution Plans
In this tip we look at different SQL Server queries to get an understanding of how the clustered index is used.
Create an Extended Date Dimension for a SQL Server Data Warehouse
Learn how to create a comprehensive data warehouse date dimension for richer data analysis to address business and performance needs.
Why Surrogate Keys are Needed for a SQL Server Data Warehouse
In this tip we look at some of the reasons why surrogate keys should be used when building a data warehouse.
Loading Historical Data into a SQL Server Data Warehouse
In this tip we look at how to load historical data into a data warehouse when the transaction system only stores the latest records.
Understanding SQL Server Recovery Models and Transaction Log Use
In this tip we look at how the SQL Server database recovery model impacts how the transaction log is used and grows.
Generic Approach to Identify Modified SQL Server Rows
Check out this T-SQL script to help easily find modified rows in your SQL Server tables.