ETL Capabilities and Create Missing Data


(Rohit Kumar) #1

Hi Techies,

I have data source which gives data for analytics but I need some more data which is not present in data source to design analytics , so I need to create it in Looker. The data I need to create by ETL has few sets of algorithm. I am mentioning the same below:

  1. I have some client which follow different date calendar like
    Client 1: Week start from Monday and fiscal month start from the same date and year.
    Client 2, Sunday and fiscal week and month and year start from the date we give for any sunday

So I want to make an calendar table where I just need to give one date lets say 4 Jan 2016 which is first monday of 2016 and first week will be 4 Jan to 10 Jan, fiscal year starts from 4 Jan 2016 and other date fields, like ytd, previous ytd etc will be calculated by using date functions.

So this kind of algorithm I want to write in look and generate the data which is not present in main data source but we create it in looker.

Please help me to know is it possible to do it if yes how.
I know it is is possible in Qlik which has its own ETL but I want to do the same things here


(Izzy) #2

I know that we have some customers who have created complex calendar table mappings for things like this exact situation (complex fiscal year setups), so maybe someone will hop in and speak to this-- For now I’ll just say that if you can build the calendar table with SQL, you could easily make it a derived table in Looker-- and that way the calendar data could be generated entirely in Looker instead of in a separate ETL flow.

Here’s also an example of a way to do this entirely in dimensions-- This example is for a 4-4-5 calendar, but it could potentially be modified to have different fiscal years: 4-4-5 Calendar Using Lookml