How to: Delete records from your MDS Entity table

Here’s an example script which you could use to delete records from your MDS Entity/table:

Delete records from your MDS entity table

 

Here’s the code:


--Start clean:
TRUNCATE TABLE stg.[Actuals_Leaf_Leaf]

--Retrieve CodeID's:
INSERT INTO stg.[Actuals_Leaf_Leaf] (ImportType,ImportStatus_ID,BatchTag,Code)
SELECT
4 AS ImportType
,0 AS ImportStatus_ID
,'Delete-20160310' AS BatchTag
,Code
FROM mdm.Actuals_Leaf a
WHERE a.Year = 2016
AND a.Month = 2

--Verify:
SELECT * FROM stg.[Actuals_Leaf_Leaf]

--Execute:
EXEC stg.[udp_Actuals_Leaf_Leaf] @VersionName = 'VERSION_1', @LogFlag = 0, @BatchTag = 'Delete-20160310'

--Verify results:
SELECT COUNT(*)
FROM mdm.Actuals_Leaf a
WHERE a.Year = 2016
AND a.Month = 2

--End clean:
TRUNCATE TABLE stg.[Actuals_Leaf_Leaf]

 

More info on: https://clinthuijbers.wordpress.com/2015/11/02/mds-how-to-truncate-an-entity-table/

 


 


 

The use and misuse of Master Data Services (MDS) in SQL Server

After watching the SQL Hangout (Hangout #27 Master Data Management?), something told me to also shed light on other ways to use and misuse MDS.

Let start with the definition of MDS (by Microsoft):

Master Data Services (MDS) is the SQL Server solution for master data management. Master data management (MDM) describes the efforts made by an organization to discover and define non-transactional lists of data, with the goal of compiling maintainable master lists…The result of a successful MDM solution is reliable, centralized data that can be analyzed, resulting in better business decisions.

So…master data….check! You may immediatly think about a single version of the truth regarding customers, products, etc.
But in practice I found out that thanks to its brilliant (spoiler: a bit slow in 2012, but should be fixed in 2016) Excel add-in, business users can easily upload and maintain fairly static datasets. The web interface is horrible, you really need to get used to the way it’s been arranged. I still have figured out how the versioning-system works btw, but just create a subscription view and then use it as a source for your DWH. Beats building SSIS packages for each and every Excel-sheet the business user wants to ‘upload’.

Business users of my current client are use MDS for:
Master Data (list of branches, clusters, geo-locations, etc.)
Hierarchies (GL account, e.g. Profit & Loss or Balance sheet)
Targets for SSRS reporting (e.g. min/max target values per branch for a certain KPI)
Figures which are uploaded by the business, like:
* Actuals (e.g. illness rates, number of FTE’s, etc.)
* Budgets (e.g. P&L, etc.)
* Forecasts (e.g. P&L, etc.)

MDS wasn’t build for this and it isn’t fast when you want to publish upload or retrieve 295.356 records (via the MDS Excel Add-In).

MDS - Many many records
(Yep I’m Dutch)

But whatever…if it works….it works 🙂

An example of our list of views (MDS subscription views):

Use and misuse of Master Data Services 2012 - MDS


























Want to learn more about MDS? Follow Kenneth Nielsen‘s blog:
http://funkylab.com/tag/mds/