Two stars and one field of DD should involve other star

Currently a request about two starschemas ('A' and 'B') and the customer wants to limit the results (automatically) of star 'B' with the field 'a' in star 'A'. This is a Yes/NO field.

So i'm thinking about building a DD dimension (from star 'A') in star 'B'. So if the customer decides to limit the star 'A' with field 'a', the measures in star 'B' is automatically limited because of the joining the tables. I've to test test this in analysis services but when i connect the two stars with the DD dimension and when i select a
dimension 'C' and set the field a with Yes than theresults in Star 'B' is automatically limited (that's the theory).

Plan B could be integrating field a in star 'B' and let the user set field a in star 'A' and fielda in star 'B' to get the required results. This option is not preferable, IMO.

Plan C is building a combined star (view) on both stars but also not very happy with this solution because extra views are needed, cube adjustments and perhaps reports.

Am i thinking in the right direction or do i have a blackout?

Gr,
Hennie

It doesn't follow that you can get the correct result from B based on a filtering of A. The only way to ensure that B is filtered properly is to include the dimension that is part of A into B.

If that is not possible (the added dimension would affect the cardinality of B) then you do not have a way of accuruately filtering B. Here is why:
To join to fact tables, you must aggregate both along common dimensional attributes, then join on those attributes. So, if you query A with the filter, you can get a list of attributes common to B for use in filtering B. But, since those attributes are independent of the attribute you are filtering on, it may include tuples that have both true and false values of the filtering attribute in A. In other words, the results may be much larger than expected and the fitering against B becomes ambiquous.

Okay, thanx for your answer.

So my option 1 is a suitable solution ?!

It only works if there is a 1:n relation between Fact A (1) en FACT B (N). So the DD dimension from Fact A can be used at multiple rows in Fact B but not the other way around or else i need to build a bridge? But then you can get conflicting Yes's and No's? I think it's enough to record the DD dimension with the Yes/No field in Fact B.

So i need to create a Fysical database dimension in SQL Server with factid's of Fact A en field a, connect this to the appropriate rows of Fact A and B.

The only drawback of this solution is that this a solution in concrete. If i want to combine both facts and want to use the Yes No field in Fact A and not in Fact B..You can't, Right? Or should i do both options: build the field a in Fact A en build the DD dimension between Fact A and B?!

hennie7863 wrote:It only works if there is a 1:n relation between Fact A (1) en FACT B (N). So the DD dimension from Fact A can be used at multiple rows in Fact B but not the other way around or else i need to build a bridge? But then you can get conflicting Yes's and No's? I think it's enough to record the DD dimension with the Yes/No field in Fact B.

Not necessarily - it works if the dimension you are filtering by takes on a single value at the granularity of each of your fact tables.