How to automatically export/backup the MDS model

I noticed that I was making backups of the MDS model manually, so it’s time to automate that in a basic but effective way 🙂
When using CMD, it’s always tricky to get the correct timestamp (which I’ll use in the output filename). If found a nice script on: https://stackoverflow.com/questions/1064557/creating-a-file-name-as-a-timestamp-in-a-batch-job

Normally when you would like to import or export/backup a MDS model, you’ll use these scripts:

CREATE:
CD C:\Program Files\Microsoft SQL Server\130\Master Data Services\Configuration
MDSModelDeploy createpackage -service MDS1 -model "Biml meta-data" -version "VERSION_1" -package "E:\MDS\BimlMetaData_20170804.pkg" -includedata

DEPLOY:
CD C:\Program Files\Microsoft SQL Server\130\Master Data Services\Configuration
MDSModelDeploy deployclone -package "E:\MDS\BimlMetaData_20170804.pkg" -service MDS1

 

To automate this, create a BATCH-file (*.bat) in your local ‘MDS backup directory’ (in my case ‘E:\MDS\’) and copy/paste the following script into it:

:: THIS CODE WILL DISPLAY A 2-DIGIT TIMESTAMP FOR USE IN APPENDING FILENAMES

:: CREATE VARIABLE %TIMESTAMP%

for /f "tokens=1-8 delims=.:/-, " %%i in ('echo exit^|cmd /q /k"prompt $D $T"') do (
for /f "tokens=2-4 delims=/-,() skip=1" %%a in ('echo.^|date') do (
set dow=%%i
set mm=%%j
set dd=%%k
set yy=%%l
set hh=%%m
set min=%%n
set sec=%%o
set hsec=%%p
)
)

:: ensure that hour is always 2 digits

if %hh%==0 set hh=00
if %hh%==1 set hh=01
if %hh%==2 set hh=02
if %hh%==3 set hh=03
if %hh%==4 set hh=04
if %hh%==5 set hh=05
if %hh%==6 set hh=06
if %hh%==7 set hh=07
if %hh%==8 set hh=08
if %hh%==9 set hh=09

:: assign timeStamp:
:: Add the date and time parameters as necessary - " yy-mm-dd-dow-min-sec-hsec "

set timeStamp=%yy%%mm%%dd%_%hh%%min%%sec%

:: --------- TIME STAMP DIAGNOSTICS -------------------------

:: Un-comment these lines to test output

:: echo dayOfWeek = %dow%
:: echo year = %yy%
:: echo month = %mm%
:: echo day = %dd%
:: echo hour = %hh%
:: echo minute = %min%
:: echo second = %sec%
:: echo hundredthsSecond = %hsec%
:: echo.
:: echo Hello!
:: echo Today is %dow%, %mm%/%dd%.
:: echo.
:: echo Your timestamp will look like this: %timeStamp%
:: echo.
:: echo.
:: echo.
:: pause

CD C:\Program Files\Microsoft SQL Server\130\Master Data Services\Configuration

MDSModelDeploy createpackage -service MDS1 -model "Biml meta-data" -version "VERSION_1" -package "E:\MDS\BimlMetaData_%timeStamp%.pkg" -includedata


 

IMPORTANT: Make sure that you change the ‘MDS backup directory’ in the code above.

Now create a scheduled task (standard Windows functionality) that executes the BATCH-file and runs either on a specific time and/or system startup:

 



 



 

 


 


 


 


 


 


 


 


 


 


 


 


 

Other handy scripts:
PowerShell: Delete old files based on a given retention period (days) #maintenance

Use PowerShell to script all database objects

 


 


 

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‘):

 


 


 

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 🙂

 


 


 


 


 

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

 

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

 


 


 

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).

(Yep I’m Dutch)

But whatever…if it works….it works 🙂

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

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