Problem with data model

[#1]

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

[MK_QSL]

Provide screenshot of your datamodel..

[p_verkooijen]

Use

Fact_Excursion:

LOAD ....

Fact_PAX:

CONCATENATE LOAD ....

Fact_Revenue:

CONCATENATE LOAD ....

[prieper]

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

[buzzy996]

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.

[#6]

This is the screenshot loading facts and table:

[Roop]

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.