Actually you can do both. Put all the fields you wish to carry history on in the ON clause for matching. This will tell the MERGE you want a new record any time there is something that doesn't match, which is when you have new information in a field you wish to carry history on. For update only fields put an update clause on the fields you wish to update in the when matched clause. For static fields, just leave them in the insert. Don't make them part of the update.
Easier if I just show you the template I use on my MERGE statements:
--JA: MERGE source with destination
MERGE dbo.DimCustomer Destination
USING --JA: source
(
SELECT
DC.[HistoryCol1]
,DC.[UpdateOnlyCol1]
,DC.[HistoryCol2]
,DC.[HistoryCol3]
,DC.[UpdateOnlyCol2]
,DC.[StaticCol1]
FROM dbo.DimCustomerStage DC
)
AS [Source]
(
[HistoryCol1]
,[UpdateOnlyCol1]
,[HistoryCol2]
,[HistoryCol3]
,[UpdateOnlyCol2]
,[StaticCol1]
)
ON (
[Source].[HistoryCol1]= Destination.[HistoryCol1]
AND [Source].[HistoryCol2]= Destination.[HistoryCol2]
AND [Source].[HistoryCol3]= Destination.[HistoryCol3]
)
WHEN MATCHED --JA: here i specify that in order to qualify for update, the binary sum of the two rows must not be equal (a change has occured in source) - checksum is faster than checking every column individually
AND BINARY_CHECKSUM
(
Destination.[UpdateOnlyCol1]
,Destination.[UpdateOnlyCol2]
)
!= BINARY_CHECKSUM
(
[Source].[UpdateOnlyCol1]
,[Source].[UpdateOnlyCol2]
)
THEN UPDATE SET --JA: put only the columns you wish to update here - less = better performance
Destination.[UpdateOnlyCol1]= [Source].[UpdateOnlyCol1]
,Destination.[UpdateOnlyCol2]= [Source].[UpdateOnlyCol2]
WHEN NOT MATCHED BY TARGET THEN --JA: when no matching id can be found, insert
INSERT
(
[HistoryCol1]
,[UpdateOnlyCol1]
,[HistoryCol2]
,[HistoryCol3]
,[UpdateOnlyCol2]
,[StaticCol1]
)
VALUES
(
[Source].[HistoryCol1]
,[Source].[UpdateOnlyCol1]
,[Source].[HistoryCol2]
,[Source].[HistoryCol3]
,[Source].[UpdateOnlyCol2]
,[Source].[StaticCol1]
)
; --JA: MERGE must be terminated by a semicolon
Also if you plan on cubing your warehouse I've been told by Will Brown, a Microsoft expert in SSAS, you get some performance benefit from snowflaking your warehouse because then the cubes you build from it don't have to process as many distincts during cube processing. I guess it depends on how much of your querying will be via your cube versus directly from your warehouse.
-------------------------------------------------------------------------------------------------
My SQL Server Blog