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

[linoyel · Accepted solution]

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.

[denwo2010]

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

[linoyel]

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?

[denwo2010]

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.

[linoyel]

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?

[denwo2010]

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

[linoyel]

Thank you!

[denwo2010]

No probs, everyone on here is happy to help!