Using "Fact" Relationship Type on the "Dimension Usage" tab in a SSAS OLAP cube

Question

I was hoping someone could explain the appropriate use of the 'FACT Relationship Type' under the Dimension Usage tab. Is it simply to create a dimension out of your fact table to access attribute on the fact table itself?

Thanks in advance!

Answer 1

Yes, if your fact table has attributes that you would like to slice by (create a dimension from), you would use this relationship type.

Functionally, to the users it behaves no differently than a regular relationship.

Answer 2

After you create your dimensions and cubes you need to define how each dimension is related to each measure group. A measure group is a set of measures exposed by a single fact table.

Each cube can contain multiple fact tables and multiple dimensions. However, not every dimension will be related to every fact table.

To define relationships right click the cube in BIDS and choose open; then navigate to the Dimension Usage tab. If you click the ellipsis button next to each dimension you will see a screen that allows you to change dimension usage for a particular measure group. You can choose from the following options:

• Regular default option; the dimension is joined directly to the fact table

• No relationship the dimension is not related to the current measure group

• Fact the dimension and fact are derived from a single table. If this is the case your dimensional warehouse has poor design and isn't likely to perform well. Consider separating fact and dimension tables.

• Referenced the dimension is joined to an intermediate table prior to being joined to the fact table. Referenced relationship resembles a snowflake dimension, but is slightly different. Suppose you have a customer dimension and a sales fact; you'd like to examine total sales by customer, but you also want to examine line item sales by customer. Instead of duplicating the customer key in the line item fact table you can treat the sales fact as an intermediate table to join customer to line item.

• Many-to-many this option involves two fact tables and two dimension tables. Dimension A is joined to an intermediate fact A, which in turn joins to dimension B to which the fact B is joined. Much like with fact option if you need to use many-to-many option your design could probably use some improvement. This type of relationship is sometimes necessary if you are building cubes on top of a relational database that is in 3rd normal form. It is strongly advisable to use a dimensional model with star schema for all cubes. For example you could have two fact tables: vehicles and options; each vehicle can come with a number of options. You're likely to examine vehicle sales by customer, and options by the items that are included in each option. Therefore you would have a customer dimension and item dimension. You could also want to examine vehicles sales by included item. If so the vehicle fact would be joined to the options fact and customer dimension; the options fact would also join to items' dimension.

• Data mining target dimension is based on a mining model which is built from a source dimension. Both source dimension and target dimension must be included in the cube.