Distinct Count Measure on Varchar Field

• scottcabral

  Default port

  Points: 1495

  More actions

  December 6, 2012 at 2:05 pm

  #285915

  I need to create a Distinct count measure on a varchar field that exists in my fact table. the field is actually a Degenerate Dimension called Claim_Num.

  When i try to create the distinct count measure on this field, it gives me the following error:

  "SSAS distinct count measures requires numeric data type"

  Is there a workaround for this? Possibly using MDX calculation?

  thanks

  Scott

• robert.gerald.taylor

  SSChampion

  Points: 13133

  More actions

  December 7, 2012 at 6:04 am

  #1566582

  scottcabral (12/6/2012)

  ---

  I need to create a Distinct count measure on a varchar field that exists in my fact table. the field is actually a Degenerate Dimension called Claim_Num.

  When i try to create the distinct count measure on this field, it gives me the following error:

  "SSAS distinct count measures requires numeric data type"

  Is there a workaround for this? Possibly using MDX calculation?

  A quick search finds:

  1. You could turn your strings into numbers by loading the distinct values into a table with an identity column. Then join this table with the fact table as the fact (view) for your cube. The identity numbers can be used for the distinct count column.

  2. The second option is to create a dimension of the character strings, and create a calculated member to dynamically count the distinct values at query time. As outlined in http://richardlees.blogspot.com/2008/10/alternative-to-physical-distinct-count.html. Some people have an initial reaction that they couldn't possibly have a dimension with so many members. However, the cost of the dimension is likely to be less (in cube size) than the distinct count measure. This is because the distinct count measure will have the numbers repeated many times in the cells that have that number. Whereas the dimension will only have the number (or could be string) stored once, albeit with more granularity in the cube.

  http://social.msdn.microsoft.com/Forums/en-US/sqlanalysisservices/thread/f5432fcd-fa7e-4e52-b3a7-a3dce1da4690

  HTH,

  Rob