SSAS : understanding SSAS relations behaviour between Facts and Dimensions (design problem)

Question

Hi

I've a question about the following schema:

 

FactA = eg Order

FactB = eg. orderlines

FactA DD dimension is a Factdimension of FactA en i've a FK column in FactB to this FactA DD dimension (in the dimension usage tab).

What if i want to select (filter) with the Type dimension records in FactB? As i know now the same value is shown when browsing the cube. Is it possible to filter some records from the Type dimension and show only the relation records of FactB? Or should i include the Type dimension in FactB also? This doesn't seems a good solution to me..

 

Hennie

 

 

 

Answer 1

Hi Hennie

You don't need to include a key to the Type dimension in Fact B, you can create a referenced relationship using Fact A DD Dimension.

See the example below where I'm joining Purchase Order Line(Fact B) to Date Order (Type Dimension), using Purchase Order (FACT A Degenerate Dimension.

Hope this helps
Dave 

---

David Stewart | My Microsoft BI Blog | @dstewartbi

Answer 2

Initial, i declared this forumpost of Dave answered, but now i not convinced of the correct answer. As i'm reading about the referenced dimension, it 's about snowflaking  between dimensions. But the problem i have is :  Dimension - Fact- Dimension - Fact.

I've set the refernced dimension like this :

Now i've connected the type dimension with the DD dimension (snowflake). I can't seem to find another option of doing otherwise.

 

Perhaps i'm doing something wrong but this seems not correct in my opinion.

Hennie

Answer 3

Nobody? I think i'm gonna have to solve this with MDX in the report. The situation i've described seems normal behaviour of the cube (otherwise why is there the option ignoreunrelateddimension available)

On the other hand, it will never be possible selecting values in Excel by end users like this? Why is this behaviour? It seems possible (IMO)to generate MDX in Excel combining facts.

Answer 4

No, the difference is if it's a degenerate dimension then it also has a key to the dimension table. If you haven't included the dimension key in the degenerate dimension then no this won't work, and you'd have to use a many to many relationship.

Your actual relationship is...

(Fact B) ---> (Fact A Dimension/Fact) ---> (Type Dimension)

Logically the degenerate dimension and fact are a single table or entity.

---

David Stewart | My Microsoft BI Blog | @dstewartbi

Answer 5

Hi David,

In my opinion there should be no dimensionkeys in the degenerated dimension. What would store in the fact table, otherwise? The DD dimension contains keys that don't have a dimension like social security number and license plate. 

M2M relationship crossed my mind but i do think that  this is also a dimension relationship and not dim - Fact - Fact. But i will give this a try. Let you know.

 

Hennie

Answer 6

You don't display the key to the type dimension in the degenerate dimension, you just include it in the dimension and hide it. That way you can create a referenced relationship and the end user doesn't see the dimension any differently.

---

David Stewart | My Microsoft BI Blog | @dstewartbi

Answer 7

Hi Hennie,

Besides the referenced relationship mentioned by Dave, you also can use the many-to-many between FactB and Type dimension, the FactA can be as their intermediate fact table.

Regards,

Jerry

TechNet Subscriber Supportin forum
If you have any feedback on our support, please contact  [e-mail]

Answer 8

@Dave :  I think i do understand the solution you proposed. I was reasoning from Kimball theory and i'm now starting to realize that de cube can not completely deal with the theory of kimball and therefore i need to dive in the practical solution and that is adding the Type dimension to the DD dimension and create a referenced dimension.

@Jerry: thank you. Dave already mentioned the M2M referenced relationship. It seems that i have two options to investigate.

From a more fundamental viewpoint seen, i still don't understand why this isn't working like i thought it should work. Perhaps it has something to do when you have multiple conformed dimension and the SSAS engine don't understand which path it has to take?

The statement is that when multiple facts are involved and a selection needs to be done a  'connection' (common dimension) between the facts needs to be available?!

Does that mean i had better developed a joined 1:n fact table? Is that a best practice?

Hennie

Answer 9

SSAS does benefit from a good underlying dimensional model, but there is a difference between querying underlying tables and specifying relationships in the cube. From a T-SQL join perspective you could easily join from Fact B to Fact A to the Type dimension, but you need to think slightly differently for SSAS.

While you could reconstruct the previous join using a many to many relationship in SSAS, I wouldn't recommend it. You've already got a dimension that logically relates Fact B to the Type dimension, so creating a referenced relationship is simple, will perform better and be easier to understand than a many to many relationship. This doesn't invalidate any best practices and is a simple and elegant solution. If you've defined your keys correctly in the DSV, you may find that SSAS populates this relationship for you automatically.

With the Kimball methodology you should apply it as a guide rather than a strict set of rules. There are exceptions to these principles and often design choices must be made on a case by case basis.

---

David Stewart | My Microsoft BI Blog | @dstewartbi

Answer 10

Okay let's go for option "referenced dimension relationship". What do you mean by logically relation ? As in logical model or logically in reasoning?

 

To be practical:

1. I need to transfer the technical key of the Type dimension of fact A (1) to the DD dimension (in SQL)?
2. Then add a connection the DD dimension with type dimension in SSAS with referenced dimension

Hennie

Answer 11

Hmmm. I've created a cube factdimension as a DD dimension (and not physical). I suspect that i have materialize this in SQL Server to get this thing working?!

Answer 12

The way I tend to handle degenerate dimensions for use in SSAS is by using a single table for the fact and dimension. With the degenerate dimension you'll have a 1 to 1 relationship with the fact, so there's little point splitting it out.

It also has the added bonus of cutting down the number of database objects and the amount of ETL you have to develop!

---

David Stewart | My Microsoft BI Blog | @dstewartbi

Answer 13

Hi Dave,

You're saying this????:

Gr,

Hennie

Answer 14

Yes. Given it's a one to one I just don't see the point of splitting it into two tables. You just don't include the columns you don't want as dimension attributes, and only create the measures you want in the fact.

---

David Stewart | My Microsoft BI Blog | @dstewartbi

Answer 15

Hi David,

It's a 1:n relation between A (1) and B (n).

Hennie

Answer 16

Sorry, I mean that Fact A to the degenerate dimension is 1 to 1. The cardinality of the relationship between Fact A and Fact B isn't really important.

---

David Stewart | My Microsoft BI Blog | @dstewartbi

Answer 17

Okay. I'll investigate this a bit further..

 

Currently in discussion with my customer about this issue and they want more of this kind of solutions for analyzing the data. Even better, they want to use all of dimensions of the 1 fact (A) with the measures of the n Fact (B). Not really happy with this situation.

 

I've created some conformed dimensions like department, client and date. So this means that i can only combine the data with these conformed dimensions or else i need the trick described above (and build custom connections/reference dimensions between the facts)? This seems a small number of possibilities and a small number of choices for combining the factinformation?

Answer 18

a blogpost of mine about this issue (part I) : http://bifuture.blogspot.com/2011/10/ssas-selecting-facts-withe-different.html

 

Answer 19

http://bifuture.blogspot.com/2011/11/ssas-selecting-facts-with-reference.html