Blog.sqlauthority master data services sql server là gì năm 2024
Master Data Services (MDS) is Microsoft’s platform for supporting the discipline of Master Data Management (MDM). MDS allows you to manage a master set of your organizations’ data, which typically involves the following: Show
We’ll look at these in more detail below: MDS ModelsIn MDS, the model is the highest-level container in the structure of your master data. You create a model to manage groups of similar data, for example, to manage product data. A model contains one or more entities, and entities contain members that are the data records. A model is therefore like a database, and entities are like tables. Attributes are objects that are contained in MDS entities. Attribute values describe the members of the entity. Members are the physical master data and are like rows in a table. The diagram below shows part of a model to manage product data. Note that the ProductSubCategory attribute uses the SubCategory entity. MDS Business RulesBusiness rules are used to ensure the quality and accuracy of your master data. They are used to validate data before updates are made to the master data set. Validation typically involves checking to see if entered values meet specific criteria for an attribute (eg Cost must be > 0) and then taking an action (eg flagging an error or setting a default). MDS Permissions and RolesPermissions and roles in MDS are used to ensure that users have access to the specific master data necessary to do their jobs and to prevent them from accessing data that should not be available to them. They are also used to define who can create and edit models, and who can approve data updates. Where do I get Master Data Services?MDS ships as a part of the Microsoft SQL Server relational database management system. It can be enabled as a feature when you run the SQL Server setup.exe. At this point, I need to warn you that installing and configuring MDS is not as simple as you might hope. At some stage, you may well end up cursing as yet another obscure missing pre-requisite is flagged at some stage of the process. The main steps in the process are summarized below and detailed instructions can be found here.
The web UI screenshot below shows the Supply Chain group of attributes in the Product entity in the Product model. What is the Master Data Services Add-In for Excel?There are two ways to interact with master data in MDS: the web UI or the MDS Add-in for Excel. There is some overlap in the capabilities of each, but generally, the web UI is aimed at administrators that need to create models or business rules, whilst the Excel Add-in is for users to make updates to master data using Excel. Using the MDS Add-in for ExcelThe installation of the Master Data Services Excel Add-in is not included in the main installation process. Installation is, however, a simple process and you can download it here. Once installed, the Master Data menu will be available on the ribbon in Excel. The next step is to create a connection to the MDS database that you created in the MDS Configuration Manager. Click on the ‘Connect’ button and then click the ‘Manage Connections’ button to bring up the Manage Connections dialog. Click on ‘Create a new connection’ and enter a name for the connection and the MDS Server address – this is the address you created in the configuration manager. Once the connection has been created, the Master Data Explorer is displayed on the right-hand side. You can now select a Model from the drop-down list to load the data into Excel. When you select a model, the entities within the model are displayed in the data explorer. You can now select the entity you want to work on and the data will load into the active sheet in Excel. Note that once you have loaded data into a sheet in Excel, the data explorer is disabled. To view data in other entities you need to create a new sheet in Excel and select the Model > Entity from the data explorer. Ok, you now have your Master Data in Excel – what next? The most common use cases for the MDS Excel Add-in are:
Updating Master DataMaking updates to master data is the most common use case. Let’s look at an example to see how the process works. In the screenshot below you can see the data for the Product entity. We’re going to update the values for the StandardCost attribute. As you make changes to data in Excel, the updated cells are highlighted. These will remain highlighted until you publish the data back to the MDS database in SQL Server. Once you have made all your changes, you can click the ‘Publish’ button. When you do this, the following happens:
Auditing Master DataYou can view when and by whom changes were made by clicking on the ‘Show Audit Info’ button. The status of any business or validation rules can be viewed by clicking on the ‘Show Status’ button. In the screenshot below you can see that validation against one of the business rules has failed for some of the members because the rule ‘StandardCost > 0’ has not been met. 3 Reasons to Consider an Alternative to MDSMicrosoft’s Master Data Services platform ticks plenty of boxes when it comes to Master Data Management. It does, however, also come with some negatives:
SQL Spreads: An alternative to MDSSQL Spreads is an Excel add-in that allows you to use Excel as a front-end to update data in SQL Server. It includes features which support a Master Data Management initiative, including change tracking, data validation, role-based access, and more. Let’s look at a simple example to see how this could work. We’re going to use the AdventureWorks database for our example. In the screenshot below we’ve used SQL Spreads to connect to the AdventureWorks database and import the Products table into Excel. We’re now going to assume that we are a data steward for the Product data and we’re going to do the following:
Data ValidationBy default, SQL Spreads will always validate all entered values against the Data Types in SQL Server. There are a couple of other ways that we can enforce validation. The first is to force the user to select from drop-down lists when entering certain data types such as categories, colors, sizes – master or reference data, in other words. You can either specify a static list in SQL Spreads or lookup values in another SQL Server table. Lookups are set up in the Columns tab in the SQL Spreads Designer. The screenshot below shows how we specify that we want to look up the ProductSubcategoryID in the ProductSubcategory table and return the Name field to display in the cell. Once this is done, the user is limited to selecting from the values for Subcategory that are contained in the ProductSubcategory table. The second way is to use a data post-processing script in SQL Spreads. The data post-processing script is a SQL query that will be executed as the last step in the transaction that updates the database with the changes made in Excel. To add a Data Post-processing script in SQL Spreads, open Document Settings and click the Edit Post-Save SQL Query button. In the Post-Save SQL Query dialog box, we can now enter our validation script. The validation script contains the logic that we want to check and an error message that we can display to the user. The example below shows a SQL query that checks if any of the values entered in the StandardCost column of the Product table are invalid (ie less than or equal to 0). Now, if the user enters an invalid value in the StandardCost column, when trying to save to the database, the following message will be displayed, and the update transaction will be rolled back. Change TrackingTo enable change tracking you need to specify the columns in your SQL table into which the username and date/time will be inserted whenever changes are made in Excel. In the Product table in SQL, we therefore need to add the following columns:
Once these columns have been added, 0pen the SQL Spreads Designer and click on the Settings tab. You can now select the CreatedBy, CreatedOn, ModifiedBy, and ModifiedOn columns from the drop-downs as shown below. Now, when we make changes and save them to the database, these columns will be populated. Data ChangesThe process of making changes to the data is obviously easy, as we are working in the familiar environment of Excel. With the kind of validation and lookup fields that we’ve described above, users can be comfortable working with critical data in the knowledge that mistakes are unlikely to happen. If required, additional settings can be applied to, for example, limit users to only changing data or adding rows and not allowing row deletion. SummaryIn this article, we’ve looked at Microsoft’s MDM platform – Master Data Services. Under the hood, MDS is a powerful enterprise-grade solution for a Master Data Management initiative. Microsoft’s release of the Master Data Services Excel Add-In makes it easy for a wide range of users to manage their master data. As we’ve seen, however, MDS may be too much for some organizations that would like to perform a simplified version of MDM. In which case, the use of a simple tool like SQL Spreads can go a long way to instilling some of the basic MDM principles into an organization. Click here to download the trial version of the SQL Spreads Excel Add-in, or get in touch to find out how SQL Spreads can help you get started with Master Data Management. Andy McDonaldAndy has worked 20+ years in the Engineering, Financial, and IT sectors with data analysis and presentation using tools such as SQL Server, Excel, Power Query and Power BI. |