Yes, Jeff, I saw that bit about it being based on CLR later in the day (although not from your post), but figured if they used CLR then they "MUST" have made that decision because, perhaps not unlike your 2K8 CSV Splitter function, it's the only thing "faster"... Boy did that turn out to be a bad assumption. Anyway, there's a ton of stuff I've learned over the last couple of days that makes things a LOT more clear for what I need to do. Here's what I found, and why I'm now tossing this query into the trash heap:

1.) The problem I was running into was that the existing query to populate the dimension was making mistakes on start and end times for every single leap day, where it was tossing in a date portion of 2/28, even while correctly creating the values for Hour_Quarter_ID as well as various other elements.

2.) That problem was exacerbated by a query that was pretty darned ugly, so I figured it was easier to start from scratch... mostly...

3.) Once I realized the primary reason for the query only messing up on 2/29, and that it was how the date was being constructed, I was then "sure" that I was on the right track by starting mostly fresh. That's the point at which I constructed the "new" query that used FORMAT ... kinda because I could, and it seemed "easier"... how little I knew. I suppose it should have been a clue that the original query ran faster than my "new" one... anyway...

4.) The date construction in the original query was messing up because it was constructing the date from a series of DATEADD functions, where the innermost one added a DAY value to date 0 (aka 1/1/1900). Bingo! This morning I had the insight that I could just switch the innermost and outermost DATEADDs and now the YEAR value one would be innermost, with the DAY value one outermost, and the MONTH one in the middle. This actually fixed the original query, and after a few other cleanup items and a fix to ensure a different problem with the HOUR dimension as source data didn't create havoc, I had a query that instead of running in about a minute on the hardware it has to run on, it now runs in about 45 seconds.

5.) As that "fixes" the issue, I'm probably good, but I'm going to look at the various other posts here that refer to maintaining integer math and their timed performance runs and apply as much of that as I can to the "fixed" query. Some of the improvements over FORMAT are positively STUNNING!

6.) One person posted about whether such additional elements as those referencing prior periods are necessary. In my case, they actually are, as there are no actual cubes being built on these dimensions yet, and it may be quite a while if this DIM is any indicator of what else I might run into. A reporting tool is using the dimension tables for dashboard reporting, and I know it's not creating cubes, ... at least not in the traditional sense anyway.

Many thanks to everyone that provided posts here... Once again, I've learned a ton from this one.