Data Governance is one of the most important aspects of BI systems, that unfortunately seen less important in many organizations. All usefulness and gratefulness of BI system will only be seen if there is a good data governance in place. If you build superb dashboards and data visualizations it won’t help until the quality of data is high. your ETL scenario won’t be so much great when there are more than one source for the same data but with different versions of it.
Data Governance and Enterprise Information Management (EIM) are concepts that needs to be covered in every organization working with the data, despite the fact that they use BI system or not. So in nutshell EIM and Data Governance are not components of BI system, they are separate systems, that can be used side by side of a BI system. However because of their very close relationship with BI system (especially because BI system is based on the data and information) we would cover them here. I would like to comment here that there won’t be a good BI system without good EIM or data governance in place.
There are some really good books that reveal concepts of EIM and Data Governance, as an example I would like to mention this book;
Book: Making Enterprise Information Management (EIM) Work for Business
5- Master Data Management
Master Data Management (MDM) is one of the main components of EIM or data governance. Most of you worked in organizations that there are multiple versions of a reference data. Consider below scenario that a company has customer data in multiple systems; part of customer data comes from a CRM system, part of it comes from a web application, part of it comes from text files from other partners. All of these data sources contains the Customer Data entity, but with different versions, they are not sync with each other, and the main pain point is always mapping these data sets together.
Without MDM:
Master Data Management comes to help is these scenarios. Master Data Management ensure that there is only one single version of truth for master data. In above scenario all systems will be subscribers to Master Data Management system, they can read, and write from it, but the main and central version of truth is always in a single place.
With MDM:
Microsoft release a service for Master Data Management, named MDS: Master Data Services. This service was first time offered through SQL Server 2008 R2 product. Nowadays there are still improvement plans for this product. There are major differences between 2008R2 version of MDS and 2012/14. I strongly recommend you to read only materials related to the version you are working with. if you are new to this technology so you’ll probably would work with newer versions of it, and because of that I only mention resource for 2012/14 version of MDS.
Book: Microsoft SQL Server 2012 Master Data Services
There was not a significant different between 2012 and 2014 version of MDS, so you can use this book as the latest technology book for this services.
Master Data Services Tutorial Videos:
- SQL 2012 Master Data Services Overview
Overview of Master Data Services in SQL Server 2012 and presents its capabilities, architecture and key scenarios. (25:34)
Download: WMV | MP4 - Managing Data Warehouse Dimensions with MDS (part 1)
How business users can manage a simple data warehouse hierarchy. (16:45)
Download: WMV | MP4 - Managing Data Warehouse Dimensions with MDS (part 2)
How business users can leverage MDS to manage complex recursive hierarchies in the data warehouse. (14:07)
Download: WMV | MP4 - Using MDS Excel Add-In to Model and Manage your Master Data
The Master Data Services Excel Add-in in SQL Server 2012 helps you create and manage models. Learn about the MDS Excel Add-in feature set and see how you can use Excel to manage data in an agile and effective manner. (13:29)
Download: WMV | MP4 - SQL 2012 Master Data Services – Web User Interface Explorer overview
Learn about the Explorer and the Hierarchy management user interface and feature set of Master Data Services in SQL Server 2012. (8:02)
Download: WMV | MP4 - SQL 2012 Master Data Services – Hierarchies and Collections
How to use Hierarchies and Collections in Master Data Services SQL Server 2012. (23:38)
Download: WMV | MP4 - SQL 2012 Master Data Services Security
How to use Security in Master Data Services SQL Server 2012. (15:39)
Download: WMV | MP4 - SQL 2012 Master Data Services – Entity Based Staging
Use Entity Based Staging in MDS SQL Server 2012 to load all members and attribute values for an entity at one time. (27:47)
Download: WMV | MP4
Referenced from this MSDN page:
https://technet.microsoft.com/en-us/dn912438
6- Data Quality
Quality of Data is one of the most important assets, and there are different methods for data quality assurance, there are also different tools to help keeping quality of data high. Microsoft Fortunately has a tool released as part of SQL Server 2012 named DQS (Data Quality Services) for this purpose. DQS works on a knowledge base driven data quality method. This means For keeping high quality of the data you have to build a good knowledge base contains domains, composite domains, and domain rules. The person or people involved in building and enhancement of that knowledge based called as Data Stewart. DQS also has some components for data matching as well.
There are five main types of data quality issues that DQS can resolve, which illustrated below as; Completeness, Accuracy, Conformity, Consistency, and Uniqueness.
DQS released in 2012 but it is not a new technology, as it was with another company previously called Zoomix, Microsoft bought acquisition of this product and now it is part of the SQL Server product. Unfortunately there is no good book resource for this technology, but there are number of videos to start with;
Data Quality Services Tutorial Videos:
- Data Quality Basics and Introducing DQS
First video in series: Introduces the concept of data quality and its importance to any business. Also introduces Data Quality Services (DQS), new feature in SQL Server 2012, which provides a knowledge-driven solution to easily improve data quality through profiling, cleansing, and matching of data. (37:32)
Download: WMV | MP4 - Knowledge Management and Data Cleaning in Data Quality Services (DQS)
Second video in series: Introduces the knowledge base in DQS, concepts around knowledge base creation and management, how to cleanse data using the knowledge base, and provides information about composite domain parsing. (1:08:46)
Download: WMV | MP4 - Data Matching in Data Quality Services (DQS)
Third video in series: Provides information on how duplicate data adversely affects your business and introduces the concept of data matching, which is a process to eliminate duplicate records in your data. It also provides information on data matching principles in DQS and how data matching is done in DQS to discover and eliminate duplicate records. (1:11:57)
Download: WMV | MP4 - DQS Integration with SSIS: Data Cleansing Using SSIS
Fourth video in series: Provides information on how to use the knowledge in a DQS knowledge base to cleanse data within the Integration Services packaging process. Introduces the DQS Cleansing transformation in Integration Services, and demonstrates how to use the transformation in an Integration Services package to leverage the DQS knowledge and cleanse the data. (27:16)
Download: WMV | MP4 - DQS Integration with MDS: Data Matching using MDS
Fifth video in series: Provides information on how to enable DQS integration in Master Data Services to use the DQS matching functionality. You can leverage knowledge in a DQS knowledge base to perform data matching by using the Master Data Services Add-in for Excel. (12:44)
Download: WMV | MP4 - How Do I: Install and Configure DQS
Learn about the prerequisites to install SQL Server Data Quality Services (DQS), how to install DQS, and how to perform post-installation tasks to configure DQS. (9:42)
Download: WMV | MP4 - Using MDS Excel Add-In for Matching with DQS
The integration of MDS and DQS offers the ability to match and optionally de-duplicate data in an entity, using DQS within the MDS Excel Add-In interface. (6:20)
Download: WMV | MP4
Referenced from this MSDN page:
https://technet.microsoft.com/en-us/dn912438
Stay tuned for next parts of this walk-through.
Related Posts:
Walk-through Steps: I’m New to BI, Where to Start? – Part 0: Prerequisites
Walk-through Steps: I’m New to BI, Where to Start? – Part 1: Fundamentals, Data Warehouse and ETL
Walk-through Steps: I’m New to BI, Where to Start? – Part 2: Modelling with BISM
Walk-through Steps: I’m New to BI, Where to Start? – Part 3: Data Governance
Walk-through Steps: I’m New to BI, Where to Start? – Part 4: Data Visualization
Walk-through Steps: I’m New to BI, Where to Start? – Part 5: Power BI
Walk-through Steps: I’m New to BI, Where to Start? – Part 6: Data Mining
Walk-through Steps: I’m New to BI, Where to Start? – Part 7: Azure
Reza is author of more than 14 books on Microsoft Business Intelligence, most of these books are published under Power BI category. Among these are books such as Power BI DAX Simplified, Pro Power BI Architecture, Power BI from Rookie to Rock Star, Power Query books series, Row-Level Security in Power BI and etc.
He is an International Speaker in Microsoft Ignite, Microsoft Business Applications Summit, Data Insight Summit, PASS Summit, SQL Saturday and SQL user groups. And He is a Microsoft Certified Trainer.
Reza’s passion is to help you find the best data solution, he is Data enthusiast.
His articles on different aspects of technologies, especially on MS BI, can be found on his blog: https://radacad.com/blog.
4 thoughts on “Walk-through Steps: I’m New to BI, Where to Start? – Part 3 : Data Governance”