NullProcessing and Reference Dimensions

Have a Measure group in a fact table. This fact table links to the project dimension and the time dimension. In the project dimension there are some additional dates like ProjectStartDate and ProjectDueDate. So I create a new time dimension for these dates.

So for the Measure Group / Time (ProjectStartDate) combination, I define the dimension usage as a reference to the project dimension.

But when I process I get som errors on ProjectStartDate that date values (the NULL date) not exist time dimension (which is correct).

To solve this I try to set up the NullProcessing for these dimensions. But in contrast with a regular dimension there is no advanced section for reference dimensions.

Also the following don't solve the problem: setting NullProcessing on the ProjectStartDate attribute in the Project Dimension, setting NullProcessing on the Time dimension or setting NullProcessing on the project dimension.

Anyone know how to achieve NullProcessing:UnknownMember on a reference dimension ?

tx /Dirk

Try and solve this problem by defining Named Query in your DSV.

Replace your dimension table in DSV with Named Query that filters out NULL's.

Edward.
--
This posting is provided "AS IS" with no warranties, and confers no rights

|||

Filtering out the NULL's is not really an option since it would remove data from the cube.

I could fill in the NULL with dummy values (although for a time dimension this stays stange) but that's exactly what the NullProcessing should do. Put in a (black, zero, are UnknownMember) where there is a Null in the attribute or related key field.

For attibutes you specify it in the properties and this works fine.

For regular dimensions you specify this in the advanced options and works.

But what to do for a referenced dimension ? There is no such option, so I thought that specifying it in the referenced dimension might do the trick but it doesn't.

|||

You are right. In this case Analysis Server behaves differently.

But taking this aside. In general you are running into age old problem of having clean data and good referential integrity. Telling Analysis Server to hide some data that you have no keys for under Unknown member is not such a good practice. You will see your totals not summing up when try and sum child nodes manually. Strange things like that can create a perception with the user of Analysis Server showing incorrect data. You might be solving problem in short run by changing processing options, but in a longer run, you will be showing your users "inconsistent" data and that is in my opinion is quite dangerous

Edward.
--
This posting is provided "AS IS" with no warranties, and confers no rights.

|||

Thanks Edward,

Just thought that SSAS would make life a little simpler, Just have to get my ref-int scripts updated I guess. Still stupid that SSAS supports some cases but not all, which indeed makes it more or less useless.

/Dirk