TRAIN!!! FREE SQL Server Training: 4/20/13 SQL Saturday Orange County

If you're anywhere near Huntington Beach this week, and you're interested in SQL Server, data,

analytics or any combination of those, come check out SQL Saturday #201.

All day long (8-5), there will be non-stop sessions on a wide variety of data topics.  From DBA fundamentals to Big Data to Business Intelligence to Cloud to Data Mining...  The list goes on.  Did I mention it's FREE...

Check out the schedule here:
http://www.sqlsaturday.com/201/eventhome.aspx

References for ETL Design Patterns talk

4/4 San Diego SQL BI User Group:

Here are those references I mentioned in my 4/4 talk on ETL Design Patterns.  Lots of good content in here.  And for those that haven't studied Project REAL yet, definitely recommend giving it a look.  Even though it is a few years old, still very relevant for many implementations.




Considerations for High Volume ETL Using SSIS (2008)
     http://msdn.microsoft.com/en-us/library/cc671624.aspx

CodePlex SSIS components
     http://sqlsrvintegrationsrv.codeplex.com/

Thomas Kejser - early-arriving facts with partial-cache lookup & proc
     http://blogs.msdn.com/b/sqlcat/archive/2009/05/13/assigning-surrogate-keys-to-early-arriving-facts-using-integration-services.aspx

Partition Mgmt project on CodePlex
     http://sqlpartitionmgmt.codeplex.com/

Matt Masson - Design Patterns
     http://blogs.msdn.com/b/mattm/archive/2010/06/29/ssis-performance-design-patterns-video.aspx

Masson - SCD Tuning
     http://blogs.msdn.com/b/mattm/archive/2010/08/05/optimizing-the-slowly-changing-dimension-wizard.aspx

Veerman - Project REAL
     http://technet.microsoft.com/en-us/library/cc966416.aspx

Jamie Thomson - blog
     http://sqlblog.com/blogs/jamie_thomson/

Pareto Chart with PowerPivot

A Pareto Chart can be a powerful visualization to help you apply the 80/20 rule.  This viz focuses attention on the ~20% of the universe (customers, products, etc.) that often contributes 80% of the desired outcomes (sales, conversions, etc.).  Allowing you to focus your efforts on the ones that deliver the most important results (the items that are even with and to the left of where the running total crosses the target 80%).

Building a viz like this is insanely easy in Excel with PowerPivot.  Especially in Excel2013.

Analyzing Events using a Time dimension in PowerPivot / AS Tabular

Who knew Delorean's topped out at 88 MPH...

This is a follow-up post to one I did a little while back on Time dimensions in PowerPivot and AS Tabular (here's a link to that one).  I want to walk through building a model with that Time dimension.  And then use the model to analyze event data and explore for patterns by different date and time slices. 

Excel 2013 & PowerPivot: Multiple Copies of a Calculated Measure for use with "Show Values As" Analysis

I've been working with Excel 2013 more and more recently, and have to say I'm impressed with all of the enhancements the product team pulled together.  Especially in the BI area.  All the hard work up in Redmond is definitely paying off.  PowerPivot is continuing to mature and become more smoothly integrated with Excel.  And the Excel analytics features keep getting better.  Not to mention the addition of PowerView.

I wanted to do a few posts and share my thoughts on some of the things that I really like and think are particularly useful when working with PowerPivot models and building visualizations in Excel.  Lots of improvements that make it easier and faster to get from raw data to insights.  And allow users / analysts to spend less time and less clicks building and more time analyzing and exploring.

So for this first one, I'll focus on the ability to use multiple copies of the same PowerPivot calculated measure in a given pivot table or pivot chart.  This is something I really needed early last year on a dashboard effort, so when I saw this in Excel2013, I was stoked to say the least. 
 

DAX Groupers: SUMMARIZE and AGGX(VALUES())

Groupers are your friends

Sometimes you need to perform what I'd call a multi-pass aggregation.  Rolling up data to a higher level of granularity and then performing additional calculations on it.  Basically, the need to achieve the equivalent of SQL's GROUP BY in DAX, and then apply some additional calcs. 

Luckily DAX has a few Groupers that can handle this quite nicely.

A Time dimension specifically for PowerPivot & AS Tabular

I need a time dimension BOYEE!!

The other day I needed to build a model that would enable slicing of events by time of day.  My users wanted to be able to easily focus in on events that happened just in the morning or just after noon.  Several different slices or bands of time.

So obviously, since I'm a big proponent of dimensional models, and since my users naturally think dimensionally when they analyze and explore data, I knew that I needed a big clock in my model... 

Make that a time dimension.  But not just any old time dimension.  I needed one with flavor...

PowerPivot Viz - Historical Sales Versus Forecast Continuous Line Chart

Had a requirement the other day to be able to visualize historical sales and forecasts in one continuous line chart.  Actually pretty simple in Excel.  And when you are working with a PowerPivot model and have the power of DAX at your disposal, it's even easier.

SoCal Rock & Roll Code Camp - 6/24 - 6/25

The SoCal Rock & Roll Code Camp will be going off again in a few weeks (6/24 - 25).  Back at UC San Diego-Extension for the annual weekend extravaganza of diverse technology and dev content and the coolest kids in the San Diego tech community :-)

http://www.socalcodecamp.com/

SQL Saturday #120 - Huntington Beach, California

March 24, 2012.  Mark your calendars.

If you live in Southern California or are heading this way, come check out the Orange County SQL Saturday event in Huntington Beach.  Register at the link below and come expand your brain with a full day of hour-long classes on all things Microsoft Data. 

Topics include everything from intros to advanced topics and best practices in the areas of Database Admin, DB Dev, Data Warehousing, Business Intelligence, Testing, Cloud, Agile... 

The list of sessions continues to grow every day and won't be finalized until just before the event most likely.  Check out the schedule of sessions at the link below and secure your spot. 

I'll be doing a session or two on dimensional modeling, data warehousing and Microsoft OLAP topics.  Hope to see some of you there. 

http://www.sqlsaturday.com/120/eventhome.aspx