5

Until now, we have used stored procedures for data massaging and cleansing but we have now decided to move to using Data Quality Services (DQS) and Master Data Management (MDS) in our data warehouse projects.

There is one very common function which we use in our stored procedures - it trims and replaces the , character with white space e.g.:

UPDATE [Table] SET Column=(ltrim(rtrim(replace(Column,',',''))))

I have spent hours to figure out a way to achieve this using DQS and MDM but couldn't find any way to achieve the same goal.

I would really appreciate if you guys can help me out with this. In fact, thank you already for your precious time.

1

I think if you can use SQL Server 2017, this new function Trim() Can help you write it better. if you insist on using DQS for this matter, I think this article can help you find your way. Sorry that I couldn't write it for you. Hope it helps.

0

I am sorry as I couldn't reply earlier. I figured DQS trims the data automatically and output trimmed column, so every column you want to be trimmed you need to pass it through DQS.

For replacing a special character in a column we can use MDS user scripts in business rules. We can make our custom SP and call it in MDS UI just as any massaging SP's.

CREATE PROCEDURE [usr].[SetCodeNameDerivedValue] (@MemberIdList mdm.[MemberId] READONLY -- memberID is a user defined table data type. -- This parameter will pass along a list of members for which the action needs to run. ,@ModelName NVARCHAR(MAX) ,@VersionName NVARCHAR(MAX) ,@EntityName NVARCHAR(MAX) ,@BusinessRuleName NVARCHAR(MAX) ) AS BEGIN DECLARE @Code NVARCHAR(MAX) = '0'; WHILE(1 = 1) BEGIN SELECT @Code = MIN(Code) FROM @MemberIdList WHERE Code > @Code IF @Code IS NULL Begin Break; End Else Begin INSERT INTO [stg].[Entity_Leaf] (ImportType ,BatchTag ,Code ,CodeNameDerivedColumn) SELECT 0 -- import type 0 = inserting or updating members ,N'Business Rule Extension Test' ,Code -- code of the member, supplied by @MemberIdList ,'A'+Code+LEFT(REPLACE(Name, ',',''),5) FROM mdm.EntityView where code = @Code; End END -- run the staging batch job to process the staging records EXEC [stg].[udp_Entity_Leaf] @VersionName = @VersionName ,@BatchTag = N'Business Rule Extension Test'; END

Here is the link to make custom/extended business rules in MDS Business Rule Extension in Master data services

The problem is that the list MDS pass to this SP only contains code values of subjected records and no other attribute is passed in @MemberIDList variable, So, I have to bring other attributes from MDS entity subscription views. Also, I needed to use a while loop, which is working as foreach loop in this scenerio and would run only for those records which are passed in this SP. I could have not used the loop, but as we know that, MDS sometimes apply business rules in batches and sometime only for a single record updated, we don't want to run cleansing on whole table, every time the record is changed.

I think, this feature of MDS is amazing, as we can achieve almost anything, what we would be doing in massaging SP's, though it has certain limitations as well.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.