What is the title these days for a person that assures data quality? (I need to hire a person to make sure my data is as good as it can be. They need to inspect the data for issues, create logic for how it can be found and fixed, and finally, court the project through application development for a robust solution to stop it from occurring in the first place.)
Answer:
Quality of the data shouldnt be a responsibility of just one person — ideally, you want all members of the team (and broader business community) to care and own some part of it. But i like the idea of one person owning the “co-ordination” of how this gets done. It might not be a full time gig in a small org but can see this as a full time role in bigger orgs and enterprises. Some titles:
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!
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) Configuring the 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) 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.
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:
Matching results:
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
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 .
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:
Note that I am not showing the web service API using which you could write code that talks with Master data. More details: Developer’s Guide (Master Data Services)
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:
3. click on Browse the site and it would open the site.
4. After the site opens, you would also get a chance to see the location of the site
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.
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:
“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!”
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
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:
1b: Enter the instance name and credentials:
1c: specify the MDS database name and collation:
1d: Specify the Administrator Account and review the summary. After it installs successfully – you’ll see a message:
2. Create Master Data management Web Application
2a. Switch to Master Data Services configurations > web configuration > select Create new website
2b. 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
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
3b. Select the database that will get associated to the application selected.
* I selected the database that I had created in Step 1
3c. After successful installation, you’ll see a message:
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.
