Search results matching tag 'Data Warehousing'

Little Data Remains Important in Healthcare IT

andyleonard — Tue, 30 Apr 2013 14:31:24 GMT

In his article Healthcare's Big Problem With Little Data, author Dan Munro raises salient points about the state of health-related data. Electronic Health Records (EHR) were promoted as the end-all-be-all solution for the industry – a standardization that, I suppose, many thought would organically and naturally occur, stabilize, and be maintained.

It hasn’t. At least not yet.

My doctor and I speak about this almost each time I visit with him. The corporation that operates his practice nowadays seems endlessly locked in cycles of changing billing and EHR systems in search of low-cost compliance and integration. They’ve (literally) spent millions of dollars and my doctor hates the interfaces forced upon him and his patients (well, one, at least) hates the complexity of the billing and patient records systems. Can’t these systems all just get along?

The result? Higher medical data management costs. I’ll give you one guesses who pays these costs.

Munro posits the following from his article:

By at least one estimate (here) there are now about 500 independent EHR vendors. Out of that large group is a subset of about 400 with at least one customer that has applied for Federal stimulus dollars through the labyrinthine process of meaningful use attestation. That would suggest a “first-cut” of about 100 vendors who made some commitment around certification – but have no reported customers (at least to date). That’s a staggering number of single-purpose software vendors for any industry to support – even bloated healthcare. The simple fact is it can’t. While there have been a few high-profile cases of EHR vendors shutting down, this last week was the first high-profile example of a vendor that was effectively decertified by the Feds for both their “ambulatory” and their “inpatient” EHR products. From the HHS.gov website last Thursday:

“We and our certification bodies take complaints and our follow-up seriously. By revoking the certification of these EHR products, we are making sure that certified electronic health record products meet the requirements to protect patients and providers,” said Dr. Mostashari.“Because EHRMagic was unable to show that their EHR products met ONC’s certification requirements, their EHRs will no longer be certified under the ONC HIT Certification Program.”

You may ask yourself, well, how did we get here? This, folks, is a mess. What’s missing? Applied standards.

“But Andy, you’ve told us standards slow down development!”

And I stand by that statement; standards do slow down development…unless you’re building interfaces. And then standards become the means for decoupled snippets, functions, methods, applications, and even platforms to communicate with each other. In some cases, we simply cannot be productive without standards – like TCP/IP. What would happen if everyone coded their own version of internet traffic? If that was the case, very few of you would reading this post.

Yes, standards slow things down. And yes, they are necessary to insure base functionality. In my humble opinion, we have to get this right with healthcare data. We simply must. While we see similar issues of data management across many fields, medical data is too important to mess around with; it’s (often literally) life and death. And it is certainly a high cost.

More to Consider

Standards exist. Administering and certifying 400-500 vendor solutions is hard.

Part of the Solution

From the actions of the Department of Health and Human Services last week, one can ascertain HHS is taking steps to address the matter. But will all 400-500 companies voluntarily congeal their schemas? Possibly, but doubtful.

My experience delivering US state Medicaid ETL solutions informs me there will be a need for data integration – regardless of the existence of standards and in spite of certification. Why? Standards are not static. The idea of de facto standards emerges from the life cycle of software because software is organic. Even if everyone agreed on the same interpretation of rigid standards (and they won’t), versions 2.0 through n.n will – at a minimum – add fields to the schema. And with additional fields comes additional data.

Standards will be revised when enough product schemas adopt the de facto, and this will drive the need for yet more integration. Don’t take my word for it, examine the entropic history of ICD-9 and ICD-10 codes – the direction of progress is more data, not less.

Learn More

This is one reason we at Linchpin People are focusing on Medical Data Integration. The recording of our first (free!) webinar about Medical Data Integration with SSIS 2012 is available here. Kent Bradshaw and I continue the series tomorrow presenting Medical Data Integration with SSIS 2012, Part 2 in which we focus on loading Provider and Drug data.

I hope to see you there!

:{>

Data Warehouse modelling deliberations – foreign keys and unknown members

jamiet — Tue, 16 Apr 2013 21:36:00 GMT

Earlier today I posted the following question on Twitter:

Foreign keys in a data warehouse. Yes or no? This discussion is looming at work, i know my position but am interested in what others think.
(https://twitter.com/jamiet/status/324215986729385986)

Specifically, I wanted to know if people were in favour of creating foreign keys from their fact to to their dimension tables or not.

To say it prompted a good response is a bit of an understatement, 38 responses so far and counting. Here are some of those responses:

It certainly seems to be an emotive subject and its clear (to me) that there’s no correct answer, just lots of opinions. That’s a good thing. The majority of responders appeared to be of the opinion that a data warehouse should contain foreign keys and that is my position too. In this blog post I want to outline why I believe that one should create foreign keys from a fact table to its dimension tables:

Maintain integrity

Of course, this is the main reason why foreign keys exist – to protect the integrity of your data. I see no reason not to use them for this purpose in a data warehouse. The main argument that I see going against is that with a sufficiently robust ETL solution it shouldn’t be necessary. That is true but I would counter with “how do you know that your ETL solution is suitably robust?” I don’t think its possible to anticipate every eventuality that may arise and for that reason I like the safety net that foreign keys provide. I liked Devin Knight’s response here, foreign keys breed confidence.

Communicate business logic

When I join a project that has a database in place the first thing I do is try and understand the data model – to do that I go and look at the foreign keys in that database. Understanding the dependencies between entities is crucial in any data model and the best means of communicating those is via foreign keys. If I encounter a database that is bereft of foreign keys then my heart sinks a little.

Performance

Foreign keys can, in some circumstances, be beneficial in improving query performance. Take a read of A Check and Foreign Key Constraint Improves Query Performance by Sarvesh Singh or Do Foreign Key Constraints Help Performance? by Grant Fritchey.

Code Generation

I am a big fan of generating ETL code where possible and foreign keys can be invaluable when doing so.

As I said there is no correct answer here so if you have any opinions, either agreeing or disagreeing, I look forward to reading your thoughts in the comments below.

Mark Stacey’s comment prompted an interesting digression into talking about surrogate keys for denoting unknown members and this is something I have strong opinions on too:

In short, I don’t like the practice of using “–1” as the surrogate key for an unknown member. My reasoning is simple, I don’t like giving meaning to something that is supposed to be meaningless. How then should we indicate which is the unknown member? I propose a single-row table that includes a column for each dimension table, each with a foreign key to the unknown member in the respective dimension table.

Moreover I don’t like the practice of starting surrogate key counters from 1; the first value available for the integer datatype in SQL Server is –2147483648 so why not start from that?

I discuss both of these issues in much more depth at Considering surrogate keys for Unknown members.

Again if you have any thoughts on these subjects please put them in the comments. If nothing else I find it both fun and educational to debate this stuff.

@Jamiet

Update, Chris Adkin posted a comment below that contained a link to Microsoft's own guidance on building datawarehouses where it is stated:

"Many physical designs for data warehouses follow the star schema but do not completely specify the relationships between the fact and dimension tables, as mentioned earlier for foreign key constraints, for instance. Without the foreign key constraints explicitly specified, SQL Server must depend on heuristics to detect star schema query patterns."

Chris' take on this:

"So, assuming we are talking about a Kimball DW, there is most definitely value in using foreign key contstraints as this provides a fail safe for the heuristics getting it wrong."

Thanks Chris.

Presenting Loading Data Warehouse Partitions with SSIS 2012 at SQL Saturday DC!

andyleonard — Mon, 19 Nov 2012 18:37:00 GMT

Join Darryll Petrancuri and I as we present Loading Data Warehouse Partitions with SSIS 2012 Saturday 8 Dec 2012 at SQL Saturday 173 in DC!

SQL Server 2012 table partitions offer powerful Big Data solutions to the Data Warehouse ETL Developer. In this presentation, Darryll Petrancuri and Andy Leonard demonstrate one approach to loading partitioned tables and managing the partitions using SSIS 2012, and reporting partition metrics using SSRS 2012. Objectives

A practical solution for loading Big Data Fact tables (1B+ rows).

Learn more about SQL Server 2012 table partitioning.

Learn more about using SSIS Expression Language to generate dynamic SQL in SSIS 2012.

I hope to see you there!

:{>

About Darryll Petrancuri:

Darryll Petrancuri is a Data & Business Intelligence Architect for SPS Commerce. He has been in software development for over 30 years, working across a wide variety of industries and domains serving as a consultant, developer, architect, thought leader, visionary, mentor and instructor. His background in the Microsoft technology stack includes VB (from Classic 1.0 Beta & .Net), C#, SQL Server 6.5 - 2012 (Core, Reporting Services, Integration Services, Notification Services, Analysis Services), component, application, database, data warehouse and business intelligence architecture and development. He currently specializes in innovative data warehouse solutions architecture and development, leveraging custom metadata repositories and automation.

About Andy Leonard:

Andy Leonard is CSO of Linchpin People, an SSIS Trainer and Consultant, SQL Server database and Integration Services developer, SQL Server data warehouse developer, community mentor, blogger, and engineer. He is a co-author of SSIS Design Patterns. His background includes Visual Basic and web application architecture and development and SQL Server 2000-2012.

SSIS Design Patterns, the Book

andyleonard — Mon, 06 Aug 2012 16:37:43 GMT

For the past two years, I have had the honor and privilege or authoring SSIS Design Patterns alongside Jessica Moss, Michelle Ufford, Tim Mitchell, and Matt Masson. Publication of the book – like many projects of this scope – has been delayed. The current publication date is 27 Aug 2012 and I have high confidence in this date.

I take responsibility for publication delays and apologize to those who pre-ordered the book. The reasons for the delays are not important. I have built a career as a software developer and architect based on the following maxim:

Deliver quality late, no one remembers.
Deliver junk on time, no one forgets.

The shared goal of everyone working on this project has been to deliver quality. Proofing the manuscripts, I believe we have achieved that goal.

:{>

Debunking Kimball Effective Dates

jamiet — Sat, 28 Nov 2009 15:59:00 GMT

Those who are familiar with Ralph Kimball’s theories on data warehousing modelling may be familiar with his assertion that a type 2 dimension member record should have a StartDate and an EndDate that defines the effective period of that record. He outlines this approach in his paper Slowly Changing Dimensions, Types 2 & 3 from the October 2008 edition of DMReview:

I recommend adding five additional fields to a dimension that is undergoing Type 2 processing… begin-effective-datetime & end-effective-datetime are full time stamps that that represent the span of time between when the change became effective and when the next change becomes effective. The end-effective-datetime of a Type 2 dimension record must be exactly equal to the begin-effective-datetime of the next change for that dimension member .The most current dimension record must have an end-effective-datetime equal to a fictitious datetime far in the future.

My opinion, which is based on experience of implementing this technique, is that it is a bad idea and in this blog post I’ll explain why. Let me clarify that I do not think the practise of managing dimension members as Type 2 slowly changing dimensions is a bad idea, only that maintaining an effective period using a start date and an end date is.

Framing the problem

Let’s look at an example where I am modelling customers as a type 2 dimension. Following Kimball’s advice we would implement a table something like this:

Note how we have two records for “Henry” because at some point he got married and had a child (attributes that we treat as type 2 changes) and how the [SCDEndDate] of the first record equals the [SCDStartDate] of the second record.

Can you spot the obvious problem here? We’ve got the same piece of information in more than one place; namely the date ‘27/11/2009’. What’s the point? We know that the [SCDEndDate] of a record equals the [SCDStartDate] of the record that supersedes it so using our old friend SQL we can easily work out the [SCDEndDate] of any record. I’ve seen too many examples of Kimball’s approach being used and I’ve hardly ever seen it not be a problem; just recently I worked on a data migration project and we had untold problems created by the use of [SCDStartDate] and [SCDEndDate] because the database could not constrain this relationship and neither, seemingly, did the code that had been written to manage the data.

At the root of this problem is the fact that Kimball’s method cannot be constrained in a relational database management system (RDBMS) without a complicated and debilitating set-based check constraint, it ignores the goodness of relational theory and normalisation that data professionals have had drummed into them for years.

A better way

Here follows how I would solve this problem; note that the syntax here is for SQL Server but this method would work perfectly well for any RDBMS.

Firstly, let’s create a table and put some data in it:

CREATE TABLE [Customer] (

   [Id]                INT             PRIMARY KEY

,  [NId]               NVARCHAR(50)

,  [Name]              NVARCHAR(50)

,  [HomeTown]          NVARCHAR(50)

,  [MaritalStatus]     CHAR(1)

,  [NumberOfChildren]  INT

,  [SCDStartDate]      DATETIME   --Note only [SCDStartDate], no [SCDEndDate]

,  CONSTRAINT  Customer_UK UNIQUE

   (

       [NId]

   ,   [SCDStartDate]

   )

);

INSERT [Customer] ([Id],[NId],[Name],[HomeTown],[MaritalStatus],[NumberOfChildren],[SCDStartDate])

VALUES (1,'Cust001','Henry','London','S',0,CONVERT(DATETIME,'20050324'))

,      (2,'Cust001','Henry','London','M',0,CONVERT(DATETIME,'20070726'))

,      (3,'Cust002','Sarah','Birmingham','M',2,CONVERT(DATETIME,'20060213'))

,      (4,'Cust001','Henry','London','M',1,CONVERT(DATETIME,'20091127'));,'M',2,CONVERT(DATETIME,'20060213'));

I’ve created three rows here for Henry for demo purposes. Over time we notice that Henry got married and then later had a child; note that there is no [SCDEndDate]:

Now let’s write a view that gets us the [SCDEndDate] for each record:

CREATE VIEW vCustomer  
AS

SELECT c.[Id],c.[NId],c.[Name],c.[HomeTown],c.[MaritalStatus]

,      c.[NumberOfChildren],c.[SCDStartDate]

,      COALESCE(MIN(c2.[ScdStartDate]),CONVERT(DATETIME,'99991231')) AS [SCDEndDate]

,      CONVERT(BIT,CASE    WHEN MIN(c2.[ScdStartDate]) IS NULL THEN 1 ELSE 0 END) AS [IsLatest]

FROM   [Customer] c

LEFT   OUTER JOIN [Customer] c2

ON     c.[Nid] = c2.[Nid]

AND        c.[SCDStartDate] < c2.[SCDStartDate]

GROUP  BY c.[Id],c.[NId],c.[Name],c.[HomeTown],c.[MaritalStatus]

,      c.[NumberOfChildren],c.[SCDStartDate];

Here is what we get if we select from that view:

Note that each row for Henry has an [SCDEndDate] that is guaranteed to be the same as the [SCDStartDate] of the record that supersedes it. Note also that we are guaranteed there will be no time period gaps since when Henry first became known to us. (Edit: Another advantage of this approach was pointed out to me by Phil Nolan in the comments below: we do not have to update a record when it gets superseded which we would have to do were we explicitly storing [SCDEndDate])

We have achieved this by joining the [Customer] table to itself and, for each row, finding the row that supersedes it and use the superseding row’s [SCDStartDate] as the current row’s [SCDEndDate]; if there is no superseding row we simply use a fictitious future date exactly as Kimball suggests. Moreover we can also derive an [IsLatest] field which is another field that Kimball recommend we use and also another field whose value cannot be constrained by the relational model. We have achieved the same as Kimball’s bunkum [SCDStartDate] & [SCDEndDate] table columns and still maintained the integrity of our data.

The counter argument to doing this is that Kimball’s method will be quicker because we don’t need to write an INNER JOIN to achieve the data that we want. Well that is true but ask yourself what is more important, querying speed or the integrity of your data? If your answer is '’querying speed’ then you should probably ask yourself why you’re bothering to use an RDBMS at all; data integrity is the reason that we build a relational data model. [That’s a pretty controversial opinion so I’ll look forward to debating it in the comments of this blog post :)]

If you absolutely need the [SCDEndDate] persisted somewhere then I’ll concede that the view resultset can be materialised into a table but really I don’t think there is that much need to do so; the main use of an [SCDEndDate] is to make your ETL easier and I’m sure its extremely rare that an ETL process cannot cope with the slightly increased querying time created by using the method I outline herein.

Conclusion

In this blog post I have outlined the problems that I believe are inherent in Kimball’s method of defining a time period for a slowly-changing-dimension record and also an alternative method that exhibits a purer method of achieving the same information. I welcome your thoughts in the comments below.

I have uploaded the script that I used herein so that you can try this technique for yourself. Download it from my Skydrive: http://cid-550f681dad532637.skydrive.live.com/self.aspx/Public/BlogShare/20091128/DebunkingKimballMethodEffectiveDates.sql

@Jamiet

P.S. There are some great debates going on in the comments section of this blog post so if you’re reading this using a blog aggregator/feedreader click through to http://sqlblog.com/blogs/jamie_thomson/archive/2009/11/28/debunking-kimball-effective-dates.aspx to read more opinions on this.

UPDATE: The debate continued over on KimballGroup.com (where all respondents disagreed with me, unsurprisingly).

Considering surrogate keys for Unknown members

jamiet — Wed, 14 Oct 2009 21:35:00 GMT

All data warehouses have the notion of an Unknown member somewhere in their make-up. That is, a dimension member to which we can map fact records when the member that we need to map to is, well, unknown. The canonical example of where the Unknown member becomes useful is in a retail environment where the retailer uses a customer loyalty card that enables them to track who purchased what items; if the customer does not have a loyalty card then they will get mapped to the unknown member in the Customer dimension.

Surrogate keys (SKs) are also particularly prevalent in data warehouses. SKs are meaningless values that get generated as part of the warehouse population process to uniquely identify a record in a dimension table and are usually integers (aside: Mladen Prajdić has a terrific discussion of the merits of SKs in a recent blog post at Why I prefer surrogate keys instead of natural keys in database design – don’t miss the comments).

It is common practice to pre-populate a dimension table with its Unknown member prior to the first real data population and moreover it is almost equally as common to artificially set that member’s surrogate key value to be –1. Indeed, Ralph Kimball even talks about doing this in his book “The Data Warehouse Toolkit”:

Map all the bad fact rows to the Unknown member for that dimension by supplying a key of –1 (or whatever your Unknown member key is)
Ralph Kimball, The Data Warehouse Toolkit

Now, that’s not quite a recommendation but it is very much in line with what is considered to be a well-known, consistent, convention.

Consistency is good. Convention is good. We like both of those things and indeed I have employed this practice of using –1 for the Unknown member surrogate key on many many projects in the last few years. Of late though I’ve started to question whether it really is a good idea and here I’ll explain where my doubt emanates from.

Meaningless meanings

The main issue I have with using –1 is quite simply this: in doing so you are giving meaning to something that is inherently supposed to be meaningless. The whole point of generating surrogate key values is that they don’t mean anything and therefore have no relevance to the real-world thing they are representing. By stipulating that -1=Unknown we are violating one of the justifications for using surrogate keys in the first place. That doesn’t sit easily with me.

Lax development

The common method used to discover SK values is to lookup against a dimension table using the natural key. Its not uncommon however for developers to say to themselves “I know that this is an unknown member so I won’t bother with a lookup, I’ll just hardcode –1 instead”. In other words the unknown members get treated differently to everything else; all of a sudden some inconsistency has crept into our system.

“What’s that?” I hear you say, “Inconsistency? You just told me that using –1 was supposed to be consistent?”

Hmmmm… Not only has this practice introduced some inconsistency but we’re hardcoding values into our code as well, another practice that makes me shudder slightly. Now, I will concede that this exhibits a lack of developer discipline rather than an inate problem with –1 values but still, removing temptation is no bad thing.

Ignoring half your range of values

When –1 is being used for the Unknown member you can be as sure as night following day that the data population process will start generating SK values from either 0 or 1 and count upwards. One question, WHY? What is so special about –1, 0 & 1? If you’re using 4-byte integer SKs then your range of values is –2147483648 to 2147483647 each of which is equally as meaningless as all the others so what’s the point in instantly disregarding half of them?

Again I’ll concede that this will create a problem in only a miniscule number of cases but still, if you have taken the time to choose a data type with a suitably large value range I don’t really see the point in excluding half of that range before you have even begun.

All that being said its hard to rail against common convention and for that reason I suspect I’ll continue to use –1 for Unknown members in the future because my colleagues will pressure me into it, I’ll just think twice about it before I do.

What say you dear reader? Have I managed to persuade you that using –1 is a bad idea?

@JamieT

UPDATE 2010-09-13: Almost a year on and I have a counter argument to the main argument that I put forward above. That is, a reason why using -1 to indicate Unknown might actually be a good thing.

On my current project we have to add a new dimension to an existing fact table. That fact table does of course have data in it and the new dimension column needs to be NOT NULLable - the typical way of handling this is to default the column to the Unknown member using a DEFAULT constraint. However, DEFAULT constraints cannot be defined with a SELECT statement to get the Unknown member surrogate key (an attempt to do so will return an error: "Subqueries are not allowed in this context. Only scalar expressions are allowed.") thus that Unknown member surrogate key needs to be a known value e.g. -1.

This limitation can be worked around using a ~~scalar~~ function, but its something else to think about when considering Unknown members and their surrogate keys.

Any other issues one should be considering?

Madison is shipping to TAP customers

AaronBertrand — Mon, 24 Aug 2009 19:06:00 GMT

They call it their new, massively scalable data warehousing solution; they say it can load 1TB per hour and support data warehouses in the petabyte range, all on commodity hardware. Bold claims, for sure, and I bet you're thinking, "I'll believe it when I see it."

If you can get on Microsoft's TAP list (TAP = Technical Adoption Program) and meet other undisclosed criteria, you can see it now. Otherwise, you'll have to wait until 2010H1. For more details, see:

http://blogs.technet.com/dataplatforminsider/archive/2009/08/24/microsoft-ships-the-first-technology-preview-for-project-code-named-madison.aspx

http://www.microsoft.com/sqlserver/2008/en/us/madison.aspx

Sample Chapter: "Building a Data Warehouse: With Examples in SQL Server", Chapter 1: "Introduction to Data Warehousing"

Sample Chapters — Sat, 15 Mar 2008 19:43:00 GMT

Go to File Gallery to Download

Building a Data Warehouse: With Examples in SQL Server describes how to build a data warehouse completely from scratch and shows practical examples on how to do it. Author Vincent Rainardi also describes some practical issues he has experienced that developers are likely to encounter in their first data warehousing project, along with solutions and advice. The RDBMS used in the examples is SQL Server; the version will not be an issue as long as the user has SQL Server 2005 or later.

The book is organized as follows. In the beginning of this book (Chapters 1 through 6), you learn how to build a data warehouse, for example, defining the architecture, understanding the methodology, gathering the requirements, designing the data models, and creating the databases. Then in Chapters 7 through 10, you learn how to populate the data warehouse, for example, extracting from source systems, loading the data stores, maintaining data quality, and utilizing the metadata. After you populate the data warehouse, in Chapters 11 through 15, you explore how to present data to users using reports and multidimensional databases and how to use the data in the data warehouse for business intelligence, customer relationship management, and other purposes. Chapters 16 and 17 wrap up the book: After you have built your data warehouse, before it can be released to production, you need to test it thoroughly. After your application is in production, you need to understand how to administer data warehouse operation.

What you’ll learn

A detailed understanding of what it takes to build a data warehouse
The implementation code in SQL Server to build the data warehouse
Dimensional modeling, data extraction methods, data warehouse loading, populating dimension and fact tables, data quality, data warehouse architecture, and database design
Practical data warehousing applications such as business intelligence reports, analytics applications, and customer relationship management

Who is this book for?

There are three audiences for the book. The first are the people who implement the data warehouse. This could be considered a field guide for them. The second is database users/admins who want to get a good understanding of what it would take to build a data warehouse. Finally, the third audience is managers who must make decisions about aspects of the data warehousing task before them and use the book to learn about these issues.

Go to File Gallery to Download

My Top 10 list for SQL Server 2008

ErinW — Sun, 20 Jan 2008 17:21:00 GMT

Long gone are the days when you can reasonably expect to know all aspects of SQL Server at a deep level. When a new version starts to emerge, I try to put some scope around the features that I will dive more deeply into. I'm publishing my list here so that like-minded SQL Server enthusiasts may become aware of a feature or two that was lost in that one-page Powerpoint slide you see in most of the presentations on SQL Server 2008.

Note: I put together this list during the summer as an roadmap for personal testing with the CTP releases. In December 2007, a whitepaper, "An Introduction to New Data Warehouse Scalability Features in SQL Server 2008", was released that includes a short writeup on each of these features, as well as some additional improvements to SSAS, SSIS and SSRS. If you are interested in the types of features I mention below, I highly recommend you check out this whitepaper.

My focus is on Business Intelligence and performance (particularly in terms of relational data warehouse performance). With that said and in no particular order, here is my list of top 10 features in SQL Server 2008, and why. I plan to post more detail about several of these in the future:

MERGE command - this is a new TSQL command that will allow you to combine an Insert with an Update command, sometimes referred to as an UPSERT. This is particularly useful in loading a data warehouse. You will no longer have to test for a row's existence in order to take one of two paths (INSERT or UPDATE, or even DELETE).
Star Join - this could have a tremendous impact on queries in a relational data warehouse. Data warehouse queries are characteristically performance hogs. Since a large percentage of data is usually selected, the query optimizer often cannot take advantage of indexes like it can with more selective OLTP queries.
Change Data Capture (CDC) - this feature can be used in SQL Server 2008 data sources to automatically track changes in data that require a row to be re-sent to a data warehouse. This makes ETL from a SQL Server 2008 data source far more efficient and straight-forward.
INSERT INTO - no structural changes have been made to the statement, but minimal logging can be implement, much like BULK INSERT or SELECT..INTO, under the right conditions.
Lookups in SSIS - though SSIS lookups were incredibly enhanced from DTS (where they were virtually unusable), the performance of this task has been tuned to improve performance and minimize resource utilization. In lieu of blogging about this later, I'll refer to a blog post by Jamie Thomson who explains this feature in detail and far better than I could.
Data compression - this is potentially huge! First of all, compression is an option, so if if the CPU hit is an issue you can choose not to implement compression. Data compression means more data on fewer pages, which has a domino effect on performance (more pages in memory, better memory utilization, and improved page life expectancy).
Partitioning enhancements - there's an issue in SQL Server 2005 partitioning parallelism that affects queries on a few number of partitions but greater than 1. If only one partition is queried, intra-partition parallelism is implemented effectively. If greater than one, a single thread is used to process each partition, which results in under-parallelism with queries on just a few partitions. This has been addressed in SQL Server 2008 (future post).
Resource Governor - if you haven't seen the demo on this, you should. You can implement resource governing rules that affect inflight queries. One scenario I see for this is in environments where ETL occurs simultaneously with data warehouse queries, such as in a real-time environment.
Backup compression - this will dramatically affect backup times for large data warehouses. This capability is currently available through third party vendors but some DBAs are currently unable to leverage these solutions due to company standards or budget constraints.
Partition-aligned indexed views (IVs) - this allows for the use of IVs on fact tables. Indexed views on partitioned tables is virtually unusable in SQL Server 2005 because of the requirement to drop and recreate dependent IVs whenever a fact table partition SWITCH is made. I'll explain why I think this is such a big deal in a future post.