SUCCESS FACTORS: HOW TO QUERY MORE THAN 1000 ROWS OF A DATASET
Olga Dontsova
Senior Power BI Developer | Microsoft Power BI Community Super User | Trainer | Visual Storyteller | Data Enthusiast
One of the requirements I really had to sweat with was getting more than 1000 rows out of Success Factors using Web API connector. In this article I am going to walk you through the function that is going to save you some good amount of time trying to figure out how to retrieve all the rows from SF tables.
Loading large datasets can be very time-consuming, that is why many services including Success Factors rely on Pagination, a technique typically used for creating smaller data subsets from the large datasets by breaking them into Pages.?In this case, the server doesn’t return all the records at once but loads the data incrementally limiting the result to only 1000 records per Page. ?
There are different kinds of Pagination supported by Success Factors, but we are going to use cursor pagination (paging=cursor) in our function.
The whole concept works as follows:
If you have a table with multiple pages (i.e., more than 1000 rows in your dataset), requesting the first data page will provide you with a link to the next page of your data, the so called _next URL.
Example of _next URL:
https://apiXXX.successfactors.eu/odata/v2/EmployeeTime?$filter=timeType%20eq%20%270200%27&$paging=cursor&$format=json&$skiptoken=eyJzdGFydFJvdyI6MTAwMCwiZW5kUm93IjoyMDAwfQ==
or
https://apiXXX.successfactors.eu/odata/v2/EmpEmployment?$paging=cursor&$format=json&$skiptoken=eyJzdGFydFJvdyI6MTAwMCwiZW5kUm93IjoyMDAwfQ==
This _next URL contains the skiptoken property at the end. The skiptoken value shall be passed onto the subsequent data requests to get the next page of your dataset. So, basically you must create a loop where you repetitively call the _next API using the value of skiptoken until you get all the data.
Before you start writing your function, connect to the data and retrieve the data of the first page.?
Step 1: Connect to SF through Web API connector.
Adjust your code. The Source code shall contain a link to Success Factors, relative path to a selected Table and a Query indicating the paging type and format.
Example:
Json.Document(Web.Contents("https://apiXXX.successfactors.eu/",
[RelativePath="odata/v2/EmpEmployment", Query= [#"$paging"="cursor",?#"$format"="json"]]),?65001)
?If you have additional filter options that you would like to include into the query, then list those under the query parameters.
Example:
Json.Document(Web.Contents("https://apiXXX.successfactors.eu/",[RelativePath="odata/v2/EmployeeTime",Query=[#"$paging"="cursor",#"$filter"=("timeType?in?'0200'"),?#"$format"="json",#"$skiptoken"=skiptoken]]),?65001),
?Step 2: Click on the Record to retrieve the Results and _next URL.
Step 3: Convert the results to a table.
? Step 4: Transpose a table.
?Step 5: Promote the Headers:
?Step 6: Now it is time to retrieve the skiptoken value from the column _next. Add the skipvalue in M code. ?Advanced Editor ->
Quelle?=?Json.Document(Web.Contents("https://apiXXX.successfactors.eu/",[RelativePath="odata/v2/EmpEmployment",Query=[#"$paging"="cursor",?#"$format"="json"]]),?65001),
??d?=?Quelle[d],
??#"In?Tabelle?konvertiert"?=?Record.ToTable(d),
??#"Transponierte?Tabelle"?=?Table.Transpose(#"In?Tabelle?konvertiert"),
??#"H?her?gestufte?Header"?=?Table.PromoteHeaders(#"Transponierte?Tabelle",?[PromoteAllScalars?=?true]),
??skipvalue?=?Text.AfterDelimiter(#"H?her?gestufte?Header"[__next]{0},?"=",?2),
?If you have a filter in your query you would need to take the third Position:
?skipvalue?=?Text.AfterDelimiter(#"H?her?gestufte?Header"[__next]{0},?"=",?3),
Step 7: Add the Final step by Combining the Promoted Headers step with the function.
IMPORTANT: Logically this step shall be implemented after you have created the function CallNextPage.?
???d?=?Quelle[d],
??#"In?Tabelle?konvertiert"?=?Record.ToTable(d),
??#"Transponierte?Tabelle"?=?Table.Transpose(#"In?Tabelle?konvertiert"),
??#"H?her?gestufte?Header"?=?Table.PromoteHeaders(#"Transponierte?Tabelle",?[PromoteAllScalars?=?true]),
??skipvalue?=?Text.AfterDelimiter(#"H?her?gestufte?Header"[__next]{0},?"=",?2),
??Final?=?Table.Combine({#"H?her?gestufte?Header",CallNextPage(#"skipvalue")}),
?As a result, we can see here that on the first snapshot there are 2 pages of data, while on the second there are 6 pages. Logically, the last page doesn’t have a _next link to the next page because there is no page after that.
Here is the M-Code for the Final is not complete, it accounts only for the case when there is more than 1 page of data and is going to fail if it doesn’t find the _next column, let us adjust it for both scenarios.
领英推荐
Example:
?if?Table.HasColumns(#"H?her?gestufte?Header",?"__next")?then??Table.Combine({#"H?her?gestufte?Header",@CallNextPage(#"skipvalue")})?else?#"H?her?gestufte?Header"
If the table that we received at the step of Promoted Headers has a column _next then combine that table with the tables resulting from calling the CallNextPage function by passing skiptoken values as a parameter, otherwise go ahead with the result that you received at Promoted Headers Step.
?Then, we expand first the lists then the records and voi-la you get the table with all the data.
CREATING A FUNCTION
If we look back at Step 5, at this point we have one next link with skiptoken value, we need to plug in that value into our Source URL and get the next _next link with the value pointing to the next page and so on.
?To do this let us write the function, which is gonna be our first step in the Applied Steps. You shall navigate to the ?Advanced Editor and adjust your code from there. ?
IMPORTANT: Logically these steps you have to do before Step 7 so you have the function to reference in Step 7 (called Final on the snapshots).
Let us create a function called CallNextPage. We have to make sure that the function iterates through all of the available pages, i.e. skiptoken values to return the full dataset.
To start with, we need to recreate first 6 steps of the Applied Steps ?in the function.
?Step 1: Define the Page referencing the link you used to connect to the Success Factors. Add #"$skiptoken"= skiptoken?after the format parameter.
?(skiptoken)?=>
????let
???????Page?=Json.Document(Web.Contents("https://apiXXX.successfactors.eu/",[RelativePath="odata/v2/EmpEmployment",Query=[#"$paging"="cursor",??#"$format"="json",#"$skiptoken"=skiptoken]]),?65001),
?Step 2: View the returned results.
????pagedata?=?Page[d],
?Step 3: Convert the results to a table.
????ToTable?=?Record.ToTable(pagedata),
?Step 4: Transpose a table.
????TransposedTable?=?Table.Transpose(ToTable),
?Step 5: Promote the headers to get two columns:?results and _next
????PromotedHeaders?=?Table.PromoteHeaders(TransposedTable,?[PromoteAllScalars=true]),
?Step 6: Define the skipvalue by retrieving it from the values from the column _next
????#"skipvalue"?=?Text.AfterDelimiter(PromotedHeaders[__next]{0},?"=",?2),
If you have a filter in your query you would need to take the third Position:
????#"skipvalue"?=?Text.AfterDelimiter(PromotedHeaders[__next]{0},?"=",?3),
?Step 7: What shall be the output? Same as in the step Final/Step 7 in the Applied Steps. If the table that we received at the step of PromoteHeaders has a column _next then combine that table with the tables resulting from calling the CallNextPage function by passing skiptoken values as a parameter.
?????Output1?=?if?Table.HasColumns(PromotedHeaders,?"__next")?then??Table.Combine({PromotedHeaders,@CallNextPage(#"skipvalue")})?else?#"PromotedHeaders"
???????in
????Output1
?Now we go back to Applied Steps and add there Step 7.
So, the whole code will look like that:
?(skiptoken)?=>
????let
???????Page=Json.Document(Web.Contents("https://apiXXX.successfactors.eu/",[RelativePath="odata/v2/EmpEmployment",Query=[#"$paging"="cursor",??#"$format"="json",#"$skiptoken"=skiptoken]]),?65001),
????pagedata?=?Page[d],
????ToTable?=?Record.ToTable(pagedata),
????TransposedTable?=?Table.Transpose(ToTable),
????PromotedHeaders?=?Table.PromoteHeaders(TransposedTable,?[PromoteAllScalars=true]),
????#"skipvalue"?=?Text.AfterDelimiter(PromotedHeaders[__next]{0},?"=",?2),
????Output1?=?if?Table.HasColumns(PromotedHeaders,?"__next")?then?Table.Combine({PromotedHeaders,@CallNextPage(#"skipvalue")})?else?#"PromotedHeaders"
???????in
????Output1
?Have a great time with HR Reporting ??
Senior Human Resources Project Manager at Tenneco
1 年Thanks for writing this! It was very useful in learning how to do this. I managed to adapt your code to my setup, however because of the data volume (about 70k records) it just times out when trying to refresh. Is it because of the method itself or am I doing something wrong here? Any advice on how to make this work for larger datasets? Thanks!!