0

I have Calendar table and I want to join this table with multiple time dimensions present in parent cube(Employee). For example.

cube(`Employee`, {
  sql: `SELECT * FROM \`DEMO\`.\`Employee\``,
  joins: {
    Calender: {
      relationship: `hasMany`,
      sql: `${Employee}.startDate = ${Calender}.full_date`
    }
  },
  measures: {
    count: {
      type: `count`,
      title: `Total Count`
    }
  },
  dimensions: {
    name: {
      sql: `${CUBE}.\`name\``,
      type: `string`
    },
    age: {
      sql: `age`,
      type: `string`
    },
    startdate: {
      sql: `${CUBE}.\`startDate\``,
      type: `time`,
      title: `Start Date`
    },
    enddate: {
      sql: `${CUBE}.\`endDate\``,
      type: `time`,
      title: `End Date`
    }
  }
});

How can I join the startDate as well as endDate column dynamically OR on demand. i.e. If my query timeDimensions object contains endDate then it should be automatically join with that column.

I already refered below links.

Following are my expectations.

Query: 1

{"measures":["Employee.count"],"timeDimensions":[{"dimension":"Employee.startDate"}],"dimensions":["Employee.startDate","Calendar.day","Calendar.month","Employee.endDate"]}

Expected Output:

{
    "Employee.startDate": "2020-01-08T00:00:00.000",
    "Employee.endDate": "2020-05-01T00:00:00.000",
    "Calendar.day": "2020-01-08T00:00:00.000",
    "Calendar.month": 01,
    "Employee.count": 122
}

Query: 2

{"measures":["Employee.count"],"timeDimensions":[{"dimension":"Employee.endDate"}],"dimensions":["Employee.startDate","Calendar.day","Calendar.month","Employee.endDate"]}

Expected Output:

{
   "Employee.startDate": "2020-01-08T00:00:00.000",
   "Employee.endDate": "2020-05-01T00:00:00.000",
   "Calendar.day": "2020-05-01T00:00:00.000",
   "Calendar.month": 05,
   "Employee.count": 122
}
Harshal Nathe
  • 95
  • 1
  • 8
  • You can't join dynamically and most likely you don't need that. Could you explain what're you trying to achieve and how do you want your query look like in more detail? – Alex Vasilev Jul 09 '20 at 20:21
  • Thanks for the reply @Alex! I want to join the Calendar table with the multiple date columns. In the above cube, I have a start date and end date dimensions with me and I want to perform a dynamic join on my cube with these two columns. I have updated my question in more detail. If there is no need for this then can you please suggest another way to do this. – Harshal Nathe Jul 16 '20 at 07:40
  • @HarshalNathe It would be helpful if you can elaborate more on a business use case here. – Pavel Tiunov Sep 04 '20 at 21:18
  • @PavelTiunov Basically, the use case is that users want to view the data(from **fact** table) on the charts as per Calendar year and also as per the Fiscal Year of the organization. So for this, we build a Calendar table and we joined this with the date column of the fact table on which the user wants to apply a time dimension filter. But in some cases, the user wants to perform an analysis on data, with multiple date columns so in this scenario we have to join the calendar table twice with the fact table. and has to use the same column from the Calendar table with different alias names. – Harshal Nathe Sep 06 '20 at 17:22
  • This is the query that we have to build now: ```SELECT sales.amount, Sales_Date.Day_Name AS Sales_Date_day_of_week, PaymentDate.Day_Name AS PaymentDate_day_of_week FROM Sales sales INNER JOIN Calendar AS Sales_Date ON Sales_Date.Calendar_Date = sales.Sales_Date INNER JOIN Calendar AS PaymentDate ON PaymentDate.Calendar_Date = sales.PaymentDate``` If this is the case then we have to join calendar table those many times that many date column user wants to use for analysis. – Harshal Nathe Sep 06 '20 at 17:26

0 Answers0