NullProcessing on Dimension keys

Running in to the following problem

Have a dimension (time dimension) with a datetime keyfield. Have a fact table with a date field that is sometimes not filled in. A regular relationship exists in the dimension usage tab between this time dimension and the measure group of the fact table based on the date field.

So I set up the time dimension with dimesion property UnknownMember visible, set the NullProcessing property of the keyColumn of the dimension keyfield (you're still folowing ;-) to UnknownMember because I want to avoid the default beheaviour of SSAS making it 0 or blank.

Deploy the thing and get the following 2 warnings:

Warning 1 Errors in the OLAP storage engine: The attribute key cannot be found: Table: dbo_ProjectCard, Column: ProjectDateSold, Value: 0:00:00. 0 0
Warning 2 Errors in the OLAP storage engine: The attribute key was converted to an unknown member because the attribute key was not found. Attribute Time of Dimension: Project Sold Date from Database: CDWH OLAP database, Cube: CDWH, Measure Group: Project Card, Partition: Project Card, Record: 8. 0 0

Both of them seem perfectly normal (Notice that the value of warning 1 represents the NULL date) and that warning 2 actualy tells me that it was converted to an unknown member, but then I get 3 errors

Error 3 Errors in the OLAP storage engine: The process operation ended because the number of errors encountered during processing reached the defined limit of allowable errors for the operation. 0 0
Error 4 Errors in the OLAP storage engine: An error occurred while processing the 'Project Card' partition of the 'Project Card' measure group for the 'CDWH' cube from the CDWH OLAP database database. 0 0
Error 5 Errors in the OLAP storage engine: The process operation ended because the number of errors encountered during processing reached the defined limit of allowable errors for the operation. 0 0

So it seems that's no good, This works perfect for dimension attributes but what am I missing here ?

Tx /Dirk

Found the solution

In the dimension usage tab, when you define the relationship. You can click the advanced button. In this dialog you can also modify the NullProcessing. So you need to set it here to UnknowMember instead of keyColumn of the dimension itself.

Wished MS would make it more consistent.

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