Justin Hylton on 11 Jul 2022 14:07:13
Surrogate integer keys often make the data models more efficient. However, creating them using the current Table.AddIndexColumn() causes query folding to break. There may be a way to get the data server to include the row number from a query folded step, which could be used as a surrogate integer key while maintaining query folding.
- Comments (2)
- Merged Idea (1)
RE: Add an "Add Index Column" type function that Query Folds
Currently a Table.AddIndexColumn() breaks the query folding towards a SQL database. This should be possible to implement. In it's simplest form this could be done with:
select
row_number() OVER(ORDER BY (select null) asc) as row
from Table
I understand there might be problems with eventual sort order proceeded by a Table.Sort(), but I'm sure the PQ team will figure out a solution for this. I see two alternatives: 1) incorporate the sort order in the Over(ORDER BY..., or 2) add an extra argument to the Table.AddIndexColumn() representing the sort order
Why is this needed: Creating data models with facts and dimensions out of flat files needs a key, and not using Data Flows, the data source is red many times and adding the key each time is not efficient
RE: Add an "Add Index Column" type function that Query Folds
This seems like a no-brainer. Translating "Add Index Column" to:rownum() +nso that the index column can start at any chosen integer....