Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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
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.
To help you try to upload a version of your tables in a QVW. Run with debug mode with 10 records.
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.
Thank you, I was not able to think about a star data modeling. But now everything seems to work, even if I am still checking the result.
thank you
Carlo A. Babini