Hello and Greetings from SQL Rally in Sunny Orlando FL!
Today was the day of Pre-Con’s, four different tracks to choose from and I went with the Business Intelligence Track. All of the other track’s had One presenter, heck every pre-con I’ve ever been to has had One presenter, but not today.
Today was an end to end presentation covering SSIS, SSAS, SSRS, Powerpivot, Sharepoint, and Performance Point. Something that big requires not One, not Two, no Dear Reader not even Three, but Four Presenters.
Mike Davis (@MikeDavisSQL), SQL MVP Patrick Leblanc (@PatrickDBA), Devin Knight (@Knight_Devin), and the Big Man Himself, The Sultan of SSAS, The Conqueror of Cubes, SQL BI MVP Adam Jorgensen (@Adam_Jorgensen) delivered a presentation that was large in scope and in delivery. All of the presenters work for Pragmatic Works, all are published authors, and all will be speaking during the main SQL Rally event.
Pragmatic Works is a company that offers Consulting, SQL Products, and Training. They are very active in the SQL Community. If you’ve ever been to a SQL Saturday then you have probably had the opportunity to hear one of them Speak. If you have visited or posted a BI question on BIDN you have probably had one of them assist you in some way. They are good and gracious people and it has been a pleasure interacting with them in the past, and seeing them again today.
So with my buddy Gareth Swanepoel (@GarethSwan) showing the way to the Coffee, the experts were in place and we were Ready to Roll!
FUNDAMENTALS & SSIS
I thought it was very cool and original to have a rotating staff during a full day Pre-con, it kept it very fresh and lively, and as an audience member keep lulls from occurring. Patrick started out the day by introducing himself and the other members of his team. They quickly moved into a format where one person would be presenting and another would be up front as a co-presenter as well.
Devon quickly briefed us on the concepts and terminology that we would be using for the remainder of the work shop. We needed to know the fundamentals:
Fact Tables -which will be the center of our BI “Data Model”, but as a rule should contain columns that you can aggregate.
Dimensions Tables- the support tables that will provide information that we would use to sort or filter data by. Think of the information in Dimension tables similar to the data you would use in a Where clause in T-SQL.
Surrogate Keys – These are keys without a natural identifier. Think of using and Integer Identity Column in SQL. But the usage in BI is important on two fronts, not only will they be used as an identifier but to insulate us, the users, from source data changes in Natural Keys.
Before the day was over we would covered Type 0, Type 1, and Type 2 Dimensions, Attributes, Members, Hierarchies, and Role Playing Dimensions. We looked at Star Schemas and discussed the what makes a Snow Flake Schema, a BI Server in a VERY COLD room.
After an introduction we moved to SSIS and spent the next several hours there. We reviewed the many ways we could use SSIS, and in some cases SSIS & T-SQL, for Extract, Transform, and Load. Configuring SSIS packages for migration through environments, storage of packages MSDB vs. File System, and deployment of packages took almost the remainder of the morning.
CHANGE DATA CAPTURE
When you start talking about loading millions or billions of rows of data into a data warehouse every night, as a DBA I get nervous. We all know the overhead that BCPing Data out of and into systems can have. The overhead of disabling and rebuilding indexes, the ETL involved, Latches, Locks, and Blocks OH MY! Part of you wants to say there has to be a better way, how do we find the data that has changed and migrate only that?
Change Data Capture was the way home. Patrick had code and demo’s that I’m really looking forward to diving through and testing out. This was a concept that really clicked, and it was one that I had not considered before. Using the Log Sequencing Number, and tracking the columns that we need to capture (AND ONLY THOSE COLUMNS), we can minimize the overhead required to load data into a data warehouse.
LUNCH
Lunch was a great bag lunch with gourmet sandwiches, chips, water, apple, and a very delicious chocolate chip cookie. Tasty and delicious, but the best part was the company I walked around and had great conversations with Jack Corbett (@unclebigguns), Kendal Van Dyke (@SQLDBA), Andy Warren (@SQLAndy), met Grant Fritchey (@Gfrichey), and finally sat down and enjoyed the warm Florida sunshine with my cohort Dan Taylor (@DBABulldog) and Stacia Misner (@StaciaMisner). The SQL Community is a tight one, and every time I get together with people it seems like we pick up the conversation right where it left off.
SSAS-IT'S ALL ABOUT THE CUBE BABY
Adam was up next to discuss SSAS. He tied together concepts to show us how the regular SQL Engine & the SSAS Engine were similar and different all at the same time. How integers are more efficient in queries, which is why in a Date Dimension the SK should be an INT instead of a date time value, regardless of the fact that small date time and INT are both 4 bytes; How XMLA is to DDL as MDX is to DML, and the drawbacks of calculated measures vs. Transparent Aggregation were all on the table.
He blew the audience away with the Usage Based Optimization wizard, which literally is a wizard that based off of Internal Usage Statistics can be used to tune SSAS at a granular user level. He briefly touched on partitioning strategies, MOLAP, HOLAP, ROLAP, and when you would and should see each.
SSRS & SHAREPOINT
Mike did a great job of taking us through Sharepoint, demonstrating many of the configuration options, SSRS, Performance Point Reporting, and looking at the dynamic built in nature of SSRS 2008 R2 when it has the power of a data cube behind it.
TIME TO WRAP IT UP
Needless to say these guys are some of the top professionals in the world when it comes to BI, and it was fun to watch them work.
The crowd was very responsive and if you can’t tell I enjoyed it immensely.
This was 7 hours of training, 1 hour of lunch, and 7 pages of notes. Let me say that one more time 7 PAGES OF NOTES! No matter what I’ve written I couldn’t do justice to the information provided!
I’ve only touched on the highlights of the topics Dear Reader, if you’re interested in more I’d encourage you to go to the Pragmatic Works website and watch some of the free video’s they have up for training. They offer free webinars monthly on a variety of topics, and head over to the SQL Lunch website as well of which Patrick is the founder.
Now I’m off to bed, so I can be up and ready for tomorrow, SQL Rally Day 1!
Thanks,
Brad
No comments:
Post a Comment