Top 10 Things I Learned While NOT at PASS 2012

Last week was the PASS Summit in Seattle, Washington. I was unable to attend this year, but I watched the keynote addresses on PASS TV, a live streaming video feed from the conference. Normally I post a "Top 10 Things I Learned" from each day of the Summit. Because I didn't go this year, I didn't do this. But that is not to say I learned nothing last week. In fact, I spent last week developing my first functioning "data mart" for one key area of the Shelby v.5 application: GlobaFILE. GlobaFILE is the core repository of names and name-related data. It does not have any true data warehouse style "facts" in it, such as financial transactions, but it does serve as the basis of core name content for all other modules apart from the General Ledger. The biggest bonus to creating this data mart is the ability to capture changes over time, which is by itself a pretty big win for historical analysis.

While I learned last week is a combination of aspects of SSIS, T-SQL, and news from PASS during the keynote addresses. Without further ado, here are my top 10, in no particular order:

1. SSIS: I learned that most of the expressions used in SSIS are based on C# syntax rather than on SQL or VB.NET. This was a frustration because it is yet another language syntax I will need to learn in order to work effectively in SSIS.

2. SSIS: Most of the data transformation I need to do from my OLTP database to the as-yet-in-design OLAP database cannot be done with the basic SSIS data tasks. I would need to use C# conditional statements and VB.NET scripts together to achieve the result. But by the same token, I can do most of the data transformation with T-SQL in queries. So right now I'm not certain if SSIS is really an essential part of the development of a Shelby v.5 data warehouse solution, especially in light of the next Top 10 item.

3. T-SQL: The MERGE command in T-SQL can populate slowly changing dimensions and facts in the data mart. I am glad that I already knew how to create Common Table Expressions prior to attempting to populate my data mart database using MERGE. The CTEs are where the data extraction and transformation take place, and the MERGE statement itself is where the data are loaded into the data mart tables.

4. T-SQL: Define the surrogate key column of a fact or dimension table with the "identity" property so that it will auto-increment a new value for every new row. For a dimension table, specify 0 as the first value for the "identity" assignment, thus enabling a manual INSERT query to populate the "N/A" row.

5. T-SQL: Define a "trigger" on a dimension table in order to automatically assign a "RowEnd" date to any row that is supplanted with a new row with the identical business key.

6. T-SQL: Use calculated columns to make explicit some data that are stored implicitly in other columns but which will make the end-user's experience much easier to have at hand, such as "age" and "RowCurrent."

7. SSIS: Unless I'm missing something, deploying solutions from SSIS to a production server context is much more difficult than deploying reports in SSRS to the Report Manager. This is another reason that I am currently preferring using MERGE over SSIS.

8. KEYNOTE ANNOUNCEMENT: Microsoft is in development of an enhancement to SQL Server 2012 called "Hekaton" that will optimize selected tables and procedures so that they run completely in memory, yielding improvements in efficiency of one to two orders of magnitude.

9. KEYNOTE ANNOUNCEMENT: Excel 2013 includes a completely stand-alone instance of Power View visualization reporting. This is huge. This means that some reporting options that were reserved only for owners of SQL Server Enterprise AND SharePoint Enterprise are not available to any owner of Excel 2013. I found Office 2013 available on TechNet downloads, and I can't wait to get it installed and make sure I understood the announcement correctly. If I did, this could be a game changer for reporting at all levels, including for the churches who use Shelby v.5 and Microsoft Office.

10. BONUS: It pays to tweet. As part of a Twitter contest sponsored by PASS, I tweeted what I liked best about PASS Summit 2012 (which was the PASS TV live feed of certain sessions of the Summit). And I was one of two winners of the contest! :-) I don't yet know what I won, but I'm hoping for the USB key with videos of all the sessions of the Summit on it. It's a long shot, since that's listed as a "grand prize," but I'm keeping my fingers crossed until I hear something, hopefully this week!

Next year PASS will be in Charlotte, North Carolina, and I'm definitely planning on going next year. My next PASS event is coming up much sooner: I'm speaking next month at SQL Saturday in Washington, D.C. on December 8th. Hope to see you there!

SQL Saturday #96 Wrap-Up and Top 10

SQL Saturday #96 happened in Washington, D.C. yesterday, and it was a great event. It is a special event to me because it was my first opportunity to speak at a PASS event and give back to a community that has given me much and transformed my queries for the better time and time again. If you are visiting my blog because you came to one of my sessions, please leave me some feedback on the presentation -- constructive criticism is as welcome as praise.

If you would like my PowerPoint presentations along with the .sql files I used for the demos in SQL Server Management Studio, you can download them from the SQL Saturday #96 Schedule page.

And in the same vein as my comments on the PASS Summit, I'm going to post the top ten things I learned yesterday. These are in roughly chronological order.
1. If you ever go to a SQL Saturday event, definitely use the Speed Pass and print it off before you to the venue on the day of the event. If you don't, you'll likely be in a line waiting to check in before you can join the fun.

2. Apparently Twitter or some Twitter-related organization ranks people's "influence" based on their tweets and the number of people who respond to them. This ranking is quite important to many people, and it was a point of animated discussion in the speaker's room at this event.

3. I discovered that people who are coming to T-SQL and SQL Server from Oracle and other platforms can benefit from an introductory course such as my introduction to Common Table Expressions, even when their time of experience with SQL is greater than mine. I suppose this is self-evident, but it was a little eye-opening to me.

4. Even though features have been around since SQL Server 2005, some people who have been using T-SQL for a long time may have overlooked a feature that I take for granted now, such as the window functions. I was a little pleased when I heard a couple of attendees express surprise that it is possible to generate an aggregate value without a GROUP BY clause because it meant that I had something to share that I knew could help them tremendously.

5. Receiving applause for a presentation is truly gratifying, even when it is a customary act done for every presenter. It seemed authentic in both my sessions, and therefore it was meaningful to me.

6. It seems that for most Business Intelligence professionals, having the primary justification for having a data warehouse is the ability to create an OLAP cube for analysis. I'm not sure that will be my justification. I think its primary benefit as a companion to our OLTP database would be to capture historical data for slowly changing dimensions and a close second or even a tie for first would be to enable a clearer semantic model for our end users in report design.

7. The date dimension generally adds the most overhead to any OLAP cube because it generally adds 365 rows x however many years are tracked. If there is any way to summarize to the weekly granularity or less, that will be a huge win for the efficiency of the cube.

8. It is ever clearer that any data warehouse that might be built as a companion for the Shelby v.5 OLTP database will include a single "EntityNames" role-playing dimension that contains all the entity attributes across all the available modules supplemented with views that take the core table and breaks out the module-specific attributes.

9. Even a large software company like Microsoft can overlook a feature that the end user sees as patently obvious. The case in point for today is the ability to update automatically all the Reporting Services reports that use a report part that has been changed. It's a manual process for now.

10. The built-in change tracking mechanism in SQL Server may be a great option for customers who want to know when things have changed at a more detailed granularity than we capture in the OLTP tables. But it only captures three days' changes by default, so a secondary mechanism for moving that data to a flat file or supplemental table is needed to go further back from there.

There you have it, my top 10 things learned yesterday at SQL Saturday #96. I'll return to blogging more procedural stuff after today. Let me finish this entry by saying that I really enjoyed the privilege of sharing my knowledge with the PASS community, and I hope to have similar opportunities in the future. If I do, you'll definitely be reading about it here. :-)

Top Ten Things I Learned at SQL PASS Summit on 10/14/2011

Travel and busy-ness with my baby Destiny have prevented my posting this last installment in my list of top lessons learned at SQL PASS, but now here it is:

1. Buck Woody and Rob Farley are not only superstars in SQL technologies, they are talented as singer/songwriters too. Check out the video of the opening of the keynote session for proof. Cue it up at 00:02:27 to skip to the song. Their song was my favorite moment of the week.

2. On Wednesday I wrote that I wanted to learn more about Hadoop since it was part of a new feature of SQL Server 2012, and today I learned more about it than I really wanted to know, but the presentation by Dr. DeWitt was fascinating. Hadoop is a method of "divide and conquer" applied to massive amounts of unstructured (or more accurately loosely structured) data, the kind collected by websites such as Facebook, eBay, and Google.

3. If you add NOSPLASH after the path to SSMS executable in the shortcut properties, the application will open a few seconds faster. It does not seem like much, but it's a handy tip that I put in place right away.

4. If there are more than a couple of OR conjunctions in any given WHERE clause, using UNION ALL is probably a better approach. The query optimizer in SQL Server is much better at streamlining two or more small query conditions rather than one huge, complex one.

5. When possible, replace correlated subqueries in the SELECT clause with joins in the FROM clause. This is because the correlated subquery must execute once for every row in the main results, eliminating any optimization that is possible when the data are correlated in the FROM clause.

6. I learned how to create a "temporal snapshot fact table" for a data warehouse solution. This improves the efficiency of a "daily summary" fact table by using date ranges instead of individual days as the granularity level, ranges during which nothing changes in the data. I suspect this may come in handy when constructing fact tables for pledges and donations. It is definitely a nice concept to keep in the toolbox of report design.

7. I learned that the query optimizer can anticipate the best query plan even when part of the query statement has been "refactored" into a common table expression (CTE).

8. Thanks to Audrey Hammonds, I learned some great techniques for explaining CTEs effectively. I plan to use some of her presentation style and content ideas when I present an introduction to CTEs at SQL Saturday #96 in Washington, DC.

9. CROSS APPLY is comparable to an inner join and OUTER APPLY is comparable to a left join. In that same context, I learned that there is no difference in query efficiency to move a CROSS APPLY table valued expression into a correlated subquery within the SELECT clause. This is reassuring, since I have already made such a move with the FOR XML technique of turning row values into delimited strings.

10. Snow Crash by Neal Stephenson is a book I need to read. When Audrey referenced it in her presentation, about half the audience recognized it and praised it as a great read. She mentioned a few other books throughout her talk, but this one definitely stirred the strongest positive response.

Top Ten Things I Learned at SQL Pass on 10/13/2011

The learning continues...

1. The "AlwaysOn" feature of SQL Server 2012 greatly simplifies high availability through redundant servers and simultaneously optimizes reporting by allowing you to use secondary servers for reporting and the primary server for transaction processing. However, it is currently unknown whether or not AlwaysOn will be in any edition other than Enterprise. It seems likely that it is an Enterprise-only feature.

2. The "ColumnStore Index" feature in SQL Server 2012 could radically speed up queries against tables in the Shelby v.5 database, especially attendance history. I can't wait to try it out. I may install Denali to try it out even before the official release of 2012.

3. There is a version of SQL Server Express coming out with 2012 that can easily be embedded into applications. This could potentially be a way to simplify the distribution of Shelby v.5 for those customers who do not own a license of SQL Server Standard. I will be looking into it more back home.

4. The "Semantic Model" features of SQL Server 2012, when coupled with the "FileTable" feature make for a powerful combination, capable of doing searches for document content based not just on matching of strings but actually pairing of concepts. I was blown away by the demo. Any librarian in the world would have been salivating over the processes and results I saw.

5. Two more times in one day I was shown the fallacy of putting dates inside of a function in the WHERE clause. I will never do it again, I promise! But I also learned that datetime and smalldatetime are being depricated in SS2012, meaning that we should probably start using the other, new data types for time. I will pass that info along to our database developers.

6. I learned that by simply changing the varchar data type to nvarchar, we could be capturing special characters for names, addresses, and more, which would make the v.5 database friendlier to international users. I'm going to do some testing on this when I get home too, to verify what is happening in our db right now when someone puts special characters into the varchar fields.

7.  I need to kick the habit of not specifying the length of varchar values and others when I use them as variables. I've been snakebit by that one before, and what I learned today just reinforces that I need to specify length 100% of the time.

8.  Some people, no matter how smart they are about what you want to discuss, can just be too annoying to talk to. It's just better to bide your time and find someone who may not be as knowledgeable but who is willing to listen and to converse at a pace that matches your own.

9.  The 2013 Summit will be in Charlotte, NC. That will be much more convenient for me to attend, and I am very much looking forward to not having to adjust to a new time zone in order to participate.

10.  Making free form reports with PowerPivot data in the background is not only possible, it allows for greater control over formatting and the embedding of elements such as sparklines into the data results. Free form reports can even use slicers to control the content. The only downside is the fixed nature of the rows and columns, which is why PivotTables are so handy in the first place.

Top Ten Things Learned at SQL PASS on 10/12/2011

Wednesday is when the conference starts in earnest, and so I definitely learned more than I can summarize here, but these ten things really stood out to me. As with my first list, this is roughly chronological over the course of the day.

1. There are over 4,000 registered attendees at this event. Add in the presenters and the vendors, and you can imagine how packed the rooms are. It is good to have such a thriving community; and yet, it does strain the capacity of the venue.
2. SQL Server Code Named "Denali" has officially been named "SQL Server 2012," and is expected to be released in the first half of next year. "Crescent" has officially been named "PowerView" and will be a part of the SS2012 release, but apparently it requires the Enterprise edition. This is unfortunate, because that will put it in reach of only a couple of our Shelby Systems, Inc. customers.
3. SQL Server 2012 will provide connectors and (eventually) drivers for something called "Apache Hadoop." This is something I will need to learn more about. Apparently Hadoop is a way to consume gigantic amounts of essentially unstructured data (on the order of pedabytes) and generate meaningful output. I don't know that I'll ever need that in my current role at Shelby Systems, but it is an interesting feature.
4. I learned several things during the demos in the keynote, including: "sentiment" is now a measureable fact that can be quantified and reported on through the Microsoft Data Marketplace and the upcoming "Data Explorer"; only John Wayne has made more movies than Samuel L. Jackson.
5. Stephen Few is a name I heard multiple times with regard to optimizing report style and layout. I believe I'm going to have to look into his web site at www.perceptualedge.com and check out his books.
6. If I can get ahold of some mapping files that include the ZIP code layer (the Census Bureau is said to have some in the public domain), I should be able to get the mapping feature in Reporting Services to map down to that level. That would be much better than the current limit of reporting at the state level.
7. Satori Software (a vendor here as well as a partner with Shelby Systems, Inc.) could actually return latitude and longitude information back to the NAAddresses table if we added that to our service agreement with them. The additional licensing cost makes it unlikely that this feature would be a part of all our customer's ShelbyMAILROOM package, but it is interesting to know.
8. The inactivated slicer problem I noticed in the Excel PivotTables that are created programmatically from ShelbyQUERY is probably triggered by some option in the automated set up of the table. A manually-create OLE DB connection can use slicers just fine. It appears to have something to do with an OLAP-related setting, but I'm going to have to follow up on this more with Microsoft. Helpfully, one of the PowerPivot techs gave me his contact info and invited me to e-mail him if I couldn't pinpoint it on my own. I have to give props to the Microsoft staff here at PASS, who are as helpful as they can be.
9. I have been doing date filtering in the WHERE clauses of queries in a way that will impede the efficiency of the query, possibly adding multiple seconds of time to getting the query results.  I have to learn ways to avoid putting table columns into functions, even the DATEDIFF() function.
10. The pie chart is not an effective way of communicating comparisons in reports. Bar charts are far an away the best chart for most comparisons. Gauges too are relatively ineffective communicators. But the bullet chart is exceedingly good at communicating "KPI" style data with more context. I will have to learn how to create them in Reporting Services.

Top Ten Things I Learned at SQL PASS on 10/11/11

More lessons from the SQL PASS Summit 2011:

1. When your laptop is a block or more away, that is the point at which you will most need it urgently.
2. It is quite a good feeling to be an "alumnus" of the event and be able to help the first timers with getting where they need to be and oriented to the essentials for the best PASS experience.
3. Although the views I have built for use in the ShelbyQUERY environment of our software are helpful for simplifying query writing, they are not really "data warehouse" structures. They blend too many distinct "dimensions" of data together, and they confuse (at points) the dimension/fact dichotomy. Thus I cannot really use that work as the guideline for a data warehouse database design.
4. A dimension that is a "role playing" dimension does not have to exist as multiple instances in the data warehouse, not even as views of the different roles. If two keys in the fact table reference the same dimension for distinct purposes, it is a "role playing" dimension.
5. Pragmatic Works, a SQL consulting firm based in Jacksonville, Florida has actually designed a data warehouse solution for a large Baptist church in that state. I'm sure my jaw dropped when Brian Knight (founder of Pragmatic Works) told me this in today's precon session. How could this be, and I didn't know it?! I will be meeting with Brian at the Pragmatic Works booth later this week to find out more.
6. The topic "Building a Microsoft Business Intelligence Platform" sounds too ambitious to be effectively covered in a one-day training session because it actually is. Yes, Brian and Devin (the presenters) touched on each phase of the process, but the touches were alternately too light and too technical, leading to frustration on my part and (I suspect) on the part of others in attendance. It was often a case of lingering too long on a technical detail of the process so that the presenters rushed the remaining steps in the current topic to move on to the next.
7. Subway stores in Seattle do not stock provolone cheese and do not accept the points card that is so handy in the Subway stores in Virginia. On the other hand, they do have the caloric values of all the sandwiches and other menu items displayed on the menu.
8. There is an interesting similarity in the number of languages one must learn to be a biblical scholar and to be a SQL Server developer. To be a biblical scholar, one must learn Hebrew, Greek, Aramaic, German, and Latin (at least). To be a SQL Server developer, one must learn T-SQL, MDX, DAX, VB.net, and the proprietary "expression language" of SSIS and SSAS. And, just like the languages of Hebrew and Aramaic, they can be similar enough to be confusing when you are trying to learn them all.
9. The PASS Virtual Chapter for Business Intelligence has four to five live webinar-style presentations a month, generally over the lunch hour, and these webinars are then posted for free as recorded webinars the next day. I will definitely be joining this "chapter" of PASS to take advantage of the resources it provides and (perhaps down the road) to find a way to contribute back to the SQL community that has so helped me in my work these last couple of years.
10. There are people here at PASS who are familiar with Shelby Systems, Inc, even apart from the Pragmatic Works folks who worked with our customer in Florida. I met a man at lunch today from Houston who attends a church where the staff uses Shelby v.5. He volunteers as IT support at his church of about 1000 members. They have been users of v.5 since before the converstion to SQL Server from MS Access, and he confirmed that the move was a tremendous improvement in the stability of the database.

Top 10 Things I Learned at SQL PASS on 10/10/2011

I have the priviledge of being here in Seattle for the SQL PASS Summit conference all week, including two days of "pre-convention" all-day training topics. Today was the first day of the conference, and I wanted to continue a tradition of summarizing the top ten things I learned today at PASS. These are in roughly chronoligical order.

1. It is good to get back on a treadmill and work out, even though a four-month break means that I could only go 2.6 miles in 45 minutes instead of 3.1 miles in about 40 minutes.
2. The scuttlebutt I heard last year is true: the Paramount Hotel is indeed a great place to stay for PASS, as it is reasonably priced and only a block or so from the convention center. And most of the walk there is under covered awnings. If you are familiar with Seattle weather in October, you know this is no small fact.
3. Talking to and singing to my almost-three-month-old daugher Destiny over the phone is a pale substitute for doing those things with her in my arms, but even through the phone her baby talk can melt my heart.
4. Apparently SQL PASS Summit attendees receive a new backpack every year.
5. The Guidebook app for Android phones (and probably iPhones too) is a great tool for an event such as this conference, and it is definitely something we should consider using for the International Shelby Conference next year.
6. In SQL Server Reporting Services, if you use a "cast" function (such as CDate or CStr) on a textbox value to give it a specific data type, it opens up a large set of VB.net methods that can make working with that value much easier. This is especially true for datetime values.
7. I really need to become comfortable with creating and using stored procedures over plain Jane SQL queries and views. They offer distinct benefits, which appear to be worth the trouble of being unable to create them easily with ShelbyQUERY.
8. In Reporting Services it is possible to create code blocks that do more involved VB.net functions than can be summarized into a single one-line of code in an expression. I knew this had to be the case, but today I learned how to make that happen.
9. Using a "post back" concept in the Action property of a Reporting Services report element creates the ability to have interactive "buttons" that change the parameters of the report content; for instance, it is possible to add "slicer" style buttons to a chart or matrix report design.
10. A walk from the hotel to Starbucks to get a refreshing shaken iced tea lemonade takes just enough time to wait out a false fire alarm at the hotel and return to see the firefighters climb into the fire engine and head out.

Top 10 Things I Learned Thursday at SQL Pass Summit

Sorry for the delay in posting this last Top 10 list of things I learned at the SQL PASS Summit in Seattle. Here is the final list:

10. The Paramount Hotel is probably where I need to stay next year if I get to come back.
9. A data mart/data warehouse solution would answer a lot of the temporal reporting challenges I have been unable to solve with straight T-SQL against the OLTP database.
8. The query execution process turns out to have three layers: the visible "typed" layer, the logical layer, and the physcial layer. I was only aware of the first two before.
7. Query optimization is more arcane and difficult than I would have ever thought (not that I ever thought about it).
6. The "shared data sources" and "mini-charts" in Reporting Services 2008 R2 will be a great help for self-service reporting.
5. The T-SQL function APPLY allows the query to apply a calculated table column or function to every row of the "left" table.  Believe me, it is much more useful than it sounds.
4. "ETL" means "extract, transform, and load."
3. I need to learn how to use the ETL functions in SQL Server Integration Services.
2. A "fact table" is a table comprised of measureable (i.e. usually numeric) "facts" along with a set of keys to to the facts to "dimension tables" comprised of descriptive (i.e. usually non-numeric) "dimensions."  Together these two tables form a "star schema," the fundamental structure of data marts and data warehouses.
1. I need to learn how to create a data mart for the Shelby v.5 OLTP database.

As a result of SQL PASS Summit 2010, you will be reading a lot more about PowerPivot, data marts, OLAP cubes, and more. I certainly have enough to learn to keep me busy until next year's Summit!

Top 10 Things I Learned at SQL PASS Summit Today (Wednesday)

10. The joy of having the ability to play arcade games an unlimited number of times all night long is somewhat tarnished by two factors: a) the hundreds of other people in the same arcade with equally unlimited number of times to play and b) I am no longer 12 years old.
9. What I do is actually a hybrid of T-SQL development and BI development.
8. It is an interesting feeling being the SQL novice in every conversation during the conference.
7. I should never post proprietary code on my blog. I don't think I've done that exactly, but the references to the Shelby v.5 database might be a gray area on that one. I am going to have to reconsider my posting style, and I may change how I handle code that is specifically for Shelby v.5 data.
6. I really need to learn the DAX "language" to make PowerPivot a truly potent reporting tool.
5. PowerPivot has no innate "grouping" ability. This means that the query tables I make for PowerPivot usage need to have grouping columns. A little planning ahead will go a long way in making that easier.
4. SharePoint is growing in importance as a collaborative platform in the Microsoft world, meaning that it will be a necessary tool for managing BI tools.
3. I can set up a SQL Azure account for just under $10 a month to have my own little database-in-the-cloud architecture to play with.
2. Reporting Services uses VB .Net for its expression language.
1. I have not even scratched the surface of the amazing things Reporting Services can do.

Top Ten Things I Learned at SQL PASS 2010 Today

10. The food served at dinner leaves much to be desired.
9. Report Designer v.3 (in SQL Server 2008 R2) has a mapping feature that can be really cool, but needs a lot of massaging to be truly useful.
8. Building a cube in Analysis Services works very easily if the data are stored in a "star schema." I don't know what that is, but I intend to find out.
7. The PowerPivot tool in MS Excel 2010 is not innate to Excel and must be downloaded and installed separately.
6. The PowerPivot tool is a basic cube technology, so it may not be necessary to learn how to make a "star schema" in order to use cube-style analysis on Shelby v.5 data.
5. Never assume that room 2 is immediately adjacent to room 3.
4. Recursive Common Table Expressions, which I have only recently discovered how to use, should be avoided because of the overhead they add to a query.
3. Tally tables are a way to avoid recursive CTEs.
2. SQL Server "Denali" is going to be available tomorrow for public review of the beta code.
1. SQL Server "Denali" includes "Crescent," a tool that will take PowerPivot ease of use and put it into  Reporting Services, along with a slew of new graphical representation report tools that are simply mind-blowing.