Data Modelling and association property.

[#1]

I have a sales fact table which contains data from two different system A and B based on transaction id.

Fact_Sales:

date     trans_id     dist_id     ret_id     value

For system A sales we have date, trans_id,dist_id,value   and for system B we have date,trans_id,ret_id,value

I have a dist dimension table which contains distributor details and similarly a ret table for retailers and they are associated with fact table. dist and ret tables are associated by ret_id

dist table : dist_id,dist_name,dist_manager               ret table : ret_id, ret_name

I have another table Ledger which contains year,month,ret_id,enroll,transact. enroll and transact columns are flag.

My data model contains a calender also.

If I select a manager from dist table which has different dist_id associated How it will select the data in system ?? Will it first select dist_id corresponding to manager and then go to fact_sales and then go to ret table.

When I select dist_id and ret_id in a table I can see ret_id for dist_id although in data model they are not linked Bcoz in fact table for every row if there is dist_id then ret_id is null and same for ret_id.

[its_anandrjs]

In qlikview when the association is proper and when you select any data in the field the association are reflected in all the referenced fields.

[sujeetsingh]

here is your structure

Fact_Sales:

,date   

,trans_id   

,dist_id   

, ret_id   

,value

dist table :

dist_id

,dist_name

,dist_manager              

reet table :

ret_id

,ret_nam

It will select all the entries you are having in your System table for that  dist_ID.

[#4]

I my fact table no any relation b/w dist_id and ret_id. In any row if dist_id exist then ret_id is null and vice-versa.

But when I select dist_id and ret_id in a table they are coming associated as each dist_id has one or many ret_id.

So my confusion is How QV is linking dist_id and ret_id

[its_anandrjs]

Hi,

Please check this is your expected data model

I Suggest you if the rettable fields (ret_id,ret_name) are available in the Fact_Sales table by joining then the ret_id completely available in the fact table then there is only one reference table Ledger is available.

[#6]

I have to keep ret table seperate as it is master table for retailer so contains multiple information like ret type1 typ2 etc.