MyTable, ServerTimeDimension, Null-Values

Hello experts,

I’ve got one Problem and four solutions, but none is a good one =(

My entire problem:

I’ve got a Dimension with a column Production_Date. I would like to relate it with a ServerTimeDimension. I created a ServerTimeDimension and related about the Dimension Usage it to my Dimension. In the Production_Date column are some Null values and now I’ve got some Problems.

My four solutions:

1. to alter my dimension through one query and take just this rows with correct date à not good, because I don’t want to lose some Information
2. to create a new named Calculation and alter all Null values through a very old date. Something like this:
   case when (INS_PRODUCTION_DATE is null)
   then '01.01.1969'
   else (CONVERT(VARCHAR(10), INS_PRODUCTION_DATE, 104))
   end à there are some wrong information now, that’s not so good too
3. make a time dimension one this column à not so good, because I can’t make a hierarchy right now
4. and to work with the ErrorConfiguration and KeyErrorLimit is the worst solution

Have somebody another good idea?

Best regards

Tschi2001

As a best practice, it is recommended you implement a date dimension table in your data warehouse and then add foreign key references to your fact table. The NULL date can be represented easily with this approach.

If that is not an option here, what is wrong with the UnknownMember/ErrorConfiguration option (Option 4)? This type of situation is just what that is for.

B.

|||

Hello Bryan,

You are right, that’s not a option here.

In my Opinion a correct cube have to work without use ErrorConfiguration.

And the Option UnknownMember doesn’t work with my cube or I make something wrong.

The way I try to solve this problem with UnknownMember:

- I take the Production Time Dimension (it’s my ServerTimeDimension)

- Went to Dimension Structure

- Look at the Date-Properties (it’s the Key of this Dimension)

- Went to KeyColumns

- Set NullProcessing to “ZeroOrBlank”

- Try to deploy

Came the old failure…

Made I something wrong or could it be that I misunderstand something?

Best regards

Alexander

|||

So, the first thing you need to do is configure the UnknownMember in your time dimension. To do this, set the UnknownMember property on the dimension to either Hidden of Visible. Then, open the cube and move to the Cube Structure tab and select each measure group referencing the time dimension. Set up the ErrorConfiguration as follows:

NullKeyConvertedToUnknown = IgnoreError

KeyErrorLimitAction = Stop Logging

Try that and see if that fixes the problem for you.

B.

|||

Thank you very much, it works very good !!!!