How to link two different tables to show data by years and other dimensions as filters?

[NenadV]

Hi,

I would appreciate help around showing relevant data when dealing with two different data sources which need to be linked to be beneficial for the business in the form of a bar chart or a table.

For example:

Market (Country, Year, AmountM)

History (Region, Country, Year, AmountH)

Thanks

[marcus_sommer]

Don't link the tables else just concatenate them, like:

Market (Country, Year, Amount, 'M' as Source)

History (Region, Country, Year, Amount, 'H' as Source)

- Marcus

[NenadV]

Thanks Marcus. That was my first version, but was not able to provide data users expected. For example, when the Region is selected to show Countries belonging to this Region and to show History relevant to this Region and general country market amounts for the same year. One Country can be part of more than one Region.

Is there are a specific reason why you proposed to create a new dimension you named Source?

Thanks

[marcus_sommer · Accepted solution]

It's not possible to link Region and Country against Country - something will be always missing. Completely independent from the used data-model.

One way to handle such scenario is to ignore any Country selection within the expressions. The other possibility would be to fill the missing values - maybe just with a simple mapping. If a Country could belong to multiple Region it becomes more complicated because now extra records would be needed and the values appropriate divided.

Beside of the above mentioned topic of different granularity respectively data-quality a concatenation is the simplest way to create the officially recommended star-scheme data-model. The extra field Source is important for such an approach to be able to differentiate between the source-tables with dimension and/or selections and/or set analysis conditions.

- Marcus