Apparent set analysis inconsistency

[paolo_cordini]

Hello

In my Datamodel there is a calendar, linked to the fact table, as usual…:

Now, the measure in the table below has a set analysis which selects the dates above April 20^th

The field “DataMovSettimana” contains the week, it is calculated in the script as Week(DataMov).

As you can see, the current selection is DataMovSettimana=18.

Then, as expected, the result is the intersection of the set analysis and the current selections, thus showing only dates pertaining to week 18.

So, the result of the table below is the expected one.

Then, I remove the dimension “DataMovSettimana” from the table, without modifying anything in the selections, and the result is shown below:

As you can see, the measure total value is different as it apparently disregards the selection of the week. Moreover, the espression “week(DataMov)” is not calculated for the dates excluded in the set analysis.

Then, I remove one of the selections (not the week). Now the result is correct….

Moreover, how this can be explained?

In order to debug it, I made a copy of the date fields, both in the fact table and in the calendar table

This is the result. Does it make sense? Anyway, it is a workaround for me in order to get correct results.

I guess there is some datamodel issue but I am running out of ideas to throubleshoot it.

Any suggestion?

[paolo_cordini]

Unfortunately, this is not the case...

and no duplicates in the numeric dates....

guess the issue is something more tricky

[Firefly_cam]

Paolo... these 2 are Dimension and measure
Please Do 2 Dimessions: DataMov and Num(DataMov)
Or in your implementation do Count(Distinct Num(DataMov)) instead of Num(DataMov).
I'm pretty sure I get what's happening there

[marcus_sommer]

Were there really all relevant fields included in the table-box? Just the dates are not enough else all DataMov versions and all fields from the object dimensions + measures as well as all selected fields. Often helpful is also to include an unique key and if none exists to create one with recno() and/or rowno() - just to ensure to see all data and to have a view on all possible duplicates.

Beside this make sure that there are no synthetic keys or circular loops within the data-model.

[paolo_cordini]

Yes, you are right, but nevertheless, there should be something else

[Firefly_cam]

I think they've done a crazy thing and combined the As-of table with an actual Calendar using dual values In their DateKey. Which explains everything including the case when Only(Week(Date)) is not working

[paolo_cordini]

It is not this case.
I am not the author of the script but it is quite straightforward. It's a simple usual calendar, no As-of table...

[Firefly_cam]

That's the only way I can explain this and it makes sense. Also it explains why you need two identical dates in TabCalendario.

This case appears to be almost untraceable, tried on my end and only this formula worked:
Date(Num(Date))=Date#(Text(Date))

[marcus_sommer]

I think the calendar is not the problem else the DataMov from the facts - and an extra parallele num() + isnum() and similar might be useful there. Further if there are several fact-tables with DataMov as key repeat the doubling of them as separate fields and apply them too in the above suggested table-box. It's very like that your issue comes from not proper associations.

[paolo_cordini]

I simplified a lot the datamodel for debugging purpose.I kept only the fields involved in the example.

From here, if I use a LEFT KEEP when loading the "m-prosta" table then the issue is solved (the "ProdNome" field is included in the selections. But I have no explanation for this behavior. 

[marcus_sommer]

I think it means that the associations between the data were not suitable for your view and the selection within a sub-dimension restricted the other selections and/or the reverse was happens and there were a n:m relationship which wasn't covered by the object granularity/expressions.

Without diving deeper it's hard to say what's the issue but the above mentioned table-box with all related fields and depending on the data a recno() + rowno() from each single table should have provide hints in regard to gaps and/or a poor data-quality and/or any duplicates.

Many of such association-trouble could be avoided by developing the data-model from the beginning as a star-scheme which means a single fact-table with n surrounding dimension-tables.