Handling SCD2 Dimensions and Facts with PowerPivot
Having worked a lot with Analysis Services Multidimensional Model in the past it has always been a pain when building models on facts and dimensions that are only valid for a given time-range e.g. following a Slowly Changing Dimension Type 2 historization. Opposed to relational engines, Analysis Services Multidimensional Model does not support complex relationships that would create more than one matching row like in terms of SCD2 a join using T-SQL's BETWEEN. In Multidimensional Models each fact-row has to be linked to exactly one member in a dimension. There are some techniques to mimic BETWEEN-Joins like abusing ManyToMany-Relationships but all of them have several drawbacks and are just workarounds that do not solve the actual problem.