MDS: How to truncate an Entity-table?

Here’s how you can quickly (with using the GUI or Excel Add-in) truncate an Entity-table in MDS by using the accompanied Staging-table (stg_*):

MDS-truncate-enitity-table









MDS needs the Code-ids together with the ImportType = 4 to delete records via staging.
Basically the first step is to populate the staging table by using the MDS view (which contains the current set of records and thus the Code-ids), after that you’ll need to execute the accompanied MDS stored procedure to process these changes.
Important: make sure that both BatchTag’s are identical!



Here’s the T-SQL code:

TRUNCATE TABLE stg.[TableName_Leaf]

INSERT INTO stg.[TableName_Leaf] (ImportType,ImportStatus_ID,BatchTag,Code)
SELECT
4 AS ImportType
,0 AS ImportStatus_ID
,'Delete-20151102' AS BatchTag
,Code
FROM mdm.JOBS_TableName

SELECT * FROM stg.[TableName_Leaf]

EXEC stg.[udp_TableName_Leaf] @VersionName = 'VERSION_1', @LogFlag = 0, @BatchTag = 'Delete-20151102'



More info about working with MDS staging tables (and ETL):
http://www.sqlchick.com/entries/2013/2/16/importing-data-into-master-data-services-2012-part-1.html
http://www.mattmasson.com/2013/06/bulk-loading-into-mds-using-ssis/
http://www.sqlservercentral.com/blogs/simon_says/2014/01/28/master-data-services-for-the-beginner-part-03/








Advertisements

How to create a view (with end-date) for an INSERT-ONLY SCD Type 2 table?

Well Albert Garcia Diaz wrote a great example for that.
He uses LEAD() combined with the OVER-clause to easily accomplish this:
“…LEAD provides access to a row at a given physical offset that follows the current row…”

More info about LEAD():
https://msdn.microsoft.com/en-us/library/hh213125.aspx


CREATE VIEW [hstg].[vw_Table_History]
AS
SELECT
ETL_EXECUTION_DT AS START_DATE,
ISNULL(LEAD(ETL_EXECUTION_DT) OVER (PARTITION BY BusinessKey ORDER BY ETL_EXECUTION_DT), '9999-12-31 00:00:00.0000000') AS END_DATE,
[CURRENT_RECORD] = CASE
WHEN LEAD(ETL_EXECUTION_DT) OVER (PARTITION BY BusinessKey ORDER BY ETL_EXECUTION_DT) IS NULL THEN 'Y'
ELSE 'N'
END,

[BusinessKey],
[Field1],
[Field2],

ETL_BATCH_DT AS ETL_ROW_START_DT,
S.ETL_BATCH_ROW_ID,
CONVERT (DATETIME, '99991231') AS ETL_ROW_END_DT,
ETL_ROW_HASH,
CONVERT (NVARCHAR (512), 'AdvWorks') AS ETL_ROW_SOURCE,
ETL_CDC_OPERATION,
ETL_EXECUTION_DT
FROM [hstg].[Table] S








Handy stored procedure to delete old ETL ‘Error’-records based on a retention period (days)

Here’s a handy stored procedure to delete old ETL ‘Error’-records based on a retention period (days):


-- =============================================
-- Author: Clint Huijbers
-- Create date: 2014-08-02
-- Description: This SP is used to delete old Error-records saved by the ETL-process.
-- =============================================
CREATE PROCEDURE [dbo].[usp_DeleteOldErrorRecords]
(
@NrOfDays INT
)
AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;

DECLARE @SQL VARCHAR(800) --SQL query to execute

IF (@NrOfDays IS NULL) SET @NrOfDays = 7 --Default retention period

DECLARE CursorName CURSOR FOR
SELECT 'DELETE FROM [dbo].['+Name+'] WHERE [ProcessDate] < DATEADD(DAY,-'+CAST(@NrOfDays AS VARCHAR(10))+',GETDATE())' AS SQLStatement
FROM sys.tables
WHERE is_ms_shipped = 0
AND Name LIKE '%_Errors'

OPEN CursorName
FETCH NEXT FROM CursorName INTO @SQL
WHILE (@@fetch_status = 0)
BEGIN

EXEC(@SQL)

PRINT @SQL+' --- Executed'

FETCH NEXT FROM CursorName INTO @SQL
END

CLOSE CursorName
DEALLOCATE CursorName








DateTimeOffset to Date Time Offset (Dimension) Identities

These SQL functions might come in handy for your ETL and DWH.
I’m currently designing a DWH which will have three ‘TimeDimensions’:
DateDimension (grain = day)
TimeDimension (grain = second)
TimeZoneDimension (grain = offset in minutes)

Simply said, these functions convert a DATETIMEOFFSET value into three types of integer identity values (which can be used in your dimensions).

DateTimeOffset to Date Time Offset Identities - Results

Want it? 🙂
Download it here:
DateTimeOffset to Date Time Offset Identities – SQL Code