How can I include NULLs in a PivotTable Count over SSAS?

Question

I have a view that joins orders to web tracking data which is being used as a fact table. I have lots of nulls because it takes a while for orders to obtain web tracking information. As you can see i have a total row count of 86432. However my measure count is showing 52, 753 (simple row count when you build a measure group). (Is using exactly the same view).

I believe my counts are going to be wrong due to the nulls in my data. How can I get SSAS to correctly count my null values? (I am limited to what I can do to the source database as I don't have access to change the core structure of the source system).

I understand what you are saying about counting a field vs all fields however as you can see by creating a new measure in SSAS you have the option of count of rows of a source table. This is the behaviour I would expect and I would expect the same count as SELECT * on the table as shown in my images...

Answer 1

I believe DimAd does not have a null or zero AdKey row. And I believe during processing you have to change the error configuration to have it discard or ignore any fact table rows where the foreign key is null.

My top recommendation is to change your fact table foreign keys to be not null. You will need to create a -1 key in each dimension and then use it in the fact table instead of null as described here.

If that's not feasible then add null or zero AdKey rows to any dimension where the fact table foreign key can be null. SSAS should convert the Bulls to zero so either should work. Then during processing those rows won't be dropped because they join fine. And you won't have to change the error configuration during processing.

If that's not feasible or acceptable then you can turn on the Unknown member on all dimensions which could be nullable. Then in the Dimension Usage tab set each relationship to fallback to the Unknown member. This process is described here.