Facebook
From Unreliable Pelican, 3 Years ago, written in Plain Text.
Embed
Download Paste or View Raw
Hits: 176
  1. cube(`Orders`, {
  2.  
  3.   sql: `SELECT id,total_price, profile_id, channel_id, shipping_price, discount_amount, voucher_amount, ordered_at   FROM datalake_a where ordered_at > '2015-01-01'`,
  4.  
  5. joins: {
  6.     Channels: {
  7.       relationship: `belongsTo`,
  8.       sql: `${CUBE}.channel_id = ${Channels}.id`
  9.     }
  10.   },  
  11.  
  12.  
  13.  
  14.   measures: {
  15.     count: {
  16.       type: `count`,
  17.       drillMembers: [id,  orderedAt]
  18.     },
  19.  
  20.     avgTransaction: {
  21.       sql: `total_price`,
  22.       type: `avg`,
  23.       format: `currency`
  24.     },      
  25.  
  26.  
  27.  
  28.     totalPrice: {
  29.       sql: `total_price`,
  30.       type: `sum`,
  31.       format: `currency`
  32.     },
  33.    
  34.    
  35.     shippingPrice: {
  36.       sql: `shipping_price`,
  37.       type: `sum`,
  38.       format: `currency`
  39.     },
  40.    
  41.    
  42.     discountAmount: {
  43.       sql: `discount_amount`,
  44.       type: `sum`,
  45.       format: `currency`
  46.     },
  47.    
  48.     voucherAmount: {
  49.       sql: `voucher_amount`,
  50.       type: `sum`,
  51.       format: `currency`
  52.     }
  53.    
  54.  dimensions: {
  55.     id: {
  56.       sql: `id`,
  57.       type: `number`,
  58.       primaryKey: true
  59.     },
  60.    
  61.        
  62.     orderedAt: {
  63.       sql: `ordered_at`,
  64.       type: `time`
  65.     },
  66.  
  67.     channelId: {
  68.       sql: 'channel_id',
  69.       type: 'number'
  70.     },
  71.  
  72.  
  73.     channelName: {
  74.       sql: 'name',
  75.       type: 'string'
  76.     },
  77.  
  78. });
  79.  
  80.  
  81.  
  82.  
  83.  
  84.  
  85.  
  86. Preagg cube file
  87.  
  88.  
  89. cube(`OrdersPreAgg`, {
  90.  extends: Orders,
  91.  preAggregations: {
  92.   main: {
  93.    type: `rollup`,
  94.    measureReferences: [count,totalPrice],
  95.    dimensionReferences: [channelId,id],
  96.    granularity: `month`,
  97.    timeDimensionReference: orderedAt,
  98.    external: true
  99.   }
  100.  }
  101. });
  102.  
  103.  
  104.  
  105. Query made
  106.  
  107. {
  108.   "measures": [
  109.     "OrdersPreAgg.count"
  110.   ],
  111.   "timeDimensions": [
  112.     {
  113.       "dimension": "OrdersPreAgg.orderedAt",
  114.       "granularity": "month"
  115.     }
  116.   ],
  117.   "filters": []
  118. }
  119.  
  120.