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

to this!

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)

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

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:

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

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

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.

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!!

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.