When to use a separate dimension for denegerate dimension?

[LuizBales]

Hi

On kimball data warehouse toolkit is said that degenarate dimension atributes are the ones that are used for filter and are stored on the fact table.

When these values are separted from the fact table, they are not a degenerate dimension anymore, Kimball says that it's not recommended to to that.

But, on Power BI manual, the following is said:

"When columns from a fact-type table are used for filtering or grouping, you can consider making them available in a separate table. This way, you separate columns used for filter or grouping, from those columns used to summarize fact rows. This separation can:

• Reduce storage space
• Simplify model calculations
• Contribute to improved query performance
• Deliver a more intuitive Fields pane experience to your report authors

Consider a source sales table that stores sales order details in two columns."

Source: https://docs.microsoft.com/en-us/power-bi/guidance/relationships-one-to-one#degenerate-dimensions

So, whats the right to do? I have the same example of Power BI manual, I have both order and order line atributtes, is it right to separted them on a different dimension?

[LuizBales]

Well, maybe this is the case for me, I'm removing a lot of data that are used on only a page (I use the order id for agregation that page), also I transformed a composite key in a surrogate key.

I removed from the fact table:

The order ID (it's the invoice number0

The serial from order ID (the order ID is based on a accounting specific number (like an invoice) of my country, and it has different cattegories called serial. Also I put the branch id there, but it also stayed on the fact table (because every branch has an specific serial an invoce numeration that can coincide)

The item line (The invoice can have more than one product and thats the granularity of my fact table). 

I also inserted the status of the invoiced (if is active ou canceled) and a accounting specifc number that indicates the type of operation (e.g. sales to another country, sales on the same city).

[AlexisOlson]

I think I'd have to disagree with the Power BI documentation here. Creating a one-to-one relationship with a table derived from degenerate dimensions doesn't seem that useful to me.

I don't see how you can reduce storage space by creating a new high-cardinality composite key.

Some calculations can be easier with a composite key but you don't need a new table for that.

Improved query performance will likely only be in cases where you're materializing more than you need to and can likely be achieved by focusing the code to work with columns rather than whole tables.