Common Calendar with Different Fact Keys

[#1]

How do I create a Common Calendar with Different Fact Keys? (This post has been edited based on feedback in the below comments)

Fact Key	DateType
OrderID	OrderDate
OrderID	ShipDate
ConcessionID	ConcessionDate
CustomerServiceTicketID	TicketCreatedDate

Attached is an example of the tables I am working with - and the different fact keys they use.

Message was edited by: Andii Toole

[pljsoftware]

Hi Andii,

sorry but I don't understand your question.

Could you add an image of your schema tables? You do Left Join (Orders) but where is the code for Orders table?

Hint, when you concatenate data from different LOAD is a best practic to use CONCATENATE (DateLink).

Why do you create a table Key? If you use Left Join (Orders) you not need "Key:"

Regards

Luca Jonathan Panetta

[Mark_Little]

Hi,

It isn't a real answer, but it is certainly worth a read and considering, this is how i would normally approach multiple dates for calendars.

Canonical Date

Mark

[vincent_ardiet]

I don't understand what is your problem.

[Order] contains the [Key] field so it is joined to [DateLink].

[DateLink] contains the [Date] field and [CommonCalendar] also.

So everything is connected.

[manojkulkarni]

Refer below link

Canonical Date

The Master Calendar

[#6]

Hi everyone,

I guess I should start from the beginning - I am trying to create a Common Calendar, but in order to do so you need the same Fact Key connecting each piece of the data link.

However, I have a situation where I do not have the same Fact Keys. See below:

    Date                         Fact Key

OrderDate                     OrderID

ShipDate                    OrderItemID

ConcessionDate         ConcessionID

TicketCreatedDate         OrderID

What I was trying to do is create a common "Key" using the AutoNumberHash function, in order to combine all the Fact Keys - then use that to create my common calendar. However, it is not working - and I'm not sure why.

Can anyone help me with simply, start to finish, creating a Common Calendar with Different Fact Keys?

[swuehl]

Can you elaborate how your fact table structure looks like?

Best by posting some sample (potentially mock up) data records, or even better a sample QVW that can be reloaded.

[#8]

Hi, good idea! I re-wrote my original question and attached a mock-up of the kind of information I am using, along with a couple comments (in the script) to show what I'm looking at and where I am encountering problems. Any and all suggestions are welcome!

[MarcoWedel]

Hi,

maybe helpful as well:

Fact Table with Mixed Granularity

Generic keys

regards

Marco

[pljsoftware]

Hi Andii,

you need to create a custom Key for each table, I have add LOAD before LOAD * inline [...];

LOAD
  OrderID & '_' & OrderItemID & '_' & ConcessionID as Key
// ,ConcessionID
  ,ConcessionAmount
// ,OrderID
// ,OrderItemID
  ,ConcessionDate
;
LOAD * INLINE [
    ConcessionID, ConcessionAmount, OrderID, OrderItemID, ConcessionDate
    10, 5.00, 123, , 9/1/2015
    15, 5.00, , 5, 8/1/2015
    16, 50.00, , 6, 8/10/2015
];

In attach my solution for you.

Best Regards

Luca Jonathan Panetta