How do I connect fact & dimension table

[#1]

Hi,

I have attached the view of a data model. I want to learn how would I relate CREATED_DATE field of Opportunity table to

DW_LOAD_DATE field in Time table.

My requirement is I want to pick a fiscal year & fiscal month or (QTR_DESC- Quarter Description) and find all the items that has been created after the selected date range.

Do i need to map or create a new time dimension. I am really out of clue.

I have been trying for a solution to this problem, but seems luck went for a shopping tour.

Thanks,

Bikash

[#2]

It looks like your DW_LOAD_DATE is already tied to something in your Opportunity table? Did you do something like Load CREATED_DATE as DW_LOAD_DATE in your script? If so they should tie together just fine provided they are in the same date format.

[#3]

Thanks Leonard for the suggestion, but they are in different date format.

CREATED_DATE is in Julian Date format and DW_LOAD_DATE is in normal (MM/DD/YYYY) format.

Thanks

Bikash

[#4]

So do Load date(CREATED_DATE, 'MM.DD.YYYY') as DW_LOAD_DATE to convert it to match the format of DW_LOAD_DATE.

[#5]

Should it be 'MM.DD.YYYY' or 'MM/DD/YYYY'?

Just to make sure.

Thanks again Leonard,

Regards,

Bikash

[#6]

To be 100% certain I would suggest you do both date fields and rename them so you don't lose any timestamps that may exist.

Load date(CREATED_DATE, 'MM.DD.YY') as date_key

&

Load date(DW_LOAD_DATE, 'MM.DD.YY') as date_key

[#7]

Hi Leonard,

Yesterday I was able to create the time dimension.

I have attached a view of the Data Model, but now it seems the OPP_ITEM(Item Number) is no more

related to these new date field.

And I am clueless yet again and don't know what should be the next step.
Please help.

Regards,

Bikash

[#8]

The new date field is CreateTime.

[#9]

Bikash,

The OPP_ITEM field is tied to every other field within the same row in your Opportunity table. Thus it is tied to your Time table. Any date you select in the Time table will automatically limit all items in your Opportunity table to only those dates that would apply based on both your selections and the DW_LOAD_TIME.

Now, from what I can see in the model, you have 8 separate time fields at the least in your Opportunity table. Is there a business need to have just as many time tables to look at Prod, Stage, Proto, Create dates and all the various iterations of their quarters, years, etc...? If you have a listbox with the years displayed in your app, for users to see what happened in 2010, which date field would that reflect?

I suspect you are going to want to approach your dates a bit differently. I would determine which of the dates in the Opportunity table is the most significant and leave only that date in the table linked to the Time table, just like you currently have with DW_LOAD_TIME. Having not seen the data, I can only guess at what else you would want to do next.

Could you post a sample of your app by chance? Also, do a search here on the community for a Master Calendar script as I suspect you are going to want one with all these various dates.