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 Uncategorized | 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 Uncategorized | 3 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.

Summary

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

Speaking at SQL Saturday 300 – Kansas City

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 13th of September at 12:45pm.

–Reeves

Posted in Biml, Speaking | Leave a comment

Stairway to Biml: Biml Language Elements – Level 5

I’m happy to announce that the fifth level in the Stairway to Biml has been released.

It was a super fun to write and I hope it clears up some of the Biml language.

Go here for the full series.

Thanks Andy/Tim/Steve

Reeves

Posted in Biml | Leave a comment

Speaking at SQL Saturday 331 – Denver, CO – Pre-Con


Tim Mitchell and I will be delivering a Getting Started with Biml all day session. If you have not worked with Biml and want to see what all of the buzz is about come join us for a great day of training.

Getting Started with Biml (Business Intelligence Markup Language)

Have you ever needed to generate dozens (or even hundreds) of similar SSIS packages? Do you ever find yourself needing to regenerate a lot of packages when some piece of the upstream data format changes? These are not uncommon scenarios faced by ETL developers, but historically there haven’t been any good tools to help with this. However, we’re going to introduce you to one of the most useful tools you may ever use: Biml.

For ETL developers, Biml (Business Intelligence Markup Language) is a game changer. This is a great new tool for Microsoft business intelligence developers who want to make the most of their development efforts. In short, it’s an XML-based language that will allow you to programmatically create SSIS packages. Even better, it can be enhanced with VB.NET or C# scripting to create BimlScript, which allows you to dynamically build and modify a few, dozens, or even hundreds of packages with minimal effort. Best of all, you don’t need budget approval – if you already have Visual Studio, you can use Biml and BimlScript for free!

In this full-day presentation, we’ll introduce you to dynamic package generation through Biml. Business intelligence consultants Tim Mitchell and Reeves Smith will walk you through the essentials of Biml – getting started, syntax, and common uses – followed by dozens of design patterns to improve your efficiency as an ETL developer.

Among the topics that we’ll cover:

  • Overview of Biml – What is it? Where do I use it?
  • Introduction to Biml syntax
  • Biml in the Visual Studio environment
  • Introduction to BimlScript syntax
  • Dynamic package generation with BimlScript
  • Metadata management with Biml
  • Create a simple staging package project with dozens of packages in less than an hour
  • Package (re)generation
  • Source control
  • Common design patterns for Biml in the enterprise
  • Taking Biml to the next level with Mist

By the end of this full-day presentation, you should be equipped to get started immediately using Biml. It’s a revolutionary technology, and we hope that you’ll be as excited about Biml as we are.

Audience

This course is intended for business intelligence developers and other database professionals with experience creating and supporting SQL Server Integration Services (SSIS) packages. Attendees should have a working knowledge of the SSIS essentials. In addition, some experience with either C# or VB.NET, and a basic understanding of XML (or other markup language) would be helpful.

For more details go here: Event Site

Posted in Biml, Speaking | Leave a comment

Speaking at Mile High Tech Con, Denver, CO

Master Data Services, How Does it Apply to My Enterprise?

Understand how a Master Data Management (MDM) and Data Governance methodology can better enable business clarity across the enterprise. Get introduced to the Microsoft MDM offering and how to start a project at your company.

Session Summary: Understand how a Master Data Management (MDM) and Data Governance methodology can better enable business clarity across the enterprise. Get introduced to the Microsoft MDM offering and how to start a project at your company. Session Abstract Master Data Services (MDS) 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.

Come by and see this session the 25th of July at 1:00 pm.

Increase your SSIS productivity with Biml

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 26th of July at 9:00 am.

Location is at: Auraria Higher Education Center; 777 Lawrence St; Denver, CO 80204

For more details go here: Event Site

Posted in Biml, Master Data Services, Speaking | Leave a comment