Measure Aggregate Functions - SSAS 2012

In SQL Server Analysis Services Enterprise Edition, there are many Aggregate Functions

are available as shown below. In this blog I am going to explain one by one. All the aggregate functions are available only in SSAS Enterprise edition, for example “None” is only available in Enterprise Edition if you try to use in standard edition it will throw an error.

When you create new measure, first thing is you need to select the Aggregation Function that shows as Usage.

Sum: Calculates the sum of values for all child members. This is the default aggregation function.

Count: Retrieves the count of all the child members.

 

Min: Retrieves the lowest value for all child members.

Max: Retrieves the highest value for all child members.

Distinct Count: Retrieves the count of all child members. A few of the more typical application for DISTINCT COUNT analysis are:

• Sales and Marketing, especially counting distinct number of customers.
• Insurance claims relating policies to damages. One claim may have many damages.
• Quality control data relating causes to defects. A defect can be caused by multiple factors.

The below example shows that how distinct count is different from other aggregation functions.

Product Dimension Members	Distinct Customers Count
All products	200
Hardware	80
Computers	70
Monitors	60
Printers	30
Software	150
Home	100
Business	100
Games	80

In this example, 70 customers bought computers, 30 customers bought printers, and 60 customers bought monitors. However, the total number of customers who bought hardware, according to the result set, is not 160, or 70+60+30, as shown in the table. The query results display an actual count of 80 total hardware customers. The reason for this irregularity is simple: many customers bought more than one product. Some customers bought both computers and monitors, others bought the whole three-piece package, and some replaced just the monitor, and so on. The end result is that there is no way to infer directly from the lower level results what the customer subtotal really is. This discrepancy continues through the upper levels as well: 80 customers bought hardware, 150 bought software, and all together, All Products totals only 200 customers.

None:  No aggregation is performed, and all values for leaf and nonleaf members in a dimension are supplied directly from the fact table for the measure group .if no value can be read from the fact table for a member, the value for that member is set to null.

By Account: calculates the aggregation according to the aggregation function assigned to the account type for a member in an account dimension. If no account type dimension exists in the measure group, treated as the Non aggregation functions.

Account type can be defined by unary operations as below:

In here ~ operator meaning ignore aggregation and + operator meaning add to preceding sibling likewise aggregation will be based on particular column value. As this brought subject, I will cover these details of this on other blog.

Average of Children: (Average over time) this is only along a Time Dimension.

Example creates a measure using the sum aggregation type for Sales Amount; create a Non-Empty value or (count of Row) measure as Count, from these we can calculate a measure as

[Measures].[Sales Amount]/[Measures].[Count].

Another important consideration, which depends on the business scenario, is the type of the Count aggregate. It could be Count of rows (Row Bound) or Count of non-empty values. The difference is whether we want to include or exclude the empty values from our aggregate. Either way, the described technique will work equally well.

FirstChild: Retrieves the value of the first child member.

LastChild: Retrieves the values of the last child member.

FirstNonEmpty : Retrieves the value of the first non-empty child member.

 

LastNoneEpty:  Retrieves the value of the last non-empty child member.

Cheers!