Category Archives: PASS

Earlier today I had the pleasure of speaking with the PASS Business Intelligence Virtual Chapter on getting started with Power BI and time calculations! There were a bit of audio issues on my end but thankfully we were able to still have a great event with lots of great questions!

If you missed the webinar, have no fear! You can still watch the recording right here. Just jump ahead to minute 11 as I had some unfortunate connection issues!

If you have any additional questions or feedback, please leave a comment down below.

SQL Saturday #442 in Orlando, FL has come and gone but what a turn out! The event was excellent, we had a great turnout for our session and had a blast! And as a bonus, the BBQ lunch, baked beans, coleslaw, mac n cheese and dessert were amazing. Seriously one of the best lunches I’ve had a SQL Saturday event! Plus, the Lego name tags were epic! 100% without a doubt the coolest name tag ever.

Like this:

Thank you to everyone that attended my webinar titled Power Pivot 101: An Introduction! Also, thank you to Thomas Leblanc (blog|twitter) for making it possible. I had a great time presenting to the PASS Excel BI Virtual Chapter and I’d love to be able to do it again any time.

If you weren’t able to make the webinar, you can easily view the entire recording right here!

Feedback?

We had a lot of questions at the end of the webinar and I didn’t have time to answer all the questions. If you had a question that I didn’t get to, please just leave a comment down below with your question.

If you had any other feedback, you can leave that comment down below, as well. Thanks for reading and I hope you enjoy the webinar.

I’m excited to be able to say that coming up next week on Thursday September 10, I’ll be presenting my session Power Pivot 101: An Introduction to the PASS Excel BI Virtual Chapter! For a lot of users, Power Pivot is like the Ferrari you had in your garage but weren’t aware and that’s one of the reasons I’m so excited to be able to present on this topic. This session is completely free and available to all who would like to attend. It doesn’t get much better than that!

Power Pivot is a powerful yet flexible analytics tool built into a familiar environment yet many users remain unsure of how to take advantage of this dynamic tool. In this session, we’ll discuss the purpose of Power Pivot, where Power Pivot fits within your organization and the basics of designing a Power Pivot model that integrates disparate data sources with the goal of gaining previously unrecognized insight into key business metrics.

Now for the questions! Many of you asked some great questions but unfortunately we ran out of time to answer all of the questions during the webinar. So here are a few of the questions we didn’t get to.

Q: How do I link if column have more than one column is key column in tabular?A: If you need to create a composite key in a Tabular model table, you will need to create a calculated column that concatenate the columns that make up your composite key. You’ll need to do this in both tables you wish to relate. Once you’ve done that, then you can create the relationship between the two tables using your new columns.

Q: Can DAX be used to access cubes?A: In the SQL 2012 SP1 CU4 release, DAX support for multidimensional cubes was added, so as long as you are running on SQL 2012 SP1 CU4 or later, you should be able to query cubes with DAX expressions. On a side note, MDX can also be used to query a Tabular model.

Q: Since tablular solution is many ways better than Muti Dimensional..then my question is when to go for Multi dimensional solutionA: This is one we covered extensively during the webinar. Here are some of the things to consider:

How much data are you dealing with? If its too much to fit into memory for your Tabular model, then MOLAP is the way to go.

Do you have a need for complex relationships? If so, MOLAP may be the answer. Role playing dimensions and many-to-many relationships are possible to create in a Tabular model, but they’re easier to create and manage in a MOLAP cube.

Do you need to perform many complex calculations involving complex Scope assignments? If so, MOLAP is the answer here.

Q: Can you use a Multidimensional database as the source for a Tabular model and improve performance when creating low level granular reports?? This goes back to the performance differences between Multidimensional vs Tabular when creating granular reports.A: You can use a Multidimensional database as a data source for a Tabular model, but I would suggest getting the data from the original source for the tabular model. If granular type queries are slow against your cube, those same queries are still going to be slow when you execute them to process your Tabular model.

Like this:

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!

Flexibility

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.

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?

Clustering

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

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.