1

For an ETL reporting system, is it normal for the total execution time for a pull of 15 minutes with no data to be similar to a 24 hour pull with data?


I had expected that the total time for ETL when there is NO data would be shorter but that is not the situation between a 15 minute and 24 hour pull. But I must confess that I know nothing about the internals of the T and L phases within a report server.

Can someone shed light on whether the T and L phases are typically fixed in duration (up to a point)?

3
  • I found this dba.stackexchange.com/a/64324/163203 and want to keep the link for later investigation. Might be relevant.
    – D-K
    Jun 1, 2021 at 13:41
  • For anyone that considers this question opinion based: The concrete portion is in regards to whether the total execution time for a pull of 15 minutes with no data can be similar to a 24 hour pull with data. The answer from Piotr shows that it certainly can be. Perhaps I can reword the question to better reflect this.
    – D-K
    Jun 2, 2021 at 18:18
  • I wanted to share some information on this saga. I posted my saga here: dba.stackexchange.com/a/298377/163203
    – D-K
    Aug 22, 2021 at 0:50

3 Answers 3

2

There's nothing specifically quantifiable about the abstract concepts of Transform and Load, only their concrete implementations are measurable. To be able to comment on your case, we'd need to know specifically what your Transformation and Loading processes are actually doing. Obviously some Transformations can take multitudes longer than others.

But generally speaking, the amount of data being processed definitely should affect the overall runtime of an ETL process. If there's a significant difference in the amount of data between a 24 hour timeframe vs a 15 minute timeframe, yet your ETL process is executing at roughly the same average runtime for both cases, then something is definitely suspect, and that's not normal.

Even if an index scan is occuring in both cases, if there's a significant difference in the amount of data, the total runtime should certainly reflect it. The runtime of an index scan is linear (generally speaking) based on the number of rows in the index.

0
2

I would also add few things about Power BI.
Often in a Power BI models you will use "import data" mode. In this case when you update even few rows in your source data Power BI storage engine will create a brand new full copy of all source data (or if the process is optimized just specific partitions). Power BI uses the same engine like non-updatable columns store indexes, so the whole index partition needs to be rebuild after every change.
You can read more about power BI refresh here: https://learn.microsoft.com/en-us/power-bi/connect-data/refresh-data.
Usually you will not use partitions smaller than one day, so it is expected that refreshing 15 minutes or 24h will cause rebuild of the same partitions and this phase of ETL will take similar amount of time.
Of course it is just one of the phases of the ETL process, but often the longest one.

3
  • But as I reread your answer today - I'm understanding that the partition size, which is typically not smaller than one day, is a possible design and therefore explains the behavior I am seeing. Interesting! Thank you.
    – D-K
    May 29, 2021 at 14:25
  • During my next agile sprint I will be investigating but it is unlikely I will be able to mark this as the answer for at least 2 weeks.
    – D-K
    Jun 2, 2021 at 18:12
  • So a few weeks have gone by. What I now know is that this really isn't power BI. It is simply an SSIS package extraction issue. It turns out that ONE of the tables does not have a timestamp column and the 'whole' table is brought over every single time. This is 4 million rows of 3 columns of small data. The problem is that the 'lookup' action had the caching option disabled. It took 40 minutes for these same 4 million records to always be processed regardless of the time range being pulled. I'm going to create a separate answer. Thank you for your time on this question.
    – D-K
    Jul 19, 2021 at 19:28
1

The answers from J.D. and Piotr are useful and contain worthwhile data but unfortunately not the real answer for this problem. That is not their fault.

As I started digging into this I found that this is simply an SSIS package extraction issue. At the time I did not have enough knowledge to dig into the SSIS integration report dashboard to understand what I was seeing.

The final step I needed to take was opening the project within visual studio and seeing the SSIS toolbox designer showing the multitude of steps. Learning how the process works. Very interesting and powerful!

I finally arrived at a table that was being pulled (extracted) in its entirety because it lacks a timestamp column. This table contains 4 million rows of 3 small columns and the SSIS logic uses a 'lookup' action to decide whether the report database needs to be updated or inserted.

This SSIS toolbox lookup action had the in memory cache option disabled ! Yikes!

It was taking 40 minutes to process this table every single time regardless of how many minutes the original extraction was set for.

Power BI has nothing to do with this. My apologies for the confusion.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.