SQLAuthority.com – Notes from the Field


Another article was posted to the Notes From the Field series that is hosted out on SQLAuthority.com. This blog post continues the introduction of Biml with a good use case for why you might want to give Biml a look on your next Integration Services project.

What is Biml and How Can it Help Me with SQL Server BI?

The Why of Biml?


Posted in Biml | Leave a comment

Speaking at SQL Saturday 349 – Salt Lake City, UT

Increase your SSIS productivity with Biml

Discover the power of Business Intelligence Markup Language (Biml) and increase your Integration Services development productivity.

Business Intelligence Markup Language (Biml) is an XML language for Microsoft Business Intelligence that can take your Integration Services package development to the next level. Creating template packages added productivity to your Integration Services development effort. If the template design changes after development begins, a fair amount of rework to packages could be needed. See how Biml can help create template packages that can better adapt to changing business needs and allow for more comprehensive design or redesign. This session will be a demo based session that will inspire you to make Biml your number one integration services development tool. Learn how to use Biml scripts to create staging tables and integration services packages. Add metadata to your Biml scripts and really watch the possibilities expand. 

Come by and see this session the 25th of October at 1:20pm, at Spencer Fox Eccles Business Building 1655 Campus Center Dr, Salt Lake City, UT 84112


Posted in Speaking | Leave a comment

PASS Summit 2014 – Speaker Idol

clip_image002 Space clip_image004

The Speaker Idol participants were released yesterday and I was lucky enough to make the cut. With that being said, I guess I will be speaking at the PASS Summit 2014, well kind-of.

This will be the first ever Speaker Idol competition and the group of people speaking are very talented. I think this will be the start of a great program to give less known speakers (speaking for myself only) an opportunity on the big stage and receive some great speaking feedback. We all like that, ummm… yea…

Does this schedule mean I’m going first?


  • Reeves Smith
  • Pieter Vanhove
  • Mike Byrd
  • Wagdy Ishac


  • Sergey Olontsev
  • Ed Leighton-Dick
  • Rob Volk
  • Tim Costello


  • John Flannery
  • Luan Moreno Medeiros Maciel
  • Mitchell Bottel
  • Jason Carter

Best of luck everyone.


Posted in Speaking | 1 Comment

Microsoft MVP, WOW

I was awarded the Microsoft Most Valuable Professional for SQL Server this month and I am a bit overwhelmed, to say the least. It is an honor and privilege to be added to the group of current MVPs. This is an award that is granted for giving time back to community. SQL Server is the product that I have been working with since 1996 when I started working for MCI. Ever since that time I have worked hard to better understand it and maybe even master it. Not sure if the day will ever come that I master it, but I will chase that goal each and every day.

I would like to recognize the people that made this possible. First and foremost, this award is about time being given, and since time is a finite resource, the time spent contributing to this community takes away from time spent in other areas of my life. I’d like to thank Amy Smith, my wife and best friend. She makes it possible for me to contribute to this community. She takes care of the kiddos and lets me travel without ever saying a word. Thanks Amy.

The second group would be Linchpin People, a great company with truly rock star people. I am very blessed to work with a company whose people provide the motivation to put in the extra time. Thanks.

The last group and no less important is the community itself. The people who I have met along the way and have become friends with are outstanding. Unlike me, they might be weird, (yep that is you Jason Horner) but are some of the nicest people in all of the technical communities. <evil grin>

This is a journey that I’ve just begun and hope to continue for quite some time.

By the way, I love my job…

Reeves Smith

Microsoft Most Valuable Professional for SQL Server

Posted in Career | Leave a comment

Speaking at the Greenville BIG

I like the sound of that.

I will be in Greenville, SC this coming week to meet with the folks at Varigence and talk all things Biml.

I will be speaking on Master Data Services at the Greenville Business Intelligence Group on Thursday September 11th.at 6:00 pm

Master Data Services, How Does it Apply to My Enterprise

Understand how a Master Data Management (MDM) and Data Governance methodology can enable business clarity across the enterprise. Get introduced to Master Data Services (MDS) which is a Master Data Management solution on the Microsoft Platform. This solution enables the management of non-transactional data that defines a business entity within the enterprise. Get a good business and technical understanding of how MDS can help obtain better business clarity across the organization through a data governance strategy. Data governance is a methodology that represents quality, policies, and process management, in relation to handling your enterprise data. The demo will walk through the basics of getting started with Master Data Services 2012 including the Excel Add-in for Master Data Services.

For all of the details and registration please visit the event site here.

Hope to see you there.

– Reeves

Posted in Speaking | Leave a comment

4 Things I Wish I Knew Sooner As a Consultant

This topic continues from an earlier blog written by Mike Walsh (http://www.straightpathsql.com/archives/2014/08/softskillsiwishihadasdba/) and followed up by Tim Mitchell (http://www.timmitchell.net/post/2014/08/28/four-things-i-wish-id-known-back-then/) who tagged me to continue it. I hope to add something insightful to the mix even though so much has already been said from other people within the community.

With that I will add an additional 4 items that have not directly been mentioned.

1) Get Involved in the Community

There are many ways to get involved in this community, and opportunities to get involved will vary from person to person. This was the last thing that I embraced from my list and I still want to say thanks to Steve/Andy/Tim/Jason and the many others that have helped me along with this.

Start off by heading to the local SQL Server users groups to meet some of your fellow colleagues. This is a great place to start to connect with others that you can reach out to when you have questions or are in need of more work. For the most part, the SQL Server community is friendly and welcoming, and you will be surprised at how it will help your career along. It is actually fun too.

Other ways to be involved include writing blogs, helping out in the forums, etc.

2) Pay Attention to what is required

This would seem straight forward but can sometimes get lost when a project is in full swing. Sometimes we lose track of the fact that someone is paying us to do work based on the idea that your work can create value, which in turn, makes money. Technology does not make money, again, technology does not make money. The application of technology towards a business problem will make money.

Take the time to listen to the business problem you need to solve and make sure your solution meets all the requirements and solves the problem, before trying to implement some new technical trick or the latest and greatest techniques. The business will come to professionals like us for help with a business issue and how it is accomplished might not even interest them, and that is OK. Blog about it (see item #1) because someone else in the community would really appreciate it.

I have a story for this one. I was once at a company working as a consultant and we had a large group exercise where we grouped up to build a bridge made of newspaper. The bridge had to fit some specific dimensions and as a bonus, needed to hold as much weight as possible. The bridge that held the most weight would win. Needless to say two teams of top notch consultants built a bridge that was super strong but did not fit the dimensions. I could not believe it. They missed the requirement for the dimension and went straight for the optional requirements. I wonder if those same teams would have worked on a software project the same way.

3) Admit it when you do not know something

This can be one of the biggest issues I see when working together with teammates, and especially when interviewing a potential candidate.

In an interview I always look for at least one question that you will not be able to answer. I’ll ask the GDP of Luxemburg in 1963 if I have to, but I want to see how you handle “I don’t know.” I look at this as an indication of what you realize about yourself and how you will be able to work with the team. In this industry, you will work with other team members all the time. If you are not confident or forthcoming in what you can or cannot do, or what you do and do not know, the odds that the project will fail will increase. We will all need help, (see Item #1) and understanding that is the first step in figuring out that it is OK to not always be the hero.

4) Be Nice

This industry is filled with smart people but I sometimes find that these very intelligent people have a difficult time working with others. If you are this person, think about how much value you can bring if you are a) that smart and b)… wait for it…. willing to work with other people as a team.

Think about it and BE NICE, because it makes the work and the community a better place.

Posted in Career | 4 Comments

Power Query and Stored Procedures with Parameters

A great question come up on the Power Query forum:

“I would like to know if there is a way to pass parameters to a Stored Procedure used within a Power Query.”

Here are the steps in detail that I was not able to supply in the forum reply.

The short answer to the above question is: “Yes, you can.”

This example will use the AdventureWorks2012 database with the following objects: [HumanResources].[Employee] table and the [dbo].[uspGetEmployeeManagers] stored procedure.

First, you need to return the [HumanResources].[Employee] in a Power Query within the workbook and add a table to use as the parameter to pass to the stored procedure. Here’s how to do this:

  • Open an excel workbook, and click on the POWER QUERY tab. Then select the From Database drop-down button, and select From SQL Server Database.

  • Fill in the Server and Database textboxes, and Click OK.

  • In the Navigator window, double click the HumanResources.Employee table.

  • The Query Editor will open. Hold down the control key and select every column except BusinessEntityID, LoginID, and OrganizationNode. Click the Remove Columns button. Click the Close & Load button to return to Excel.

    You now have a much more manageable table. You will only use this table for reference.

    Note: You could have easily added the SQL query to the Microsoft SQL Server connection dialog window.

    SELECT [BusinessEntityID], [LoginID], [OrganizationNode] FROM [HumanResources].[Employee]

  • After you save the Power Query, a table will be created within Excel. To the right of the table, Add: BusinessEntityID to Cell E1 and 25 to cell E2. Highlight the two cells, and go to the INSERT tab. Click the table button to turn the two cells into a table. Go to the TABLE TOOLS Design tab, and change the name of the table to Parameter.

  • With the Table still highlighted, select the POWER QUERY tab and click the From Table button. A new Power Query will be created.

  • Click the Close & Load button to return to Excel.

The next step will create the connection to the stored procedure. Some small steps along the way will create multiple Power Queries for demonstration purposes.

  • Create another Power Query like in step 1 above.
    • Server:
      Yourserver (I used localhost above)
    • Database: AdventureWorks2012
    • SQL Statement: EXECUTE [dbo].[uspGetEmployeeManagers] @BusinessEntityID = 278

  • Three rows will be returned. Name the Power Query SPwithNoParameter. Click the Close & Load button to return to Excel.

  • Three Queries will be in the Workbook Queries window. Right click on the SPwithNoParameter Query, and select Duplicate.

  • Double click on the new SPwithNoParameter (2), and open the Query Editor window and rename it to SPwithParameterAndExtra.

    Notice the M language statement within the Formula Bar.

    = Sql.Database(“localhost”, “AdventureWorks2012″, [Query=”EXECUTE [dbo].[uspGetEmployeeManagers] @BusinessEntityID = 278”])

    You are going to have to modify this statement to take the value from the parameter table. But first, let’s take some small steps.

  1. Modify the statement to add the following to the end of the statement: ” & Number.ToText(278) ])

= Sql.Database(“localhost”, “AdventureWorks2012″, [Query=”EXECUTE [dbo].[uspGetEmployeeManagers] @BusinessEntityID = ” & Number.ToText(278) ])

  1. Click the Refresh Preview button. Nothing will change, and that’s good. (Small steps, remember.)
  1. Now you have to reference the Parameter table from Excel. Replace the 278 with Parameter[BusinessEntityID]{0}

Note: The {0} at the end of the table syntax is limiting the return to the first row because the table could contain multiple rows and our stored procedure needs a scalar value and not a table object.

= Sql.Database(“localhost”, “AdventureWorks2012″, [Query=”EXECUTE [dbo].[uspGetEmployeeManagers] @BusinessEntityID = ” & Number.ToText(278) ])

  • A Warning will come up and state that permission is required to run this native database query. Click the Edit Permission button, and then click the Run button.

    And with that you would be done, but the security guys are looking out for us. Power Query does not allow data from two different data source to be referenced within one step. So you get the following message.

    Now are you see my naming convention, “AndExtra

  • Click the Close & Load button to return to Excel.
  • Right click on the SPwithParameterAndExtra Query and select Duplicate.
  • Double click the query and rename the query to SPwithParameter.

    This time you will have to add more code to the M Query.

  • Select the View Tab, and click the Advanced Editor.

  • Add the following:

    SpParameter = Excel.CurrentWorkbook(){[Name=”Parameter”]}[Content], before the Source entry. Click Done.

    Note: This statement was from the Parameter query. I did have to name it SpParameter.

  • Go to the Formula Bar, and modify the statement to point to the SpParameter.

    = Sql.Database(“localhost”, “AdventureWorks2012″, [Query=”EXECUTE [dbo].[uspGetEmployeeManagers] @BusinessEntityID = ” & Number.ToText(SpParameter[BusinessEntityID]{0}) ])

  • One more security warning: Information is required about data privacy. Click Continue.

  • Select Public, and click Save.

  • Notice you have now selected only one row. Click Close & Load.

  • Go to the HumanResources Employee query, and pick another ID. Take that ID and place it within the Parameter Query.


To review what was done in the steps above, you created a table (Parameter) that was used by another query (SPwithParameter) that was sourced from a stored procedure. You used the Parameter table to change the parameter of the stored procedure. After implementing all of these steps, you are able to update a table in your Excel document and have the results of other queries be driven off the modification in Excel.

The next logical question would be: Why go through all of this trouble?

If you are creating easy to use self-service BI projects, it seems easier to have a business user modify a table on an Excel document and not have to update the M code like above.

Mileage may vary, but the easier you make their experience, the more time you have for other cool stuff.

Posted in Power Query | 1 Comment