The key new features of SQL Server 2008 R2 relate to the management of organizations’ master data (Master Data Services – MDS ), administration of multiple servers (SQL Server Utility), and processing large amounts of data (PowerPivot for Excel, formerly known as Project Gemini).
One of the main concerns of any organization is the management of its master data, various departments have their own databases (and often times different systems) containing data that they work with on a regular basis. Each department has a set of master data (if they are using different systems then it is often in a different format) for e.g. the Sales department will have Customer master, the Purchase Department would have a Vendor master, and there might be a Product master in the Inventory department. At the management level, if somebody wants to generate a report from the master data, or do some analysis on it, they would require an interfacing tool that interacts with these disparate systems. In such as scenario, it would be very helpful to maintain a central database of master data where all the master data is available for reporting and analysis. Master Data Services (MDS) is a component in SQL 2008 R2 that makes this possible. It is a component that allows storing of Master Data in a central repository called the Master Data Services Database and provides for its further use in business intelligence, reporting, and data warehousing. As of the CTP, MDS is available only on 64-bit systems.
When you install MDS, the following components and tools are available:
- Master Data Services Configuration Manger –used to create and configure MDS Databases and web applications
- Master Data Manager – where users manage the master data.
- Master Data Services Web Service – a service for developers to develop or extend custom solutions for MDS.
There are three steps to using the MDS application:
(1) Create the MDS database.
(2) Create the website and application, enable web services.
(3) Create models, entities, attributes and start using the MDS application to manage your master data.
There are three pages available within MDS Configuration Manager, the Server Configuration Page, the Database Page and the Web Configuration Page. When you launch the Configuration Manager, the first screen you will be presented with is the Server Configuration Page as shown below.
There are two options in the left pane: Databases where you create the MDS Database, and Web Configuration where you create and edit the MDS Web Site and Web Applications. First, we will look at the Databases page and how to create a Master Database. Click on the Databases option and the Databases page opens up with two sections – Current Database and System Settings as shown below:
If you click on Create Database, you will be taken to the Create Database Wizard where in stages, you will be asked to provide information for s the Database Server on which you want to create the database, the Database name, its collation, the Service and Administrator account, before finally your MDS database will be created.
Once you’ve created the database, you establish a connection to the database using the Select Database button.
In the System Settings section, the following options are available:
- Fixed levels in Subscription Views: specify whether there is a fixed number of hierarchy levels while exporting data.
- Web Services time-out threshold: time out value for web services before the master data manager web application returns a timeout error.
- Staging Tansaction Logging: whether or not to log transactions when loading data from staging tables to the master data services database.
- Levels in Subscription Views: specify the number of hierarchy levels in subscription views.
- Copy only Committed Transactions: specify whether users can copy only committed transactions into the master data services database.
- Rows per batch: indicate the number of rows per batch to be imported.
- Email frequency: frequency, in seconds, of emails to be sent in case of a business rule validation failure
- Master Data Services root URL: the root url under which the master data services web site will be placed.
Database Mail Profile: specify the database mail profile to be used for sending emails in case of business rule validation failure. There is a button Create Profile, which will open up the Database Mail Profile window, when clicked. Here you can create a new Mail profile with options as shown in the screen below. You will be required to supply the profile name to be created, and the account name to be associated with the profile. You will also need to configure the SMTP Mail server options for outgoing emails and the authentication mode. As a best practice, you should create a specific user account with minimal permissions for using with database mail profile.
Turning now to the Web Configuration page (shown below), here you can create a web site and web application and enable Web Services to be able to use MDS Web Service.
Click the Create Web Site button to open the create web site window as shown below where you can create the website. SQL Server doesn’t create the Master Data Manager web application by default (Master Data Manager Web application is the place where users manage the Master Data).
In this interface, you either enter your website name in the Web site name field provided or accept the default. You have two options to specify the protocol: http for Hyper Text Transfer Protocol or https if you want the web server and client to exchange information over an encrypted channel. Unless you are using specific IPv4 or IPv6 IP addresses, leave the default All unassigned and port number values be selected. Host header is an optional value to specify a domain name for the server that is hosting the web site. If you give this a value, then users have to use this value rather than the server’s IP address. In the Application pool section, specify the name of the application pool and the user account details that will have administrator privileges for that application pool. This application pool holds all the MDS web applications and web sites.
The Create Application button pops up the Create Web Application window (as shown below) where you provide the Virtual path for the application. Your web application will be created under this virtual path. After specifying the application pool details, click OK, and then select the MDS database for this web application in the Database section. Finally, click on the check box in the Web Services section to allow the web services to be able to use the MDS application.
After creating the database and the web site, and enabling web services, when you click the Apply button, your settings will be saved and you will be taken to the Master Data Services Web Application site. After providing the login credentials, you see the Getting Started page where you click on the Open the master data manager home page link which directs to the Master Data Manager (MDM) home page as shown in the figure below:
As you can see, the MDM home page has five options: Explorer, Version Management, Integration Management, System Administration, and User and Group Permissions.
First, create and manage models objects in System Administration section. Once you have created the models, use the Explorer page (shown below) to manage hierarchies, entities, members and attributes for a model:
For managing different versions of models, you use the Version Management section: .
In User and Group Permissions, you can assign permissions to users and groups to maintain security of the master data:
Finally, you can integrate your master data from different sources and make it available to subscribing systems in the Integration Management section:
In the Staging batches pane of the Integration Management section, you can view the status of all existing staging batches that were created to load the master data. In the Unbatched Staging Records pane, you can view information about records in the staging tables. Staging tables are tables where your data is loaded prior to importing it into the master database.
That’s quik lap around the new Master Data Services of SQL Server 2008 R2, in subsequent articles we will turn our attention to SQL Server Utility and PowerPivot.
]]>