How to handle 2 fact tables related to cascading many-to-many dimensions?
-
Hi,
i got this Situation:
(The left "Dim A" is "Dim D".... copy & paste, sorry)
And I don't know how to solve it here:
because in the marked blue box one have to tell how the "Dim D" is related to "Intermediate B to A", if I'm thinking the right way, it is obviously connected through the fact. And the SSAS does let me choose the facts if when i choose the many-to-many relationship, but it does only allow to connect to one of the fact tables.
As far as I know, you need to tell SSAS how "DIM D" is related to "Intermediate B to A" because SSAS has to know the "Path" from DIM D to DIM C.
When you need data in a Format like that:
If you dont do that, you get the same data in every row, and that is not correct.
I used this PDF to learn about many-to-many Dimension-relationships.
http://www.sqlbi.com/articles/many2many/
My Problem is, that I have to connect it to one fact table, but I need to connect it to both fact tables.
SSAS doesn't let me select both fact tables.
So, how do I solve this?
Greets,
Chris
PS.:
I also asked this question here:
-
Does Fact1 and Fact2 share the same dimensionality? If so, you can combine them.
Otherwise, you may need to duplicate DimD as (DimD_via_Fact2).
So, How is DimD connected to DimC? If i had a measure from "Intermediate B to A" i wanted to show in a grid
with DimD on Rows, and DimC on columns, How would you calculate it by hand? If you can answer that question on paper, then we can work on getting SSAS to answer it as well.
-
SpringTownDBA (12/1/2011)
Does Fact1 and Fact2 share the same dimensionality? If so, you can combine them.Otherwise, you may need to duplicate DimD as (DimD_via_Fact2).
So, How is DimD connected to DimC? If i had a measure from "Intermediate B to A" i wanted to show in a grid
with DimD on Rows, and DimC on columns, How would you calculate it by hand? If you can answer that question on paper, then we can work on getting SSAS to answer it as well.
I re-read your post, Fact1 and Fact2 don't share the same dimensionality....
-
You don't need to specify relation between "DIM D" and "Intermediate B to A". What for?
Are you going to calculate some values in "Intermediate B to A" by "Dim D"? I don't think so, because Intermediate table is not a Fact table and it should be nothing to measure there.
Alex Suprun -
weighting factors are measures that go in the bridge table, otherwise, distinct counts are typically the only useful measure in a bridge table.
here's Microsoft's example from msdn:
http://msdn.microsoft.com/en-us/library/ms345139(v=sql.90).aspx#sql2k5_mmdim_topic4
anyway, the OP has to pick either fact1 or fact2 for the relationship between DimD and [Intermediate A to B], can't do both.
-
SpringTownDBA (12/1/2011)
weighting factors are measures that go in the bridge table, otherwise, distinct counts are typically the only useful measure in a bridge table.here's Microsoft's example from msdn:
http://msdn.microsoft.com/en-us/library/ms345139(v=sql.90).aspx#sql2k5_mmdim_topic4
anyway, the OP has to pick either fact1 or fact2 for the relationship between DimD and [Intermediate A to B], can't do both.
Tell me at least one reason why the OP needs relationship between DimD and [Intermediate A to B]? Unless he wants to see a Distinct count of [Intermediate A to B] by Dim D. But it doesn't make any sense because in this case Fact (1 or 2) table becomes a bridge table and I don't think it's what the OP wants.
Alex Suprun -
Alexander Suprun (12/1/2011)
SpringTownDBA (12/1/2011)
weighting factors are measures that go in the bridge table, otherwise, distinct counts are typically the only useful measure in a bridge table.here's Microsoft's example from msdn:
http://msdn.microsoft.com/en-us/library/ms345139(v=sql.90).aspx#sql2k5_mmdim_topic4
anyway, the OP has to pick either fact1 or fact2 for the relationship between DimD and [Intermediate A to B], can't do both.
Tell me at least one reason why the OP needs relationship between DimD and [Intermediate A to B]? Unless he wants to see a Distinct count of [Intermediate A to B] by Dim D. But it doesn't make any sense because in this case Fact (1 or 2) table becomes a bridge table and I don't think it's what the OP wants.
OP already gave a reason.
Justify (12/1/2011)
As far as I know, you need to tell SSAS how "DIM D" is related to "Intermediate B to A" because SSAS has to know the "Path" from DIM D to DIM C.When you need data in a Format like that:
If you dont do that, you get the same data in every row, and that is not correct.
I used this PDF to learn about many-to-many Dimension-relationships.
In the pdf, on pages 22-23, it describes the exact situation the OP is trying to solve.
-
SpringTownDBA (12/1/2011)
Alexander Suprun (12/1/2011)
SpringTownDBA (12/1/2011)
weighting factors are measures that go in the bridge table, otherwise, distinct counts are typically the only useful measure in a bridge table.here's Microsoft's example from msdn:
http://msdn.microsoft.com/en-us/library/ms345139(v=sql.90).aspx#sql2k5_mmdim_topic4
anyway, the OP has to pick either fact1 or fact2 for the relationship between DimD and [Intermediate A to B], can't do both.
Tell me at least one reason why the OP needs relationship between DimD and [Intermediate A to B]? Unless he wants to see a Distinct count of [Intermediate A to B] by Dim D. But it doesn't make any sense because in this case Fact (1 or 2) table becomes a bridge table and I don't think it's what the OP wants.
OP already gave a reason.
Justify (12/1/2011)
As far as I know, you need to tell SSAS how "DIM D" is related to "Intermediate B to A" because SSAS has to know the "Path" from DIM D to DIM C.When you need data in a Format like that:
If you dont do that, you get the same data in every row, and that is not correct.
I used this PDF to learn about many-to-many Dimension-relationships.
In the pdf, on pages 22-23, it describes the exact situation the OP is trying to solve.
OK, I got it now.
What is Data on the picture by Dimensions C and D? Is it Fact1 and Fact2 only? Or Intermediates as well? If we don't need these values then we can just hide the Intermediates so user cannot get incorrect results. Even the provided document says that there are 2 options here:
At this point, we must choose between making this measure invisible or fixing this measure. The second approach is better if, in the future, we might expand the UDM: more defined relationships will make the cube easier to explain. However, the first approach is easier to maintain and could result in faster queries, but you should remember to hide such a meaningless measure from the end user.
If we really want to show counts from Intermediates then as you already said we have to create 2 Dim D dimensions and then define all the necessary relations.
But I think I found another issue why OP may see wrong results when doing Facts by Dim C. The relation between Dim C and Facts should be done via [Intermediate B to A] instead of [Intermediate C to B]
Alex Suprun -
SpringTownDBA (12/1/2011)
Does Fact1 and Fact2 share the same dimensionality? If so, you can combine them.Otherwise, you may need to duplicate DimD as (DimD_via_Fact2).
So, How is DimD connected to DimC? If i had a measure from "Intermediate B to A" i wanted to show in a grid
with DimD on Rows, and DimC on columns, How would you calculate it by hand? If you can answer that question on paper, then we can work on getting SSAS to answer it as well.
Fact 1 and Fact 2 does not have the same dimensionality, at least one of the dimensions is not connected to both facts.
You can See that in the second picture (gray boxes of the facts).
I thought about combining them because they share the same granularity, but Fact 1 is for quantities and Fact 2 is for durations.
I would get many NULL values ( or values I use to replace NULL [Unknown Member]) if I combine them, so I left it as it is.
Let's say: DIM D is for items.
I want to know what Item and how many of them has DIM B or DIM C.
Alexander Suprun (12/1/2011)
But I think I found another issue why OP may see wrong results when doing Facts by Dim C. The relation between Dim C and Facts should be done via [Intermediate B to A] instead of [Intermediate C to B]I noticed and corrected this, but thanks.
I think it would help if I show you the whole schema, because there is also a DIM E directly related to DIM A.
And on the other side a got a DIM F directly related to DIM G which is then related to both facts. And yes, I have to answer the question on how many of DIM B or C is/was data of DIM F and what Data of DIM F was there.
So, when you say "double DIM D" the conclusion is: double every other dimension.
And the conclusion of that is: build 2 cubes or join the facts together.
If you think "Why is this schema so complex?" or "Does he rebuild the source ER-Model?", no I don't.
It is just the best way to answer the questions.
BTW.: I can't get Hirarchies to work from DIM C to DIM A, why is that?
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply