I am sure this is well known by most of you, but I am a noob! and want to help other noobs out there that are like me.

I needed to Create a new Cube today, and in the process I figured we needed an ‘UNKNOWN’ row for all those Unknown situations that could pop up in a Pivot table. Well, when you create a Cube, most of your Dimension and Fact tables are driven off of Keys that you set as Identity. Or at least this is how MS came to our shop and showed us how to do it.

Now, the problem here is Identity is READ-ONLY so our staging tables and Views could not properly add a ‘UNKNOWN’ with a regular INSERT when trying to force that Identity value to -1. Why negative one? So that you will ALWAYS know that this is either an UNKNOWN or N/A.

So, here is what we did within the initial load of one of the Dimension tables:

TRUNCATE TABLE PM_DWT.dbo.Dim_Partnership
SET IDENTITY_INSERT PM_DWT.dbo.Dim_Partnership ON
GO
INSERT INTO PM_DWT.dbo.Dim_Partnership (Partnership_Key,Partner_ID,Partnership_Name)
VALUES (-1,-1,'N/A')
GO
SET IDENTITY_INSERT PM_DWT.dbo.Dim_Partnership OFF
GO
DBCC CHECKIDENT (Dim_Partnership, RESEED, 0)
GO
INSERT INTO PM_DWT.dbo.Dim_Partnership (Partner_ID, Partnership_Name)
SELECT
Partner_ID
,Partner_Name
FROM PM_Cluster.dbo.v_Dim_Partnership
ORDER BY Partner_ID
GO

The above code will insert your first record for -1 and UNKNOWN. Then it will enable the Identity column again. What happens here though, is the Identity column see an entry, so it naturally wants to add one, so that your next value inserted will actually have 2. We want to force it to restart even though we inserted a record.

DBCC CHECKIDENT (Dim_Partnership, RESEED, 0)
GO

Will do just that…so that the main INSERT will now begin at 1.