I have a below tables

1. Customer Product ARR (customer_id, quarter_id, product_id, arr)
2. Dimensions: Customer Region (country, region)
3. Product: (product_id, product_name, line_of_business, business_group) hierarchy parent>child: business_group>line_of_business>product_name
4. Quarter: (quarter_key, FY_Quarter, FY)

So far simple. I have one fact table and two dimension tables, modelling is working fine.

Now I want to support subscription type: it is a calculated state for every quarter.

Subscription Type: product_subscription_type, customer_type ( hierarchy: customer_type>product_subscription_type)

• item_subscription_type: new/existing. Please note: product_subscription_type can be new but customer type can be existing if customer subscribes to additional products in subsequent quarters.
• customer_type: new/existing.

Please suggest best way to model subscription type. Feel free to recommend other options that works best. Thanks much in advance.

Option-1: add subscription_type as a column to (presentation) fact table

• Disadvantage: against the recommendation that facts should not have non measures

Option-2: add subscription_type as a dimension.

Approach: add a calculated column to fact (physical) table, join with new table "Subscription Type" in physical layer. Create a dimension table in logical/presentation tables for Subscription Type.

Question: How will the system behave if I aggregate the values at line_of_business or biz group level? for e.g. if I want to get ARR by subscription type for every quarter.