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!

Best method: TOP 1 or DISTINCT or MAX

'TOP 1' or 'DISTINCT' or 'MAX'
Any sugestions on which is better to use if I need to select a record that has the highest value - could be a INT or sometimes a DATETIME.Distinct will not get you a max value, if you use top make sure you use the order by.

HTH|||To select the entire record, use TOP 1 on a sorted recordset. To get just the highest value for the field, use MAX().|||[blindman]: someone else suggested that TOP is more efficient then MAX is that true?|||I don't know. It probably depends on a lot of factors and makes little difference either way.|||Best way to test this is to use the "set stistics IO on" command to check your logical IO (number of times you hit a page)|||Originally posted by rhigdon
Best way to test this is to use the "set stistics IO on" command to check your logical IO (number of times you hit a page)

I used "SET STATISTICS IO ON" command and got a line for each table in query...

Table 'tblUser'. Scan count 1, logical reads 2, physical reads 2, read-ahead reads 0.
Table 'ctsJrn_Location'. Scan count 7, logical reads 14, physical reads 2, read-ahead reads 0.
Table 'ctsIndex'. Scan count 8, logical reads 16, physical reads 0, read-ahead reads 0.

Can anyone tell me what does each count of 'reads' mean?

Thanks,
Lito|||Scan count - number of times data or clustered index pages were scanned;
Logical reads - total number of records read from cache (I think);
Physical reads - total number of pages read from disk (I think);
Read-ahead reads - number of pages optimizer chose to read ahead (I think)

But the point is, you want to minimize the first 2 indicators. And, BTW, scan count does not always mean that the actual scan occurred. It just means that the optimizer had to look at data/clustered index pages of the corresponding table so many times.|||Originally posted by rdjabarov
... But the point is, you want to minimize the first 2 indicators...

What range should those indicators be in, are mine ok?|||It depends on number of rows the tables have vs. number of rows returned.|||but is there a ratio?

I am selecting one row from 9 joint tables with approx. 18k records each|||Then your numbers are actually good.|||The only counter I truly look at is logical IO as it is the number of times a page is hit (not number of pages) the lower you canb get this the better. The problem with physical and read-aheads is they can be optimistic and not exactly accurate.

HTH|||your physical reads should be zero or as close to zero as possible.
this means that you are reading pages from disk into memory.. that is something that you want as little of as possible.

you will want logical reads to be as low as possible as well but those numbers are based on the actual work that SQLSVR had to perform to retrieve your query. so the number is academic based on your query, statistics, indexing etc.

typically you should only retrive the rows that you need in a query result, so if the question is which would be the best query to perform? so if you want to just get one row the logical answer would be an aggregate function

Select Max(Col1) as 'MAXNUM' from table2
this will retrieve a scalar value for you (one row one column)
ex
MAXNUM
=====
100

as far as distinct and top, are concerned
DISTINCT does not give you a max value, it removes duplicates from the columns gueried which i guess you could then sort decending to get the largest value
""select distinct state from table2 order by state Desc""
ex
STATE
====
TX
GA
FL
CA

TOP 'n' is designed to return an restricted set of values
""select TOP 5 col1 from table2 order by col1 desc""

COL1
====
5
4
3
2
1

your best method here would be to run the query with each of the different types of commands
view the stats io and compare all three.|||Thank you all for your comments and sugestions, this helped me alot. Learn something new every day...

Lito

Being smart or bug?(mssql2000)

Why this query works:

use northwind
select distinct em.EmployeeID
from employees as em
inner join EmployeeTerritories as et
on em.EmployeeID = et.employeeid
where e.m.country='uk'

Pay attention on the las line.
As you see it is written "e.m.country" instead of "em.country".

This is a bug in the parser for SQL Server 2000. It is present in SQL Server 2005 also but only in compatibility modes below 90. If your database is in 90 compat mode then you will get an error like:

.Net SqlClient Data Provider: Msg 4104, Level 16, State 1, Line 1
The multi-part identifier "e.m.country" could not be bound.

The behavior is retained in older compat modes for backward compatibility reasons and to ensure that existing code works fine upon upgrade to SQL Server 2005. Hope this clarifies.