Null Problem

Null Problem

• Abdul Wasie

  In my case, i have a unique id field, is the field that i using to count...

  My original problem is this:

  I have a fk in the fact table to a dimension, this fk can be null, i have other dimensions too, the problem is: in the fact table i have only the rows that this fk field is not null, and i want all the rows... I need all the rows becaues i want to examine the data using the other dimensions too..

  
  
  

• Daniel McElhinney

  I am in analysis services 2000....

  I don't find how to set a measure usage to "Count of rows"..

  The solution you gave is fot AS2000

  
  
  

• SLMOFLZ

  I have to create a calculated member, or i change the source column of the measure ( i have no idea how to make the that sql expression :( , i was trying but i can't do it)
  
  

• dseifert

  Create a count measure with a row binding instead of a column binding and it will count each row in the table regardless of it's contents. In the Cube Editor, click on the New Measure toolbar button (or menu item) and then in the New Measure dialog set the Usage to "Count of Rows" and then set the table to your Services table.
  
  

• Mac Whore

  In AS2000 you can actually embed a SQL expression in the SourceColumn property for a measure. The easiest way to come up with the working SQL expression is just to write a select query on the table which uses an expression to create a calculated column. If the expression works in the select, then it will usually work in the SourceColumn property. You also have the option of adding such a calculated column to a view in the source database and then building your cube over that view.
  
  

• JonathanMA

  The idea is simply to identify a candidate key - a set of columns that uniquely identifies each row - and concatinate these columns into a single column value. In the worst case scenario where all columns are independent this could mean concatinating all columns in the table. If this is the case, you'll probably want to go back and add an identity (a.k.a. autoincrement) column to the table to serve as a key. As an example on the SQL Server sysobjects table, you could concatinate the name, id, and xtype columns into a single column using the following query (although this is not necessary for sysobjects since name is already unique and non-nullable):

  select name + '|' + Convert( varchar, id) + '|' + xtype from sysobjects

  
  
  

• JCakeC

  Yes, i know how to embed sql in a source column, and also i use viewsin the cube, the thing i did'nt understand is the sql expression, i don't catch the idea, in other words i don't understand how to make this sql expression to consider all the rows...
  
  

• billwhelop

  Ok, now I understand. To have counts for the rows with null FK values included in the cube, you must associate members in the dimension the FK refers to since ever cell must have a co-ordinate in each dimension. The easiest way to do this is to add a new "unknown member" to the dimension table (can be done with a SQL view using a Union) and then replacing null values in the fact table FK column with references to the new "unknown member". The null replacement in the FK column can be done with a SQL expression either in a view or the source column property.

  
  
  

• Nchantim

  My answer was for Analysis Services 2005. For AS2000, you'll need to create a SQL expression in the SourceColumn that returns a distinct, non-null value for each row in the table. If the table has a key, use the columns that make up the key. You can concatinate them in a SQL expression and some delimeter that you know is not contained in the columns.
  
  
• Null Problem