ETL Input Query and PARTITION BY

Enrico Menotti
Enrico Menotti Member Posts: 34
in Birst

There are sporadic mentions of PARTITION BY used in ETL Services Input Queries, right after the ORDER BY statement. I have been unable to figure out what that exactly does. There is a snippet of documentation at:

https://docs.infor.com/birst/2024.x/en-us/birstdataandmodels/Data/TransformETL/ETL_Services_Overview.html?Highlight=PARTITION

By browsing through the old communities, I found a couple of mentions, most notably this one:

https://community.infor.com/infor-birst/f/admin---administration-user-management/36228/cross-apply-in-scripted-source/95099#95099

All of this is related to a need that I have to do some windowing, alike T-SQL's OVER (PARTITION BY …). I have also found:

https://community.infor.com/infor-birst/b/legacy-blog-posts/posts/how-to-build-this-functionality-in-birst

which mentions the following URL:

https://community.birst.com/thread/8912

This is dead and I found no archive over the Internet. Does Infor have any copy of old Birst forums?

Best Answer

  • Riina Silverberg
    Riina Silverberg Member, Staff, Staff Moderator Posts: 13
    Answer ✓

    Enrico,

    Birst partition by has nothing to do with windowing. It was an attempt to speed up parallel processing in some specific use cases way back.

    Riina

Answers

  • Riina Silverberg
    Riina Silverberg Member, Staff, Staff Moderator Posts: 13

    Hello again, Enrico! I spoke about this one, too, with the team. Partition by is originally from the (old) Classic Birst help, and it should no longer be part of the documentation. Partition by does not enable parallel execution in Birst.

    We are currently working on refreshing and improving the documentation, but sometimes mistakes happen. My apologies that this one slipped through the review process. We will remove it from the documentation asap.

    Riina

  • Enrico Menotti
    Enrico Menotti Member Posts: 34

    Hi Riina,

    I understand the docs are under review, that's clear. My point here is that "PARTITION BY" is indeed accepted by Birst in ETL Input Queries. And, as you also said, it does not seem to do anything.

    For my final understanding: if I got it right,

    • PARTITION BY was an old command from the old Classic Birst, where it was used to distribute a script execution over parallel processes (documentation not very clear honestly, but anyway);
    • PARTITION BY is now still accepted (I suppose for backward compatibility reasons) but does not do anything anymore. Apparently, parallel processing is managed automatically by the system;
    • PARTITION BY has nothing to do with the homonymous command from SQL, which is used for windowing functionalities.

    May you be so kind and confirm the above with the Birst team, in particular the last point?

    Thanks a lot!

    Enrico

  • Enrico Menotti
    Enrico Menotti Member Posts: 34

    Thanks Riina and I will mark that as an answer. Before that, would you be so kind and let me know also this:

    "Does Infor have any copy of old Birst forums?"

    (please see my original question).

    Enrico

  • Riina Silverberg
    Riina Silverberg Member, Staff, Staff Moderator Posts: 13

    Hi Enrico!

    I've asked around and it seems we don't have a copy of the old Forums anywhere.

    Riina

  • Enrico Menotti
    Enrico Menotti Member Posts: 34

    Ugh. That's not a good policy IMHO. Anyway.

    Thanks a lot for your commitment!

    Stay well,

    Enrico

Categories