How to create a MDS entity via Excel add-in?

In this Post, we’ll see how I (as an Administrator) created a Master Data Services Entity via MDS add-in for Excel:

1. Created a Model via MDS Web Application. I named it “customer Information”

1 create a Model in Master Data Services2. Switch to Excel

3. Open the File that has the Data that you want to load to MDS

4. Switch to Master Data tab in Excel
[Resource: Steps I followed to Install SQL Server 2012 Master Data Services (MDS) on my Demo Machine: And then Installed the Excel Add-in]

5. Connect to MDS server (via Excel add-in)

6. Select Model as CustomerInformation

7. Under Build Model, select Create Entity

create entity Master Data Services Excel

8. Configure the values in the “Create Entity” > click OK

SQL Server create entity Master Data Services Excel

9. Switch to MDS web application to see the new entity:

MDS web application explorer SQL Server

I hope this helps! Your comments are very welcome!

How to view error(s) that occur during Master data Service’s Staging Process?

To view error(s) that occur during MDS’s staging process, we have two views: stg.viw_name_MemberErrorDetails & stg.viw_name_RelationshipErrorDetails. For the purpose of this blog post, let me show you how I (as an administrator) view information from stg.viw_name_MemberErrorDetails.

1) Name of the entity: supplier & Name of model is Suppliers

2) I imported data into staging table stg.supplier_leaf via SSIS

3) Now here’s how you can see the errors that occur during MDS’s staging process:

we can go to MDS web application to initiate the staging process > start the batch > after completion you can see the status as well as if it has any errors or not:

Master Data Services Errors4) Now if you see that there’s an error, then you can go to stg.viw_name_MemberErrorDetails to see what are the errors. In my case, I am going to run the query:

select * from stg.viw_supplier_MemberErrorDetails where Batch_ID=2

You can get the above query via MDS web application too:

Master Data Services SQL Server Staging Process

5) And as you can imagine, you can get access to this error data via SSIS (SQL Server Integration services) too. So if you have a workflow that a. Loads data to MDS and b. initiates the Batch process via Stored Procedure; then you can program it to get access to errors from the stg.viw_name_MemberErrorDetails & stg.viw_name_RelationshipErrorDetails tables.

SQL Server Integration Services and Master Data Services

That’s about it for this post! I hope this helps. Your comments are very welcome!

Matching activity in Data Quality Services in action!

In this post, we’ll see matching activity in action. For the demo purpose, I’ll be using Data-Sets that I’ve found via Movies Sample & EIM tutorial .

Situation 1:

we’ve a list of movies and we want to identify “matching” movie titles.

Solution 1:

Create a Matching Policy

1) Mapping the Domains:

2 Data Quality Services matching policy

2) Configuring the Matching Policy:

3 Data Quality Services matching policy

Note: You can have Matching Rules on more than one domain. I used one domain for demo purposes.

3) View Matching results:

4 Data Quality Services matching policy de duplication

4) Publish the KB (which stores the matching policy)

Once you have the matching policy, you can use this in a Data Quality Project:

5) See How I ran a Data Quality Project (w/ matching activity) in the image below.

5 Data Quality Project matching activity policy de duplication

Note: You can export the clean data-set via Data Quality Project.

Situation 2:

we’ve a list of Supplier Names and we want to identify “matching” supplier names.

Note that in this situation, you would see how to use more than one domain to create a matching rule.

Solution 2:

Most of the steps would remain same as situation 1, But I want to show you Matching Policy & Matching Results

Matching Policy:

6 supplier ID and name two domains in matching rule data quality services

Matching results:

7 supplier ID and name two domains in matching rule data quality services

Also, I want to show that, the matching policy (DQS KB) that we created earlier can be used in Master Data Services too! For more details check out the resource: DQS, MDS & SSIS for EIM

DQS MDS Data quality services and master data services

Conclusion:

In this post, we saw how DQS can be used to clean “matching” records. For step by step tutorial, please refer to Movies Sample & EIM tutorial .

Master Data Services: Analogy to remember what are Models, Entities and Attributes

Let’s understand master data services terminologies using an analogy. We’ll use the world of RDBMS for our analogy:

Terminology in MDS Analogy w/ RDBMS
 Models  SQL server Schema which is used to group logical entities together.
 Entities  Table
Attributes Columns

And here’s a picture that captures the thought: Models contains entities; entities contains Attributes:

master data services models entities attributes

And Here’s the official reference: http://msdn.microsoft.com/en-us/library/ee633763.aspx

Conclusion:

Thinking about the master data services terminologies by drawing an analogy helps me remember them – I hope it helps someone out there too.

what are the end – to – end processes for developers to build out a Master Data Services 2012 solution?

I recently completed an end to end tutorial that taught me basics of Master Data Services 2012. And I thought I would take what I’ve learned and create a diagram for reference that helps developers see end – to – end processes that are involved in building out a SQL Server Master Data services 2012 solution. So here it is:

SQL Server 2012 Master data services steps

Resource: Learn to build an Enterprise Information Management system using Data Quality Services, Master Data Services and SQL Server Integration Services

[UPDATE 29 Dec 2012: One more whitepaper that’s on the same subject and should be read after the tutorial: Cleanse and Match Master Data by Using EIM ]

Here’s the resource: Tutorial: Enterprise Information Management using SSIS, MDS, and DQS Together

Here’s what you’ll learn:

- Learn to use Data Quality client to create DQS Knowledge Base, cleanse data in an excel data, remove duplicated data from the excel file

DQS client domain values clean data data quality services

- Learn Use MDS add-in for Excel, store the cleansed and matched data in MDS

Master data services add in for excel

- Learn how to use the MDS Web UI:

SQL Server 2012 master data services MDS Web UI

- Learn to Automate the process of receiving input data, cleaning and matching it and storing the master data into MDS via SSIS!

SSIS automate DQS MDS data quality component

That’s about it! check out the Tutorial to learn about all the DQS, MDS & SSIS goodness!

 

Where’s my SQL Server Master Data Manager Web UI?

A reader emailed me to ask about where is the SQL Server Master Data manager Web UI? I guess, the reader might not recollect the URL that was created after the successful installation of Master Data Services. - And in fact, I had forgotten the Master Data Manager Web UI url once too and this how I got the URL of the master data manager web UI:

1. Start > IIS Manager

2. Find the site from the sites section of the IIS manager:

SQL Server 2012 master data services web manage UI

3. click on Browse the site and it would open the site.

SQL Server 2012 master data services web manage UI

4. After the site opens, you would also get a chance to see the location of the site

SQL Server 2012 master data services web manage UI

That’s about it.

This might not be new to most of you, but I hope that this helps someone out there who might need a pointer about searching for the URL of the Master Data manager web UI.

Conclusion:

In this blog-post, we saw how to see the location of the Master data manager web UI via the IIS manager.

Things I shared on Social Media Networks during Oct 11 – Oct 18

The Goal of this series is to recap the conversations that I’m having on social networks and I do not want my Blog readers to miss that. So Here is this week’s post:

1)

WOW! Global Internet Traffic (in Peta Bytes Per Month):
27483 PB/Month!
via http://www.evolutionoftheweb.com/

 growth of the internet big data

(Thanks Hardki pandya for sharing. Here’s his tweet: https://twitter.com/HPsay/status/258207371162181632 )

Also for the a related link:

https://www.facebook.com/suvendu.shekhar.giri said “This is really awesome and very informational ”

evolution of the web and surge in Data

2)

Patent wars: http://flowingdata.com/2012/10/08/patent-war/

patent wars google apple motorola htc samsung

3)

Quote: One reason Wikipedia is still a popular source for information is because most people don’t know they can edit it themselves.

Via https://www.facebook.com/thedolt and Gaurang Patel (https://www.facebook.com/Gaurang.patel7 ) commented that: “Yes, Absolutely true,most people don;t know they can edit post them selves,and i think they don’t have other alternative to get quick information.”

4)

https://www.facebook.com/janakiramm quote:

“Without Stackoverflow, 50% of the developers cannot continue to be developers and without Google, 80% of the developers cannot even claim to be developers!”

5)

Status update: “I just worked on a SQL Server 2012 Master Data Services Tutorial where I learned how to 1) create a new Model 2) Use MDS Excel Add – In to load entities to the model 3) use Master data manager web app 4) add a new member via MDM web app 5) And create a subscription view to retrieve entity members. if you want to do learn that: search for “SQL Server 2012 Developer Training kit“. Awesome resource!”

MDS SQL Server 2012 master data services tutorial

6)

Shared a Resource: Introducing SQL Server 2012 Master Data Services Improvements http://channel9.msdn.com/posts/SQL11UPD05-REC-03

7)

SolidQ mentioned me a post about the Sep 2011 SolidQ Journal edition: https://plus.google.com/u/0/105279914944150120482/posts/cKh6hurWndj

8)

SolidQ India (https://twitter.com/SolidQIndia) Re-Tweeted my post about “Blog: Why am I not seeing “Month Names” in correct logical order?” http://parasdoshi.com/2012/07/22/powerpivot-model-why-am-i-not-seeing-month-names-in-correct-logical-order/

9) Milena Petrovic said “Lovely Graph” for this graphic: https://plus.google.com/u/0/105010538932095629627/posts/2uvE5B2HSk5

From the webinar "How to Walk The Path from BI to Data Science: An interview with Michael Driscoll, data scientist and CEO of Metamarkets" - A global surge in Data // A nice slide about Big Data

Image Courtesy: From the webinar “How to Walk The Path from BI to Data Science: An interview with Michael Driscoll, data scientist and CEO of Metamarkets” – A global surge in Data

That’s about it. You can read last week’s post here: Things I shared on Social Media Networks during Oct 3 – Oct 10

Let’s connect and converse on any of these people networks!

paras doshi blog on facebookparas doshi twitter paras doshi google plus paras doshi linkedin

Steps I followed to Install SQL Server 2012 Master Data Services (MDS) on my Demo Machine:

In this blog-post, we’ll see the step by step guide to install SQL Server 2012 Master Data Services (MDS) after you have installed SQL Server 2012 on your machine. I’ve had installed it on my demo machine and you can build one for yourself using the resources listed here: Bird’s Eye view of SharePoint BI Dev Environment Setup process (SQL Server 2012, Sharepoint 2010)

As I mentioned, these are post-sql-server-installation tasks and so I assume you have your BI environment using SQL Server 2012 already up. The official note can be found here: Install Master Data Services

For the purpose of this blog-post, we’ll break the steps into three main sections:

1. Create MDS Database

2. Create Master Data Management Web Application

3. Associate MDS database w/ web Application

So, let’s get started:

Got to: Start > SQL Server 2012 > Master Data Services > Master Data services Configuration

1. Create MDS Database

1a: click on Create Database:

SQL Server 2012 Master Data Services MDS create database

1b: Enter the instance name and credentials:

SQL Server 2012 Master Data Services MDS database credentials

1c: specify the MDS database name and collation:

SQL Server 2012 Master Data Services MDS specify database name

1d: Specify the Administrator Account and review the summary. After it installs successfully – you’ll see a message:

SQL Server 2012 Master Data Services MDS successfully installed

2. Create Master Data management Web Application

2a. Switch to Master Data Services configurations > web configuration > select Create new website

SQL Server 2012 Master Data Services MDS create new website2b. Specify configuration details for the website:

Here’s what I entered:

Website Name: MDS

Port: 81

Host Header: Left it blank

Entered username and password

SQL Server 2012 Master Data Services MDS website configuration

2c. Click on OK and in the web configuration dialog box, click on Create Application

3. Associate MDS database w/ web Application

3a. Under the Associate Application with Database click on Select

SQL server 2012 master data services MDS assoicate database

3b. Select the database that will get associated to the application selected.

* I selected the database that I had created in Step 1

elect the database that will get associated to the application selected.

3c. After successful installation, you’ll see a message:

SQL server 2012 master data services MDS

The web application is then launched in the browser, here you’ll see getting started guide, videos and MDS Samples. Go Explore!

Conclusion

In the blog post, we saw the three post installation tasks for Master Data Services w/ SQL Server 2012.

Again, the official reference is here: Install Master Data Services