SQL SERVER – Adding Reference Data to Master Data Services – Notes from the Field #081

[Note from Pinal]: This is a 81th episode of Notes from the Field series. Master Data Services is one of the most important, but very little explored feature of SQL Server. If you have been reading this blog, when it is about BI, I always take help from LinchPin People who are BI experts. I requested Reeves from LinchiPin people to help me answer this unique question.

In this episode of the Notes from the Field series database expert Reeves Smith explains why one should add referencing data to Master Data services to our enterprise. Read the experience of Reeves in his own words.


Reeves Smith SQL SERVER   Adding Reference Data to Master Data Services   Notes from the Field #081

In a previous post, I talked about Master Data Services and how it can enhance your Enterprise Data Warehouse. Through the Master Data Services add-in for Excel, the business can access and manage reference data without much involvement with IT. This might sound like a bad idea, but the subject matter experts (SME) for the data often do not reside in IT. SMEs should have the ability to manage reference data, and with the Master Data Services add-in for Excel they can.

In this post, I’m going to outline the three steps needed to get your data governance people up and running after Master Data Services has been installed. The three are:

  • Create a model to store the reference data (completed on the MDS website)
  • Format the reference data in Excel
  • Create an Entity within the Master Data Services add-in for Excel

IT will only need to perform the first step of the process. The first step to manage reference data is creating a container for the reference data. This step must take place within the Master Data Services website.

Step 1 – Create a model to store the reference data

  1. Open the Master Data Service’s website and Select System Administration, as you see in Figure 1.

notes81 1 SQL SERVER   Adding Reference Data to Master Data Services   Notes from the Field #081

Figure 1 – Master Data Services System Administration

  1. After you select System Administration, the website will list three menus: Model View, Manage, and System. Select Manage and then select Models from the pop-out menu.
  2. Click on the green plus sign to create a new model.
  3. In the Add Model dialog, which you see in Figure 2, add a model name that would be relevant to the appropriate reference data. In this example I will use Demo. Unselect all of the check boxes under the model.

 

Figure 2 – Add Model

  1. Test your model by connecting to it from the Master Data Services add-in for Excel.
    1. Open a blank Excel workbook and select the Master Data Services tab.
    2. Click the Connect button to create a new connection to your Master Date Services Instance.
    3. In the Master Data Explorer, which you see in Figure 3, select your newly created model.

Figure 3 – Master Data Explorer

notes81 2 SQL SERVER   Adding Reference Data to Master Data Services   Notes from the Field #081

Step 2 – Format the reference data in Excel

  1. Create an Excel worksheet with the formatted reference data and provide a header for each of the columns.

Note: Master Data Services requires a Code column and suggests the use of a Name column. Both of these columns do not need to be directly called Name and Code but should have like columns within the reference data. The code column must contain unique values.

notes81 3 SQL SERVER   Adding Reference Data to Master Data Services   Notes from the Field #081

Step 3 – Create an Entity within the Master Data Services add-in for Excel

For this example, I will create a list of customers from the Adventure Works database to enable the discussion of enhancements to the data set in a later post. Your reference data should be less complex then the customers list.

  1. With an open connection to Master Data Services and the worksheet with your reference data, click the Create Entity button, as you see in Figure 4.

notes81 4 SQL SERVER   Adding Reference Data to Master Data Services   Notes from the Field #081

Figure 4 – Master Data Services tab with the Create Entity highlighted

  1. Click the red arrow to the right of the Range text box and select the range of your reference data. Be sure to include the column headers and select the My data has headers check box, as shown in Figure 5.
  2. Select the newly created model; in my case that would be Demo.
  3. Add and new entity name and select the appropriate Code and Name column.

Note: Master Data Services 2012 and forward enables the use of an identity values for the Code if you do not want to manage unique keys.

notes81 5 SQL SERVER   Adding Reference Data to Master Data Services   Notes from the Field #081

Figure 5 – Create Entity Dialog

  1. Click OK.

notes81 6 SQL SERVER   Adding Reference Data to Master Data Services   Notes from the Field #081

At this point you should have a working model with a new entity that is fully managed within Master Data Services.

Summary

In this post, I have walked through the steps that are required to move reference data from an Excel workbook and create a model within Master Data Services. Under MDS you get all of the benefits of a managed master data management solution, like audibility and security.

What’s Next

In a coming post, we will walk through enhancing or reference data. One enhancement we will demonstrate, will include adding domain values within the Model. This helps the business users select the correct data elements and increase the data quality of your master data.

Hope this helps.

If you want to get started with BIML with the help of experts, read more over at Fix Your SQL Server.

Reference: Pinal Dave (http://blog.sqlauthority.com)

SQL SERVER – SSIS and How to Load Binary Large Objects, or Blobs – Notes from the Field #080

[Note from Pinal]: This is a new episode of Notes from the Field series. Every time I give an introductory note, however, this time there is no need of intro note. This note is from Andy and as we all know he is amazing person when we have to understand the fundamentals. He has written this blog post with such an interesting way that you must read it to understand the very basic fundamental of SSIS.

andyleonard SQL SERVER   SSIS and How to Load Binary Large Objects, or Blobs   Notes from the Field #080


I still remember my first experience using SSIS to load binary large objects, or blobs. I was doing a gig for a large retailer, moving data for a new in-store application. Part of the data was product images, mostly 3kb-5kb files. During testing, I noticed the load ran much faster if I did not load the images. I wondered why, so I searched the interwebz for an answer.

I did not find an answer. I found lots of comments and posts telling me I could not, in fact, use SSIS (2005) to integrate blob data. At least not the way I was doing it. My response was, “Huh,” as I was already using SSIS to (apparently) do the impossible. I knew right away this represented an opportunity. I learned everything I could about blobs and now, just nine short years later, I’m here to sharea.

When I searched the interwebz this time, I found an excellent blog post by my friend, John Welch (blog | @john_welch), titled Importing Files Using SSIS. John’s idea is straightforward and very “SSIS-y,” as Kevin Hazzard (blog | @KevinHazzard) says. With John’s permission, I am modeling the example for this post on John’s post.

How Does SQL Server Store Blobs?

Before we dive into a demo project, it’s important to know more about how SQL Server stores blob data. The short answer is: it depends. I can hear you asking, “What does it depend on, Andy?” It depends on the size of the blob data. For larger binary large objects, a pointer to a file location is stored in the row. When the row is read, the pointer points to the file location containing the binary data, and the binary data is streamed to the output.

In this example, we’ll take a look at how we use SSIS to move data from the file system into a SQL Server table. I changed a few things but, again, this example was inspired by John Welch’s post titled Importing Files Using SSIS.

Part 1 – Import Column

The Import Column transformation streams file binaries – the contents of a file – into a binary large object (Blob) “column” in a Data Flow Path. From the Data Flow path, these data can be streamed into a database table Blob field. Let’s demonstrate:

In a default instance of SQL Server 2014, I created a database named ImportPics. Then I created a table named PicFile using this statement:

CREATE TABLE PicFile
(
ID INT IDENTITY(1,1)
,
FilePath VARCHAR(255)
,
FileContent IMAGE
)

Next I created an SSIS 2014 project named ImportPicFiles and renamed Package.dtsx to ImportPicFiles.dtsx. I added a Data Flow Task and created a package parameter named ImportFilesDir to hold the path to a directory filled with Snagit screenshots:

notes 70 1 SQL SERVER   SSIS and How to Load Binary Large Objects, or Blobs   Notes from the Field #080

I add a script component as a Source adapter, then configure it to consume the $Package::ImportFilesDir package parameter as a ReadOnlyVariable:

notes 70 2 SQL SERVER   SSIS and How to Load Binary Large Objects, or Blobs   Notes from the Field #080

I add an output column named filename (DT_STR, 255):

notes 70 3 SQL SERVER   SSIS and How to Load Binary Large Objects, or Blobs   Notes from the Field #080

In the Script Editor, I add the following code:

using System;
using System.Data;
using Microsoft.SqlServer.Dts.Pipeline.Wrapper;
using Microsoft.SqlServer.Dts.Runtime.Wrapper;
using System.IO;
[Microsoft.SqlServer.Dts.Pipeline.SSISScriptComponentEntryPointAttribute]
public class ScriptMain : UserComponent
{
public override void CreateNewOutputRows()
{
DirectoryInfo dir = new DirectoryInfo(Variables.ImportFilesDir.ToString());
foreach (var file in dir.GetFiles())
{
Output0Buffer.AddRow();
Output0Buffer.fileName = file.FullName;
}
Output0Buffer.SetEndOfRowset();
}
}

(Yes, that’s C#. I’m learning new things. Yay me! :{>)

Next, I add and configure an Import Columns transformation. On the Input Columns page, I select the fileName field:
notes 70 4 SQL SERVER   SSIS and How to Load Binary Large Objects, or Blobs   Notes from the Field #080

On the Input and Output Properties tab, I expand the Import Column Output node of the treeview, select Output Columns, and click the Add Column button. I name the column “FileContents” and set the DataType property to DT_IMAGE:

notes 70 5 SQL SERVER   SSIS and How to Load Binary Large Objects, or Blobs   Notes from the Field #080

This next part is a little tricky. You can learn more about configuring this – and other tricky SSIS transformation properties – here.

Select the LineageID property value for the FileContents column and copy it to the clipboard:
notes 70 6 SQL SERVER   SSIS and How to Load Binary Large Objects, or Blobs   Notes from the Field #080

Next, expand the Import Column Input treeview node, then expand Input Columns, and then select the fileName column. In the FileDataColumnID property, paste the value you just copied to the clipboard:

notes 70 7 SQL SERVER   SSIS and How to Load Binary Large Objects, or Blobs   Notes from the Field #080

Add an OLE DB Destination adapter, connect it to the database where you created the dbo.PicFile table earlier, and configure the OLE DB Destination adapter to load dbo.PicFile:

notes 70 8 SQL SERVER   SSIS and How to Load Binary Large Objects, or Blobs   Notes from the Field #080

A successful test execution of the data flow task will appear as shown:

notes 70 9 SQL SERVER   SSIS and How to Load Binary Large Objects, or Blobs   Notes from the Field #080

The Import Column transformation is a powerful to load files into database blob columns.

Read SSIS and Blobs, Part 2 to learn even more.

If you want to get started with SSIS with the help of experts, read more over at Fix Your SQL Server.

Reference: Pinal Dave (http://blog.sqlauthority.com)

SQL SERVER – Using Package Configurations in SSIS 2012 and Beyond – Notes from the Field #079

[Notes from Pinal]: I know quite a lot of things about SSIS but every single time when I read notes from the field, I realize that there are so many small but very important features exist. A similar concept has been Using Package Configurations in SSIS 2012 and Beyond. Packages are the most critical part of the SSIS and configuring it correctly is extremely important.

Tim Mitchell SQL SERVER   Using Package Configurations in SSIS 2012 and Beyond   Notes from the Field #079Linchpin People are database coaches and wellness experts for a data driven world. In this 79th episode of the Notes from the Fields series database expert Tim Mitchell (partner at Linchpin People) shares very interesting conversation related to using package configurations in SSIS 2012 and beyond.


If you are considering upgrading from an older version of SSIS to version 2012 or 2014 but are worried that you’ll lose the ability to use those package configurations you spent so much time developing, there is good news. Although it is not a heavily advertised feature in later versions, the classic package configuration option is still alive and well in SSIS 2012 and 2014.

The Configuration Design Pattern

Storing runtime configuration data outside of SSIS packages is a critical feature of a mature ETL process. Building a design pattern that externalizes values such as database connection strings, file paths, and other data that may change over time can reduce the amount of maintenance effort required later when those values need to be updated.

In versions of SSIS before 2012, the most common way to externalize connection strings and other runtime values was to use one or more SSIS package configurations. Although package configurations could be a little clunky at times, they provided a very effective means through which the ETL developer could avoid hard-coding variable data in packages.

This configuration pattern evolved significantly in 2012.  For new development in SSIS 2012 and later, the typical setup now involves using the SSIS catalog (which was first released with version 2012) to store and execute packages. Similarly, those designs usually include the use of package parameters and SSIS environments to supply runtime values for said parameters. As a result, the package configuration option is no longer the preferred method for variable externalization in new package development.

However, there are many organizations with significant investments in the old-style package configurations. One of the more common questions I’m asked about upgrading SSIS is whether package configurations can still be used in newer versions of SSIS. I’m happy to report that package configurations are still around (albeit a bit harder to find) and are just as usable in later versions of SSIS as they were in prior versions.

Configuring Package Parameters in SSIS 2012 and Later

In SSIS 2005 and 2008, you could access package configurations by simply right-clicking on an empty space in the package and selecting Package Configurations similar to what is shown below.

 notf 79 1 SQL SERVER   Using Package Configurations in SSIS 2012 and Beyond   Notes from the Field #079

However, if you’re using SSIS 2012 or 2014 in project deployment mode (the default setting for new projects), this option no longer exists.

 notf 79 2 SQL SERVER   Using Package Configurations in SSIS 2012 and Beyond   Notes from the Field #079

Even though the option no longer appears in this shortcut menu, it can still be accessed directly by using the package properties. In the package properties window, there is a collection called Configurations that will allow you to set one or more package configurations.

notf 79 3 SQL SERVER   Using Package Configurations in SSIS 2012 and Beyond   Notes from the Field #079

Clicking on the ellipsis next to this collection brings up the familiar package configurations menu, in which you can create XML, environment variable, or table storage configurations.

notf 79 4 SQL SERVER   Using Package Configurations in SSIS 2012 and Beyond   Notes from the Field #079

There are a couple of things to keep in mind on using package configurations in SSIS 2012 and beyond. First of all, you can use package configurations in addition to newer configuration methods (including package parameterization and SSIS environments). However, my recommendation is that you choose just one configuration method per project to avoid confusion or conflicting values. Also, be aware that the way package configuration values are logged differs from the way package parameter and SSIS environment values are logged in the SSIS catalog. If you do use the classic package configuration design pattern, be sure to review your execution logs to confirm that you’re getting all of the information you need to test, troubleshoot, and audit your package executions.

Conclusion

The old-style SSIS package configurations have largely yielded to the newer and more popular package parameters and SSIS environments. However, package configurations are still around and are fully accessible in later versions of the product.

If you want me to take a look at your server and its settings, or if your server is facing any issue we can Fix Your SQL Server.

Reference: Pinal Dave (http://blog.sqlauthority.com)

SQL SERVER – Recovering from Snapshots – Notes from the Field #078

[Note from Pinal]: This is a 78th episode of Notes from the Fields series. What do you do when data is deleted accidentally or you are facing disaster? Well, there are plenty of the things, you can do, but when in panic quite often people make mistakes which just intensify the disaster. Database snapshot is very important but less frequently used feature.

JohnSterrett SQL SERVER   Recovering from Snapshots   Notes from the Field #078

In this episode of the Notes from the Field series database expert John Sterrett (Group Principal at Linchpin People) explains a very interesting subject of how to recover the database from snapshots. Linchpin People are database coaches and wellness experts for a data driven world. Read the experience of John in his own words.


One of the most common – and most forgotten – scenarios in disaster recovery plans is data being updated or deleted accidentally. This surprises me because, in the field, I see accidental data changes as a common disaster. If you have enterprise edition you can leverage database snapshots as a step in your disaster recovery plan to help recover faster, or at least allow you to pull back the majority of your data while you concurrently restore backups (to bring the rest of your data back). Restoring most of your data quickly can be critical with very large databases (VLDBs), especially if you have tight Recovery Time Objectives (RTOs).

Database snapshots require sparse files and store the original pages in these files when they are changed. Therefore, you will want to manage the sparse files and make sure you purge database snapshots as needed. You might also be using database snapshots without even knowing it. If you are doing database integrity checks with the default values, a special snapshot is taken in the background and your integrity checks are executed against that snapshot.

HOW DO WE CREATE A SNAPSHOT?

The following example uses the AdventureWorks2014 sample database. We need to use the CREATE DATABASE syntax, include the names of your data files, and include the file location of the sparse files. Finally, include AS SNAPSHOT OF database syntax to define the database as a snapshot.

CREATE DATABASE [AW2014_Snapshot_morning] ON
( NAME = AdventureWorks2014_Data, FILENAME =
'C:\Demo\AW_data.ss')
AS SNAPSHOT OF AdventureWorks2014

REAL-WORLD ACCIDENTAL DATA CHANGE STATEMENT

Here is a common case of a mistaken DELETE statement. We have a DELETE statement with the primary key included to delete a single row. By mistake we didn’t highlight the filter so all the rows will be deleted.

If you execute this statement, you will see an unexpected result: we deleted all rows in the table as shown below.

notd 78 SQL SERVER   Recovering from Snapshots   Notes from the Field #078

(19972 row(s) affected)

HOW DO WE REVERT FROM SNAPSHOT?

You have two options if you created a database snapshot earlier.

First, you could insert the data back from your snapshot database as shown below. This could be done with SSIS, BCP or many other tools. For this quick example we will do this with an INSERT INTO SELECT statement.

SET IDENTITY_INSERT Person.EmailAddress ON
INSERT INTO
Person.EmailAddress (BusinessEntityID, EmailAddressID, EmailAddress, rowguid, ModifiedDate)
SELECT *
FROM AW2014_Snapshot_morning.Person.EmailAddress
SET IDENTITY_INSERT Person.EmailAddress OFF

Second, you can revert the database from the snapshot. Keep in mind this second option will revert all data changes in the database not just the data deleted in your accidental data change statement.

USE MASTER;
RESTORE DATABASE AdventureWorks2014 FROM
DATABASE_SNAPSHOT = 'AW2014_Snapshot_morning';
GO

From these examples, you can see database snapshots are a tool to help you recover data quickly. Please note that you wouldn’t want database snapshots to be your sole plan for disaster recovery and unplanned data change statements. If your snapshot or the original database suffers corruption, you wouldn’t be able to recover. So make sure you add snapshots into your existing disaster recovery plans, which should – at a minimum – include database backups to give you a better recovery point objective.

Are your servers running at optimal speed or are you facing any SQL Server Performance Problems? If you want to get started with the help of experts read more over here: Fix Your SQL Server.

Reference: Pinal Dave (http://blog.sqlauthority.com)

Hey DBA – Watch Your Vendors! Hey Vendors – Try a Little Harder! – Notes from the Field #077

[Note from Pinal]: This is a 77th episode of Notes from the Field series. Every week, I personally wait for the notes from the fields from Mike because it contains lessons of life which directly impacts DBA and Developer’s life positively. Mike Walsh is an amazing person to meet and know. I am fortunate to be his friend. Every single time I meet him, I learn something as a human. He is always looking for an opportunity to improve the life of DBAs and Developers.

mikewalsh Hey DBA – Watch Your Vendors! Hey Vendors – Try a Little Harder!   Notes from the Field #077Everybody uses third party softwares and third party vendors in their organization. Our ecosystem is built around that concept and it is absolutely difficult to avoid this situation. When two different parties depend on each other to succeed in their life there should be some basic ground rules to follow for mutual success. I asked the very same question “How should be the relationship between Vendors and DBA?” and database expert Mike Walsh decided to guide me with the answer of this question.

Read the entire story in his own words.


It seems I rant about independent software vendors about once every 6-8 months. I’ve not done it on a post here on Pinal’s fine blog, though. Why do I do this? Because it really matters. Because it really should be different. Because I’ve seen the difference when DBAs and Vendors come together, plan properly and have a working relationship. Finally – because I’ve seen environments brought down hard by a failure in this important relationship and in this important “unwritten contract” between the parties. So, I’m continuing with the theme of the “Hey DBA!” type posts with advice we can all use, but I’m talking to vendors, too.

If your company buys software that your business runs on – don’t get stuck in this trap and learn some questions to ask the vendors.

If your company sells software to other companies? Make sure these things are clear for all of your customers and people know what they own and don’t own.

vendor Hey DBA – Watch Your Vendors! Hey Vendors – Try a Little Harder!   Notes from the Field #077

What Does Failure Look Like Here?

It takes different shapes. In most cases the following symptoms are there (not all are there, but many may be):

  • No maintenance is being done (no index rebuilds, no statistics updates, no DBCC CHECKDB jobs)
  • Sometimes an incorrect backup strategy exists
  • The database for an application is on an undersized, underperforming SQL Server
  • SQL Server is missing a lot of best practices and configurations, instead it feels more like it was installed by clicking next a bunch of times and finish once and never again touched.

But these symptoms can lead to downtime. They can lead to unresolved, and undetected corruption. They can lead to a restore that doesn’t go exactly as the business planned or expected or as the application vendor wants.

In many environments, a company will buy software that runs on SQL Server and not even fully realize or understand that it is running on an enterprise database platform that needs attention, care and regular maintenance. Often times the client eventually realizes it only when they’ve had a disaster and are trying to pick up the pieces and get their critical application online.

DBAs – What can you do?

Be on the lookout for applications coming into the company. This is sometimes easier said than done – but trying to get yourself into meetings before an application is purchased to understand the database needs can make a huge difference. Let it be known that you are there to help and you want to make a difference in the stability, performance and uptime of any new applications that come in and have a database backend. Let your managers speak on your behalf, let the project managers know, be vigilant and get involved when applications are brought in. And earlier is better. If a vendor is doing something dangerous, against best practices, or in violation of a security audit you have to go through – it is easier to get them to make those changes in the sales process than after you’ve purchased the application.

Be proactive – in so many of my posts here and on my own blog at http://www.straightpathsql.com and over at http://www.linchpinpeople.com, I focus on being proactive. Get to know your environments. Run checks on them, understand the uses, and understand the maintenance. Over at Linchpin People we have a free WellDBA™ Checklist that you can use to go through and check the most common mistakes we find in production environments. Grab that checklist and check your own environment. Dig in where there are issues. You can get that free checklist here (http://www.linchpinpeople.com/landing/well-dba-sql-server-checklist/)

Be Inquisitive – On my blog, I have a list of some sample questions you can ask a vendor. This checklist is a bit older now and I should probably update it – but the point in the questions should become clear as you look at them. You want to ask the questions of the vendor to know what challenges you’ll face as a DBA. You want to gauge who is responsible for which pieces, you want to get to know and trust your vendor and understand their expectations of you and what they are great at and what you may need to spend more time looking. Create a vendor interview question yourself and make sure any new software vendors bringing applications into your environment give you the information you need. You can see an example checklist on my personal blog – http://www.straightpathsql.com/archives/2009/01/dba-questions-to-ask-a-vendor/

vendor2 Hey DBA – Watch Your Vendors! Hey Vendors – Try a Little Harder!   Notes from the Field #077

Vendors – What can you do?

It’s pretty simple – be clear, be informative and be prescriptive…

Be Clear – Your customers may or may not understand what they are getting into with a database. They may have a great DBA, no DBA or a mediocre DBA. Be clear on what you expect from them and what they have to worry about. You can’t just say, “they’ll figure it out… backups are really important so they’ll understand this and they’ll start doing them…” You’ve made some big assumptions there. That’s true about maintenance, knowing what to do when there are issues, etc. Be clear on what you are responsible for, what you are not responsible for and what they should be worried about and what they should take care of on their own.

Be Informative – Don’t just be clear – but get to know your customers. If you are deploying a SQL server application – you should explain that you like to work with a DBA during the pre-sales and installation process. Get to know the DBA team. Talk about challenges that folks face – check out that set of questions above and think of other questions you’d want to ask if you were a DBA. Have answers to them and supply them even if the client doesn’t ask.

Be Prescriptive – I love it when I see a vendor with best practice guides, deploy guides, maintenance guides, SQL Server configuration guides, etc. These things are gold to your customers. But they are also gold to your image, and your respect and your referrals. If you prescribe what the environment should generally look like, what kind of maintenance should be done, what kind of recovery typically works best and give advice on how to configure SQL for success – you are equipping your customers for greater success. You don’t need to go specifics on everything but general prescriptions here, with latitude for companies with DBA teams that understand how to turn your guidelines into reality within their existing policies is great. But you should consider being specific enough to show the smaller shops with a system administrator playing the role of DBA what they should be learning about and either figuring out or bringing in consulting or mentoring help to get right with them.

Doing these things makes you ahead of the curve of software vendors and puts you in a great spot – and it probably saves your support budget down the road with calls that are not needed by your customers not paying attention to their SQL environment.

Everyone – What can you do?

Talk… Seriously – many of these issues come down to communication. A lot of DBAs complain about software vendors. A lot of software vendors probably secretly complain about their customers. If we all talked just a bit more about these things we’d be in a better spot and our environments would be better for it.

If you want to get started with performance analytics and Database Healthcheck of SQL Servers with the help of experts, read more over at Fix Your SQL Server.

Reference: Pinal Dave (http://blog.sqlauthority.com)

SQL SERVER – Walking the Table Hierarchy in Microsoft SQL Server Database – Notes from the Field #076

[Note from Pinal]: This is a 76th episode of Notes from the Field series. Hierarchy is one of the most important concepts in SQL Server but there are not clear tutorial for it. I have often observed that this simple concept is often ignored or poorly handled due to lack of understanding.

In this episode of the Notes from the Field series database expert Kevin Hazzard explains Table Hierarchy in Microsoft SQL Server Database. Read the experience of Kevin in his own words.


KevinHazzard SQL SERVER   Walking the Table Hierarchy in Microsoft SQL Server Database   Notes from the Field #076

When you need to manage a set of tables in Microsoft SQL Server, it’s good to know the required order of operations. The order could be hard-coded into the process but such approaches tend to fail when the database schema evolves. Instead, I prefer to use the catalog view named [sys].[foreign_keys] to discover the relationships between tables dynamically. Long ago, I wrote a function called [LoadLevels] that I’ve used in hundreds of processes to make them reusable and more resilient. The code for that function is shown in Listing 1:

-- ==========================================================================
-- Description: Get the load levels by tracing foreign keys in the database.
-- License:     Creative Commons (Free / Public Domain)
-- Rights:      This work (Linchpin People LLC Database Load Levels Function,
--              by W. Kevin Hazzard), identified by Linchpin People LLC, is
--              free of known copyright restrictions.
-- Warranties:  This code comes with no implicit or explicit warranties.
--              Linchpin People LLC and W. Kevin Hazzard are not responsible
--              for the use of this work or its derivatives.
-- ==========================================================================
CREATE FUNCTION [dbo].[LoadLevels]()
RETURNS @results TABLE
(
[SchemaName] SYSNAME,
[TableName] SYSNAME,
[LoadLevel] INT
)
AS
BEGIN
WITH
[key_info] AS
(
SELECT
[parent_object_id] AS [from_table_id],
[referenced_object_id] AS [to_table_id]
FROM [sys].[foreign_keys]
WHERE
[parent_object_id] <> [referenced_object_id]
AND [is_disabled] = 0
),
[level_info] AS
(
SELECT -- anchor part
[st].[object_id] AS [to_table_id],
0 AS [LoadLevel]
FROM [sys].[tables] AS [st]
LEFT OUTER JOIN [key_info] AS [ki] ON
[st].[object_id] = [ki].[from_table_id]
WHERE [ki].[from_table_id] IS NULL
UNION ALL
SELECT -- recursive part
[ki].[from_table_id],
[li].[LoadLevel] + 1
FROM [key_info] AS [ki]
INNER JOIN [level_info] AS [li] ON
[ki].[to_table_id] = [li].[to_table_id]
)
INSERT @results
SELECT
OBJECT_SCHEMA_NAME([to_table_id]) AS [SchemaName],
OBJECT_NAME([to_table_id]) AS [TableName],
MAX([LoadLevel]) AS [LoadLevel]
FROM [level_info]
GROUP BY [to_table_id];
RETURN
END

The [LoadLevels] function walks through the table relationships in the database to discover how they’re connected to one another. As the function moves from one relationship to the next, it records the levels where they exist in the hierarchy. A partial output of the function as executed against Microsoft’s AdventureWorks2014 sample database is shown in Figure 1.

notes76 SQL SERVER   Walking the Table Hierarchy in Microsoft SQL Server Database   Notes from the Field #076

Ordering to show the highest load levels first, notice that the most dependent table in the AdventureWorks2014 database is [Sales].[SalesOrderDetails]. Since the load levels are zero-based in the function output, that table is eight levels high in the hierarchy. In other words, if I were developing an Extract, Transform & Load (ETL) system for [Sales].[SalesOrderDetails], there are at least seven other tables that need to be loaded before it. For all 71 tables in the AdventureWorks2014 database, the function reveals some interesting facts about the load order:

  • Level 0 – 25 tables, these can be loaded first
  • Level 1 – 8 tables, these can be loaded after level 0
  • Level 2 – 8 tables, these can be loaded after level 1
  • Level 3 – 19 tables, …
  • Level 4 – 7 tables
  • Level 5 – 1 table
  • Level 6 – 1 table
  • Level 7 – 2 tables, these must be loaded last

The [LoadLevels] function uses two Common Table Expressions (CTE) to do its work. The first one is called [key_info]. It is non-recursive and gathers just the foreign keys in the database that aren’t self-referencing and aren’t disabled. The second CTE is called [level_info] and it is recursive. It starts by left joining the tables in the database to the foreign keys from the first CTE, picking out just those tables that have no dependencies. For the AdventureWorks2014 database, for example, these would be the 25 tables at level zero (0).

Then the recursion begins by joining the output from the previous iteration back to the key information. This time however, the focus is on the target of each foreign key. Whenever matches are found, the reference level is incremented by one to indicate the layer of recursion where the relationship was discovered. Finally, the results are harvested from the [level_info] CTE by grouping the table object identifiers, resolving the schema and table names, and picking off the maximum load level discovered for each entity.

The reason for grouping and selecting the maximum load level for any table becomes clear if you remove the GROUP BY clause and the MAX() operator from the code. Doing that reveals every foreign key relationship in the database. So for example, in the AdventureWorks2014 database, the [Sales].[SalesOrderDetails] table appears in 22 different relationships ranging from three levels high in the hierarchy to eight levels high, output as [LoadLevel] 7 in Figure 1. By grouping and selecting the maximum level for any table, I’m certain to avoid loading tables too early in my dynamic ETL processes.

In summary, you can use the [LoadLevels] function to identify dependencies enforced by foreign key constraints between tables in your database. This information is very useful when developing a process to copy this data to another database while preserving the referential integrity of the source data.

If you want to get started with SQL Server with the help of experts, read more over at Fix Your SQL Server.

Reference: Pinal Dave (http://blog.sqlauthority.com)

SQL SERVER – The Basics of the File System Task – Part 2 – Notes from the Field #075

[Note from Pinal]: This is a new episode of Notes from the Field series. SQL Server Integration Service (SSIS) is one of the most key essential part of the entire Business Intelligence (BI) story. It is a platform for data integration and workflow applications.

andyleonard SQL SERVER   The Basics of the File System Task   Part 2   Notes from the Field #075

In this episode of the Notes from the Field series I asked SSIS Expert Andy Leonard a very crucial question – What are the Basics of the File System Task and where do we start with it? Andy was very kind to answer the questions and provides plenty of information about how a novice developer can learn SSIS from the beginning and become expert in the technology.


Many data integration scenarios involve reading data stored in flat files or performing extracts from a relational (or legacy) system into flat files. Learning how to configure and use the SQL Server Integration Services (SSIS) File System Task will support your efforts when loading data to and from flat files. In a previous article, I described configuring the File System Task to archive a file. In this article, I will repeat the exercise, but I will add flexibility (and complexity – the two always go together) by using SSIS Variables to manage the Source File and Destination Directory locations. This article is an edited version of The Basics of the File System Task, Part 1. I chose to write it this way for those who find this article but haven’t read Part 1.

Remember: SSIS is a software development platform. With “SQL Server” included in the name, it is easy for people to confuse SSIS as a database tool or accessory, but Control Flow Tasks put that confusion to rest.

SSIS provides several Control Flow tasks. Here is a list that provides a good approximation of which tasks I use most, from most-used to least-used:

In this article I provide an advanced example of configuring the SSIS File System Task, shown in Figure 1:

notes 75 1 SQL SERVER   The Basics of the File System Task   Part 2   Notes from the Field #075
Figure 1: SSIS File System Task

The File System Task provides one way to implement an SSIS Design Pattern for source file archival. When you first open the File System Task Editor, you will note several properties in the property grid. Whenever you see an Operation property in an SSIS task editor, know that that property drives the other property selections. Options for the Operation property of the SSIS File System Task are shown in Figure 2:
notes 75 2 SQL SERVER   The Basics of the File System Task   Part 2   Notes from the Field #075
Figure 2: SSIS File System Task Operation Property Options

The Operation options are:

  • Copy directory
  • Copy file (default)
  • Create directory
  • Delete directory
  • Delete directory content
  • Delete file
  • Move directory
  • Move file
  • Rename file
  • Set Attributes

I stated the Operation property drives the other property selections. Take a look at the File System Task Editor when I change the Operation option from “Copy file” (Figure 2) to “Delete file” as shown in Figure 3:
notes 75 3 SQL SERVER   The Basics of the File System Task   Part 2   Notes from the Field #075
Figure 3: The File System Task Editor with the “Delete file” Operation Selected

See? There are less properties required for the “Delete file” operation. The available properties are even more different for the “Set Attributes” operation, shown in Figure 4:
notes 75 4 SQL SERVER   The Basics of the File System Task   Part 2   Notes from the Field #075
Figure 4: The File System Task Editor with the “Set Attributes” Operation Selected

The Operation property changes the editable properties, exposing some and hiding others. With flexibility come complexity. Even though the File System Task is complex, I’ve found the task is stable and extremely useful. Let’s look at a practical example; using the File System Task to archive a flat file.

To begin configuring the SSIS File System Task for file archival, select the “Move file” operation as shown in Figure 5:
notes 75 5 SQL SERVER   The Basics of the File System Task   Part 2   Notes from the Field #075
Figure 5: SSIS File System Task with the “Move file” Operation Selected

Using the IsSourcePathVariable and IsDestinationPathVariable properties extends the flexibility of the File System Task and further changes the list of available properties in the property grid, as shown in Figure 6:
notes 75 6 SQL SERVER   The Basics of the File System Task   Part 2   Notes from the Field #075
Figure 6: Opting to Use Variables for Source and Destination Paths

Note the SourceConnection and DestinationConnection properties are hidden and the SourceVariable and DestinationVariable properties are available in their place. Click the SourceVariable property dropdown, and click “<New variable…>” as shown in Figure 7:
notes 75 7 SQL SERVER   The Basics of the File System Task   Part 2   Notes from the Field #075
Figure 7: Selecting “<New variable…>” from the SourceVariable Property

When the Add Variable window displays, enter “SourceFilePath” for the variable name property and a full path to your source file in the Value textbox, as shown in Figure 8:
notes 75 8 SQL SERVER   The Basics of the File System Task   Part 2   Notes from the Field #075
Figure 8: Configuring the SourceFilePath SSIS Variable

Click the OK button to close the Add Variable window and return to the File System Task Editor. Click the DestinationVariable property dropdown, and then click “<New variable…>” to open a new Add Variable window. Configure the new variable by setting the Name property to “DestinationFolder” and the Value property to a location you wish to move the file, as shown in Figure 9:
notes 75 9 SQL SERVER   The Basics of the File System Task   Part 2   Notes from the Field #075
Figure 9: Configuring the DestinationFolder SSIS Variable

Click the OK button to close the Add Variable window and return to the File System Task Editor. You have configured an SSIS File System Task to move a file using SSIS Variables to manage the source and destination of the file, as shown in Figure 10:
notes 75 10 SQL SERVER   The Basics of the File System Task   Part 2   Notes from the Field #075
Figure 10: An SSIS File System Task Configured to Move a File Using SSIS Variables

The SSIS File System Task is now configured to archive a file. Let’s test it! Click the OK button to close the File System Task Editor. Press the F5 key or select SSIS->Start Debugging to test your work. My result is shown in Figure 11:
notes 75 11 SQL SERVER   The Basics of the File System Task   Part 2   Notes from the Field #075
Figure 11: Successful Test Execution of the SSIS File System Task

Viewing the source and destination directories, we see the file was successfully moved – shown in Figure 12:
notes 75 12 SQL SERVER   The Basics of the File System Task   Part 2   Notes from the Field #075
Figure 12: The File, Moved!

One tricky part when configuring the SSIS File System Task to move a file is realizing that you need to select the actual file for the source and the directory for the destination.

As I stated earlier, the SSIS File System Task is powerful, flexible, and robust. This article has demonstrated another way you can use the File System Task to archive files. Archiving files after loading the data they contain is a common practice in data integration.

If you want to get started with SSIS with the help of experts, read more over at Fix Your SQL Server.

Reference: Pinal Dave (http://blog.sqlauthority.com)

SQL SERVER – 3 Common Mistakes of Agile Development – Notes from the Field #074

[Note from Pinal]: This is a 74th episode of Notes from the Field series.  Agile is a time boxed, iterative approach to software delivery that builds software incrementally from the start of the project, instead of trying to deliver it all at once near the end. Storytelling is an art very few have mastered in their life. When I requested Stuart to share his ideas about agile, he shared a very interesting insight on this subject. He had very interesting story to share about 3 common mistakes of agile developments. I very much enjoyed his notes from the field and I am confident that you will like it too.

In this episode of the Notes from the Field series database expert Stuart Ainsworth explains about 3 Common Mistakes of Agile Development.


 SQL SERVER   3 Common Mistakes of Agile Development   Notes from the Field #074

I’m a developer by history, but a project manager at heart.  I’ve started becoming much more interested in helping teams improve their workflow, not just helping them write better code.  To that end, most of the development shops that I’ve worked have struggled with the ongoing battle to get features built and shipped on a schedule that satisfies business requirements.  Good developers are proud of their craft, and want maximum time to create; business needs features to go out the door quickly in order to compete.  These goals are often in conflict with each other.

Agile methodologies (such as scrum) try to help balance this tension by encouraging active conversation between business and development, and continuously delivering working software that is flexible and adaptable to change.  In the shops where I’ve seen agile development fail to deliver, I’ve noticed the following 3 bad habits:

  1. We have a failure to communicate.

Communication sounds easy, but it’s really, really hard.  Well-defined requirements help, but they’re no substitute for ongoing mutual conversations between developers and business people.  If a requirements document is the only method of explaining what to build and when to build it, you lose the ability to forecast what comes next in terms of building blocks.

Several of the principles of that Agile Manifesto deal with communication between business and development, but my favorite one is “business people and developers must work together daily throughout the project.”  If you want to be an agile development shop, you have to be an agile business.   Business needs to understand the challenges faced by developers, and developers need to be involved in understanding the changing needs of business.

  1. Code releases are always a feature release or a bug fix.

Bug fixes are good, and features are what make money; however, if the only time your shop is releasing code is to implement a feature or fix a bug, then you’re not continually improving your product.  The temptation is to fall back into a waterfall-like methodology; deployments become huge stacks of code that are not added to the product (or operational environment) until the day that a feature is supposed to be released.  The larger the stack of code, the harder it is to test, and the greater the risk of failure.

What agile principles suggest is that you should “deliver working software frequently, from a couple of weeks to a couple of months, with a preference to the shorter timescale.”  The goal is to release code frequently, even if that code is only establishing groundwork for future development efforts; smaller code is easier to test, and ultimately, a feature release becomes the final step in a series of releases.

  1. Software release dates depend on the quality and completeness of the code.

This is similar to the second issue, but a slight variation; a shop that constantly changes the length of their iteration will ultimately experience burnout.  It becomes harder and harder to stay on schedule and feature releases get pushed further and further back.

I prefer to have a fixed iteration period, either every four weeks or once a month.  There’s something about a cadence that motivates people to focus and get things done.  If a developer is working on a bit of code that’s supposed to ship in a month, it’s easy to evaluate how likely that’s going to happen within a couple of weeks; if it’s not going to be complete, build the working software, and release it.  With each iteration, it becomes easier to define what can be done in a fixed-length sprint.

Summary

Agile software development lends itself to creative implementations, but it’s important to stay true to the principles of good communication, continuous improvement, and maintaining a constant pace for development.  Avoiding some basic pitfalls can help your team stay productive in the ongoing race to get features out the door.

If you want to get started with SQL Server with the help of experts, read more over at Fix Your SQL Server.

Reference: Pinal Dave (http://blog.sqlauthority.com)

SQL SERVER – Mistake to Avoid: Duplicate and Overlapping Indexes – Notes from the Field #073

Kathi SQL SERVER   Mistake to Avoid: Duplicate and Overlapping Indexes   Notes from the Field #073[Note from Pinal]: In this episode of the Notes from the Field series database expert Kathi Kellenberger explains about indexes and its impact. We often believe that indexes will improve the performance of the query, but it is not true always. There are cases when indexes can reduce the performance as well. Read the experience of  Kathi in her own words.


Having the correct indexes in place can improve the performance of your queries immensely. Knowing just what the correct indexes are, however, is not an easy task. SQL Server provides tools to help you create indexes based on a specific query or the workload that has run since the last restart of the instance. While these are handy tools, you must take the guidance they give with the time-honored grain of salt.

Having done my share of index tuning over the past few years, I have seen the consequences of blindly creating indexes based on the suggestions of the Database Engine Tuning Advisor, the missing index DMV, or the missing index recommendation found in the execution plan. It’s easy to copy a slowly running query into the Query window of SSMS and view the execution plan only to find that an index can be created to drastically improve the query performance. The problem is that this recommendation does not consider indexes that are already in place. By creating every index that these tools suggest, you will end up with too many indexes and many that overlap. At first you may not think this is a problem: more is better, right?

Having too many nonclustered indexes can cause numerous problems. First, unneeded indexes take up space. This impacts storage costs, backup and recovery times, and index maintenance times. Indexes must be kept up to date whenever data changes. The performance of inserts, updates, and deletes is impacted by nonclustered indexes. Have you ever heard of a SELECT query that runs more slowly because there are too many indexes on a table? I have seen it happen. When the optimizer comes up with a plan for a query, it must consider the available indexes, three types of joining, order of joins, etc. The number of plan choices increases exponentially. The optimizer won’t take long to come up with a plan, however, and will sometimes stop with a “good enough plan”. It’s possible that the optimizer didn’t have enough time to figure out the best index because there were too many to consider.

To avoid creating unnecessary indexes, always take a look at the existing indexes on the table when you think you should add a new one. Instead of creating a brand new index, maybe you can just add a key or included columns to an existing index. Following this practice will help keep the number of indexes from spiraling out of control. Another thing to watch out for is the cluster key. The cluster key is included in every nonclustered index. It’s there to locate rows in the clustered index, but you won’t see it in the nonclustered index definition. The index tools will often tell you to add the cluster key as included column, but that is not necessary.

By following all of the index recommendations from the tools without considering other indexes, you will end up with tables that resemble the following:

CREATE TABLE TestIndexes(Col1 INT, col2 VARCHAR(10), Col3 DATE, Col4 BIT);
CREATE INDEX ix_TestIndexes_Col1 ON dbo.TestIndexes (col1);
CREATE INDEX ix_TestIndexes_Col1_Col2 ON dbo.TestIndexes (col1, col2);
CREATE INDEX ix_TestIndexes_Col1_Col2_Col3 ON dbo.TestIndexes (col1, col2, Col3);
CREATE INDEX ix_TestIndexes_Col2 ON dbo.TestIndexes (col2);
CREATE INDEX ix_TestIndexes_Col1_includes1 ON dbo.TestIndexes (col1) INCLUDE(Col4);
CREATE INDEX ix_TestIndexes_Col1_includes2 ON dbo.TestIndexes (col1) INCLUDE(Col2);

You may think that this is a contrived example, but I see this pattern all the time. How do you find the overlapping indexes that need to be cleaned up? There are many scripts available, but here is a simple script that just looks at the first two index keys for duplicates:

WITH IndexColumns AS (
SELECT '[' + s.Name + '].[' + T.Name + ']' AS TableName,
i.name AS IndexName,  C.name AS ColumnName, i.index_id,ic.index_column_id,
COUNT(*) OVER(PARTITION BY t.OBJECT_ID, i.index_id) AS ColCount
FROM sys.schemas AS s
JOIN sys.tables AS t ON t.schema_id = s.schema_id
JOIN sys.indexes AS i ON I.OBJECT_ID = T.OBJECT_ID
JOIN sys.index_columns AS IC ON  IC.OBJECT_ID = I.OBJECT_ID
AND IC.index_id = I.index_id
JOIN sys.columns AS C ON  C.OBJECT_ID = IC.OBJECT_ID
AND C.column_id = IC.column_id
WHERE IC.is_included_column = 0
)
SELECT DISTINCT a.TableName, a.IndexName AS Index1, b.IndexName AS Index2
FROM IndexColumns AS a
JOIN IndexColumns AS b ON b.TableName = a.TableName
AND b.IndexName <> a.IndexName
AND b.index_column_id = a.index_column_id
AND  b.ColumnName = a.ColumnName
AND a.index_column_id < 3
AND a.index_id < b.index_id
AND a.ColCount <= B.ColCount
ORDER BY a.TableName, a.IndexName;

notd 73 SQL SERVER   Mistake to Avoid: Duplicate and Overlapping Indexes   Notes from the Field #073

Once you find indexes that are subsets or even exact duplicates of other indexes, you should manually review each match and figure out what can be deleted or consolidated. Just be sure to test your changes before implementing them in production. After reviewing the information, I came up with the following changes:

DROP INDEX ix_TestIndexes_Col1 ON TestIndexes;
DROP INDEX ix_TestIndexes_Col1_Col2 ON TestIndexes;
DROP INDEX ix_TestIndexes_Col1_Col2_Col3 ON TestIndexes;
DROP INDEX ix_TestIndexes_Col1_includes1 ON TestIndexes;
DROP INDEX ix_TestIndexes_Col1_includes2 ON TestIndexes;
CREATE INDEX ix_TestIndexes_Col1_Col2_Col3_includes ON TestIndexes
(Col1, Col2, Col3) INCLUDE (Col4);

Now there are two indexes, none of them overlap, and I haven’t lost anything that was in place before.

I always say that index tuning is both a science and an art. Now when you use the science of missing index information you will know how to apply the art of avoiding duplicates.

If you want to get started with BIML with the help of experts, read more over at Fix Your SQL Server.

Reference: Pinal Dave (http://blog.sqlauthority.com)

SQL SERVER – Adding a Master Data Services to your Enterprise – Notes from the Field #072

[Note from Pinal]: This is a 72th episode of Notes from the Field series. Master Data Services is one of the most important but very little explored feature of SQL Server. If you have been reading this blog, when it is about BI, I always take help from LinchPin People who are BI experts. I requested Reeves from LinchiPin people to help me answer this unique question.

In this episode of the Notes from the Field series database expert Reeves Smith explains why one should add Master Data services to our enterprise. Read the experience of Reeves in his own words.


Reeves Smith SQL SERVER   Adding a Master Data Services to your Enterprise   Notes from the Field #072

With all of the focus on big data, I want to remind everyone not to forget about data governance and master data management in general. While working on various data warehouse projects, I have found this area to be often overlooked. I want to stress that if you are building a data warehouse or a reporting system, then data governance should be one of the key components of almost any project.

I wanted to start off with a couple of posts to show how you could implement SQL Server Master Data Services in you enterprise. Tyler Gramm wrote a white paper “Implementing a Phased Approach to Master Data Management” (https://msdn.microsoft.com/en-us/library/ff626496.aspx) that outlines taking small steps to start your first master data management project. In this series I will outline some steps to help bring reference data in SQL Server Master Data Services instance to start you off on your way.

Let’s first define data governance to better understand where master data management fits within that methodology. Data governance can be defined as a complete process with the ability to manage the data assets within the enterprise. Data governance also defines how you manage the assets that have the greatest business value for your organization’s day-to-day operations.

Where does master data management fall within a data governance approach?  Master data management is one area within the data governance process that focuses on management of reference and master data. Master data management is combined with a fair amount of business process and I will leave the process aspect for another author. An example of a business process within master data management would be the decision on which data source is considered the truth when a discrepancy in master data arises between two source systems.

In this post I will discuss the why you would want to consider Microsoft SQL Server Master Data Services. If Microsoft SQL Server is in your environment, integration will easy and Excel provides a great interface for the data stewards.

Note: SQL Server 2012 Master Data Services and SQL Server 2014 Master Data Services are nearly identical.

Master data management is as much a business function as a technical one. Master data is managed within SQL Server and often integrated within Extract-Transform-Load (ETL) processes, but the actual management and oversight of the data should be performed by the business. This involvement is provided with help from roles like a data steward. A data steward can been seen as a representative of the data, while the technical resources serve as the caretakers of the data. Accepting these roles is one of the first hurdles to overcome with any master data management project. The business will control most of the direction within the project, and the technical team will be in charge of system integration and support.

Setting up a Master Data Services instance within SQL Server is a fairly straightforward process after you’ve installed all of the prerequisites with Microsoft Internet Information Services (IIS). Figure 1 shows the SQL Server 2012 Master Data Services Configuration Manager. See https://msdn.microsoft.com/en-us/library/ee633744.aspx for more details on how to install and configure the server and backend processes used within the Master Data Services process.

72notes1 SQL SERVER   Adding a Master Data Services to your Enterprise   Notes from the Field #072

Figure 1 – Master Data Configuration Manager

Note: You may configure the web services portion of Master Data Services install on another machine. It does not need to be on the same machine as the SQL Server database. Multiple licenses could apply with this configuration, so check with a licensing professional.

After setting up Master Data Services, you will want to give data access to the business to enable the actual management of the data. This is best provided by the installation of the Master Data Services add-in for Excel, which you see in Figure 2. This add-in will give the business users with appropriate knowledge of the data direct access to work with and support master data from their favorite data tool, Excel.

72notes2 SQL SERVER   Adding a Master Data Services to your Enterprise   Notes from the Field #072

Figure 2 – Master Data Services Add-in for Excel

Note: The add-in for Excel does not support some functions, such as model creation and hierarchy. You will have to manage the unsupported functions from the web site.

Getting Started

One of the best projects for beginning a master data management implementation is to start with managing reference data. Reference data exists in every environment and is frequently managed within Excel spreadsheets without database durability and/or version control.  Moving this data into Master Data Services from an Excel spreadsheet is a fairly straight forward task that can be accomplished with little risk.

What’s Next

In the coming post, we will walk through moving reference data from an excel worksheet to an entity within a Master Data Services model.

At a high level the steps that we will discuss in the following post will be:

  • Create a model to store the reference data (completed on the MDS website)
  • Format the reference data in Excel
  • Click Create Entity button on the Master Data tab within Excel and select your data

Hope this helps you get started.

If you want to get started with BIML with the help of experts, read more over at Fix Your SQL Server.

Reference: Pinal Dave (http://blog.sqlauthority.com)