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/

 


 


 

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/








MDS database upgrade failed, ‘user does not have access to the application’ (db version 11.2.0.5)

Yesterday a colleague upgraded our reporting server (SQL Server 2012 SP1 CU6) to CU7 (build: 11.0.5623), which should be stable since it is out for like a month now.
(More info aboud builds? Check out: http://sqlserverbuilds.blogspot.nl/)

As always MDS requires a database upgrade (because we’ve upgraded the ‘client‘ to a new version, as in ‘the SQL Server instance‘):

MDS-database-requires-upgrade-11-2-0-5-GUI


 


 


 

Sure, let’s do that.

So I RPD-ed into our reporting server. Right-click on the ‘Master Data Services Configuration Manager‘-icon in the start-menu and clicked ‘Run as Administator‘ (important one!).
And indeed, it does require an upgrade 🙂

MDS-client-version-incompatible-with-database-error-11-2-0-5-GUI


 


 


 


 


 

Next step is to select the correct database and hit the ‘Upgrade‘-button….but…it threw me an exception:

MDS-upgrade-wizard-exception-error-user-does-not-have-access


 

Hit ‘OK‘ and ‘Cancel‘, notice that the database version jumped to ‘11.2.0.6‘?:

MDS-database-version-11-2-0-6-GUI


 


 


 

What I found was that whatever you try next, the wizard keeps throwing the same exception over and over again when you restore the MDS database and try again.

The thing is that, after a while I just ignored the exception and went to the MDS interface with IE and somehow things are working properly.

 

Conclusion:
So, when you’re upgrading the MDS database from ‘11.2.0.5‘ to ‘11.2.0.6‘ ignore the following exception from the wizard:
‘…The user does not have access to the application…’

Because the user does have access and the script did run correctly 🙂


 


 

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/








MDS: How to change the data type in an existing Entity (filled table)

Want to change the data type of an existing Entity’s attribute without reloading of re-creating that entity?

This is just a little trick you should remember, you’re unable to change the data type via the MDS web interface (GUI):

Unable to change the data type in MDS



However, this is possible in the MDS Excel Add-in! 🙂

MDS change attribute data type in excel unable via webinterface gui



Here’s how you could you could do it:

1) Open Excel, connect to MDS and retrieve the complete set of data from MDS for that specific Entity

2) Select a random cel in the column of the attribute of which you would like to change the attibute’s data type.

3) In the MDS-tab in Excel, click on ‘Attribute Properties‘ and simply change the data type into the type you want.
After that it will publish the update and give some kind of message that you need to update your subscription. Sorry my message was in Dutch so…but simply said you need to refresh/rec-create the View was made under ‘Integration Management‘ in MDS.

4) Go to ‘Integration Management‘ in MDS (web interface or GUI), click on ‘Create Views‘ and Edit that specific view. When in Edit-mode simply clicking on the Save-button will do the trick.

5) Verify in SSMS that that view now contains the changed data type (in the ‘MDS’ database on the SQL Server instance).

That’s it!
Now don’t forget that when you’re using SSIS to ETL the data…it requires a meta-data update 😉