How to load and realate fact able with multiple date keys ?

[#1]

Hi There

I have a fact table that has 3 date keys, order date, ship date, delivery date.

I can script everything ok for the first date key and alias to the TimeKey on the Time dimension.

However i am not sure what to do to get the same relationship with the other 2 date keys.

Normally in other OLAP tools i would create another date dimension copy and in dimension usage relate it to the correct key.

I tried to create multiple copies of the dimTime table by table alias but that did not work QV thinks it is a single table.

If i alias the TimeKey for another dateKey , I then get a warning that this was a loop relationship and the data model became loosely coupled and the data may be ambiguous. This makes sense as you have a fact with multiple times keys now.

So now i am unsure if i select a certain month for that fact which date key is being used to display values ?

I want to be able to on a sheet show facts ordered in certain months but delivered on others.

Bottom line: if a fact has multiple date keys , how do i script it correctly to load the data and relate to multiple dates dimensions, so that the fact values can be viewed by order date or delivery date correctly.

Thanx

[#2]

one way, should be to normalize the table in order to move from a schema like this:

Tab_dates:
LOAD * INLINE [
id_order, order_date, ship_date, delivery_date
1, 01/01/2008, 02/03/2008, 07/03/2008
2, 03/04/2008, 01/03/2009, 03/05/2009
3, 05/03/2009, 06/05/2009, 27/11/2009
];

to something like:

1	delivery_date	07/03/2008
1	order_date	01/01/2008
1	ship_date	02/03/2008
2	delivery_date	03/05/2009
2	order_date	03/04/2008
2	ship_date	01/03/2009
3	delivery_date	27/11/2009
3	order_date	05/03/2009
3	ship_date	06/05/2009

To produce such table you can use a simple script like:

Tab_dates_cross:
crosstable (date_type, date) load * resident Tab_dates;

At this point you could use one unique dimension table for time across the three different kind of dates you have.

For sure there are other approaches. This is one I came up in few mins.

[#3]

Hi

Thanx for the feedback, i need some time to play around with this. I am still not sure once you create the Tab_dates_cross table how you relate them to the date dimension correctly.

Could you elaborate on "At this point you could use one unique dimension table for time across the three different kind of dates you have."

I am used to something like :

SQL SELECT OrderDateKey AS TimeKey, ShipDateKey AS ShipTimeKey,ResellerKey ,SalesAmount
FROM Transaction.dbo.FactSales;

SQL SELECT TimeKey, DayNumberOfMonth, EnglishMonthName, CalendarQuarter, CalendarYear
FROM Transaction.dbo.DimTime;

Now the facts are related to time via the TimeKey alias column.

How do i do this once we have the cross table ? And what about all the other columns besides dates, must i change the entire fact table to a crosstable ?

Thanx

[#4]

Noproblem.

First point, with QV you have to think differently as you have done in the classic way with DWH and RDBMS.

Less you use SQL statements better you will use QV point of strenght.

It's not so obvious but it is easier than you could imagine.

I have attached a fiel I used to simulate your need; maybe is not fitting 100% you objective but I'm sure it can helps you to better focus your attention to the key element.

G

[#5]

Hi

Thank you, i agree 100% it is a different way of thinking , but i am just beginning so i appreciate the feedback. I will take a look at the file and post back.

Thanx again.

[johnw · Accepted solution]

I typically use one of two approaches.

1) The one gorombel presented. I use that approach when I only want the user looking at one date_type at a time, which I'll force by allowing one and only one date type to be selected.

As an example of that approach, one of my applications reports manufacturing yields - what percentage of produced items are not eventually scrapped. Items may not be produced and scrapped on the same day, or even the same month. I record two dates - produced date, and activity date. I have separate records for production and scrapping. A production record will have the same produced date and activity date. A scrap record may or may not have these the same.

Typically, the users are most interested in the activity date - what actually happened this month. They don't care how long ago the material was produced if they scrap it this month. And they don't care if the material may eventually be scrapped as long as it hasn't been yet this month. So I default to the "activity date" date type.

I also support a "produced date" date type. In some cases, they're trying to track down certain production problems. For instance, they might know they had a problem one week, and want to know how much of that material has since been scrapped. The "produced date" date type allows them to do this.

What this approach doesn't allow (without some extra work) is to filter the data set by both at the same time. That's fine for this particular application. I'm not sure it will be fine for yours.

2) Keep your date fields separate, and make separate calendars for each with distinctly-named fields. I'll use this approach when I want the user to be able to select more than one date at a time, such as if they want to see all material that was ordered in January and shipped in March. The previous approach doesn't directly support that.

So instead of having a "Month" field, you have "Order Month", "Ship Month" and "Delivery Month", which match up to your "Order Date", "Ship Date" and "Delivery Date".

[#7]

Hi John

2 seems to be more what i am looking for, i will try it out and get back to you, i think i tried something similar but had a loop issue. I will try again.

Thanx