Category Archives: PASS

View Getting Started with Power BI and Time Calculations with Dustin Ryan is Now Available!

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.

Additional Resources

Download my PowerPoint slide deck here.

The PASS BI VC is a great group with tons of free, quality training events! I highly recommend you connect with this group so you can stay up to date on all their great events!

The PASS BI VC also has all their previous webinar recordings hosted on YouTube so definitely check that out!

If you’re new to Power BI, I suggest you start here.

Read more about Getting Started with R Visuals in Power BI

Read more about 10 DAX Calculations for your Tabular or Power Pivot Model (Part 1)

Read more about 10 DAX Calculations for your Tabular or Power Pivot Model (Part 2)

Feedback

I hope you enjoyed the webinar and that you maybe even learned a little something. If you have any questions, comments, or feedback, please leave it down below! Thanks for reading and watching!

Advertisements

Thank You for Attending my #SQLSatOrlando Session! Slides, Resources, Recording

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.

Continue reading Thank You for Attending my #SQLSatOrlando Session! Slides, Resources, Recording

Watch the Power Pivot 101 Webinar Recording

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!

If you’d like to play along with the webinar and follow through with my examples, you can download the data sources here.

If you want to download the Power Pivot model I created during the webinar and play around with it, that can be download here, as well.

Power Pivot Learning Resources

Read about options for upgrading a Power Pivot model.

Interested in hands on training with the experts from Pragmatic Works? Consider taking their Power Pivot modeling class.

Here is part 1 of 10 DAX calculations for your Power Pivot model.

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.

Join Me for Free #PowerPivot Training w/ @ExcelBIPASS Sept 10 – 12p CDT

image

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!

Register for free Power Pivot training

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.

This free online training event is scheduled for Thursday September 10th at 12 pm CST/1 pm EST. If you’re interested in attending, all you need to do is RSVP here to let the organizers know you’re coming. It’s going to be a great event, a lot of fun and maybe even educational! 😉

Recording Now Available For The Webinar, Choosing The Right Analysis Services: MOLAP Vs. Tabular

image

Thanks to everyone that attended Devin’s and my webinar called Choosing The Right Analysis Services: MOLAP vs. Tabular. I’m pleased to announce that the recording is now available to watch for free over at PragmaticWorks.com, so please go check it out. It’s a little less than an hour so you can watch it during your lunch break.

Also, the PowerPoint slide deck Devin and I used during the webinar is also available for viewing now! Please visit this link to download the slide deck.

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 solution
A: This is one we covered extensively during the webinar. Here are some of the things to consider:

  1. 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.
  2. 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.
  3. 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.

Thanks to everyone that attending Devin’s and my webinar! If you have any other questions, please feel free to leave a comment or send me a message on Twitter!

SQL Rally 2012 Recap In Yo’ Face!

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.

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?
  • 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

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.