date design issue in my cube

I have a simple fact_service table: service_type, start_date, end_date, service_charge. If the service is not done yet, the end_date is null. I can't process the cube because I don't have a null record in my dim_date dimension.

I tried to create a vew v_fact_service_by_end_date, that processes the cube but it doesn't give me what I needed. I would end up with one time dim and two measures: charge_amt and charge_amt_from_view.

I need to have two time_dim (roll play) and one measure (charge amt) so that I can see the start_date and end_date of each service_type (to determine how long it takes to perform the service).

How should I model this? Thanks for any input.

This is a relatively common issue. You have what we refer to as an accumulating snapshot fact table wherein some dimensions are not populated at the time a transaction is first loaded. One question, do you have a transaction identifier? Is that the Service_Type reference?

Regarding the date, is this a date time field or an integer pointer to a Date dimension table? If you had a Date dimension table such as below, you could simply add a NULL date and set a reference to that record (DateID = -1):

Code Snippet

create table DateDimension (

DateID int not null identity(1,1),

Date datetime null,

....

)

alter table DateDimension add

constraint PK_DateDimension primary key (dateid),

constraint AK_DateDimension unique (date)

go

set identity_insert DateDimension ON

insert into DateDimension (DateID, Date) values(-1, NULL)

set identity_insert DateDimension OFF

go

If you aren't using a Date dimension table, your other option is to use the UNKNOWN MEMBER functionality in SSAS. You have to configure an unknown member in the dimension and also configure how you want your cube to handle missing references. Check out Books Online topic "Defining the Unknown Member and Null Processing Properties" for more details.

Hope that helps,
Bryan

|||That worked! I added a dummy record of "-1" in the time_dim. Thanks much.