Filtering while Synthetic key exist

[justISO]

Hi all, maybe someone could give me an idea how to prepare data, or adjust measures/dimensions in report level if now I have such data structure (simplified), and filtering in report level work not as expected:

I have 'client' table with discount and discount percentage. I have second table with clients additional discounts for item brand or/and group. Last table is for sales and this table with add_disc have 3 common fields. And here is a problem: in report level I show 3 separate tables for each data table. If I filter 'client', I get specific clients info. BUT, if I filter client and its additional discount type (add_disc_type), I see group/brand from second table, but no sales info according those group/brand. And I would like to see those sales of 'discounted brand/group'. I suspect it is due to Synthetic key and add_discr_type is null in sales table.

Is it possible to do something that filtering would be correct? Any suggestions or at least ideas?

[Clement15]

Hello, it is not recommended to have synthetic keys in your model. I advise you to concatenate fields to create a new key. For example, here you could use
client & group & brand as Key_Disc
then drop the disc client field. This is just an example and should be adapted to your model, but it might correct your problem.

[justISO]

Thanks for reply, and I tried that, but problem is, that one client can have additional discount for group or for brand separately or both, for group and brand (rare cases). But sales mostly goes with group and brand. My Key_Disc in this case would filter only discounts with both fields (minority of results), but not separate ones:

and I would like to get such cases by f.e. filtering add_disc_type=ggg, so related group would be in sales only too

[Clement15]

Indeed your problem is more complex. Maybe by creating a table per promotion type, each with a specific key?

[justISO]

Can you give me a brief example what do you have in mind exactly?

[Clement15 · Accepted solution]

I was thinking about the possibility of loading the table 3 times but with conditions. The first where group and brand are not empty. The second where group is not empty but brand is empty. And the opposite for the third. Make these tables with an if or where clause. And in each of these tables you create a unique key. The problem with this solution is that for your calculations you will have to take into account the 3 discount possibilities. Which is not practical when writing your measure.

[marcus_sommer · Accepted solution]

One approach might be to concatenate both discount-tables and create there a key-field from client & group & brand and then removing these fields.

If group & brand have identically values in the discount and sales - means by all other records in sales the values are NULL - it might be already suitable. If not the not existing group & brand values from the additional discounts needs to be populate before the concatenation is applied - maybe with a cartesian join and afterwards a where not exists() clause ensured the filtering.

Personally I would tend to remove the discount tables and transferring the discount information into the sales table. Two simple mapping-tables from the discount-tables and appropriate applymap() queries would be enough. Especially if the discount-requirements are a bit more complex like having also a timely- and/or amount-dependency to the discounts and/or the discounts are additive in some way to each other - a mapping-transfer of the information to the facts is much more easier than keeping them within the dimensions.

[justISO]

Thank you for ideas, seems it helped to overcome my struggles. I created middle table between discounts and sales where meets keys from each table. In this middle table from sales I created key by splitting unique client-group and client-brand options (with client-card as key to sales), and in discount table created same key with client-brand and client-group options. My script still need some adjustment, but basic idea is clear and looking for it has been my headache for days now.
Thank you very much!