topic Re: Synthetic Key Issue in QlikView

Synthetic Key Issue

Fri, 13 Mar 2015 16:27:57 GMT

Happy Friday Everyone!! I've got a right issue here and I can't work it out. Basically I want the two fact tables to relate to the two dimension tables independently and I don't know how to do it whilst keeping the field names the same. How do I avoid creating the synthetic key whilst keeping things quick....there is over 30 million rows in the back end of this!

Kind Regards,

Miles

Re: Synthetic Key Issue

Anonymous — Fri, 13 Mar 2015 16:29:54 GMT

How about concatenating your 2 Fact Tables into a single Fact table ?

Re: Synthetic Key Issue

Fri, 13 Mar 2015 16:37:58 GMT

so lets do that....I come back to my original problem - I have the key twice in my fact table, Where the item was sold at and where it was taken at. I can't name the field twice as the same name....and then again I don't want two dimensions ie when I click on country I just want to click on country and it be the same for both

Re: Synthetic Key Issue

arsal_90 — Fri, 13 Mar 2015 16:53:07 GMT

Use Link Table methodology

Re: Synthetic Key Issue

Fri, 13 Mar 2015 16:56:16 GMT

with 30 million rows though it gets really slow

Re: Synthetic Key Issue

arsal_90 — Fri, 13 Mar 2015 16:57:06 GMT

LinkTable:

Load distinct FK_DimLocation,

FK_DimCurrency,

FK_DimLocation&'-'&FK_DimCurrency as Key

Resident Fact1;

concatenate (LinkTable)

Load distinct FK_DimLocation,

FK_DimCurrency,

FK_DimLocation&'-'&FK_DimCurrency as Key

Resident Fact2;

Make a composite key in both fact tables like that ( FK_DimLocation&'-'&FK_DimCurrency as Key) and drop that fields from both facts

Re: Synthetic Key Issue

sunny_talwar — Fri, 13 Mar 2015 16:57:17 GMT

How about qualifying the two fact tables so that they don't have the same field names without needing you to change the name of each of the field name manualy?

Qualify *;

UnQualify someColumns ; (based on the idea that some columns may be allowed to link)

TableName:

LOAD yourColumns

FROM XYZ;

UnQualify *;

HTH

Best,

Re: Synthetic Key Issue

arsal_90 — Fri, 13 Mar 2015 16:58:57 GMT

use autonumber(FK_DimLocation&'-'&FK_DimCurrency,'LOCCUR') as Key

please replace all composite keys with this

Now i pretty sure your performance will not down

Re: Synthetic Key Issue

arsal_90 — Fri, 13 Mar 2015 17:00:37 GMT

LinkTable:

Load distinct FK_DimLocation,

FK_DimCurrency,

autonumber(FK_DimLocation&'-'&FK_DimCurrency,'LOCCUR') as Key

Resident Fact1;

concatenate (LinkTable)

Load distinct FK_DimLocation,

FK_DimCurrency,

autonumber(FK_DimLocation&'-'&FK_DimCurrency,'LOCCUR') as Key

Resident Fact2;

Make a composite key in both fact tables like that (autonumber(FK_DimLocation&'-'&FK_DimCurrency,'LOCCUR') as Key) and drop that fields from both facts

Re: Synthetic Key Issue

arsal_90 — Fri, 13 Mar 2015 17:03:22 GMT

Miles let me know if this solution fulfill your requirement

Re: Synthetic Key Issue

arsal_90 — Fri, 13 Mar 2015 17:05:27 GMT

Let me know the relationship of your fact table with DimCurrency & Dimlocation is one-to-one then i will let you know another solution

Re: Synthetic Key Issue

simenkg — Fri, 13 Mar 2015 17:09:32 GMT

You concatenate your two fact tables and have them share the keys for country and currency. Instead of qualifying your fields like

FKFact_SalesTakenAt and FKFact_ParlSales

Call them both Sales and add another field to your tables called FactType or similar with the values 'TakenAt' and 'Park' respectively.

Fact:

Load FKDim_Location as %LocationKey,

FKDim_Currency as %CurrencyKey

FKFact_ParkSales as Sales,

'Park' as FactType

from ParkFacts.....;

Concatenate(Fact)

Load FKDim_Location as %LocationKey,

FKDim_Curency as %CurrencyKey

FKFact_SalesTakenAt as Sales

. 'TakenAt' as FactType

from TakenAtFacts.....;

DimLocations:
Load FK_DimLocation as %LocationKey

From DimLocations ....;

You get the picture.

When your model is like this then expressions become:

Sum({<FactType={Park}>} Sales)

and

sum([<FactType= {TakenAt}>} Sales)

Selecting a coutntry will link to both the row with factType Park and the one with factType TakenAt

Re: Synthetic Key Issue

Sat, 14 Mar 2015 10:29:39 GMT

Hi,

you will try by using "Aliasing"

thanks,

krish

Re: Synthetic Key Issue

fred_s — Sat, 14 Mar 2015 13:46:04 GMT

Hi Miles,

Is it possible to share your file with some data?

Or create 4 excel or CSV files with some data (but containing all fields).

Make sure your sample data makes sense..

What (field) is the relation between FactParkSales and FactParkSalesTakenAt?

Grtz. Fred

Re: Synthetic Key Issue

Anonymous — Sun, 15 Mar 2015 01:45:57 GMT

You can also use Generic Keys