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

SSIS Expressions and Quarter Start Dates

the ultimate county fair strategery game - the quarter plow

SSIS has a pretty powerful expression language.  Of course, it's not exactly the same expression language as in SSRS.  And it's not exactly C# or VB.  It is C-like though, so it's not that bad to get the hang of.

Anyway, it can be pretty useful.  Some common uses of expressions within SSIS are when creating dynamic variables (evaluated as an expression) and derived column values in the pipeline.  This post details an example of using an SSIS expression to calculate the quarter start date for each row in the pipeline as it passes through a Derived Column component.  This same approach could easily be used to apply business rules to dates or other values in your source data on the way to your fact table.