Jeff Prom's SQL Server Blog

Sharing knowledge and tips on SQL Server, Business Intelligence and Data Management.

Archive for the ‘MDS’ Category

SQL Saturday # 640 – Los Angeles 2017

Posted by jeffprom on May 5, 2017

I’m excited to announce that I’ll be presenting at the very first SQL Saturday event in Los Angeles!

The event is coming up fast but there is still time to register. It will be on June 10th, 2017 at the Microsoft Technology Center aka MTC, 13031 W. Jefferson Blvd Suite 200, Los Angeles, California, 90094.

Reserve your spot today! http://www.sqlsaturday.com/640/EventHome.aspx

@SQLSatLA

        

Advertisements

Posted in Events, MDS | Leave a Comment »

SQL Saturday #611 – Orange County 2017

Posted by jeffprom on February 22, 2017

sqlsat611_web

SQL Saturday Orange County is coming up fast! The event will be on April 1st, 2017 at Golden West College, 15744 Goldenwest St, Orange County, California, 92647.

Reserve your spot today by going here: http://www.sqlsaturday.com/611/EventHome.aspx

I’ll be presenting two sessions:

  • SQL Injections and How To Stop Them
  • Mastering Master Data Services (Lots of changes in SQL Server 2016!)

Posted in MDS, Security | Leave a Comment »

SQL Saturday #497 – Huntington Beach 2016

Posted by jeffprom on March 14, 2016

sqlsat497_web

April is shaping up to be a fun, yet busy month. I have also been selected to present two topics at SQL Saturday #497 in Huntington Beach on April 2nd! I will be presenting on SQL Injections and Master Data Services. Here are descriptions of my presentations.

SQL Injections and How to Stop Them

Right now, there are hackers all around the world trying to get into your web applications. How safe are you? By using a technique called SQL injections, hackers can wreak havoc with web applications by compromising security, manipulating data, hoarding system resources, retrieving sensitive information, and manipulate data database objects such as dropping databases!

During the demo, we will take on a couple of different roles. As a hacker we will walk through steps a hacker might take to compromise a web application in order to retrieve sensitive data such as credit card information, usernames, passwords, and social security numbers. Assuming the role of a developer, we will then show various prevention techniques and their effectiveness in preventing SQL injections.

Attend this session to learn how SQL injections work, identify if you are being attacked, and how to stop them.

Update: Thank you to everyone who attended my session. We had a full room! 🙂

As requested, you can download the presentation and scripts here.

jeffprom-presenting

Mastering Master Data Services

As your organization grows, one challenge will be the management of data between systems and organizational units. With MDS, Microsoft provides the ability to have one accurate source of non-transactional data. This data can then be used within applications, other databases, and ETL processes. By leveraging data stewards to help manage the data repository, you can ensure that your MDS data is always up-to-date and accurate.

In this presentation we will cover MDS from start to finish including installation, creating models/entities, working with data, using the Excel add-in, security, hierarchies, views, versions, and business rules. After this presentation, you will have a good understanding of what master data management is, what MDS does, how to use it, and how to get started with your own MDS project.

To register for this event and to find additional information, you can visit the SQL Saturday Huntington Beach page here:
http://www.sqlsaturday.com/497/EventHome.aspx

Posted in Events, MDS, Security | 1 Comment »

SQL Saturday #492 – Phoenix 2016

Posted by jeffprom on March 9, 2016

sqlsat492_web
I recently moved from Minneapolis, Minnesota to San Diego, California. I’m certainly enjoying the warmer weather and am excited that I can check out SQL Saturday events in a new area. Along those lines, I am happy to announce that I have been accepted to present on Master Data Services at SQL Saturday #492 in Phoenix on April 16th! Below is a description of my presentation. I hope to see you at this event!

Mastering Master Data Services

As your organization grows, one challenge will be the management of data between systems and organizational units. With MDS, Microsoft provides the ability to have one accurate source of non-transactional data. This data can then be used within applications, other databases, and ETL processes. By leveraging data stewards to help manage the data repository, you can ensure that your MDS data is always up-to-date and accurate.

In this presentation we will cover MDS from start to finish including installation, creating models/entities, working with data, using the Excel add-in, security, hierarchies, views, versions, and business rules. After this presentation, you will have a good understanding of what master data management is, what MDS does, how to use it, and how to get started with your own MDS project.

To register for this event and to find additional information, you can visit the SQL Saturday Phoenix page here:
http://www.sqlsaturday.com/492/EventHome.aspx

Posted in Events, MDS | Leave a Comment »

SQLSaturday #291 – Chicago 2014

Posted by jeffprom on March 19, 2014

I am happy to announce that I was selected to speak at SQLSaturday Chicago! This event will be held April, 26 2014 at 1221 N Swift Road, Addison, IL 60101. I will be presenting on Master Data Services.

Master Data Services
Does your company have similar data in multiple locations? Not really sure which set of data is accurate? With MDS, Microsoft gives you the ability to have one accurate source of non-transactional data to use with your applications or send to other databases and ETL processes. Leverage data stewards to help manage the data repository to ensure it is up-to-date and accurate. In this session we will cover MDS from start to finish including installation, creating models/entities, working with data, using the Excel add-in, security, hierarchies, views, versions, and business rules. After this session, you should have a good understanding of what MDS does, how to use it, and how to get started with your own MDS project.

Additional information can be found here:
http://sqlsaturday.com/291/eventhome.aspx

Be sure to register for this event today!

Posted in Events, MDS | 2 Comments »

MN BI User Group – September 10th, 2013

Posted by jeffprom on August 21, 2013

MN BI User Group

On Tuesday, September 10th I will be speaking at the Minnesota BI User Group. The topic will be ‘Master Data Management and Microsoft Master Data Services.’ There will also be a 2nd presentation on ‘Recognizing The Parallel Data Warehouse (PDW) Workload’ presented by Jason Strate and Sanjay Kaul.

The event will be at the Microsoft office:
3601 76th Street West
Suite 600
Edina, MN 55435

For more info, visit the website at http://www.msbiusergroupmn.org

Register today!

Posted in Events, MDS | Leave a Comment »

Installing Master Data Services in SQL Server 2012

Posted by jeffprom on March 22, 2013

Master Data Services provides a central location where you can store non-transactional information. This repository serves as the primary source for accurate data within your organization. In this post I will show you how to install Master Data Services for SQL Server 2012. First launch the ‘Master Data Services Configuration Manger’ from the program menu.

MDS Config Manager

You will be presented with the main screen like the one shown below. If either of the two prerequisites, IIS and PowerShell, don’t check out you will need to install them before continuing with the installation.

MDS Config Manager Summary

Once you have PowerShell and IIS installed, select Database Configuration from the menu on the left.
On this screen you will be presented with two options. Create a new database, or select an existing MDS database. If you later decide to modify some settings on your MDS database, you can select your existing database from this screen, make your adjustments and then apply your changes. Since we are going to show a new installation let’s go ahead and select ‘Create Database’.

MDS Config Create Database

Now you will be presented with a Create Database wizard with several steps. First, we need to connect to the SQL Server instance where we want to install the MDS database. Select your authentication preference, User name and Password to connect. Click the Next button.

MDS Database Wizard Connect to Server

On the ‘Database’ tab of the wizard, enter a Database name. You can also set some settings such as collation. Once you are satisfied with the name and settings, click Next.

MDS Database Wizard Database Name

The next screen is where you enter the User name for the Administrator Account. Note: Once you click the Next button the wizard will try to authenticate the entered user account to a domain controller. You will need to make sure you can access the domain controller. If you cannot, it will provide an error and you cannot continue with the install.

MDS Database Wizard Administrator Account

Next is the ‘Summary’ screen, and finally the ‘Progress and Finish’ screen.
If everything checks out, you should see a success page like the one below.

MDS Database Wizard Success

Click Finish and you will be brought back to the Database Configuration section. However, this time you will now have a database and in the System Settings section you can make adjustments. Simply make your changes and click the Apply button.

MDS Selected Database

Next, we will configure IIS which will serve as one of the primary interfaces for MDS. Click on the Web Configuration option on the left. On this screen you can match up an IIS website with an MDS database. First we need to find a suitable IIS website for MDS. From the dropdown you can either select an existing website, or create a new one. Since we are doing a new install, we will select ‘Create new website’.

MDS Web Configuration Screen

Select a Website name and port. If MDS is the only website you plan on hosting from your server, you may want to leave it on port 80. If you host multiple sites, or are testing, feel free to enter in a new port number. In our case we are going to select port 991.

MDS Create Website

You can leave Protocol and IP Address as the default. However, you will need to add values to the Application Pool area. Select a name for your application pool, and provide a User name and Password. Once finished, click the OK button.

You should now have a Website listed. You should also have a Database listed under the ‘Associate Application with Database’ section. If you do not, simply click the Select button and find your MDS database you just created. You should now see a screen similar to the one below with an active ‘Apply’ button available to you. If you do, excellent, go ahead and click apply. This is where the leap of faith comes in. It can take about a minute to finish the install and it doesn’t give you a progress bar.

MDS Install Finish

After patiently waiting, you should finally be presented with a webpage like the one listed below. This is the Getting Started page. It offers some helpful links to get you going.

MDS Getting Started Page

Once you are ready to dig in, click the first link on the top called ‘Open the Master Data Manager home page’.
This will bring to you to the actual MDS home page where you can start working with MDS. I will cover those areas in more detail in future posts.

Even though the Getting Started page comes up, you will still have the last screen on the MDS installation open. If you have Data Quality Services installed and want to link it with MDS, this would be the time to do so. You would simply click on the now highlighted button called ‘Enable Integration with Data Quality Services’. More details about connecting MDS and DQS can be found on my other blog post here.

When you are done with the MDS configuration manager, simply click Exit and you’re ready to start using MDS!

Posted in MDS | Tagged: | 5 Comments »

SQL Saturday #206 – Madison, Wisconsin

Posted by jeffprom on February 18, 2013

SQL Saturday #206

Saturday, April 6th I will be presenting at SQL Saturday #206 in Madison, Wisconsin. This event will be held at Madison Area Technical College – Truax Campus, 3550 Anderson St, Madison, WI 53704.
I will be speaking on the following topic:

Master Data Services
Does your company have similar data in multiple locations? Not really sure which set of data is accurate? With MDS, Microsoft gives you the ability to have one accurate source of non-transactional data to use with your applications or send to other databases and ETL processes. Leverage data stewards to help manage the data repository to ensure it is up-to-date and accurate. In this session we will cover MDS from start to finish including installation, creating models/entities, working with data, using the Excel add-in, security, hierarchies, views, versions, and business rules. After this session, you should have a good understanding of what MDS does, how to use it, and how to get started with your own MDS project.

Check out the SQL Saturday #206 page for more information.

Hope to see you there!

Posted in Events, MDS | Tagged: , , | Leave a Comment »

MDS & DQS Posts Coming Soon

Posted by jeffprom on October 29, 2012

I’ve now given a couple of presentations on both Master Data Services (MDS), and Data Quality Services (DQS). However, I now realized I haven’t really blogged much about either. Well this is about to change! I have a whole series of posts planned which I will be posting in the near future. Here is a list of sub-topics which I will be posting on:

Data Quality Services

-What is it and what does it do?

-Installation

-Knowledge Bases

-Data Matching

-Data Cleansing

-Using DQS with SSIS

-Composite Domains

-Business Rules

-Reference Data Services

-Security

Master Data Services

-What is it and what does it do?

-Installation

-Creating & Managing Domains

-MDS Modeling

-Collections

-Excel Add-In

-Business Rules

-Using Versions

-Staging Data

-Hierarchies

-Importing & Export Models

-Views

-Security

More to come soon so stay tuned as I start to roll out some posts on both of these tools.

Posted in DQS, MDS | Leave a Comment »

Integrating MDS with DQS

Posted by jeffprom on September 5, 2012

I’ve installed both DQS and MDS several times, but often had trouble trying to integrate Master Data Services (MDS) with Data Quality Services (DQS). By integrating MDS and DQS, you gain the ability to do data matching on your MDS data. Sounds cool enough, right? In this post I will show how to integrate these two tools and enable a couple of DQS options when using MDS.

First, let’s run through the typical worst case scenario. After installing MDS, the very last step is that long button which enables DQS Integration.

Clicking this button, the install program looks in the same SQL Server Instance which you just installed MDS into and searches for a database called DQS_MAIN. If you haven’t yet installed DQS, it will fail and you will get the following error message.

If you installed MDS before installing DQS, all hope is not lost. After installing MDS, simply install DQS and then come back into the MDS Configuration screen and enable DQS integration.
Now when you can click the ‘Enable DQS Integration’ button, you should get a success message.

Ok, so we have both installed and they have been integrated. Now let’s take a look at our new found powers. If we fire up Excel and click on the ‘Master Data’ tab, you can see that we now have access to the Data Quality tools as well!

With these tools, we can now utilize the matching rules which we can setup in DQS.

Without integrating DQS with MDS, you can see here in this ‘before’ screenshot that the DQS tools are not available.

(If you don’t have the MDS Add-in for excel, you can download it here: http://go.microsoft.com/fwlink/?LinkId=219530)

Here is a video tutorial on how to use the DQS tools with MDS:
http://msdn.microsoft.com/en-us/sqlserver/hh828790.aspx

Here is a link which describes using Data Quality Matching in the MDS Add-in for Excel:
http://msdn.microsoft.com/en-us/library/hh548681.aspx

Posted in DQS, MDS | Tagged: , | Leave a Comment »