Category: Professional Development

Anyone Can SQL

I love the SQL Community. Plain and simple. I will extend that to the overall Microsoft Data community as well. We are a hugely supportive bunch. That fact that many of us refer to it as #SQLFamily is not an accident. There are so many ways to reach out for and provide help. A huge number of folks in this community blog and present and offer help on Twitter, StackOverflow, MSDN, etc. It is truly astounding to me the depth of help you can get from this community.

I get a lot of joy out of seeing people welcomed into the SQL Community. You want to learn SQL? Come join us. We’ll help. I love encouraging people to get involved. I often tell folks just starting out about how welcoming and supportive this community is. Sometimes they get involved and encourage others to do the same. Typically, it works out wonderfully for everyone.

Courtesy of ktylerconk on FlickrHowever, recently, I was told of a pretty terrible experience that happened at a SQL-related event. People with certain backgrounds were treated with derision and scorn. They were laughed at. They were told they would not be taken seriously in this community because of their extensive experience with database technologies other than SQL Server. This is the exact opposite of what this community stands for.

In Pixar’s Ratatouille, August Gusteau is a famous chef. He writes books, does interviews, etc. One thing he is famous for is his closely held belief that “Anyone can cook.” A key antagonist, and infamous food critic, Anton Ego, doesn’t agree with Gusteau.

Note: There will be some spoilers for the movie coming shortly. If you haven’t seen it, fix it. It is another fine example of Pixar’s spectacular ability to craft stories and characters that resonate. It’s charming.

Anton Ego prides himself on his ability to eviscerate chefs and restaurants with his scathing reviews. He raises himself up by tearing others down. In this sense, the name of the character is spot on.

It isn’t until near the end of the film, when he enjoys an amazing meal that takes him back to his childhood, a dish prepared by a chef who is a rat, that he comes to understand what Gusteau was talking about.

In his review, Ego writes, “The world is often unkind to new talent, new creations. The new needs friends. Last night, I experienced something new, an extra-ordinary meal from a singularly unexpected source. To say that both the meal and its maker have challenged my preconceptions about fine cooking is a gross understatement. They have rocked me to my core. In the past, I have made no secret of my disdain for Chef Gusteau’s famous motto: ‘Anyone can cook.’ But I realize, only now do I truly understand what he meant. Not everyone can become a great artist, but a great artist can come from anywhere. It is difficult to imagine more humble origins than those of the genius now cooking at Gusteau’s, who is, in this critic’s opinion, nothing less than the finest chef in France.”

My goal with this post is not to rant or to point fingers or to vent my or anyone else’s anger or disappointment over this situation. Rather, it is to remind all of us that EVERYONE is born knowing NOTHING about databases in general and SQL Server in particular. We all come from somewhere. I started with Access. I know many people who did. Some people started with FoxPro. Others with Sybase. Others with DB2, or Oracle, or FileMaker. The list goes on and on. The fact that we have such varying experiences helps to make the community rich and varied. Like Ego came to learn, we will not raise ourselves up by tearing others down.

The onus is upon us, in my opinion, to ensure that we can live up to the promise of this outstanding community and treat people who want to learn with respect and encouragement. The onus is upon us to apply Gusteau’s most cherished belief to our community as well: Anyone can SQL.

Achievement Unlocked: Microsoft Employee

After almost 9 years of consulting around SQL Server and other Microsoft Data Platform technologies, I have joined Microsoft. Today was my first day as a Cloud Solution Architect – Data Platform. Basically, I will be helping Microsoft customers by designing data solutions using Azure (both pure cloud and hybrid). My role is in Minneapolis, so I won’t be moving. There will be a little bit of travel here and there as I have onsite meetings with customers.

I will need to learn a LOT more about Azure than I currently know. That is a large part of what made this opportunity so attractive. My content here will start having more of an Azure bent to it as I dig deeper into it. There will still be some Power BI here and there as well.

I am incredibly excited about this new direction. Microsoft has really been doing such exciting work and I am proud that I will get to be a part of it.

Power Query Free Training Webinar Follow-Up Questions

On April 7th, I gave my Power Query: Data Chemistry for the Masses presentation as part of Pragmatic Works Free Training on the T’s series. You can find the recording of that webinar here. I want to take a moment to thank the folks at Pragmatic Works for organizing and hosting this series that is such a great asset to the community.

At the end of the session, there were more questions than we had time to handle. So, they emailed me the questions in order for me to answer them in a post like this. Behold!

What is the maximum number of rows allowed in an Excel worksheet?

This question was likely in response to my recommendation of loading Power Query results to the Excel Data Model (Power Pivot) instead of to a worksheet. According to this info from Microsoft, the maximum row count in a worksheet is 1,048,576.

When combining files in a folder can the workbook have multiple tabs?

As far as combining files using the From Folder source, I have only succeeded with text files. However, there is a post here on the Dutch Data Dude blog that explains how to combine that source with a little custom M to make this happen.

When passing a parameter to an M function, can you select from a pick list of choices?

This question was in response to my converting a power query on Nobel Prize Laureates into a function that prompts for a prize category and then returns the Laureates for that category. Typically, instead of users interacting directly with the function, the parameter is passed to the function via a custom column. There is great post here on the Data Chix blog on how to do this.

Could we get a copy of the file being used in the demo?

You can get all of the materials for the presentation, including the files used, on the page for that presentation on this very blog.

Is the option to load to data model available for Excel 2010 or later versions?

I just learned over the weekend at SQL Saturday Madison that Excel 2010 does NOT have the option to load directly to the Data Model. Bummer. So, if you are using Excel 2010, you will need to load to a worksheet and then import to Power Pivot from that worksheet.

How do you know a web page is in JSON?

Well, web pages are not really in JSON. JSON is actually a “lightweight document interchange format.” I think of it more like a simpler XML. This question comes from my use of the Nobel Prize API to download Laureate data in the JSON format as part of this presentation. This is also detailed here as part of my Power Query Decathlon – Beginner blog series. The URL for consuming the API using JSON is http://api.nobelprize.org/v1/laureate.json. You can also do it as a CSV using http://api.nobelprize.org/v1/laureate.csv which will actively download a CSV file with all of the same data.

What do the different separators mean? [] i guess means column, but what is {} for?

I assume this question is related to the appearance of [] and {} in M query syntax. You can read about these in the Query Formula Language Specification document. This is a large resource and not recommended for “light” reading. 🙂 According to the specification, “We can use the lookup operator ([]) to access the fields of a record by name.” Also, the brackets [] can be used for referring to Records by surrounding the elements within the Record. the {} curly braces are used to referring to a List. A great, and easy to follow, resource is Chris Webb’s great book Power Query for Power BI and Excel. I am still very much a student of M, so I will not go much deeper into this at the moment.

Any plan from MS to add the ability to export the resulting data to a place other than XL, Data Model, eg text file, db?

I would LOVE the ability to use Power Query to load data into a database or a file, etc. Note that Power Query is used outside of Excel for loading data in the new Power BI preview as well as the Power BI Designer, which is also currently in preview. I have yet to see public information regarding any detailed plans for using Power Query to load to a larger number of targets. However, given the investment Microsoft is making in Power Query, it seems likely to me that we will see it popping up in more places in the future.

Could we use an Itunes folder as a source and consolidate music metadata?

Yup.

Here is the M code for a query I used to do just that:

let
    Source = Folder.Files("C:\Users\Mark\Music\iTunes\iTunes Media\Music"),
    #"Removed Other Columns" = Table.SelectColumns(Source,{"Name", "Folder Path"}),
    #"Replaced Value" = Table.ReplaceValue(#"Removed Other Columns","C:\Users\Mark\Music\iTunes\iTunes Media\Music\","",Replacer.ReplaceText,{"Folder Path"}),
    #"Split Column by Delimiter" = Table.SplitColumn(#"Replaced Value","Folder Path",Splitter.SplitTextByEachDelimiter({"\"}, null, false),{"Folder Path.1", "Folder Path.2"}),
    #"Changed Type" = Table.TransformColumnTypes(#"Split Column by Delimiter",{{"Folder Path.1", type text}, {"Folder Path.2", type text}}),
    #"Replaced Value1" = Table.ReplaceValue(#"Changed Type","\","",Replacer.ReplaceText,{"Folder Path.2"}),
    #"Split Column by Position" = Table.SplitColumn(#"Replaced Value1","Name",Splitter.SplitTextByPositions({0, 2}, false),{"Name.1", "Name.2"}),
    #"Changed Type1" = Table.TransformColumnTypes(#"Split Column by Position",{{"Name.1", Int64.Type}, {"Name.2", type text}}),
    #"Split Column by Delimiter1" = Table.SplitColumn(#"Changed Type1","Name.2",Splitter.SplitTextByEachDelimiter({"."}, null, false),{"Name.2.1", "Name.2.2"}),
    #"Changed Type2" = Table.TransformColumnTypes(#"Split Column by Delimiter1",{{"Name.2.1", type text}, {"Name.2.2", type text}}),
    #"Renamed Columns" = Table.RenameColumns(#"Changed Type2",{{"Name.1", "Track"}, {"Name.2.1", "Song"}, {"Name.2.2", "File Type"}, {"Folder Path.1", "Artist"}, {"Folder Path.2", "Album"}})
in
    #"Renamed Columns"

In the Code above, I highlighted the path to my Itunes folder. Just replace that with yours and past into a blank Power Query window. Here is the query once you have done so.

image

There is more data in the Itunes Music Library.xml file, but I haven’t figured out how to use it effectively yet.

Alright. That wraps up the questions. Thanks to all who tuned in to the webinar and provided such great questions.

2015 Microsoft Most Valuable Professional (MVP) for SQL Server

image

I am thrilled to announce that Microsoft has once again honored me with the MVP Award for SQL Server. Last year was my first award and I have to say that I was blown away by the brilliance and altruism of so many members of the MVP community. I attended my first Global MVP Summit this past November and the experience was even more amazing than I expected. I am so proud to be counted in this fine group of professionals.

Wow. Between starting a new job on Monday and getting renewed as an MVP on Wednesday, this is turning out to be quite the week.

New Adventures

I am excited to announce that, as of Monday, March 31st, I moving over to Tail Wind Technologies as a Business Intelligence Consultant. My eight years at Digineer have been a lot of fun. I learned a tremendous amount from my time here and will miss the fantastic people I have had the pleasure to work with.

My new role at Tail Wind will involve training up as a full on Data Architect. I will have a terrific mentor to work with and learn from. I had mentorship early in my career but have largely been driving my own development for several years. It will be great to get some more direct help on that front.

In addition to my consulting role at Tail Wind, I will be spinning up my own training company, focused largely around Excel BI/Power BI (at least to start with). Look for more info on that in the coming months.

These particular winds of change are fantastic for me. I owe a debt of gratitude to Digineer for helping me become the consultant I am today.

My Remaining 2014 Presentation Schedule

Greetings. Since I do a fair amount of presenting, I like to keep people up to date on when and where I will present. Below are the presentations I have left in 2014.

SQL Saturday Winnipeg – November 22

In addition to serving on the committee that is organizing Winnipeg’s first SQL Saturday, I will be giving two presentations:

Analysis Services Terms and Concepts for the DBA

MDX Trek: First Contact

Minnesota BI User Group – December 2

I will be presenting a Case Study along with someone I worked with at a client. This is a make-up for September when a back spasm prevented me from delivering this presentation.

Case Study: Monitoring SSAS Usage with Extended Events

How do you measure the success of a Self Service Business Intelligence initiative? While the answer to that question has several components, a vital one is Adoption. Are people using the assets created for that initiative? Since Business Intelligence is using data to make better decisions, why not use data to help answer this question? At Capella University, we did just that.

This case study will describe how we used Analysis Services Extended Events to track the usage of Tabular Models that Capella built for Self Service BI users. We will demonstrate the following:

The business case for capturing asset usage

The particular goals Capella had in this case

How easy it is to get started with SSAS Extended Events (far easier than it looks)

The end-to-end solution created: from the event trace itself thru to the Power Pivot model created for analysis

Lessons learned (some learned the hard way)

We hope you will come away from this presentation with less trepidation around SSAS Extended Events as well as a foundation you can use for your own solution.

Minnesota SQL Server User Group (PASSMN) December Meeting – December 16

I will be participating in an Improv-style group presentation based on the format of the TV Show Whose Line is it Anyway? with other speakers from the Twin Cities.

 

Pragmatic Works Free Training Series – December 18

 

Analysis Services Terms and Concepts for the DBA

This is a make-up for September when a back spasm prevented me from delivering this presentation.

 

So, that’s it for the rest of 2014. Overall, it has been a pretty busy year as far as presenting goes. I hope to continue that in 2015.

My First Pre-Con: SQL Saturday 332–Minnesota

It is with tremendous joy (and a little trepidation) that I announce that I will be doing my very first Pre-Con as part of SQL Saturday 332 in Minnesota in October. I have been presenting for several years now and feel that it is time to take this next big step. Since I love presenting so much, the idea of presenting for a whole day is just awesome. There is a also quite a bit more pressure in this scenario. But that is part of what makes this a great growth experience, stretching myself like I have never done before.

Over the past several months, I have done a lot of client work in Excel dealing with Power Pivot and Pivot Tables. I have also been doing a bit using Power View up in SharePoint, the vast majority of which carries to Excel 2013 as well. I have always been a fan of enabling users to do more with data and learn to be more self-sufficient. My experiences in Excel have reinforced the idea that Excel is a fantastic platform in the Self-Service BI movement. The past several months working with data in Excel have been some of the most fun in my career. So, when I needed a topic for a BI Pre-Con, the choice was easy.

You can find information on all the SQL Saturday 332 Minnesota Pre-cons here. It is an impressive line-up, to be sure. The abstract for mine is below.

Microsoft Excel: The Business Intelligence Platform For The Masses

From gathering and shaping source data through data modeling and visualizations, it is staggering how much you can accomplish in Excel. This Pre-Con will walk you through creating an interesting and powerful BI solution in Microsoft Excel 2013. Whether you are a business user or a technical developer, you will get good value from attending.

1. Power Query 

• Using Power Query to gather source data from various sources both on-premise and in the cloud. 

• Use various transformations on the Ribbon

• Travel back and forth through time via Query Steps

• The basics of Power Query Formula Language (M)

2. Modeling Data With Power Pivot

• Importing Data from various sources

• Linking Tables to data residing directly in Excel sheets

• The importance of Date Tables

• Best Practices

3. DAX 101

• Introduce DAX syntax beginning with Calculated Columns including the mighty Related function

• The basics of the Calculated Fields (Measures)

• Row context/filter context

• The power of the CALCULATE function

• More…

4. Power Pivot/DAX Design Patterns

• Solving real-word problems with Power Pivot

• Many to Many relationships

• Parent-Child Hierarchies

• Segmentation

• More…

5. Excel Pivot Tables/Charts

• Connecting Excel to data sources like SSAS Cubes, Tabular Models, and the internal Power Pivot model

• Pivot Table basics

• Filtering methods and Slicers

• Conditional formatting

• Pivot Charts

• More…

6. Power View

• The basic visualizations (Bars, Columns, Matrix, etc)

• Advanced visualizations (Multiples, Cards, Scatter/Bubble Charts, etc

• Filtering views or the entire report

• Design tips to take great advantage of Power View’s capabilities

 

I will be focusing on using Excel 2013 on my machine. There is so much to cover that trying to add in Power BI-specifics is just not in the cards. But I think that makes sense as SO many more people have Excel on their machines than are using Power BI right now. And SO few of those people are taking advantage of even a tiny subset of what Excel has to offer. A major goal of this Pre-Con is to help change that.