level 3 4.6.3 Data Hub Model: SYS14 module

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
Tagged:

Comments

  • @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

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

  • @RavinderB

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