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”

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

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

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

I hope this helps! Your comments are very welcome!

Master Data Services: Analogy to remember what are Models, Entities and Attriutes (parasdoshi.com)
Matching activity in Data Quality Services in action! (parasdoshi.com)
Guest Blog: How we use Fuzzy Lookup add-in in our company to solve data inconsistency problems: (parasdoshi.com)
SQLAuthority News – Download Whitepaper – Cleanse and Match Master Data by Using EIM (sqlauthority.com)
How to clean similar textual data in Excel via Fuzzy lookup add-in? (parasdoshi.com)
Official Resource: http://msdn.microsoft.com/en-us/library/hh479642.aspx (MSDN)
Master Data Services Add-in for Microsoft Excel (MSDN)

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:

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

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.

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

Master Data Services: Analogy to remember what are Models, Entities and Attriutes (parasdoshi.com)
Here is the official resource: http://msdn.microsoft.com/en-us/library/ff486990.aspx
Matching activity in Data Quality Services in action! (parasdoshi.com)
Microsoft HDInsight Preview for Windows: How to use Sqoop to load data into HDFS from SQL Server? (parasdoshi.com)

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