Fact to dimension relationship

system · December 4, 2009, 8:53am

Hi,

I am working with BO designer XI R2.
Initially my fact table consisted of date fields. Now , the fact table is supposed to contain only the Date Key fields and no more date fields. So in order to retrieve the date fields I am to link the corresponding key in the fact table to the key in the date dimension table. The date dimension table will now contain the date field.

My fact table is linked to a dimension table which had a complex join like this.

Fact_Table. ID= Dimension_Table.ID and Fact_table. Date >= Dimension_Table. Start date and Fact_table. Date <= Dimension_Table. End date
Now the Date fields exists no more in the fact table.

The link is something like this now:

Fact_Table. ID=Dimension_Table. ID

and

Fact_table. Date_Key=Date_table.Date_Key

There is no link between the Date_Table and the Dimension table.
Please see the attachment to get an idea of the scenario.
So how do I linkt o produce the same complex join mentioned above?

[/img]
Date_Field.doc (25.0 KB)

UmaRamachandran (BOB member since 2009-08-17)

MarkP · December 4, 2009, 9:04am

If you are in a true data warehouse scenario, you shouldn’t need the date field.

Do you have surrogate keys in your dimension table?
If so, and assuming(!) that the ETL has been written properly, then the fact table should be linked to the correct version of the dimension for the time at which the fact took place. That’s the whole point of surrogate keys in a slowly changing dimension scenario.

system · December 4, 2009, 10:12am

OK. I checked with the dimension table and it contains only the PK as the ID and no surrogate keys.

So in that case what has to be done. what should be the surrogate key?

UmaRamachandran (BOB member since 2009-08-17)

MarkP · December 4, 2009, 10:14am

Is it a data warehouse?

If so, there may be work to do.
What type of database is it?

system · December 4, 2009, 10:18am

Hmmm… I am not sure how to answer this .

But I will give you the bits and pieces of info that I have.

It is a star schema and the DB is DB2.

UmaRamachandran (BOB member since 2009-08-17)

MarkP · December 4, 2009, 10:26am

So star schema means it is a reporting database of sorts, whether that be a data mart, data warehouse, whatever.

In the dimension table, what is the primary key?
Is it ID combined with Start Date?

Are you able to post the create script for the table up here? I’ll understand from company confidentiality if you aren’t. If you can’t, you could email it to me and I’ll post a cut down/anonymised version on here to explain what’s going on.

system · December 4, 2009, 10:30am

No, there os only one PK. That is the ID. and it has no combinations or composite keys.

And are you requesting for the table structure of the Dimension table? I have the only table structure with me

UmaRamachandran (BOB member since 2009-08-17)

MarkP · December 4, 2009, 11:06am

So the ID is unique? If the ID is unique and that is what joins to the fact table then you are fine.

system · December 4, 2009, 11:22am

I am not able to understand how the scenarion of the complax join will be implemented.

Becuase the linking is like this

Fact_Table. ID=Dimension_Table. ID

and

Fact_table. Date_Key=Date_table.Date_Key

There is no link between the Date_Table and the Dimension table.

So how do I check the condition for the :

Fact_table. Date >= Dimension_Table. Start date and Fact_table. Date <= Dimension_Table. End date

I am not able to get that

UmaRamachandran (BOB member since 2009-08-17)

MarkP · December 4, 2009, 11:31am

The datekey joins from the date table to the fact table.
That tells you WHEN the fact occured.

The ID joins from the dimension table to the fact table.
That tells you WHICH ID the fact happened to.

If ID is unique, you don’t need date - it was overkill.
If ID is NOT unique, your database has been badly designed but it can be repaired.

system · December 4, 2009, 12:02pm

Hmmm… I am able to get that the date key helps to find out when it occured

Ummm… in that case let me put the condition something like this.

Suppose I am to check the whether the ID falls between the start date and the end date and the fields are checked against the create date.

The start date and the end date are present in the Dimension table and the Create date is present in the Date table .

So I am trying to restrict the data that comes out for the ID based on the When condition.

Then how do I achieve that?

UmaRamachandran (BOB member since 2009-08-17)

MarkP · December 4, 2009, 12:09pm

Create two aliases of your data dimension.

Join alias one to the fact table on datekey = datekey

Join alias two to the dimension table on aliastwo.date between dim.start_date and dim.end_date

Derive your objects accordingly.

system · December 4, 2009, 12:35pm

That will not be so simple.

I will tell you why.

The second alias of the Date table will be dimension table based on what link? There is no link between the dimension and the date table.

second in case I link and try getting the where clause something like this

Date_Table.Date>= Dimension_table. Start_Date and Date_Table.Date<=Dimension_table. End_Date

The Date_table.Date contains all the date and not the specific Create date that I want.

My condition should be something like this:

Date_Table.CreateDate>= Dimension_table. Start_Date and Date_Table.CreateDate<=Dimension_table. End_Date

How do I get the Create Date?

Again by linking the fact.Key and date.key

So , we come back to the same point where we started. How to go abut this? Is there any modification that there has to be in the table structure that will help achieve this?
Or is there something else that can be done

UmaRamachandran (BOB member since 2009-08-17)

MarkP · December 4, 2009, 1:33pm

UmaRamachandran:

That will not be so simple.

I will tell you why.

The second alias of the Date table will be dimension table based on what link? There is no link between the dimension and the date table.

second in case I link and try getting the where clause something like this

Date_Table.Date>= Dimension_table. Start_Date and Date_Table.Date<=Dimension_table. End_Date

The Date_table.Date contains all the date and not the specific Create date that I want.

My condition should be something like this:

Date_Table.CreateDate>= Dimension_table. Start_Date and Date_Table.CreateDate<=Dimension_table. End_Date

How do I get the Create Date?

Again by linking the fact.Key and date.key

So , we come back to the same point where we started. How to go abut this? Is there any modification that there has to be in the table structure that will help achieve this?
Or is there something else that can be done

Where do you derive the Create Date now from then?
Is it from the fact table?

Date_Table.Date>= Dimension_table. Start_Date and Date_Table.Date<=Dimension_table. End_Date
is not going to give you a create date, it’s giving you a range of dates.

You’re not being clear on what you are trying to achieve either that or you’ve not understood what I’ve written.

system · December 7, 2009, 6:21am

OK . I think I have not properly elaborated on what my reqt. is

Ummm…

Suppose I have to derive the create date then the linking is something like this:

Fact_Table. Create_ID= Dimension_Table.ID and

The Date fields exists no more in the fact table. and has to be taken from the Date table

The link between the fact and the date table is:

Fact_table.Create_Date_Key=Date_table.Date_Key , The Date_table contains a field called as date --> which contains the date

The link is something like this now:

Fact_Table.Create_ID=Dimension_Table. ID

and

Fact_table. Create_Date_Key=Date_table.Date_Key , the date_table in turn containing the Date field

There is no link between the Date_Table and the Dimension table.

Now suppose I am to derive the Closed date then the link is something like:

Fact_Table.Closed_ID=Dimension_Table.ID
and

Fact_Table.Closed_Date_Key= Date_Table.Date_Key

So will your solution provide a solution to this?

Let me know in case I am missing upon something or you are not able to understand something

UmaRamachandran (BOB member since 2009-08-17)

MarkP · December 7, 2009, 10:37am

Fact_table. Create_Date_Key=Date_table.Date_Key , the date_table in turn containing the Date field

If that stands, then your Calendar Date in Date_Table is now your Create Date. This is because you’ve joined the fact table to the date_table on Create Date Key.

system · December 9, 2009, 6:04am

Yes and that I have mentioned clearly in my statement :

Fact_table.Create_Date_Key=Date_table.Date_Key , The Date_table contains a field called as date --> which contains the date

which is the create date.

UmaRamachandran (BOB member since 2009-08-17)

MarkP · December 9, 2009, 10:22am

So what’s the problem?

system · December 9, 2009, 11:50am

My fact table is linked to a dimension table which had a complex join like this.

Fact_Table. ID= Dimension_Table.ID and Fact_table. Date >= Dimension_Table. Start date and Fact_table. Date <= Dimension_Table. End date
Now the Date fields exists no more in the fact table.

The link is something like this now:

Fact_Table. ID=Dimension_Table. ID

and

Fact_table. Date_Key=Date_table.Date_Key

There is no link between the Date_Table and the Dimension table.
Please see the attachment in my first post to get an idea.
So how do I linkt o produce the same complex join mentioned above?

As you said in the previuos post that create an alias table for date and then link it to dimension
something like :

Fact_Table>—Dimension_table------Alias of Date_table work?

The link between the Dimension_table and table is like:

Date_table.Date >= Dim_table.Start date
and Date_table.Date <= Dim_table.End date

Right?

UmaRamachandran (BOB member since 2009-08-17)