I know this is mainly a design problem. I 've read that there is a workaround for this issue by customising errors at processing time but I am not glad to have to ignore errors, also the cube process is scheduled so ignore errors is not a choice at least a good one.
This is part of my cube where the error is thrown.
DimTime
- PK (int)
- MyMonth (int, Example = 201501, 201502, 201503, etc.)
- Another Columns
FactBudget
- PK (int)
- Month (int, Example = 201501, 201502, 201503, etc.)
- Another columns...
The relation in DSV is set as follows.
DimTiempo = DimTime, FactPresupuesto=FactBudget, periodo = MyMonth, PeriodoPresupFK = Month
Just translated for understanding.
The Relationship in cube is as follows:
The cube was built without problem, when processing the errror: The attribute key cannot be found when processing was thrown.
It was thrown due to FactBudget has some Month values (201510, 201511, 201512 in example) which DimTime don't, so the integrity is broken.
As mentioned in the answer here this can be solved at ETL process. I think I can do nothing to get the relationship if one fact table has foreign keys that has not been inserted in dimensions.
Note MyMonth can be values 201501, 201502, 201503 etc. This is set for year and month concatenated, DimTime is incremental inserted and every day is calculated that column so in this moment DimTime don't have values for 201507 onwards.
Is there a workaround or pattern to handle this kind of relationships?
Thanks for considering my question.
Is there a reason why you're incrementally populating DimTime? It certainly isn't the standard way to do it. You need the values you're using in your fact to already exist in the dimensions. I would simply script up a full set of data for DimTime and stop the incremental updates of it.