Degenerate dimensions in VSTS DB edition GDR R2 (a.k.a. TSD03136)

This is a common pattern in many BI projects that I’ve been involved with in the past four or five years for deriving degenerate dimensions without expending the effort to build and maintain star schema or snowflake tables in a data mart or data warehouse. (I’m not passing judgment, just making an observation. I’m ambivalent about them.)

CREATE VIEW [dbo].[SomeNewDegenerateDimension]
AS
SELECT DISTINCT([OneColumnOrAnother])
FROM [dbo].[SomeFactTableOrViewOrAnother]

GO

I know that they didn’t work in Data Dude before, but I had hopes that GDR R2 would’ve fixed it. GDR R2 is still fun to say. Yeah, it’s been that kind of week. In the immortal words of the Dread Pirate Wesley, “Get used to disappointment.”

Fortunately, it’s an easy fix. Unless you’ve got ~750 of them to fix in a large project that you’re trying to import into source control for change management and team development purposes... Just copy-n-paste the column name after an AS at the end of the SELECT clause.

CREATE VIEW [dbo].[SomeNewDegenerateDimension]
AS
SELECT DISTINCT([OneColumnOrAnother]) AS [OneColumnOrAnother]
FROM [dbo].[SomeFactTableOrViewOrAnother]

GO

In other news, I forgot today was the department summer party. Everybody else is @ the beach. I’m jousting with Visual Studio. In the immortal words of Dilbert, “Yay, life!”