SSAS has quite a few functions that have different purposes and can make a lot of potential calcuatlions a lot easier.  A recent issue I was faced with is I had a new measure group to build based off a fact table.  This measue group had quite a few Calculated Members that needed to be built in order to complete the all the measures that would be needed.

I came across an interesting problem where I needed to use a measure from a seperate measure group, and apply a dimension to the measure to get a total. To get down to details, I needed the Gross USD (United States Dollars), only when applied by dimenions of food and beverages...not when involving merchandise.   Now, the identifier for Food, Beverages, and Merchandise were all part of one dimension named "Family Grouping".  So I knew in the end I needed the sum of Gross USD, and I needed the value of it only against Food and Beverage. 

So, at first I thought, maybe I would use the Scope Function, but that seemed like it wasn't really answering my issue, as the SCOPE function seemed to apply values across a particular range of a given measure. 

No, what I wanted was to be sure that whenever I got my GROSS USD total, it always gave me the amount when directly related to my particular dimensions...and the answer to that was to use the SUM function.

So my first stab at the sum function was to use code similar to this in my calculated memeber, minus all the other "who-ha".

SUM({null:[Dim Type Grouping].[Type Grouping].&[2]}, [Measures].[Gross USD])

What this did is it gave me the sum of the Measure Gross USD across my Dimension of type grouping 2...but I was surprised to see it included the type Grouping 1 as well.  This wasn't what I wanted as the the type grouping of 1 had no bearing on my calculated member that I was trying to achieve.  The Null statment previous my Dimension is what caused it to pull in my first dimension as well.  Admittedly I still need to figure out why that is the case.

So to fix my calculation I made a slight modificaiton, and got this.

SUM({[Dim Type Grouping].[Type Grouping].&[2]}, [Measures].[Gross USD])

The query above in my calculated member, properly returns only the Gross USD when related to the Dimension of typeGrouping 2.

In the end, the Sum function is quite powerful, and can be used in a variety of ways, and should not be confused with the AGGREGATE function, which works a bit differently.

Sql Server Analysis Services, a powerful tool, large in scope, deep in subject.  You could literally write books on SSAS, and they have, but what is it's use?  Well, Business Intelligence is the first obvious answer, but BI can be delivered from just SSRS (SQL Server Reporting Services).   What is a report though? A report is generally speaking a predefined view of data that allows a user to get the values they want to see from a set of data.

SSAS delivers the same concept, plus other data that the user hasn't even requested yet, but, as we all know, they'll ask for it tomorrow.  Today the user is happy with seeing total sales for a particular week, itemized by stores.  However tomorrow they want to see sales for the week, itemized by category instead of stores.   Can you deliver this in SSRS? Sure you can...lets work up a new query and a new report.... but how would SSAS potentially make this easier/faster?

SSAS can bind to SSRS, or other third party reporting tools, but where it really shines, IMO, is it's "auto-magic" functionality in Excel.  As can be seen in the picture below, in 2007/2010, if you open a sheet, you can click on the Data tab, then choose "From Other Sources" and then use the option "From Analysis Services".

Naturally you'll need access to an SSAS cube that has been created already, but excel will let you work with this cube in a drag and drop style environment.  A pivot table is setup for you and calculations generated based off the columns, rows, and numbers(measures) you drag into the fields to see.  Automatically allowing on the fly filtering from drag-drop menus, filters, or if you have 2010, you can use Slicers.  Slicers being basically windows with filter options letting you quickly select multiple values to aggregate data by.

So think about it like this, with SSRS only, you could get a request for a report that you can deliver in a few hours.  With SSAS, and just Excel, that request could be avoided completely, the end-user could just drag-drop the values they want to see on a pivot table and see the numbers immediately.   Allowing them to play around and see some numbers that aren't really critical enough to actual get someone to make a report for.

Also, another handy feature of excel, is the ability to select a pivot table you already created and just spawn a pivot chart from it with a few clicks.  So essentially your spawning sales dashboards in moments, instead of hours.  And again your trashing sales dashboards you don't like because the overhead cost to make them was so tiny why not create a few and throw them away? It's not like someone slaved for hours to make a pretty pie chart or bar chart, you did it in seconds with a couple mouse clicks.

All of this of course depends on someone setting up SSAS, getting the cube built, dimensions, proper measures, and applicable permissions.  Again a very broad subject, and this blog post still probably doesn't hit the general concept well enough, but the key is to understand that SSAS is very powerful in Business Intelligence, and if leveraged correctly can save lots of time in end-user data analysis.

The term simple should probably be avoided when speaking of SSAS. For those of you wondering what SSAS is, it's short for SQL Server Analysis Services.  Now if your wondering what's that, this blog post is probably not for you.  However if you are involved in database reporting in any shape or form, I encourage you to take some time to familiarize yourself with it, it's very powerful when applied.

Recently, when trying to work on a relatively new cube for my company, I had some difficulty finding a clear explanation on how to accomplish the task of generating an Average value based off my fact table.  In my efforts to find the answer I saw that, like many other things, there are many ways to skin the cat.   While searching I came across a lot of references to calculated measures, but no great tutorials on how to implement it.   Hopefully I can remedy that by providing a somewhat decent tutorial on creating a basic calculated measure.

In my scenario, my fact table has a "Seconds Elapsed" column that represents the amount of time spent on each line item.  My goal is to allow all my dimensions, including time, sum and average the "Seconds Elapsed".  In some cases you may want to avoid including a time dimension in your measure, which is a semi-additive, and not covered in this post.  So conveniently, were going to let every dimension have it's dirty little way with our measures.

So, we have our measure for "Seconds Elapsed", and it's set to sum.  This measure includes zero values, and values greater then zero, all of an Integer type. You can see an example of the data in question from this picture taken from an "Explore Data" view of our fact table.

 So ignoring SSAS in general, what's the basic formula to find the average value of something?  Sum / Count.   Essentially an aggregate divded by an aggregate.  To find the average time elapsed we need to take the Sum of  "Seconds Elapsed" in scope and divide it by the in scope Count.  Scope is easy in our example since it has no limitations no matter the dimension.  Scope could potentailly be a bit more difficult to deal with if we were going to use our average as a Semi-additive.

So, lets build what we need for our calculated measure,  we already have our Sum measure in "Seconds Elapsed", but next we need a count measure, as a generic count of our fact table would not be suitable. The reason for this is some of our "Seconds Elapsed" fields have 0 values.  If we include these zero values in our Count, it'll skew our average. So we need to create a measure that gives us a count of only the "Seconds Elapsed" with a value greater then zero.  To do this we create a named calculation by right clicking our fact table in the data source view and choose new named calculation. This brings up a form allowing us to enter our calculation.

As you can see we use basic T-SQL here to evaluate our "Seconds Elapsed" column and result in a 0 or 1.  This will give us a new measure that we can Sum as our divsor.  Now no matter what dimension we apply, we'll have an accurate count of how many rows to include as our divisor in our average calculation.  

As you can see in the picture above, where we have a "Seconds Elapsed" value greater then zero, the "TimeAverageDivisor" column has a 1, and all other values are 0, thereby giving us our accurate count.  We will now include "TimeAverageDivisor" as a measure, by going to our cube structure, and adding a new measure, then choosing our named calculation, setting the aggregate to "Sum".

Finally, we've got all our building blocks, so now we need to create our calculated Measure.  To do this, we go to our cube and click on the calculations tab.

The small green arrow above I've so amazingly crafted with MSPaint, is pointing out the "New Calculated Measure" button.  Cllicking on this will bring the handy dandy window that lets you input your basic information about this new measure your creating, and the associated formula.  Best advice at this point is, to use drag-and-drop techniques to write your formula for you.  On the left we see our dimensions and measures.  We will drag out our "Seconds Elapsed" sum measure, enter the standard division symbol "/" and then drag out our "AverageTimeDivisor" we built earlier.

The query is a very basic MDX Expression, named "AverageTimeSpent", and will be automatically made available as a new measure in any of our analysis done.  That's the end, build and deploy your solution, and you should now have an "AverageTimeSpent" measure.  Feel free to hit me up with any questions to fill in any holes I may of missed.