Creating Proper Data Model

[Maverick999]

Hello,

I would like to know the right way to work with this data. I loaded 4 different tables, which all have a client ID field in common. What is the best data model to aim for and what types of data transformation should I do to achieve it?

Also, I am familiar with the concept of Master Calendar but how can I know if I should create a master calendar PER each date field ?

Thanks a lot.

[dplr-rn]

Ideally Always aim for a star schema as much as possible.

regarding the date field creating master calendar for each one depends on your requirement. but if you need to use canonical date concept along with it.

https://community.qlik.com/t5/Qlik-Design-Blog/Canonical-Date/ba-p/1463578

[Maverick999]

Let me update my post:
I have tried to create a star schema by concatenating by adding the « ID » keys of the different tables
I get the below schema. I also added a Master Calendar based on the date client created.

Is this schema correct?
Nevertheless, what I am trying to achieve is create 2 graphs:
- One where I would have The amount of unique clients who had spent between 1-100€, 100-300€ and +300€. For that I created a new dimension using if function in the table Order and I planned to use this dimension
- One where I can identify the clients that made an order less than 4 days after receiving a specific canal (email or sms)

[Arthur_Fong]

Hi Maverick,

After going through your second model, it seems that the  second model you shown are not the same in terms of table columns and keys if compared to the first model.

Please provide your qvw so that we can go through the data model for a better solution.

Thanks and regards,

Arthur Fong

[Maverick999]

Hello JK,

Please find attached the qvw and the data.

I put it back as the initial state, I would like to know what data modelling to do from this kind of data. Four tables linked by a key.

Thanks in advance

[Arthur_Fong]

Hi Maverick,

Attached is the qvw with my point of view for scripting this data model.

Your approach of creating a master calendar is good. However, you have to create linkage from the master calendar date towards all other table date.

I did this by creating a link table to link all 3 facts using client_id and date as key, as these are the common fields that can be found in the 3 fact tables. 

Giving the 3 dates a same name as the MasterCalendar's date (DateCal) will associate the link table with the MasterCalendar now.

With this, you may create selection panels for (Year,Month,Date) and the dates for the other 3 fact tables will also be reflected as well. 

Hope this helps in your development.

Thanks and regards,

Arthur Fong

[Maverick999]

Hello Arthur, thanks a lot for explaining me your approach and modifying the data model.

It looks like it makes great sense, however when I am trying to create a simple graph to show how many clients there were each year. I use in the expression: = count( DISTINCT id_client) and as a Dimension "Year".

But in the graph, the Year only starts at 2017 when I see in my data there there already clients (id_client) back in 2015 (client tab in excel).

So I am thinking I might be missing something related to the MasterCalendar isn't it?

[Arthur_Fong]

Hi Maverick,

Sorry that I didn't notice that your client table date is a timestamp based date.

So the formatting didn't match with the one in Link table, which causes all calendar fields that has association with client table not able to be linked.

Refer updated qvw with the updated script.

Thanks and regards,

Arthur Fong 

[Maverick999]

Thanks a ton Arthur!

I am struggling on how to use it properly since I am bit confused about which ID to use (KeyLink, Client_ID etc.).

For example, if I want to make a graph to show which are the campaigns that led to the greatest number of clients making an order less than 4 days after being exposed to the campaign.

The date when a client has been exposed to a campaign being the column "date" in CRM table and the date when a client made an order being the column "date_cmd" in the "achat" table (in the excel).

Thanks a lot for your help in advance.

[Arthur_Fong]

Glad that it helps.

Do mark the solution provided as correct for others reference.

Thanks and regards,

Arthur Fong