PASS Summit

First, Microsoft’s Joseph Sirosh provided the keynote address. The focus was on the power of data and analytics in this changing world. I didn’t write anything down during this session, so instead I’ll link to Kevin Kline’s recap of the talk.

My focus was towards some sessions to help with query writing. I first attended Andy Yun’s session, Why Your Data Type Choices Matter. Awesome session. Andy first focused on the internals (how data is stored). While I was aware that a table was not created in the order of the columns listed, I did not know much about the FIXVAR format. If using variable columns (ex. VARCHAR), the extra two bytes represent the variable column offset array. We need more metadata to read the actual data. One thing that stuck out was the question of using unicode versus non-unicode. If you use the former (VARCHAR), then data could be lost and refactoring may be required. If you use the latter (NVARCHAR), the storage requirement is doubled. The key is to right-size data types (even in temp tables…if a certain person is reading, the person is being called out!), match data types, and recall the 8KB page size limit.

During lunch, I sat with a table of developers and DBAs talking about query tuning. The major advice I received was during a conversation about date functions that related to project I had worked on previously. Basically, I was told about how scalar user-defined functions can be a terrible thing in many situations. This may be open for debate, but scalar UDFs can bring down the IO. Now I’ll have to investigate that further.

My next session was Optimizing SQL Server and Databases for Large Fact Tables, presented by Thomas Grosher. The idea was that sometimes we have to pull data from large fact tables, and we can apply some tricks to make this happen. Say, if there’s a need to read a fact table for a report which runs for two hours, how do you help it. This session did go over my head a bit, as it seemed more performance-based than BI-development-based. Though I did take away a few things. First, it’s important to choose a clustered index key wisely, such as using a lookup table for common parameters. Second, table partitioning comes into play, which I haven’t tried to do much myself, but our engineering unit has. Third, row compression. We have some tables at my company with one billion rows, and Thomas explained that 1 byte less on 1 billion rows can save 1GB. A solid session, though I may have been surprised by the deeper infrastructure content.

Final session of the day was on Biml for Beginners, by Cathrine Wilhelmsen – a great example of the next generation of data platform stars already coming to fruition. So Biml is something I had brought up in passing at work and read a few blog articles about, but I had not followed through. Here was my chance. Basically, Biml (Business intelligence markup language) is helpful to many a business, including my company, by using business logic to easily repeat SSIS packages. It’s essentially XML. I learned about the beta BimlOnline as well, which does reverse engineering to help us gather what the Biml looks like. We can even extend Biml with C# or VB code blocks to import structures. Cathrine even explained tiered Biml files to repeat such attributes like admin, source, and destination. To use a practical example, there are some weekly reports to automate coming up, and this could be relatively useful to set these packages up at a base level. Biml is not meant for deployment, but it can save us valuable development time. Easy to learn more as well. I’m adding it to the personal projects list.

Lighten up, Francis. Biml is a good thing.

Exhibitors had a fun reception with appetizers and light dinner. I’m bringing home more Idera ducks. Then came more karaoke for the night, put on by Pragmatic Works at the Hard Rock Cafe. Same drill as the previous night with networking opportunities.

Earlier this year, I came upon the opportunity to go to PASS Summit. Some of you who clicked on this link probably know what that is, but I’ll let the about section of the Summit site explain to you what the event is and why it is worthwhile. I had previously gone in 2013 when it was held in Charlotte (easy commute from Raleigh), and having the chance to go back was a no-brainer for myself and my company. At the conference, I get to network with industry professionals/mutual Twitter followers, learn new skills to advance my career, and get advice on problems and possibilities facing my department at my company to allow us to become better. Plus, I can personally thank Redgate for the awesomeness of their SQL Prompt tool, which our BI team actively uses.

Anyway, this is where I start blog recaps.

Day 0 (Monday)

Flight arrived after 5:00 (PDT), so I did not get to pick up my registration badge. I will note that it would be nice if that area was open later on that day like it is on the surrounding days. At first, introverted autistic me was concerned about showing up to events without a badge. I was due to attend a networking dinner hosted by veterans Steve Jones and Andy Warren, where people could converse and meet. I was able to talk with a few professionals, including a group of developers at stamps.com, on what they do and we shared challenges we each face on a broad scale. While I liked how the dinner gave us a chance to sit down, I sometimes hope that there’s more opportunity to move about the dinner area and talk to other tables. When someone speaks to me first and asks about me, it is much easier for me to talk, or if I’m place at a table with others I’m driven to make that conversation. That was advantageous.

After I returned to the hotel to handle a task for my work, and struggling with it (public props to my colleague for figuring it out quickly the next morning), I decided that I might want to talk with others on handling disparate data…while singing karaoke. Thanks to the magic of Twitter, the PASS community is constantly in touch about events, which led me to join many others (both first-timers and community notables) to a Chinese restaurant hosting nightly karaoke. That was more of a chance to have fun and practice “Purple Rain” in front of an audience outside Raleigh-Durham or Philadelphia. I’ve learned that singing a song in front of people who may not know you is a good opening for conversation once the song is done. It’s no secret that I have trouble approaching someone I’ve never spoken with and making small talk with that person, but there were some openings created through the power of singing songs someone else made famous.

A key takeaway professionally that night was simple: as soon as you are stuck on a problem involving data sources, ask someone who is more seasoned with the data on their thought process, and how they became acquainted with the data. If they solve the problem, find out how they did it so you can apply it next time. It can help someone doing work on the analyst side to get better. I admit it’s an area I still need to be more consistent about when trying to play hero.

Day 1 (Tuesday)

I wasn’t signed up for a pre-con because money, so it has really been a day of exploring and also following up on previous work tasks. Let’s just say I saw touristy stuff and took advantage of the #sqlsummit hashtag to meet random others. Again, PASS does a great job utilizing Twitter.

Then we got to the networking dinner. There’s a lot of standing around awkwardly for a person like myself, so this event isn’t necessarily easy. I found that moving around and striking up conversation with someone else who was also by themselves to be an effective way of connecting. I was able to talk to a DBA or three about how their systems worked, and got advice that the system administrator should not be the database administrator. Can’t treat them both the same. I can agree to some extent, but I would think it depends on if the sys admin has been trained as a DBA.

I like the networking dinner for the open and social aspect, but I do wonder if PASS could put together a networking event similar to the first-timers one for people who generally just want to meet other people but have trouble saying the first word. The first-timers one is set up speed dating style, and maybe it could be expanded to others in the future. Though with everything considered, I met plenty of new people by focusing on them directly.

Then came the fun of karaoke, yet again. How we do the connect part. Or reconnect, as I encountered professionals I had not seen in a long time…three years in a few cases.

And that was everything through Tuesday. Days 2-4 will provide even more learning.