Data Modelling

[RaviPuttaswamy5]

Hello all,

Please suggest on below issue, have 2 tables 1) Sales Quota File --> Excel Feed 2) Invoice Table.

Joining the tables based on the Key created, but not getting the values from the Sales Quota in output table when that particular ChainID Sales not available in Invoice tables. Details below.

Sales Quota File
Sales Executive	Company Chain ID	Sales Quota	Month
Suresh	121	100	1/1/2023
Suresh	123	150	2/1/2023
Suresh	124	300	3/1/2023
Ajay	432	200	1/1/2023
Ajay	243	250	2/1/2023
Ajay	342	350	3/1/2023
Preeth	567	100	1/1/2023
Preeth	675	125	2/1/2023
Preeth	665	355	3/1/2023
Ramesh	876	455	1/1/2023
Ramesh	768	660	2/1/2023
Ramesh	776	720	3/1/2023

Invoice Table
Sales Executive	Company Chain ID	Quota Achieved	InvoiceDate
Suresh	123	200	2/1/2023
Suresh	124	300	3/1/2023
Ajay	432	200	1/1/2023
Ajay	243	250	2/1/2023
Ajay	342	350	3/1/2023
Preeth	567	100	1/1/2023
Preeth	675	125	2/1/2023
Preeth	665	355	3/1/2023
Ramesh	876	455	1/1/2023
Ramesh	768	660	2/1/2023
Ramesh	776	720	3/1/2023

Output table
Sales Executive	Company Chain ID	Sales Quota	Quota Achieved	Month
Suresh	121	100	Null	1/1/2023
Suresh	123	150	200	2/1/2023
Suresh	124	300	300	3/1/2023
Ajay	432	200	200	1/1/2023
Ajay	243	250	250	2/1/2023
Ajay	342	350	350	3/1/2023
Preeth	567	100	100	1/1/2023
Preeth	675	125	125	2/1/2023
Preeth	665	355	355	3/1/2023
Ramesh	876	455	455	1/1/2023
Ramesh	768	660	660	2/1/2023
Ramesh	776	720	720	3/1/2023

Sales Quota File	Key: SalesExecutive+CompanyCHainID+Month
Invoice Table	Key: SalesExecutive+CompanyChainID+InvoiceDate

Data model is star schema where Invoice table is fact table and remaining are dimension table and Invoice table is linked to mastercalender based on invoice date field.

Thanks in Advance.

[marcus_sommer]

I think I would consider both tables as facts and just concatenating them - by harmonizing the field-names, like:

facts:
load Executive, ID, 'Sales Quota' as KPI, [Sales Quota] as Value, Month as Date
from Sales;

concatenate(facts)

load Executive, ID, 'Sales Achieved' as KPI, [Quota Achieved] as Value, InvoiceDate as Date
from Invoice;

means no trouble in creating the keys and the risks of loosing/duplicating records/information by any join-approaches and an easy access to the needed data by using the KPI as dimension and/or selection and/or set analysis condition.

[RaviPuttaswamy5]

@marcus_sommer Thanks for response, but here invoice table is having more column other than what i mentioned, i have highlighted the columns which is using to create key 

[marcus_sommer]

That a fact-table is combined per concatenate from multiple sources and becomes on this way more or less asynchronous is neither technically nor logically an issue.

Beside of this there may be further data-parts which might be transformed, for example more measure-fields which are transferred in such a KPI and Value structure instead of being kept in a crosstable-logic as well as outsourcing some fact-fields into further dimension-tables and many more possibilities ...

It will depend on various factors which measures are the most suitable ones. In this regard it's also useful to look on the size of the data-sets. If for example the invoices have 95% of the records and the sales about 5% it might be not worth to optimize the structures and logic even if this would be possible.

[RaviPuttaswamy5]

Tried using the concatenation w.r.t 4 fields, but its making issue in values for the other dimension.

seems its acting as cross table and hence getting more number.

Excuse for delayed response... @marcus_sommer 

@sunny_talwar.. can you pls suggest  on this thread

[marcus_sommer]

By concatenating several fact-tables into a single-fact-table it should work like above described. Therefore I assume that there are more fact-tables and/or any invalid associations and/or synthetic keys and/or circular loop between the tables.

Try to keep it so simple as star-scheme with one fact-table and n dimension-tables. Of course there are also other ways - but all of them will be much more complex ...