Data modelling with several NON-linked Dates

[pauljohansson]

Hi,

In many cases people want to link their dates to one MasterDate calender. In my case however, requirement need date fields to be seperated. How is best practice to data model in this case?

Currently I have 3 fact tables and one dimension;

FactTable 1 (PaymentTransaction)

CustomerKEY | PaymentValue |  PaymentType | PaymentDate

FactTable 2 (GameplayTransaction)

CustomerKEY | GamePlayValue | GamePlayType | Provider | GamePlayDate

FactTable 3 (LoginTransactions)

CustomerKEY | LoginValue | LoginDate

Dimension 1

CustomerKEY | Customerattribute1 etc..

I have chosen to seperate the facttables because the business requirements want to be able to search for seperate date intervals in the respective facttables.

For example; user needs to be able se which customers logged in in august (Facttable 3) and how these customers paymentactivity was in september (FactTable 1 ). Consequently, the date fields should NOT relate to each other.

According to best practices we should strive for ONE fact table, however I dont see how i can merge my fact tables because of the above mentioned requirement. Am I right?

I have two questions:

#Have you seen any datamodel with similiar requirement on seperate date calenders? If so, any guidance or input is much welcome.

#Do you have any immediate recommendation to merge the fact tables into one or any other tip that is expected to improve performance

Thanks in advance,

br

Paul

[Anonymous]

Paul

It is hard to give a definitive solution without seeing your actual data but how about you try something like:

• Do a MAPPING LOAD on your table [Dimension 1]
• Concatenate your three FactTables into a single fact table using an APPLYMAP to bring the Customerattribute1's

So you will now have a single table which contains your 3 dates :

• PaymentDate
• GamePlayDate
• LoginDate

As you mentioned above attempting to join these 3 dates into a single Calendar table would be a most silly to do.

Depending on what your dashboard visualisation requirements are you could one of the options below

1. Just do selections directly against your 3 dates
2. Add in your script extra date dimensions such as <Fact>Year, <Fact>Month .................
3. Or created 3 independant Calendar for each of the 3 dates

Creating Date Islands could be another option, but I do not like Date Islands as they tend to then require multiple and oft confusing set analysis expressions.

Best Regards,   Bill

[swuehl]

I think this thread should give you some answers:

http://community.qlik.com/thread/66717

Performance will depend on what you need to analyze, i.e. which fields you need to use in any single aggregation functions. Using fields from different tables is less performant than using fields from the same single table.

[Jason_Michaelides]

Hi Paul,

You may want to approach this a different way. You understand the best practice to create a single fact table - great. How about you do that and have a single master calendar. Then solve your users' requirement using standard QV functionality but you can assist by creating some buttons:

Step 1: user selects FactType = login

Step 2: user selects interested dates. This will create a list of IMPLICIT customers.

Step 3: user turns this implicit selection into an EXPLICIT selection by using "select possible"

Step 4: user clears date and changes FactType selection

You could combine steps 3 and 4 into a single button with actions if you want to spoon-feed a little more.

Step 5: user makes new date selection etc - these are now only made on the customers from step 2.

The best practice method of a single fact table allows for very detailed analysis if your users only know how to do it.

Training! 😉

Hope this helps,

Jason

[pauljohansson]

Hi Bill,

Thanks for you reply. I dont fully understand what you mean by concatenating the fact tables using the applymap.

Do you have any link or example i can look at?

br

Paul

[sebastiandperei]

Hi Paul, in my experience, the best way to know the best data model is... TRYing!

I will add another way to try. I guess that your schema isn't bad. So, you can try to making a "Date Combination" table.

For example:

dates:

Load Distinct

     key_customer,

     payment_date

Resident Payment;

Left Join

Load Distinct

     key_customer,

     gameplay_date

Resident Gameplay;

Left Join

Load Distinct

     key_customer,

     login_date

Resident Login;

Left Join

Load Distinct

     login_date,

     gameplay_date,

     payment_date,

     Autonumber(

          login_date&'-'&

          gameplay_date&'-'&

          payment_date)               as key_date

Resident dates;

Now, join key_date to each fact. In this table, you can make common assotiations between these dates. For example, making a flag to identify who was logged in a month and how was the payment in the next month:

Left Join dates

Load Distinct

     login_date,

     payment_date,

     key_customer,

     if (MonthName(payment_date)=Monthname(login_date,-1),1)     as _next_month

Resident dates;

It was an example, but you could do anything, in addition to create a descriptive fields of month, year, etc... for each date.

[preminqlik]

Final_Fact_Table:

load

CustomerKEY ,

PaymentValue ,

PaymentType ,

PaymentDate,

num(PaymentDate)               as               %CommonCalendarlink

resident PaymentTransaction;

drop table PaymentTransaction;

Concatenate(Final_Fact_Table)

load

CustomerKEY ,

GamePlayValue ,

GamePlayType ,

Provider ,

GamePlayDate,

num(GamePlayDate)                    as          %CommonCalendarlink

resident GameplayTransaction;

drop table GameplayTransaction;

Concatenate(Final_Fact_Table)

CustomerKEY ,

LoginValue ,

LoginDate,

num(LoginDate)               as                         %CommonCalendarlink

resident LoginTransactions;

drop table LoginTransactions;

left join(Final_Fact_Table)

load

CustomerKEY ,

Customerattribute1

resident Dimension1;

drop table Dimension1;

So, now create Master Calendar and link with %CommonCalendarlink, now calendar works for all fact table perfectly..

hope this helps you ...!

-Regards

Premhas

[pauljohansson]

Hi Jason,

thanks for the reply!

This was a interesting way to approach the problem. I will definitly give it a shoot and evalute performance and usability,

br

Paul

[pauljohansson]

HI Premhas,

thanks for taking time and coming back with a script reply!

However, associating the dates to a common date link are of no interest in my case if you read it carefully.

My question is the opposite actually. What is best practice when requirements are that seperated date intervals should be able to be specified without affecting each other,

If i concatenate my seperated fact tables as in your example. The result will be as soon as i select for example a specific paymentdate only that specific date for LoginDate and Gameplay date.

Maybe in my case, the fact tables simply needs to be seperated.

thanks,

Paul