Linking Dimensions on multiple criteria

Tips and tricks
1

Hi Everyone,

New here, long time phocas user but new to the database design side of things, I have created a few simple databases without issues, now i’m trying to create a more complex database with multiple data sources and i’m a bit stuck…

We have our main data source of PO’s which is just a list of PO’s their cost the product and so on. I also have a data source called product which contains the product code, description and company number (cono).

I have linked the product from the PO data set to a dimension (product) and tied the product field from the product data source to the product field from the PO data source. I have also mapped the company number (cono) from the PO data source to a separate dimension in my main design.

Because we have multiple companies each with a unique cono and each have different sets of product codes, some product codes may exist in all companies or maybe just one. When filtering by my company dimension and then clicking on the product dimension i am seeing products from all companies rather than just the company i have selected.

How can i link the product from my PO dataset to the relevant product from my product dataset based on the product code and cono?

I cant for the life of me figure out how to do this without creating a transformed column in both data sets made of the cono and product concatenated which is not what i want.

Any help is appreciated :slight_smile:

2

Hi,
If there is no purchasing transactions for the product, that means the value should be 0 when you select the company. If you untick Show Net Zero, does it do what you want?
image

3

You’re solution to connecting multiple datasets correctly lies within making the appropriate relationships between the datasets (i.e. Primary Key Identifiers).

For example, if you have a code for each company and each product, each record requires a unique ID, such as company_product_id. The relational dataset of PO would also need to include this unique identifier to join the datasets correctly.

You could build these keys upstream in SQL, otherwise you can also use the Transform in Design, and use a concat statement such as concat(company_id,’_’,product_id).