Create Operational Reports

(Pranitha) #1


Create operational reports that needs to be refreshed Hourly/Daily/Weekly/Monthly and has to return the same rows between refreshes

For the same requirement ,in MicroStrategy there is an in-memory concept called intelligent cubes which stores the data in its cache and returns the same data between the intervals/refreshes. Please suggest any equivalent functionality in looker to build this type of looks.

Database :Teradata

Note: There are around 350 reports/looks that needs to be built in looker using the same logic.

Thanks in Advance.


(Izzy) #2

We call this concept “Persistent Derived Tables” in Looker, or PDTs. Basically, you create a derived table with a SQL statement or based on an existing explore, and grant it “persistence” by telling it how often to refresh that data. If you say persist_for: 24 hours the table will return the exact same rows for 24 hours and then refresh at the 24 hour mark.

You can also tie a PDT to a datagroup, which is just a centralized way of setting these refresh schedules. You could have a datagroup called monthly_refresh and have it set to refresh monthly, and then set PDTs to trigger with that datagroup— They would then refresh monthly.

Check out this docs page, specifically the Adding Persistence section.


(Dawid) #3

Is the 24 mark calculated from the moment it’s saved or the moment this pdt will be used for the first time in query?


(Izzy) #4

It depends on how you add persistence.

With persist_for, the very first time a query is run that uses the PDT, the table is created and the countdown begins. Before the first query using it is run, the table doesn’t exist and the countdown similarly does not. After 24 hours, the table will be regenerated the next time a query is run that requires it, in which case the countdown will begin again.

With a sql_trigger_value, the table will be constantly available— The PDT regenerator typically runs every 5 minutes, though this can be configured. This means that every 5 minutes after the PDT is created & deployed to production, Looker will check if the trigger query has changed. In this case, if you set a trigger value to trigger every 24 hours, it would start the countdown from the moment it’s saved & deployed.

1 Like