This past Thursday and Friday I attended my first PASS SQL Rally event in Dallas, Texas. The week was full of some pretty amazing sessions presented by some even more amazing speakers. If you were a DBA looking for performance tuning tips or other ways to perfect your profession, there were tons of great sessions for you. If you were a developer looking some great development tips and tricks to make your life easier, there were some really informative sessions given. And if you were a business intelligence developer, like myself, hoping to take your skills to the next level, there was definitely some amazing material to take advantage of.
I focused heavily on the business intelligence space and spent most of my time attending those sessions. I learned some great tips, increased my knowledge in several areas, and also networked a little with some really bright and great all-around people.
So What Sessions Did I Attend And What Did I Learn?
Performance Tuning SSAS Processing with John Welch
The first sessions I attended was Performance Tuning SSAS Processing given by John Welch (blog | twitter). There’s no doubt about, John knows his stuff. I learned a ton from his session. Here’s a few of the tips I picked up from John’s great sessions:
- Instead of bringing entire tables into your DSV, use views. Views will allow you to tweak the SQL and use query hints to improve the performance of the Select statement.
- When Analysis Services processes data, it does so one buffer at a time. If AS detects duplicate rows in a buffer, it will aggregate the data. Because AS aggregates duplicate records, you can save on space and increase performance if you order the data in your Select statement in your partitions.
- Tune SSAS processing in 3 steps:
- Dimension Processing
- Partition ProcessData
- Partition ProcessIndexes
- Set the AttributeHierarchyOptimize property to false for attributes that are not used in queries often or if the attribute has a high cardinality and a near 1:1 relationship with the key attribute.
The Report Part Library with Jessica Moss
The next session I attended was The Report Part Library with Jessica Moss (blog | twitter). Jessica is one lady who is an amazing speaker and an SSRS guru without a doubt. Jessica’s wonderful sense of humor and bubbly personality made the session enjoyable and exciting. We covered how to create report parts, share those report parts across multiple reports, and then update those same report parts. Jessica also covered how to organize and manage a large Report Part Library. It was really great to finally meet Jessica in person.
Stop! Consolidate and Listen! with Jorge Segarra
After lunch, I attend Jorge Segarra’s (blog | twitter) session called, “Stop! Consolidate and Listen!”. Jorge covered some of the different methods of consolidation and dug into virtualization. Between Jorge’s sad rap skills and Patrick Leblanc’s even weaker beat boxing, I managed to pick up a few pointers.
Some of the Pro’s to virtualization:
- Great isolation!
- Snapshotting virtual machines (so if something goes horribly wrong, you can just roll back to the last snapshot)
- Creating and dropping virtual machines is easy as pie
- VM’s can be migrated across servers without having to be shut down!
Some of the Gotcha’s:
- Behind the scenes resources could actually be shared, so make sure you understand the systems that your VM’s are running on.
Also, check out the free MAP toolkit from Microsoft. Jorge gave a great intro into the tool and how to use it to determine which machines are prime candidates for consolidation and virtualization.
Data Mining with Devin Knight & Adam Jorgensen
The last session I attended was Data Mining with Devin Knight (blog | twitter) and Adam Jorgensen (blog | twitter). Being far less than a master of data mining, I really looked forward to this session.
Data mining does three things:
- Explores the data
- Identifies patterns in the data
- Performs predictions based on those identified patterns
There are several algorithms available for us to use:
- Decision tree
- Allows us to see how someone could come to a decision?
- Measures the distances between the cluster of points on a graph and an outlier
- This method is often used for fraud detection and data validation
- Time Series
- Predict 3 units (days, months, years, etc) into the future
- Based on historical values
- Sequence Clustering
- Used to determine somebodies next step
- Where will a person click next on a web site?
- Association Rules
- Market basket analysis
- Amazon uses this: People who bought this also bought this.
- Naïve Bayes
- A data mining algorithm that is literally hundreds of years old
- A classification algorithm that could be used to determine the differences between customer who buy a product and customers who do not buy a product.
- Neural Networks
- Typically used with FICO scores
- Takes multiple factors into account but can’t really point to one or two things to tell you how it came to a decision
Zero To Cube: Fast Track To Analytics with Adam Jorgensen & Dustin Ryan
I also had the wonderful pleasure of presenting on SSAS with Adam Jorgensen (blog | twitter). Our session was titled, “Zero To Cube: Fast Track To Analytics”. We had volunteers (some were volunteered by others) drive through creating a cube from the ground up. Within an hour and 15 minutes, we created a connection to our data source, created the data source view, made some enhancements to the data source view, created our cube, built a couple hierarchies and fleshed out the dimensions, and even built a report in Excel with a pivot table and a chart. We had a great, very participative audience and we had a blast! Thank you so much to all those that came to our session!
All in all, this year’s SQL Rally was an amazing event. I learned a lot of new things, made some great friends, and had a great time. If you weren’t able to make the event, you missed out big time. Definitely put next years SQL Rally on your calendar and make attending that event a priority. You won’t be sorry.