Posts

Featured Post

Implement MDS

Master Data Services allows organizations to manage their master data in a centralized repository  with easy to use interfaces. The implementation of Master Data Services is part of an organization’s  Master Data Management (MDM) solution. An MDM solution involves business and  technology process alignment and is often managed by a data governance committee made  up of functional business stakeholders, subject matter experts and data stewards. MDS was designed with business users in mind. It allows non-technical users to create and  manage data models, entities, attributes, hierarchies, attribute collections and apply business  rules to the master data. Business users can use the intuitive web-based user interface or  through the MDS Excel add-in, which provides a more familiarized experience in Excel. MDS provides technical users the ability to programmatically create and manage master  data using backend database objects or through the Master Data Manager Web Service. For  e

Use the Master Data Services Configuration Manager

The Master Data Services Configuration Manager can be used to do the initial setup and  configuration of the MDS database and web application. The MDS Configuration Manager can  be also used to upgrade the MDS database after a new SQL Server update has been installed  or to repair the MDS database in case of corruption or configuration mismatch after a database  has been restored from a backup. The Master Data Services Configuration Manager can also be used in migration scenarios,  for example, when the MDS database needs to be moved to a different SQL Server instance or  when to associate a different web application with an existing MDS database. In addition, the Master Data Services Configuration Manager is used to specify a series of  system settings related to database and web application services. The main settings and setting  categories include: General Settings Database connection time-out Database command time-out Web service time-out Client time-out Number

Install Master Data Services (MDS)

Image
The installation of Master Data Services consists of three steps as follows: 1. Install required Windows roles and features 2. Install Master Data Services from the SQL Server installation media 3. Configure Master Data Services from the MDS Configuration Manager Windows roles and features installation To install the Windows roles and features required for MDS, you can use Windows Server Manager  or through a PowerShell script. To start the Windows roles and features installation using  Windows Server Manager follow these steps: 1. Open Server Manager and click on Manage on the menu bar, then click Add Roles and  Features from the drop-down menu options to launch the Add Roles and Features  Wizard. 2. On the Select installation type window, select the “Role-based or feature-based installation”  option as shown and click Next. 3. On the Select destination server window, click the checkbox for the “Select a server  from the server pool” optio

Clean data by using the SSIS DQS task

Image
SQL Server Integration Services (SSIS) includes a DQS Transformation to correct data from a  connected data source. The DQS Transformation Output column is populated with one of five  status as listed below. You can use a Conditional Split Transformation to separate  out the output records based on its status. You can apply different business logic to each  stream or insert them to a table of file destination for manual processing. To clean data using the SSIS DQS task follow these steps: Open a new SSIS package in Visual Studio Create a new Data Flow Task Add a Source Component to extract the data to be cleansed Add a DQS Cleansing transformation and connect it to the Source Component Output Edit the DQS Cleansing transformation and set the Data Quality connection manager and Data Quality Knowledge Base to use for the cleansing activity Click on the Mapping tab and map the Input Columns to the corresponding Knowledge Base Domain, then click OK Add a Conditional Split tr

Clean data by using DQS knowledge

Image
The DQS Client allows you to run a Data Quality Project to perform a Data Cleansing Activity.  During the Data Cleansing Activity, DQS detects new, invalid, and correct values based on existing  valid values, Synonyms and Term-Based Relations. The interactive data cleansing allows  you to accept or reject suggestions and corrections that the DQS cleansing activity identified.  Once all records have been processed, you can export the data cleansing results data and  cleansing info. To cleanse data using DQS knowledge follow the steps below. Open the Data Quality Services client and connect to the DQS instance Click on New Data Quality Project button from the Data Quality Projects section Type a name and description for the Data Quality Project Select Suppliers from the Use Knowledge Base drop-down list Click on Cleansing under the Select Activity list, then click Next Select Excel File from the Data Source drop-down list Browse and select the SuppliersDomain.xlsx file Se

Add matching knowledge to a knowledge base in DQS

Image
A matching knowledge can be useful when building and maintaining unique, unduplicated lists as Customers, Products, and Suppliers. You can use a DQS knowledge base to maintain  unduplicated lists of Suppliers for example, by building a DQS knowledge base and setting up  a DQS Matching Policy. During a matching activity, DQS evaluates the matching rules setup  in the Matching Policy against the records being processed. It then provides a matching score  for each record. If the matching score is greater than the minimum matching score configured  then the two records are considered matches. Create a matching policy Follow the steps below to create a matching policy. Open the Data Quality Services client and connect to the DQS instance Click on Open Knowledge Base button from the Knowledge Base Management section Select Suppliers from the Knowledge Base list Click on Matching Policy under the Select Activity list, then click Next Select Excel file from the Data Source dro

Perform knowledge discovery in DQS

Image
The Data Quality knowledge base can be populated using interactive or computer-assisted  processes. During the installation process of SQL Server 2016 Data Quality Services, you can  choose to install the Data Quality Client to interactively create and maintain a DQS knowledge  base. The DQS Client can be used to create data domains and add domain values manually or  by importing them from an Excel spreadsheet or a data cleansing project. In addition to interactively maintaining data through the DQS Client, you can maintain data  by running a computer-assisted activity known as Knowledge Discovery. The Knowledge Discovery  activity analyzes a sample of data that is used for data quality criteria. The algorithms  built into DQS look for data inconsistencies and syntax errors and then propose changes to the  data. You can then approve or reject the proposed changes or apply corrections manually. To perform a Knowledge Discovery activity for the SuppliersName in the Suppliers Kno