- cube(`Orders`, {
- 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'`,
- joins: {
- Channels: {
- relationship: `belongsTo`,
- sql: `${CUBE}.channel_id = ${Channels}.id`
- }
- },
- measures: {
- count: {
- type: `count`,
- drillMembers: [id, orderedAt]
- },
- avgTransaction: {
- sql: `total_price`,
- type: `avg`,
- format: `currency`
- },
- totalPrice: {
- sql: `total_price`,
- type: `sum`,
- format: `currency`
- },
- shippingPrice: {
- sql: `shipping_price`,
- type: `sum`,
- format: `currency`
- },
- discountAmount: {
- sql: `discount_amount`,
- type: `sum`,
- format: `currency`
- },
- voucherAmount: {
- sql: `voucher_amount`,
- type: `sum`,
- format: `currency`
- }
- dimensions: {
- id: {
- sql: `id`,
- type: `number`,
- primaryKey: true
- },
- orderedAt: {
- sql: `ordered_at`,
- type: `time`
- },
- channelId: {
- sql: 'channel_id',
- type: 'number'
- },
- channelName: {
- sql: 'name',
- type: 'string'
- },
- });
- Preagg cube file
- cube(`OrdersPreAgg`, {
- extends: Orders,
- preAggregations: {
- main: {
- type: `rollup`,
- measureReferences: [count,totalPrice],
- dimensionReferences: [channelId,id],
- granularity: `month`,
- timeDimensionReference: orderedAt,
- external: true
- }
- }
- });
- Query made
- {
- "measures": [
- "OrdersPreAgg.count"
- ],
- "timeDimensions": [
- {
- "dimension": "OrdersPreAgg.orderedAt",
- "granularity": "month"
- }
- ],
- "filters": []
- }