Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 

Master Data Services for the Beginner Part 01

 

Introduction

About 12 months ago, I started to look at Master Data Services in a serious manner. I had toyed with the product in SQL Server 2008 R2 but (like others did) found it very awkward to work with and difficult to understand. Microsoft has done a super job with this version and this sparked me into picking up the product once again, test driving it and MORE IMPORTANTLY try to find some use for the product within our production environment.

As many of you know, I presented a paper at the PASS Amsterdam Rally, last November on this topic HOWEVER it was not until last August that I actually found a great use for the product, within our financial department.

Our financial systems run off of third party software, with a SQL Server database as the data repository. Last August, the CFO came to me mentioning that she wanted to add four fields (which her staff would constantly be updating) to the third party software and she was wondering if we could make the changes, in-house to avoid the costly changes to the front end.

The light went on.

In this series of articles, we shall be looking at Master Data Services from soup to nuts and how to assemble a quick and dirty web based Master Data Services front end, how to control security of who sees what, how to control the ‘updateability’ of one’s data columns shown within the Master Data Manager Explorer Function and how to develop and implement daily load and update processes.

At the end of the day….. we want to get from this

MDS for the beginner part011

to this!

MDS for the beginner part012a

The ordinary user is only able to update the color field.  ALL OTHER FIELDS ARE READ ONLY for ordinary users!!!
The data admin is able to update other fields. Further, we can permit John to update columns 1, 3 and 5, whilst Sue is able to update columns 2,4, and 6. In a further edition of this article I shall be showing you how to set this up.

Getting Started

We have a contract with ACME Super Application Maintenance who has updated and maintained the financial systems since Noah was a little lad.

This said and knowing the complexity of the front end and the related views, I shied away from effecting any changes to the application itself. While we draw our new raw data from the system each day, once in Master Data Services, the data is stored in MDS on another server. The staging tables for the daily loads and the stored procedures that run the updates are also stored on the server hosting the MDS database.

To install MDS on your server you must have opted to install the product during installation of your SQL Server instance. Enterprise or BI versions of SQL 2012 are required. Once installed we invoke the Master Data Services Configuration Manager. (See the screen dump below)

MDS for the beginner part013

Having invoked the Master Data Services Configuration manager we are presented with the following screen.

MDS for the beginner part014

The astute reader will note that both Windows PowerShell and IIS 7  or greater must be present. If NOT you will be faced with a really cryptic error message that you will realize really wants you to configure IIS correctly and this is done via executing a application within your windows environment. Should you need the link to the fix, please let me know.

We now must create and configure the MDS database. Upon creation of this database, the database will appear within the list of databases within SSMS. You must simply choose the ‘Create Database’ option, see below:

MDS for the beginner part015

We must now configure the website as MDS is a web based application.

MDS for the beginner part016

Simply click on the website drop down box and choose create a new website. The ‘create website screen’ will then appear.

MDS for the beginner part017

Opt for all the defaults. You may find that your port 80 is already being used and that you will have to choose another port number. I normally choose between 8079 and 8081 if necessary. I do NOT complete the Host Header Box.

PLEASE NOTE that you must enter a User Name and Password on the bottom of this screen. This controls access to the Master Data Services Explorer and all the other screens necessary to our application.

I CHOSE TO USE A PROCESS ID WITH A NON EXPIRING PASSWORD, as our passwords change each six weeks. This will play havoc to your production environment (with an expiring password) as you will have to re-configure your website every 6 weeks (in my case). The password should be the same as the one that you are using for the SQL Server Agent. I just found that was the easiest solution and guaranteed uniformity.

Once you have completed the web configuration portion of the process you are all set and the installation process will ask you if you now want to proceed to the Master Data Services application.

Click yes.

You will find yourself on this web page.

MDS for the beginner part018

Click ‘Open the Master Data Manager home page’ (as shown in red above). You will now find yourself on the main Master Data Manager screen where our work REALLY BEGINS!!

MDS for the beginner part019

In the next installment of this series we shall learn about the purpose of and how to construct Master Data Services Entities, Attributes and Hierarchies and see how data elements may be selectively updated and more importantly how we may lock down fields that we do not want altered.

As always should you have any questions or comments, please feel free to contact me at steve.simon@sqlpass.org

Happy Programming.

Comments

Posted by Carlos Sacristan on 29 January 2014

Why do you set the same password as the SQL Agent user? Is there a problem if you don't do that?

Posted by Stefaans on 29 January 2014

Hi, The web portion of Master Data Services is set only once per installation and the user ID and password (a proxy) are deeply hidden and NOT accessible to the users. In response to YOUR question: the issue is that should the password expire, then users are not able to bring up the Master Data Manager. Therefore whatever ID is used, it must NOT have an expiring password.

As with any other secure web application, we user windows authentication to access the server. This said and to avoid a plethora of secure ID's being propagated we decide to use the secure process ID ( with a non-expiring password) that is used by the SQL Server Agent (which is unique to this server). This was a GREAT question and I hope that this explanation helps.

Leave a Comment

Please register or log in to leave a comment.