Data model for values of different granularity

[Anonymous]

Hi guys,

we have an application which takes into account data of various granularity.

Has anybody encountered a similar situation and what would be the best data model for it?

Let me explain the problem.

We have the following dimensions:

Product (id, segment)

Market (id)

Period (year, month)

We have a measure Registrations which is defined per each intersection of Product, Market, Period.

And we have a measure Industry which is defined only per Segment of each product.

So, when we load the data first we get the warning about the loops:

We change the name of the Segment field in the Industry table and resolve the loop problem:

Unfortunately, we can't use this data model. When we have a chart with Industry and Month in dimension, the data change whenever a Product is selected. Our client wants Industry charts which completely ignore any product selection and react only on Segment change.

With quite a bit of Set Expressions this goal is achievable.

However, since Registrations are related to Product and to Industry, this relation impacts the results of the Industry chart anyway. It happens when a product was not sold on a certain market (no Registrations records). When selected, its excluded market limits the Industry records and therefore impacts the final value of the Industry expression in the chart.

We tried a workaround and unlinked the Product table from Registrations and Industry:

But this creates another problem we seem to unable to solve as for now: how to combine Segment and Product fields in a chart with a Registrations (or Industry) expression?

Any comments on this topic would be greatly appreciated.

Best wishes,

Valeriy Shylin