Problem with data model

Wed, 18 Mar 2015 15:54:27 GMT

Hi All,

i have big problems about data model. I want to create a big pivot table with various metrics calculate using 3 facts table. The 3 facts have differents granularity. Two facts have the same 8 dimensions and granularity by customer, and the other fact has granularity by cruise.

I attached the load script about facts:

Fact_Excursion:

LOAD ID_CALENDAR,

DT_DATE,

ID_CRUISE_DAILY,

CD_CRUISE_DAILY,

ID_SHIP,

CD_SHIP,

ID_MASTER,

CD_MASTER,

ID_EXCURSION,

CD_EXCURSION,

ID_PORT,

CD_PORT,

ID_CUSTOMER_PAYER,

CD_CUSTOMER_PAYER,

ID_CUSTOMER_BUYER,

CD_CUSTOMER_BUYER,

FG_PREPAID,

FG_PREBOOKED,

FG_PRICE_TAG,

DT_PDAT,

DS_BOOKING_ORIGIN,

NR_QUANT,

IM_DISCOUNT,

IM_SALESPRICE,

IM_AMOUNT_PAID,

IM_NET,

ID_CRUISE_DAILY_SYS,

CD_CRUISE_DAILY_SYS,

IM_COST,

PC_COST_OF_REVENUE,

IM_EUR_GROSS_REVENUE,

IM_EUR_COST_REVENUE,

DS_BOOKING_SOURCE

FROM

[...\QVD\Fact_Excursion.qvd]

(qvd);

Fact_PAX:

LOAD ID_CALENDAR,

DT_DATE,

ID_MASTER,

CD_MASTER,

ID_CRUISE_DAILY,

CD_CRUISE_DAILY,

ID_SHIP,

CD_SHIP,

ID_NATION,

CD_NATION,

ID_BOOKING_TYPE,

CD_BOOKING_TYPE,

ID_OFFICE,

CD_OFFICE,

DS_CLUSTER_MED,

FG_PREPAID_DTS,

NR_EXCURSIONS_BUYED,

ID_CABIN_CATEGORY,

CD_CABIN_CATEGORY,

DS_AGE_CLASS,

FG_TRAVEL_WITH,

FG_REPEATER,

DS_GENDER,

DS_TAG,

DS_STATUS,

NR_COUNT_TOT,

NR_COUNT_INF,

NR_COUNT_CHD,

NR_COUNT_ADL,

NR_COUNT_TEEN,

NR_COUNT_NOAGE,

NR_COUNT_EMB,

NR_COUNT_INF_EMB,

NR_COUNT_CHD_EMB,

NR_COUNT_ADL_EMB,

NR_COUNT_TEEN_EMB,

NR_COUNT_NOAGE_EMB,

NR_COUNT_DIS,

NR_COUNT_INF_DIS,

NR_COUNT_CHD_DIS,

NR_COUNT_ADL_DIS,

NR_COUNT_TEEN_DIS,

NR_COUNT_NOAGE_DIS

FROM

[\QVD\Fact_PAX.qvd]

(qvd);

Fact_Revenue:

LOAD ID_CALENDAR,

DT_DATE,

ID_CUSTOMER,

CD_CUSTOMER_DTS,

ID_SHIP,

CD_SHIP_DTS,

ID_BOOKING,

CD_BOOKING,

ID_MASTER,

CD_MASTER_DTS,

NR_PAX,

NR_ITEM_ACT,

NR_ITEM_AIR,

NR_ITEM_CRU,

NR_ITEM_HTL,

NR_ITEM_INS,

NR_ITEM_TPT,

NR_ITEM_TRF,

NR_ITEM_OBS,

IM_GROSS_AMT_ACT,

IM_GROSS_AMT_AIR,

IM_GROSS_AMT_CRU,

IM_GROSS_AMT_HTL,

IM_GROSS_AMT_INS,

IM_GROSS_AMT_TPT,

IM_GROSS_AMT_TRF,

IM_GROSS_AMT_OBS,

NR_TICKET_SHOREX,

IM_REVENUE_SHOREX,

CD_CUSTOMER_FMS,

IM_NET_AMT_ACT,

IM_NET_AMT_AIR,

IM_NET_AMT_CRU,

IM_NET_AMT_HTL,

IM_NET_AMT_INS,

IM_NET_AMT_TPT,

IM_NET_AMT_TRF,

IM_NET_AMT_OBS,

IM_GROSS_AMT,

IM_NET_AMT

FROM

[...\QVD\Fact_Revenue.qvd]

(qvd);

How can i create a data model without circular references? i create a link table but granularity is a problem.

Thanks a lot

Re: Problem with data model

MK_QSL — Wed, 18 Mar 2015 15:59:17 GMT

Provide screenshot of your datamodel..

Re: Problem with data model

p_verkooijen — Wed, 18 Mar 2015 16:03:24 GMT

Use

Fact_Excursion:

LOAD ....

Fact_PAX:

CONCATENATE LOAD ....

Fact_Revenue:

CONCATENATE LOAD ....

Re: Problem with data model

prieper — Wed, 18 Mar 2015 16:04:37 GMT

What about to concatenate the tables?

Might make sense, though, to check on the total number of fields - i.e. is it necessary to have all the ID... and CD.... fields in the fact-table, or would it be possible to either skip one of them or have them as dimension-table.

HTH Peter

Re: Problem with data model

buzzy996 — Wed, 18 Mar 2015 16:08:55 GMT

try this,

1.with common fields create one key field on each dimension

2. choose one table as key table and maintain relations through key fields between ur key table and other table.

tht's helps.

Re: Problem with data model

Wed, 18 Mar 2015 16:12:07 GMT

This is the screenshot loading facts and table:

Re: Problem with data model

Roop — Wed, 18 Mar 2015 16:33:27 GMT

I would also concatenate these tables together so long as they are not too huge.

The biggest difficulty will then being careful not to create some illogical presentations of the data. This is not as difficult as it seems as you will find that you will only be able to combine the data in logical reports.

topic Problem with data model in QlikView

Problem with data model

Re: Problem with data model

Re: Problem with data model

Re: Problem with data model

Re: Problem with data model

Re: Problem with data model

Re: Problem with data model