Data Model with 2 fact tables

[linoyel]

Hi,

I'm thinking what's the best way to build a data model.

The thing is that I have 2 fact tables in it which is not right to concatenate (the model is attached).

The one has information about Customers (Leads table) and the second holds information about Customer Payments (LeadSalesPayments table).

They are linked by unique_num.

I have to build 2 kind of reports:

1. Regarding Payments

2. Regarding Leads

I've started with Payments reports and using the attached Data Model it works perfect.

In this model Leads table is kind of a dimension table for Payments...

But now I need to build Leads reports and take the info from Leads table.

The problem is that it's linked to other dimensions like Agents through LeadSalesPayments table, and it won't be right.

For example, In Leads table I have lead_agent_id and I need to get his name. But I can't link Leads table directly to Agents table because it's already linked through LeadSalesPayments table. Not all unique_nums in Leads appear also in LeadSalePayments table, so I'll loose data... another example is dates: my Calendar is linked to Payments table. But in Leads table there is createDate field of the lead and I need it to be linked to the Calendar for Leads Reports...

Of course, the best thing here, I guess, is to build 2 different docs, one for Leads and one for Sales but I still wonder if it's possible to build it in the same document.

Please advise...

[giakoum]

I definitely recommend you to concatenate the 2 fact tables.

All these dimensions tables you have in your model can also disappear but simply using apply map and moving the resulting field to the facts table (provided there is not a many to many relationship).

QlikView is not performing well with any other data model type than a star schema.

Please see attached document for more info.

[linoyel]

OK, but if I concatenate them how I'll know which uniques are coming from Leads and which uniques are coming from Payments?

[giakoum]

Either by using a count on a field that is not present in both tables or by creating such a field like :

load

....

,'Leads' as Origin

.....

concatenate

load

......

,'Payments' as Origin

.....

[linoyel]

OK

And what about dates?

Payments table is connected to calendar.

But in Leads table there's also a date (createDate field) which also needs to be connected to calendar...

For example, if user will request to know how many leads  (uniques) were on a certain day...

[giakoum]

Then you need to create 2 different calendars.

See attached a nice example file from Rob Wunderlich.

[linoyel]

Thank you Ioannis.

I'll try what you've suggested

[giakoum]

Good luck!

[linoyel]

Hi Ioannis,

I've tried to concatenate the 2 tables as you've suggested but I didn't work for me since the concatenation just adds all the rows of one table to another. I loose the chance to get to the dimensions such as ClientsCountries, Substatuses etc. All these get NULL in Payments table.

I can load Leads table twice from the DB - once for Concatenation with Sales, and once for the connection to the dimensions by unique_num. But Leads table is huge, and I'm sure there's another way...

Please advise...

[denwo2010]

Hi, When you concatenate the two fact tables together make sure you have the same field names in both fact tables, even if you don't have a field name that is in one fact table but do in the other when you are creating your qvd just have a empty string '' or a numeric value 0 if it is a numeric field like something below example:

LeadsSalesPayment:

Load *;

SQL Select

     'LeadSales' as RecordType,

     unique_num,

     InvoiceNumber_key,

     UserBonusesID,

     ShiftHoursID,

     '' as category_id

From TableLeadSales;

Store LeadsSalesPayment into c:\Temp\LeadsSalesPayment.qvd (QVD);

DROP TABLE LeadsSalesPayment;

Leads:

Load *;

SQL Select

     'Lead' as RecordType,

     unique_num,

     0 as InvoiceNumber_key,

     0 as UserBonusesID,

     0 as ShiftHoursID,

     category_id

From TableLeads;

STORE Leads into c:\Temp\Leads.qvd (QVD);

DROP TABLE Leads;

This means when you concatenate the two fact tables into your QVW Document it will optimize the load and will make the performance quicker to load in.

Fact:

Load

     RecordType,

     unique_num,

     InvoiceNumber_key,

     UserBonusesID,

     ShiftHoursID,

     category_id

FROM

     c:\Temp\LeadsSalesPayment.qvd (QVD);

CONCATENATE

Load

     RecordType,

     unique_num,

     InvoiceNumber_key,

     UserBonusesID,

     ShiftHoursID,

     category_id

FROM

     c:\Temp\Leads.qvd (QVD);

Is your calendar going to be using the same date from the two fact tables?