Data Transformation preview and scheduling queries

Ryan Kearbey shared this question 2 months ago
Answered

Hello,

I have two tables in my database:

  • 5-min aggregated table with 5-min timestamps where the max timestamp is typically about 15 minutes behind real-time
  • Hourly aggregated table with hourly timestamps and is typically 2 or 3 hours behind real-time

I setup a Data Transformations on both tables. My questions are as follows:

  1. I see the data preview in my 5-min transformation but not in the hourly. What are the start/end times of the data preview query?
  2. If I schedule my transformations to run periodically, what Frequency should I use for the 5-min and hourly schedules? Today, I'm using 60 min frequency for my 5-min transformation schedule. I guess this question is similar to my first question: what is the start/end times of the scheduler's query? /e3348a2dd96ee121bc61146e07a3e5dc

Comments (6)

photo
1

Hi Ryan,

Thanks for reaching out to support with your issue.

I checked this out and was a little surprised for the lack of granularity - it would be nice to be able to define a specific time at the minute leve here. I can see we have a dev task for this -

Client is trying to set a schedule to run every x hours.. however also trying to set a start date.
e.g Run every 2 hrs, starting at 2pm AEST.

At this point in time you are unable to set a start time, it just starts once you set up the broadcast. 
There are 2 ways around this right now;

1. Create your schedule at a time that is going to give you the correct start time (e.g. create your broadcast at 2pm)
2. Create multiple daily broadcasts to cater for your broadcasts at each hour. 

Does this sound like what you're looking for? If so I can add you as an affected client for increased visibility of this Idea. You can also "like" and subscribe to the existing post -

https://community.yellowfinbi.com/topic/report-broadcast-every-2-hours-at-a-specific-time

Maybe the workaround will work for you?

Thanks,

Eric

photo
1

Hi Eric, thanks for the information; however, my particular questions are around the Data Transformation and not the Report scheduler.

It's important because I only want to fetch new data from the past hour. My transformations fetch data from one db table, add a calculated field, and write it into a separate Postgres db. So I really need to know the underlying start/end times for both the data preview and the scheduler queries.


Thanks

Ryan

photo
1

Hi Ryan,

Thanks for the clarification. In the transformation schedule there are these options -


71f3bcf3a487287ac559d678d9eb0876

There is no set time these can be scheduled; when these run specifically is based on system startup time and other factors like the task queue. Also no way to "work around" with multiple schedules on transformations it appears. You can see the current runtime of the transformation in schedule management -

a18ca0310432a33800b315cec7e11f2a


This would be the most accurate way to see the specific time of a transformation schedule at this time. If you would like the added feature of setting a specific interval for these 'x minutes' ETL processes, I can get something submitted to devs on your behalf, just let me know.

That said, you could theoretically use web services to schedule a run of the transformation at a specific time, maybe this a potential solution? See here for more info -

https://wiki.yellowfinbi.com/display/yfcurrent/Web+Services+Directory


In regards to the missing preview data, are you able to see preview data if you run the transformation manually? Do you have the "run on preview" setting enabled?

Thanks,

Eric

photo
1

Hi Eric, thanks for the reply. Yes, I'm familiar with the Schedule Management panel. I guess if I look at my Transformation Summary and history, I can see my transformation running every 60 min. In the example screenshot, you can see my latest transformation ran at 10:25 and pulled 5.4M rows. Before that, it ran at 9:25. My question is: are those 5.4M rows only from the last time to current time (between 9:25 - 10:25)?

/547d272be7b8851f2a026b79462b98d0

photo
1

Hi Ryan,

Not sure whether the records showing in the "rows output" here are all new or not; The number of rows processed in the ETL would depend on what the transformation is doing. If you need help designing the transformation for your specific requirements I can pass this along to an account manager that can schedule a consulting engagement, just let me know.

Thanks,

Eric

photo
1

Hi Eric,

I think I know who our account manager is. Let me try to reach out on my own. You can close this ticket for now.


Thanks,

Ryan