level 3 4.6.3 Data Hub Model: SYS14 module

RavinderB

In the Data Hub model, create a SYS14 Create Account>Product system module

• Dimensions: Account Flat, Product Flat 

Add lines items and functionality: 

• Data?(Boolean) filter to determine if ANY data for product SKU under the product 
  • If you are using the Level 2 Data Hub model: reference the SYS12 SKU Details and SYS13 Account SKU filter
  • If you downloaded a new Data Hub model at the beginning of Level 3: Use a formula that references entire timescale of the DAT05 Historic Revenue by Product module to filter for revenue greater than 0
• Manual? (Boolean) will be used to create relationship between product and account
• Valid? (Boolean) confirms if either Data? or Manual? line items are true 
• Code will display combined code of account and product codes (example: A001_P001) if Valid? is true
• Product Code use to create subsidiary view to display only the product code 
  
  My formula for data boolean:
  
  if ‘SYS13 Account SKU Filter’.Has Data?[lookup: ‘SYS12 SKU Details’.Product] then true else false
  
  BUT error:
  
  Invalid formula for ‘SYS14 Create Account>Product’.data?: Automatic sum of ‘SYS13 Account SKU Filter’.Has Data? over SKU Flat hierarchy is not possible as SKU Flat does not have a built-in top level
  
  
  please advise

KirillKuznetsov

@RavinderB 

You apperently refferring to wrong Product line item in lookup. This is why automatic sum is triggered. Please check if list in ‘SYS12 SKU Details’.Product is the same list as in dimension of ‘SYS14 Create Account>Product’.data?.

 

You can try to add a Top level element in the SKU flat list to check what reult you are getting.

 

Thanks

Kirill

PranayG

How did you set up the relationship between account and product in manual? line item?

Preethi

@RavinderB

The trick is to use aggregation method instead of Lookup/SUM. Please refer the previous lesson 4.4 Aggregation method other than SUM