Track source dates when loading a data warehouse
A primer on how to reduce network and source system load when reading a relational source into the data warehouse.
2012-07-30
5,697 reads
Data Warehousing
Scaling Up Your Data Warehouse with SQL Server 2008 R2
SQL Server 2008 introduced many new functional and performance improvements for data warehousing, and SQL Server 2008 R2 includes all these and more. This paper discusses how to use SQL Server 2008 R2 to get great performance as your data warehouse scales up. We present lessons learned during extensive internal data warehouse testing on a 64-core HP Integrity Superdome during the development of the SQL Server 2008 release, and via production experience with large-scale SQL Server customers. Our testing indicates that many customers can expect their performance to nearly double on the same hardware they are currently using, merely by upgrading to SQL Server 2008 R2 from SQL Server 2005 or earlier, and compressing their fact tables. We cover techniques to improve manageability and performance at high-scale, encompassing data loading (extract, transform, load), query processing, partitioning, index maintenance, indexed view (aggregate) management, and backup and restore.
2011-05-19
5,175 reads
Understanding and Controlling Parallel Query Processing in SQL Server
Data warehousing and general reporting applications tend to be CPU intensive because they need to read and process a large number of rows. To facilitate quick data processing for queries that touch a large amount of data, Microsoft SQL Server exploits the power of multiple logical processors to provide parallel query processing operations such as parallel scans. Through extensive testing, we have learned that, for most large queries that are executed in a parallel fashion, SQL Server can deliver linear or nearly linear response time speedup as the number of logical processors increases. However, some queries in high parallelism scenarios perform suboptimally. There are also some parallelism issues that can occur in a multi-user parallel query workload. This white paper describes parallel performance problems you might encounter when you run such queries and workloads, and it explains why these issues occur. In addition, it presents how data warehouse developers can detect these issues, and how they can work around them or mitigate them.
2010-12-10
4,645 reads
3 Fundamental Questions about Data Marts...and Their Answers
A data mart provides the primary access to the data stored in the data warehouse or operational data store. It is a subset of data sourced from the data warehouse or operational data store specifically focused on a business function or set of related business functions. Read on to learn the answers to fundamental questions about data marts.
2010-12-03
5,164 reads
Data Distribution - Part 1
The start of a new series from Leo Peysakhovich that looks at some of the issues with moving data around between systems and ensuring that it is in sync between them.
2010-07-26
6,823 reads
Building a Data Warehouse Blueprint for Success
One of the most integral components and critical success factors of any enterprise data warehousing initiative is the Solutions Architecture document, a high-level conceptual model of a data warehousing solution. Learn why this collaborative effort that addresses the needs of all major stakeholders, including both the business units and Information Technology (IT), is essential.
2010-07-09
2,224 reads
Data Warehousing Architecture - Designing the Data Staging Area
The staging area tends to be one of the more overlooked components of a data warehouse architecture, and yet it is an integral part of the ETL component design. Learn why it is best to design the staging layer right the first time, enabling support of various ETL processes and related methodology, recoverability and scalability.
2010-07-07
6,197 reads
How to Conduct Effective Data Warehouse and Business Intelligence Software Evaluations
Denise Rogers discusses the essential tasks in conducting effective software evaluations revolving around data warehousing and business intellegence. Each step has a dependency on the previous one, starting with establishing the framework of the evaluation and adding progressively elaborate data that facilitates a decision making process that is resolute.
2010-06-11
4,369 reads
HASHBYTES can help quickly load a Data Warehouse
Data warehouse loads can be time consuming - this method can be used in some instances to help speed things up.
2010-04-14
16,916 reads
Kimball University: Three ETL Compromises to Avoid
Why neglecting slowly changing dimensions, failing to capture metadata and overlooking scope creep can be the undoing of a dimensional data warehousing initiative.
2010-03-11
4,390 reads
Blogs
From Data Custodian to Innovation Catalyst: The Evolving Role of the CDO
By Chris Yates
There was a time when the Chief Data Officer lived in the shadows of...
Down the Rabbit Hole: Dealing with Ad-Hoc Data Requests
By Rayis Imayev
"But I don’t want to go among mad people," Alice remarked."Oh, you can’t help...
Adding a Local Model to Ollama through the GUI
By Steve Jones
I saw some good reviews of the small gemma3 model in a few places...
Forums
Create an HTML Report on the Status of SQL Server Agent Jobs
Comments posted to this topic are about the item Create an HTML Report on...
We Should Demand Better
Comments posted to this topic are about the item We Should Demand Better
Estimated Rows
Comments posted to this topic are about the item Estimated Rows
Question of the Day
Estimated Rows
I have two calls to the GENERATE_SERIES TVF in this code:
SELECT TOP 10 gs.value FROM GENERATE_SERIES(1, 10) AS gs ORDER BY NEWID () OPTION (RECOMPILE); go DECLARE @a int = 10; SELECT TOP (@a) gs.value FROM GENERATE_SERIES(1, @a) AS gs ORDER BY NEWID () OPTION (RECOMPILE);In the actual query plans, what is the estimated number of rows for each batch? See possible answers