3 Replies Latest reply: Mar 12, 2012 10:39 AM by Carlo A. Babini RSS

Data modelling

Carlo A. Babini

Hi all,

I am facing a strong problem, I have 3 data tables

- sales, where each row shows: article_id, category_id, date, sale_quantity and some other data

- purchase, where each rows shows: article_id, category_id, date, purchase_quantity, and some other data

- warehouse, where each rows shows:article_id, category_id, date, stock_quantity and some other data

 

so, when I load my data the resulting tables are sales, purchase, warehouse and $Syn1.

In $Syn1 I can obvioiusly find the columns: article_id, category and date

 

Now, it seems to be ok. But I have to add some other tables:

- article_details: this will be linked to $Syn1 by article_id and offers to me other columns (article description, etc...)

- category_details: this will be linked to $Syn1 by category_id and offers to me other columns

- master_calendar: this will be linked to $Syn1 by date

 

I don't understand why selections on master_calendar seems to be ok, and selections on article_details are effective just on sales and not on purchase and warehouse. Let me explain: if I select an article_id the resulting rows of purchase are 0, while the resulting rows in sales are >0

 

I hope that someone can understand my problem and help me to find a solution, thank you in advance.

 

Carlo A. Babini

  • Data modelling
    Juan Olivares

    To help you try to upload a version of your tables in a QVW.  Run with debug mode with 10 records.

  • Data modelling
    jp_bakhache

    hi Carlo,

     

    in my personal work, i always try to concatenate different transactional tables that usually contain a date field and other common fields, so i can link just one table to the dimension tables.

     

    i can differenciate the concatenated tables by adding for each one for example a TrxType field and giving it a related description:

     

    LOAD 'Sales' as TrxType,

    date,

    product_id,

    etc...(rest of sales table)

     

    Concatenate

     

    LOAD 'Purchase' as TrxType,

    date,

    product_id,

    etc...(rest of purchase table)

     

    then use it in the set analysis expressions to get info for a specific transaction type

    (for ex. Sum({$<TrxType = {'Sales'}>} Value) to get the sales amount

    or Sum({$<TrxType = {'Warehouse'}>} Quantity) to get the stock quantity).

     

    in this way i can apply a star schema design where i have a fact table (with the different types of transactions) and dimensional tables around (like the calendar, articles, clients, etc..).

    the date field of the master calendar will be linked to the date field of the final transactional tables (which in fact represents the date field of each of the different transactional tables).

     

    you can apply this in your case to avoid one major issue (in most of the cases) which is the synthetic keys that are probably causing the confusion in the selections/results.

     

    i hope that this will help you create an easier structure.