Showing posts with label measures. Show all posts
Showing posts with label measures. Show all posts

Sunday, March 11, 2012

Best Practice for Counts/Distinct Count Measures for Filtered Data

Let's say the Count/Distinct Count measure is based on filtered data.

When the situation allows, is it better to do a CASE statement with 1s and 0s from the table in the DSV and then do a SUM on this field? Or is it better to create a new Named Query in the DSV filtering out the data through the WHERE clause based off of the original table and then do a DISTINCT COUNT measure on the newly created Named Query?

If there's no clear cut answer as to what is the "better" approach, please offer up the advantages and disadvantages for both. My preference is best performance.

An Example:

Measure: Active SKU Count
Products dimension has product_key, SKU and Status

In the DSV for the Products dimension, should I add a CASE statement:

CASE WHEN [Status] = 'Active' THEN 1 ELSE 0 END AS [Active Status]

Then create a new SUM measure based on the Active Status column.

OR

Create a new Named Query in the DSV:

SELECT * from Products WHERE Status = 'Active'

Then create a new DISTINCT COUNT measure based on the SKU column from the new Named Query?


These 2 alternatives do not produce the same results. If a given SKU has fact records in Jan, Feb, and Mar, the first approach using SUM would return a value of 3 for Calendar Quarter 1. Then second approach based on a distinct count measure would return 1 (which is probably what you want)

Distinct count measures should be implemented in their own measure group anyway, so if you only ever need the active count then filtering out the non-active records is a good option.

If you need to get distinct counts of both active and inactive SKUs then you would be better off adding the Active flag as an attribute to the product dimension and implementing it as a slowly changing dimension.

|||Darren,

I had a feeling that these 2 alternatives would not produce the same results when I was typing up the question. Perhaps my example scenario wasn't a good one, because the limited testing with my applicable situation yielded the same results.

In any event, I will just go ahead and go forward with the separate measure group with the new Named Query approach.

Thanks!

Sunday, February 12, 2012

Beginer question - calculated aggregation

Hi,

I have two measures: quantity and price.

The default aggregation sum is not valid for price, because I dont want sum(price), what I want is somethin like:

sum(quantity*price)/sum(quantity),

in other words, a weighted mean.

How can I do that?

I think that I have to place some code in the CALCULATE section, but I don't know exactly how.

Thanks.

have you tried placing it in your query? I tried it (using Northwind) and it runs just fine...

SELECT OrderId, SUM(UnitPrice*Quantity)/SUM(Quantity) FROM [Order Details] od

HTH,|||

CryptoKnight wrote:

have you tried placing it in your query? I tried it (using Northwind) and it runs just fine...

SELECT OrderId, SUM(UnitPrice*Quantity)/SUM(Quantity) FROM [Order Details] od

HTH,

That works for leaf data, but the problem is aggregations.

By default, Analysis Services 2005, makes sum() for calculate the aggregations.

|||

You have a few options. Probably the easiest thing to do is to hide your Price measure and create a calculated measure to display instead. This could be defined as

IIF(MEASURES.QUANTITY=0, NULL,
MEASURES.PRICE/MEASURES.QUANTITY)

There are ways to overwrite the values of the original Price measure too, which I can go into if you're interested, but they're a bit more complicated.

Chris

|||

Chris Webb wrote:

You have a few options. Probably the easiest thing to do is to hide your Price measure and create a calculated measure to display instead. This could be defined as

IIF(MEASURES.QUANTITY=0, NULL,
MEASURES.PRICE/MEASURES.QUANTITY)

There are ways to overwrite the values of the original Price measure too, which I can go into if you're interested, but they're a bit more complicated.

Chris

Ok, that seems to work fine.

Only I changed IIF(MEASURES.QUANTITY=0, NULL,
MEASURES.TotalAmount/MEASURES.QUANTITY)

and I removed the old Price measure, because I dont need it anymore.

THANKS.