topic Re: Data Model with 2 fact tables in QlikView

Data Model with 2 fact tables

linoyel — Thu, 21 Aug 2014 13:49:54 GMT

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...

Re: Data Model with 2 fact tables

giakoum — Thu, 21 Aug 2014 13:55:30 GMT

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.

Re: Data Model with 2 fact tables

linoyel — Thu, 21 Aug 2014 13:58:38 GMT

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

Re: Data Model with 2 fact tables

giakoum — Thu, 21 Aug 2014 14:02:04 GMT

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

.....

Re: Data Model with 2 fact tables

linoyel — Thu, 21 Aug 2014 14:05:34 GMT

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...

Re: Data Model with 2 fact tables

giakoum — Thu, 21 Aug 2014 14:09:46 GMT

Then you need to create 2 different calendars.

See attached a nice example file from Rob Wunderlich.

Re: Data Model with 2 fact tables

linoyel — Thu, 21 Aug 2014 14:12:15 GMT

Thank you Ioannis.

I'll try what you've suggested

Re: Data Model with 2 fact tables

giakoum — Thu, 21 Aug 2014 14:14:55 GMT

Good luck!

Re: Data Model with 2 fact tables

linoyel — Sun, 24 Aug 2014 09:00:16 GMT

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...

Re: Data Model with 2 fact tables

denwo2010 — Sun, 24 Aug 2014 21:07:42 GMT

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?

Re: Data Model with 2 fact tables

linoyel — Wed, 27 Aug 2014 12:12:39 GMT

Hey Denis,

I've concatenated the 2 tables (by the way, there's no need to have the same fields, Qlikview just puts NULL in the field that exists in one table and not another).

In addition to that, I've connected the concatenated table to one of these 2 tables - Leads table (which means I basically loaded one of the tables twice) - in order to get dimension values for the concatenated table.

Yes, the calendar is the same.

In my reports I show measures for different fields for Sales and for Leads so QV knows to take the right rows.

For example, sum(income) will show the right result because only the rows from the Sales table in concatenated table have values in the field Income.

In Leads Report, if I want to show count(leads) I use Set Analysis to get the unique_nums only from the rows which came from Leads table.

Re: Data Model with 2 fact tables

denwo2010 — Wed, 27 Aug 2014 22:38:17 GMT

Hi,

I would suggest having the two fact tables concatenated together, you need to have the same fields in both for the optimized to work which makes the load time much faster.

For the Income Value, you would then use the set analysis expression to only show the fact table you want the value taken from, something like the below:

=Sum({$<[RecordType] = {'Lead'}>} [Income])

Thanks

Re: Data Model with 2 fact tables

linoyel — Thu, 28 Aug 2014 06:25:53 GMT

1. I'll try the optimized table to see if it will work faster.

2. The income is NULL anyway where Record Type = Lead, so when I do Sum(income) it would only summarize income that comes from Record Type = Sales... would you still suggest to add $<[RecordType] = {'Sales'} in order to make it work faster, or it doesn't really matter?

Re: Data Model with 2 fact tables

denwo2010 — Sat, 30 Aug 2014 19:56:27 GMT

Yes i would use set analysis to differentiate which fact table you are going to be using. It should not be null as it should show 0 as when you create qvd for the Lead have a 0 for the field income.

Re: Data Model with 2 fact tables

linoyel — Sun, 31 Aug 2014 07:44:32 GMT

OK, last question:

in optimized concatenated table I put 0 for columns that don't exist in one of the tables - but it's OK just for numeric values.

What about string values? Is NULL OK to put there? Will it still be an optimized load?

Re: Data Model with 2 fact tables

denwo2010 — Sun, 31 Aug 2014 11:36:15 GMT

Hi, I for non numerical fields I would put two single quotes '' and then the name of field so like the below: '' as FieldA Thanks

Re: Data Model with 2 fact tables

linoyel — Sun, 31 Aug 2014 11:39:49 GMT

Thank you!

Re: Data Model with 2 fact tables

denwo2010 — Sun, 31 Aug 2014 21:30:57 GMT

No probs, everyone on here is happy to help!