Master Data Services for the Beginner Part 04

Introduction

In yesterday’s discussion we looked at populating entities and their related attributes. In today’s discussion we shall be concentrating on extracting data from Master Data Services, via views.

Getting started

We start off by bringing up the Master Data Manager in the normal manner.

Once again, my link is http://r9-wxl90:8081/default.aspx   and yours should be similar however with different server and port.

Setting up a current flag

Prior to starting, we need to understand one ‘quirky’ concept which I have yet to fathom, HOWEVER this one DID get me!!!  In a nut shell, one of the reasons for utilizing Master Data Services is to permit users to update certain data fields.

Ceteris paribus, when you look at your data after having updated a field within Explorer all looks well. Should you then wish to pull the data, to be used say in Reporting Services, lo and behold, the data displayed has NOT been updated but rather is the data as it was initially loaded. This is perhaps an over simplification, meant to get the point across.

In order to extract data from MDS on a WYSIWYG basis, we need to create a ‘Current’ flag within Master Data Services AND THEN when creating a view, associate this ‘Current’ flag with the view itself. Thus what you are saying to Master Data Services is, ‘please pull the data as it currently is’.

To create the ‘Current’ Flag, choose Version Management from the home page.

MDS for the beginner part0401

The Version Management maintenance screen will appear.

Choose Manage Flags

MDS for the beginner part0402

The Manage Flags maintenance page will appear(See above).

MDS for the beginner part0403

Simply click the Green Plus Sign to add a Current Flag.

MDS for the beginner part0404

Select the Model for which you are creating this ‘Current’ Flag. The TYPE ‘Current’ into the name text box and optionally add a description. Set the ‘Committed Versions Only’ flag to false. Click ‘Save’ and we are all set.

We are now brought back to the Manager Version Maintenance Screen. What we now need to do is to select ‘Versions’ from the ‘Manage’ drop down box. Why? To associate our new ‘Current’ flag with the version. Double click the ‘Flag’ cell as shown below. You will note that the ‘Current’ flag is displayed. Click on ‘Current’ to ensure that it populates the cell.

MDS for the beginner part0405

Then click anywhere on the screen and the box / cell should be all set (as may be seen below).

MDS for the beginner part0406

We are now in a position to create our first view.

Creating our first view

MDS for the beginner part0407

Click on Integration Management.

MDS for the beginner part0408

Click on Create View (which may be found to the right of the BLUE ‘Import Data’ tab). You will be shown the create subscription view screen. You will note from my screen dump below, that I have created many views and rather than delete them to create a clean screen dump (for this article), I ask that you imagine that there is nothing there at all, for the minute 🙂

MDS for the beginner part0410

Once again, click on the green plus arrow.

The ‘Create Subscription View’ maintenance screen will appear.

MDS for the beginner part0411

Simply, enter a name for your view (in my case I called it ‘MyArticleProducts’), the model name, and choose the ‘Current’ Flag option(see the screen dump above). In the entity drop down box choose the product entity and in the format drop down box, choose ‘Leaf members’.

Then ‘Save’ the subscription view by clicking the ‘diskette’. You will be returned back to the Subscription View maintenance screen.

Our View

To confirm that you have in fact created the necessary view, let us go into SQL Server Management Studio and open the MDS database (your name for YOUR MDS database may be different depending upon what you called it during set up.. please refer to the first part of this series).

MDS for the beginner part0412

Upon opening the views, you will note that the view has in fact been created for you (see the highlighted area above) and in fact we can see a few records below:

MDS for the beginner part0413

REMEMBER, now that we have associated this view with the ‘Current’ flag, the data rendered via the view, is in fact the latest and greatest 🙂

MDS for the beginner part0414

You will also note that for those fields that were ‘Domain Based’ (see yesterday’s blog), both the ‘code’ (key) and the name(attribute value) are visible. A case at hand is the field “Available to outlet” (see above).

Wrapping up

Today we have seen

1)      How to create the ‘Current’ flag and the reason why we require this flag.
2)      We created and configured a subscription view, to be used for general decision making OR to be used with SQL Server Reporting Services.
3)      We ran a quick query from the view that we created.

In the next part of this article, we shall be starting off in SQL Server Reporting Services and we shall see how our data may be utilized.
As always, should you have any questions, comments or concerns, please feel free to contact me at steve.simon@sqlpass.org

Happy Programming

Master Data Services for the Beginner Part 03

Introduction

In yesterday’s discussion we looked at creating entities and their related attributes. We learnt that a ‘model’ could be very loosely compared to a database, an entity to a table and attributes to the fields of a table.

Today we are going to load the raw data(member) into our entities and look at all the data within Master Data Services Explorer.

In loading the data, we shall be using a spreadsheet source and SSIS to load the entities. Should you want the packages used in our examples, please feel free to contact me at steve.simon@sqlpass.org

Some background

When we create an entity as we did yesterday, and we save that entity, Master Data Services (unbeknown to us) creates an appropriate staging table in the background. Raw data to be loaded into Master Data Service from no matter what the source, must first pass through this staging table. The original source data may be data from a SQL Server table or perhaps from an Excel spreadsheet. My users tend to bulk insert via a spreadsheet so this is the method that I plan to utilize in this discussion.

The raw data for ‘color’ may be seen below:

MDS for the beginner part031

Let’s look at the various columns within this spreadsheet.

Note that in column “D” and “E” are the code and name. As mentioned in my previous post, the “key” as we know it from the normal table concept is the “code”. The value of the key is the name.

There are three other weird fields. Those are columns “A”, “B”, and “C”. These are three system required fields in order to insert the data correctly and there usage will become more apparent a bit later.

The ‘ImportType’ is the most critical. An import type of ‘1’ refers to an insert. A ‘2’ indicates an update, an import type of 4 is a hard delete (which we shall talk about in detail when we discuss daily loads). For a complete list of ‘ImportType’ s the user is referred to the following URL.

http://technet.microsoft.com/en-us/library/ee633854.aspx

The import status ID (located in column B) tells us the status of the records that we are trying to insert.

The status of the import process. Possible values are:

  • 0, which you specify to indicate that the record is ready for staging.
  • 1, which is automatically assigned and indicates that the staging process for the record has succeeded.
  • 2, which is automatically assigned and indicates that the staging process for the record has failed.

Last, but not least is column “C” which contains the batch tag. This will become more important to us (as we progress in this series of presentations)  as we shall be using the same ‘BatchTag’ over and over again when we discuss the production nightly loads (in a following portion of the article).

Now that we have our source data ‘file’, it is time to put together an SSIS package to insert the data (from the spreadsheet) into the Staging table. The staging table (stg.Color_Leaf), created by Master Data Services, may be seen below:

MDS for the beginner part032

Note that the table has a few ‘extra’ fields that we have not discussed. These fields are not required to stage the data.

Creating our load package.

I have taken the liberty of showing (below), a screen dump of my load package.

MDS for the beginner part033

Note that the package also contains ‘data flows’ for Types and Products. We shall be looking at ‘Color’ in our example. The process to load other entities is similar to what I am currently describing.

Notice that to access the sources spreadsheet, I have used an “Excel connection manager” and for the destination staging table, an OLE DB destination connection.

The data flow is shown below:

MDS for the beginner part034

Having run this portion of the package, one will see the following data within the staging table.

MDS for the beginner part035

The astute reader will note that in the screen dump above, the ImportType is set to 2 EXCEPT for the last record(to be discussed further down). I changed the ImportType to 2 for preparation of the paper so that I am able to show the batch waiting to be processed FROM this staging table to its FINAL destination in the MDS database. One important point to state at this point in time is,  do not look for a ‘color’ table per se. Data storage within MDS is not handled in a similar fashion as it is with a standard SQL Server database.

Having run our package and thus having loaded our staging table, did something ‘strange’ under the covers. Master Data Services woke itself you and if you look within the Integration Management tab of Master Data Manager, you will note that there is a batch there that requires processing (See the screen dump below).

MDS for the beginner part036

What we now need to do is to “push” this data from the staging table into the MDS database (for production usage). In short, this data is not yet visible to the user.
To complete the process, simply click on ‘Start Batches’.

MDS for the beginner part037

The system will ask you which version. To date we have not discussed versioning. Simply accept Version_1 by clicking OK.

MDS for the beginner part038

You will note that our job is ‘Queued to run’ (See the status box) . The run process could take a minute or so to complete.
Once complete, the status will change (see below).

MDS for the beginner part039

You will note that there we 13 records were updated HOWEVER one was rejected!!! The reason that it was rejected was that we requested that Master Data Services updates our colors. Record 14 from the spreadsheet above has a color “pers” (Dutch for purple) AND MORE IMPORTANTLY as ImportType of 1. This color does ALREADY EXISTS in the Master Data Service table and therefore cannot be INSERTED!  Once again, Master Data Services has covered your back!!!

More about this when we discuss view your errors in the error views.
An important point to push home is that all other entities load in a similar fashion.
To look at our data that we have just loaded, we once again go into the Master Data Manager, choose Explorer, click on the ‘Entities’ tab and opt for ‘Color’.

MDS for the beginner part0310

Once we choose color, the color entity is brought up.

MDS for the beginner part0311

Dealing with the mother Product Table

A screen dump of the mother product table is shown below:

MDS for the beginner part0312

Note that the product table has the color already shown. Let us take a look at how this is obtained from the spreadsheet shown in the NEXT screen dump.

Note that column “R” contains the color. Whilst the ‘code’ and the ‘name’ within the ‘color’ load that we just ran, are the same, what in essence is stored within column “R” is the code (the KEY). It is almost as if Master Data Services will do a “Look Up” once loaded. This will become more evident when we discuss our daily load processing (in an upcoming ‘episode’).

MDS for the beginner part0313

It should be noted that in the creation of the Product Entity, the color attribute was set up NOT as free form BUT rather as a “Domain Based” attribute. Why?
It was done in this manner to permit the end user to update a color and yet limit the user to only a prescribed subset of colors. Please see the screen dump immediately below.

MDS for the beginner part0314

Wrapping up

Today we have seen

1)      That data within a spreadsheet may be used as a data source for our Master Data Services entities.

2)      We have seen that the data (on entry into Master Data Services) is stored within a staging table.

3)      We have seen what the data looks like within this staging table.

4)      We have looked at an SSIS package to load the data from a spreadsheet into the staging table.

5)      We have seen how the Integration Management is able to push the data from the staging table into Master Data Services.

6)      We have seen how the product table (mother table or center of the star from yesterday’s discussion) contains the KEY ONLY for the color attribute. The description comes totally from the color entity. Very similar to our SQL look up.

In the next portion of this series

1)      We shall be looking at creating a view to actually look at the data that is stored within Master Data Services.

2)      We shall be looking at the extremely important concept of  a “Current Flag”

3)      We shall be looking at extracting data from Master Data Services to be used with SQL Server Reporting Services.

In the interim, should you have any questions or recommendations, I would love to hear from you. You may reach me at steve.simon@sqlpass.org

Happy programming.

Master Data Services for the Beginner Part 02

Introduction

In the first part of this discussion, I discussed step by step instructions pertaining to getting up and running with Microsoft SQL Server Master Data Services.
In this part of the discussions we are going to discuss Entities and Attributes and the members that make up our data.

The MDS Database

Unlike most of the SQL Server databases that we as DBA’s and developers deal with on a day to day basis, the MDS database is another breed of animal. It comes ‘packed’ full of tables and views of which I too, am still discovering. The point being, that the altering of features, tables or views) within the database) is not for the faint of heart. A corrupted database will give you endless sleepless nights.

Entities

Entities are a MDS word for a data table and attributes are another word for columns / fields within the entity (database table). In short, an entity contains one or more attributes. As an example one may have an ‘Invoice’ entity which has quantitative and qualitative characteristics. An invoice has a balance, a date due. It belongs to a customer, and could be overdue (a Boolean) etc.

Attributes

The balance, due date (above) may be classified as attributes of the Invoice Entity.
Before getting started, you must ensure that you have all the necessary rights. Do speak to your system administrator to ensure that you have the necessary privileges.
We first open our Master Data Manager and select System Administration.

To access the Master Data Manager user the following link which you will have to alter to you’re your server and website instance http://anc-sqldw/MDS1/default.aspx

MDS for the beginner part021

The System Administration screen will come up.

MDS for the beginner part022

Select ‘Manage’ and click on ‘Model’. Creating a model is the first step in getting the necessary data to your client. VERY ROUGHLY PARAPHRASED, a model is like a ‘database’. Thus a model (database) has one or more entities which have one or more attributes each. In my case, I called my model “Aging” going forward it will be ‘Product’.

MDS for the beginner part023

What we want to do is to “Add a model” (see above). Click on the green plus sign. Give the model a name and uncheck the check boxes (as later we shall be creating an entity with a different name).

MDS for the beginner part024

Click the “Floppy Disc” (the one on the left above the word ‘Add Model) to save. Our model is now established.

PLEASE NOTE that due to the sensitivity of our financial data I shall be continuing this discussion utilizing the models that Tyler Graham uses in his book ‘Master Data Services’. I shall however show practical examples from our system throughout this series of articles.

Getting started

It is now time to get our hands dirty and to start formulating the layout of the data in the format agreed upon with the end user and IT. There are two types of entities / “tables” that we shall be looking at and creating. Those where the users will work on a day to day bases (for the sake of argument let us call them core entities/tables) and ‘domain based’ entities. I like to think of ‘domain based’ entities as descriptive tables. Once again and using the examples from part one, we look at the users ‘Core’ entity.

MDS for the beginner part025

‘Product’ and the domain based entity called ‘Color’.

I have taken the liberty of highlighting the first row.

Note that ‘Color’ on the screen above (Right Side) is shown as a ‘drop down box’. The client only has a limited amount of colors that he may choose from. This is really a true benefit, as it prevents user ‘whoopsies’ and ensures data consistency. IF we granted the user, permission to update the color field, then he or she will be able to choose one of the colors shown in the drop down box. IF the user does NOT have sufficient rights then the color will appear in the color box HOWEVER no drop down box will be available to the user and he or she will not be able to alter the value. This is a super PLUS to the product as the data security can be controlled by window domains and all sorts of permutation of rights may be created. Similar to role based security.

The Color domain (used as a domain based entity) may be seen in the screen dump below:
All that it contains is a key and a description. In MDS term, it contains a ‘Code’ (the key) and a ‘Name’, the description. Contrary to what one may think, code and name are the attributes and the colors (themselves) are members / rows of the entity.

MDS for the beginner part026

Obviously, the examples that I am showing have already been populated with data. We shall be looking at loading our entity attribute members shortly.

Creating our first Entity

In our imaginary example, we shall assume that we have had discussions with the end users and they have given us a list of fields that they would like to see;  in addition to a list of columns  that  may be updated by the various user groups. Call it a ‘report’.

HOWEVER in this ‘report’ we shall be looking at raw data (perhaps summarized) however the critical point being that the users have been empowered to alter certain fields. A typical example could be a field call current status (Married, Divorced, Separated, Widowed etc.). Normally the fields that we permit users to alter are ‘non –earth shattering fields’ for want of a better word.

For those of you whom know me, I am all for user empowerment with their own data. Having the users take ownership of their own data tends to cause the user to reflect before making any alterations.
NATUARLLY, we cannot let users alter critical fields such as client balances etc. These are best kept for conventional processes executed according to well established business rules.
We now shall return to the Master Data Manager home screen. Simply click upon the SQL Server 2012 logo in the top left hand corner.
Once again choose ‘System Administration’, Manage and then click on ‘Entities’.
MDS for the beginner part027

The manage entity screen will then appear. Note that the model we are using is product.

MDS for the beginner part028

This time click upon the green plus sign under the words ‘Entity Maintenance’

The ‘add an entity’ screen will appear.

We shall first create an entity that will be used by our mother entity. We shall give our first entity the name ‘color’. One should try to create our ‘domain based’ entities before the mother. Think of it as the mother being the center of a star and ‘color’, ‘fabric’, ‘type’ etc. being on the point of the star. The reason for doing so will become apparent in a few minutes.

MDS for the beginner part029

We define color as shown above. NOTE that we are only creating the ‘skeleton’ not adding the meat or the values. Click the ‘diskette’ to save the color entity.
At this time and in a similar manner, one would create a ‘fabric’ and ‘type’ entity as well.
As promised above, one may see a screen dump of a real world customer follow up ‘domain based’ entity below:

MDS for the beginner part0210

Having created our entities that will be used as domain based entities we are now in the position to create the ‘mother’ entity. Once again we choose Manage, Entities, and click upon the green plus sign.

MDS for the beginner part0211

Give the entity a name. Let us call it ‘Product’. The completed form should look like the one below:

MDS for the beginner part0212

Note that we shall accept the default for the staging table (to be discussed later on) and we do NOT want to create any hierarchies at this moment. Once again merely save your entity by clicking on the diskette under the words ‘Model View’.

We now are taken out to the main entity screen.

MDS for the beginner part0213

Adding our attributes

Highlight the Product entity and then click the ‘pencil’ next to the green plus sign. We find ourselves in the ‘edit entity’ screen (for Product).

MDS for the beginner part0214

We now want to add a leaf attribute. A field / a ‘column’ to our table.
Simply click upon the green leaf button and the leaf entity screen will appear. To recap: we have created the table (i.e. the entity), NOW it is time to add the fields to the table.MDS for the beginner part0215

We shall add out first attribute (field)  ‘stockbalance’.  Stock balance can obviously not be predicted as the color of an object may be. In the case of 50.0497 kg, it is not practical to create a domain based entity to populate the ‘stockbalance’ field.

Hence we complete the screen as shown below:

MDS for the beginner part0216

Note that the ‘free form’ radio button has been checked, the data type has been set to a ‘number’ and the amount of decimal places to 2. Thus once we have completed our entry,  we once again click the diskette to save our attribute and we are returned to the ‘maintain entity’ screen.

I have mentioned ‘color’ a number of times and it is now time to add another attribute (field) to our entity (table) . I know that this time there are a limited number of color that we have and we need to ensure consistency of colors. Regardless of how beige the product may look, we want to categorize it as ‘beige’.

MDS for the beginner part0217

Note above, that I have selected the radio button for ‘Domain Based’. Once we set the radio button to domain based a list of existing entities is show (see the drop down box above). This is the reason that we created the potential domain based entities (points of the star) prior to the ‘Mother’ entity (center of the star).

To recap and summarize:

1)      We have created our first practical model.

2)      We have created two types of entities. A mother and the star points such as color, fabric and type.

3)      We have added attributes to these entities.

In our next episode, we shall be seeing how to load our raw data into our entities and do a small amount of reporting.

Happy programming.

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.

Master Data Services Disaster Recovery Challenges

Introduction

While Master Data Services is the best tool to come along to permit user to update their qualitative and quantitative data, disaster recovery from the failure of a model or the MDS database itself can be a challenge to say the least.
The main challenge comes NOT with restoring the model nor the database, but rather with restoring the data security including user rights and process ID’s access to the data models and daily processing jobs via the SQL Server Agent.
A case at hand occurred when a user asked to have an additional attribute added to a core entity. The user wanted the field placed in a particular position and between two specific columns.
As the astute user will note, this is NOT easy to do and required me to take out the columns from the critical position to the ‘end of columns’; create the new attribute, and re add the deleted attribute. This is not the critical part. The issue was that the entity is a part of a hierarchy and this further complicated matters.

Security was based upon the one attribute within the higher level entity. Satellite companies could see their own records (Member = 2) and the mother company can see their own PLUS all records from the satellite company (Member = 1 and / or two).

Security Token (mother)
Child records (Satellite Coy)
Child Records(Satellite and Main Company

I only mention this here is as this was the root cause for the restore. Please read on.
When the hierarchy was originally designed, a mother company’s employee was able to either:

1)      See all records.
2)      See satellite company records only.
3)      See main company records only.

The important point was that ‘all records’ was the default on entering explorer which was not the case after mucking about trying to get attributes in the desired position. Somehow the hierarchy became corrupt, and the normal stats in bottom left corner of Explorer showed ‘0 – 0 members of 0’available (while there were members on the screen) and in order to actually get beyond the first page of members, the end user was required to select either 1 or 2 from the security keys (listed above) and more importantly, could not see both at once.

I shall be discussing the creation of this type of security within Master Data Services in a further article.

Two critical points are to be made, prior to discussing the disaster recovery steps necessary to get back up and running.

1)      Each time one recovers, the security that you built into the user ID’s somehow does not seem to be recoverable and has to be reprogrammed.

2)      A process ID that runs the SQL Server Agent will no longer have necessary rights to the MDS models and metadata and WILL cause daily loads to fail.

The recovery process

After each daily process, we take backups not only the MDS database but the production models as well.

Prior to describing how to restore a model, the reader must understand that this should be a last resort. It is always easier to try to remedy a problem without resorting to Draconian measures.

The Steps:

1)      Delete the model from within MDS: To delete the model, simply enter the Master Data Manager as shown below: Choose the System Administration Task

MDSdisasterRecovery01

2)       Select the Manage Option and Models

MDSdisasterRecovery02

3)      Choose Model Maintenance and Delete X. You will be informed that you will lose ‘the whole bang shoot’.  Simply acknowledge this and continue.

MDSdisasterRecovery03

4)      Once the model has been removed it is time to restore our back up of the model. This is done with the following command. While one can do this within the Master Data Manager, I prefer to do this from the command prompt. The application which will restore your model is called:

MDSModelDeploy and in SQL Server 2012 this may be found in c:\program files\microsoft SQL Server\110\master data services\configuration.

Simply issue the following command.

mdsmodeldeploy deploynew -package MyFileFinalBackupToday.pkg -model Aging -service MDS1
where MyFileFinalBackupToday.pkg is the data backup and ‘Aging’, the model name. MDS1 is
the ‘service’. This is the name that you gave to the web application when Master Data Services
was initially set up. In our case (MDS1).
Great!!! The model has now been restored. This was the easy part.
The issue is that the permissions are no longer there, invalid, or can no longer be guaranteed.

Resetting permissions

Clicking on the ‘User and Groups’ permissions tab gets you to the maintenance screen.

For each user reset the necessary permissioning, remembering that there are three options for each Entity (Read Only, Update, and DENY). The astute reader, will note that these permissions may be granted at the entity level or at the attribute level. This obviously depends upon your business rules.

My setting for my account may be seen in the following screen dumps. The first screen dump shows general information and nothing more.

MDSdisasterRecovery04

The membership tab must be check as well. This is informative only HOWEVER it will show you which Window user groups your user was a member of when he last logged in.

MDSdisasterRecovery05

We now are at a critical point where we must ensure that our users has the necessary rights to do his day to day work.

Most users merely require Explorer rights. Administrators and Process IDs require
access to the remaining tabs as well as to the MDS Metadata.

MDSdisasterRecovery06

Assigning rights to the models FOR THIS USER comes next and this must be done with the greatest of care. I always work on the principle of ‘minimum necessary rights’ to achieve the user’s requirements.

MDSdisasterRecovery07

Remember however that I have assigned update permission for myself as the MDS Admin (at the Entity Level) however your users would certainly not have this.

As mentioned above, I shall discuss Security in detail in an upcoming article.

Restoring the Process ID

While I belabored the security issue above (merely as a grounding) I have found that the restoration of a SQL Server Agent Process ID is not for the faint hearted and is genuinely enough to give one grey hair.

OK, this is what must be done to restore the necessary rights to a Process ID

1)      Remove and re-add the Process ID to the MDS database within SQL server management studio.
2)      The login should be OK. The Process ID should have Administrator rights from inception.
3)      Having re-added the Process ID to the MDS database, we must delete the Process ID from the ‘User and Group’ screen (within the Master Data Manager) and ENSURE that the ID is in fact removed.  We now re-add the Process ID to the Master Data Manager.

MDSdisasterRecovery08

Having created the Process ID I now login and logout of Windows as the process ID to force the windows groups to show up in the screen dump above.

The windows permission groups for the process ID may be confirmed in the Master Data Manager by its last login and is indicative that we are on the right track.

4)      We now set the appropriate functions as was shown above.
5)      Next comes the important part setting the RIGHTS for the process ID.

MDSdisasterRecovery09

NOTE that the process ID has update rights TO EVERYTHING (All models and the metadata). This is very important.

To recap:

The Process ID has:

Windows Administrator Rights.
Update rights to the metadata.
Update right to the Data Models.

6)      We have now reached a point where we need to test to see if the process ID can read the models. THIS IS CRITICAL as if it cannot, you daily loads WILL fail.
7)      To test the visibility of the models to the process ID we once again utilize our old friend MDSModelDeploy. Note that our model ‘Aging’ is shown(See below). This indicates that the Process ID is good to go. Should the list be blank, then model visibility is STILL an issue.
MDSdisasterRecovery10

Conclusion

While normal database recovery is usually fairly painless, the recovery of the MDS database or more often your data models can be challenging to say the least. The restoration of user rights is fairly simple and takes only a few minutes for each user. The big challenge seems to be to restore the necessary rights to a Process ID running the SQL Server Agent.  Somehow the Process ID rights become corrupt upon restoration and I have found that you have to ensure TWICE that the Process ID has been removed from the Master Data Manager and from the MDS database BEFORE re-adding it to both.

As mentioned above, I shall be writing an additional article later in February describing the setup of Master Data Services security and considerations in doing so.
As always should you have any questions or thoughts, please feel free to contact me at
steve.simon@sqlpass.org

Happy programming.

SQL Server 2014: Master Data Services..Backing up your data models.. A necessary evil!

Introduction

A few days back, in my article entitled ‘Master Data Services 2014: Creating nightly load processes’, I discussed the setting up of a nightly batch process to update a Master Data Services 2014 application that I have recently created for our finance department.

This is fine and dandy, however one critical factor that one often forgets is that backups too, must be taken.

When dealing with Master Data Services (hence forward referred to as MDS), aside from the routine backup of the MDS database, it is imperative to back-up your models (with their data),  so that one is able to ‘roll back’ in case of a ‘whoopsie’ and THIS is what I shall be discussing in this article.

Backing up your data models.

Unlike other ‘non-system’ SQL Server databases which one may port from one environment to another, the same cannot be said of your Master Data Services database. This is why it is imperative to back-up your models. Models are easily ported to another SQL Server instance and installed on another.

As a recap, the reader will see a high level view of my nightly process in the screen dump below:

VSProject

What we shall be covering in this article, are the final two steps and some of the gotcha’s that I encountered.

MDSModelDeploy.exe is the key

The executable program, MDSMODELDEPLOY is the key to the whole MODEL backup process. Executing this module requires system administrative rights and this is where the whole story takes root. What we need to execute, to backup the model, is one simple statement.

mdsmodeldeploy createpackage -package OpenInvoiceFinalBackup.pkg -model Aging -service MDS1 -version VERSION_1 -includedata

Seven days of getting greyer hair!!

To begin, backing up the model (in our case) via the SQL Server Agent, requires a DOMAIN based account / process ID with system administration rights on the server hosting MDS. To further complicate matters, a part of the nightly load requires access to two other database servers on which we access the data via this domain account.

Getting all the parts to talk to one another, took me seven days and many sleepless nights.

First off and as discussed above, in order to comply with data security issues, I was compelled to utilize the domain account. The main issue came from ERRONEOUSLY trying to create a local user of the same name PLUS adding the domain account into the Administrators group. This was my first big mistake. The domain user name should NOT become a local user.

The second gotcha (and PRIOR to twigging on the fact that a local user was not necessary), was in CREATING a ‘local user’ within Windows 2008 R2  and trying to grant update rights (to the model) from within Master Data Services(explained below).

I suspected that to execute MDSModelDeploy required more than ‘Read’ rights (which in fact is so).

The issue lies in the fact that Master Data Services (MDS) will only permit one to allocate rights to local server users.

In fact, should you try to create a user utilizing the domain and user name, MDS will tell you that it cannot find the user and WILL NOT allow you to create a valid MDS user and thus you will NOT be able to assign any valid rights at all.

In short:

1)      Using the local account within as the SQL Server Agent account would permit the execution of MDSModelDeploy HOWEVER the remaining data pulls FAILED (naturally) as the other servers knew NOTHING of this local account.

2)      Using the Domain based account permitted the pulling of the data from the other database servers HOWEVER the execution of MDSMODELDEPLOY failed for the reasons stated above.

Get to the point Simon,  ..tell us the solution.

1)      Add the domain account to the Administrators group within Windows.

2)      Add the domain account within SSMS as a System Administrator login (which I had always had done).

3)      Within the security section of the MDS database, I gave the domain based ID db_owner rights.

4)      Use the domain account as the SQL Server agent login account name.

Renaming the backup file

The last step that I have in my process is to rename the backup file to incorporate the date on which the backup was made. I use the date only as the process runs once daily.
You might want to incorporate the time as well.
To do this, I have a job step that utilizes a ‘cmdExec’.
The rename statement is similar to the one shown below:

ren c:\OpeninvoiceBackups\OpenInvoiceFinalBackup.pkg OpenInvoiceFinalBackup.pkg%date:~4,2%%date:~7,2%%date:~10,4%

 

Conclusion

Backing up one’s MDS models are as important as backing up production databases. Security issues form 90% of the challenges in getting the various pieces of an application to talk to one another.
Should you have any questions or concerns, please feel free to contact me at steve.simon@sqlpass.org

Happy programming !!