 |
 | Home | Contact Us | FAQ | Search & Site Map | Link to Us |
 | Sign In | Join | Other 45 Sites in Network |
SQL Server Forum / General / Data Warehousing / December 2005Measure with "Distinct Count" Aggregate Function and Null value
I Have a cube with many measures. One of them use as "Aggregate Function" the function "Distinct Count". But some records of fact table has null value in the field of this measure. In SQL Server, when I execute a "SELECT COUNT (DISTINCT field)" command, it eliminates the Null value, as showed in the next exemple: Table X field A 1122 2345 4567 1122 1122 2345 null null When a execute the command: SELECT COUNT (DISTINCT A) FROM X I recieve "3" as result. But when I create a measure over the field A in a cube and I choose the aggregate funtion "Distinct Count", I recieve "4" as result. I think that Analysis Services is considering "null" as one of distinct values of the field A, but I don?t want that, because it makes no sense for me. Is there a way to eliminate the null value in Analysis Services, to show "3" as the result in the same way I receive in SQL Server? Thanks, Paulo I am new to SQL server, but experieced in Oracle. My top of mind test would be: select count(distinct *) from x => 4 select count(distinct a) from x => 3 It's standard SQL behaviour, I think. pls verify, tks When a execute the command "select count(distinct *) from x" , I recieve "4" as result and when a execute the command "select count(distinct a) from x", I recieve "3" as result. But my problem is not with SQL Server. My problem is in Analysis Services. I created a measure over the field "a", with the aggregate function "Distinct Count". But the result in Analysis Services is "4" and not "3", as I expected. Do you have a clue? Thanks, Paulo try to create a new cube (copy/paste the current one) then create a view on the database like: select * from mytable where mycolumntodcount IS NOT NULL use this view in the new cube. this cube will contain only the dcount measure and the null values are ignored. > When a execute the command "select count(distinct *) from x" , I recieve > "4" as result and when a execute the command "select count(distinct a) [quoted text clipped - 12 lines] > > *** Sent via Developersdex http://www.developersdex.com *** I tried this solution and It works. But I don?t want to create a new cube. That will be my last option. Is not possible to ignore the null vules with "distinct count" as the aggregate function? Is not there a propriety that I can configure? Thanks, Paulo Even if you didn't have the NULL challenge with Distinct Count, creating a separate cube for the Distinct Count is a best practice. There are aggregation and query performance benefits. The dimensional granularity to the distinct count cube (with the NOT NULL filter) would be identical to the other cube, so any queries against the virtual cube would not require any special calculations. Erik ---- Erik Veerman erik (at) solidqualitylearning.com > I tried this solution and It works. But I don4t want to create a new > cube. That will be my last option. Is not possible to ignore the null [quoted text clipped - 6 lines] > > *** Sent via Developersdex http://www.developersdex.com *** the only other option I can propose is: add a dimension in the cube, add a column in the fact table called "ToCount" which contains a Y/N or 0/1 value linked to this new dimension. case when MyColumn is null then 'N' else 'Y' end as ToCount hide the dimension. Rename the dcount measure to HiddenDcount, hide this measure create a calculated measure which is: (measures.HiddenDCount, MyDummyDimension.&[Y]) this ignore the null values. > I tried this solution and It works. But I don´t want to create a new > cube. That will be my last option. Is not possible to ignore the null [quoted text clipped - 6 lines] > > *** Sent via Developersdex http://www.developersdex.com *** Here another idea: Add a hidden measure "NULL_Exists" that's a 0 or 1 based on whether the distinct count column is NULL or not (Null=1). Use the MAX aggregate type (the result telling you whether there was a null or not across the dimensionality you are looking at) Use a calculated measure that = [DistinctCountMeasure] - [NULL_Exists] So, if there wasn't a NULL, the NULL_Exists returns a 0 and the distinct count isn't changed. If there was a NULL, then the result is the distinct count - 1. ---- Erik Veerman erik (at) solidqualitylearning.com > the only other option I can propose is: > add a dimension in the cube, add a column in the fact table called "ToCount" [quoted text clipped - 17 lines] > > > > *** Sent via Developersdex http://www.developersdex.com *** |
Reply to this Message |
 Sign In
 Join
 My Latest Posts My Monitored Threads My Blog My Photo Gallery My Profile My Homepage Start New Thread Enable EMail Alerts Rate this Thread
|
©2008 Advenet LLC Privacy Policy - Terms of Use
This website includes both content owned or controlled by Advenet as well as content owned or controlled by third parties.