SSAS : Field (Y/N) 'a' in Fact A impacts on rows Fact B

Introduction
I've a requirement from a customer about combining facts and selecting them with a Y/N field. This blogpost descibes this requirement and the translation in a solution. This blogpost is simplified version of the 'real world' situation. Suppose you have two facts : 'A' and 'B'. Fact A has a field 'a' and this field can be Yes or No. The user wants to use this field to limit the rows in Fact B, somehow.

Options
When i started to investigate this problem, i came to three options:

1. Put the Yes/No field a in a separate dimension and use the dimension in both facts
2. Load the Yes/No field during the ETL in both facts.
3. Create an view where the two facts and the field are combined.

In my opinion option 1 is more rigid option than option 2. Option 2 is more flexible because i've to ability to set field a in Fact A and B independant. I've not investigated option 3, furthermore.

In this post i'll explain option 1.

The problem

First i'll describe the initial situation i've created. Below you can see my cube with the two facts A and B, two dimensions DimDepartment and Dimdate and the involved field ShowAllYN that should impact on both facts

When i browse the cube in SSMS, select field Fact A Count and Fact B Count and watch the result per department :

The results of Fact A are limited, but the results in Fact B aren't (off course). So, how can i achieve that field "Show All YN"impacts both on FactA and FactB?

Solution (option 1)
The solution that is described in this blogpost is option 1. In option 1 a separate dimension is created, named DimFactA_DD dimension. The ShowAllYN field is not available anymore in fact A, but moved to this DD dimension. Normally you would create a Fact cubedimension for this kind of degenerated dimension fields. But in this case we need to create a separate (regular) dimension.

So the cube structure is now changed to the following structure:

And how does this look like in the cube browser? First let's look at the whether everything works ok when i don't use the DD dimension:

The next step is using the DD dimension for both facts:

As you can see selecting the Y/N field has an impact on the measures now, just as we wanted.

Conclusion
In this post I've described one option that can be used for selecting multiple facts based on one field. There are many variations on this topic. One variation could be that you leave the Y/N field in the FactA, add one in Fact B and use the combined DD dimension. This way a very flexible solution can exist, but you have some redundancy.

I've implemented option 2 at the customer and this gives me more flexibility than option 1 but the solution descibed in this blogpost is a much nicer solution in my opinion.

Greetz,
Hennie