Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Reply
powerbiharsha
Frequent Visitor

Ways to connect Power BI with SAP Success factor

Hi Team,


I didn't come across any official connection between SAP Success Factor with Power BI. Please help us out is there any connection that we can establish between with SAP Success Factor to generate the reports in Power BI.

 

Looking forward to your response.

 

 

5 REPLIES 5
robindk
Frequent Visitor

I had this problem myself and ended up having to create the custom Solution to Get this from the Odata API. If interested I can share the functions for you to copy into your instance.

 

you will need basic understanding of how to generate the url for the endpoint (I use postman to test before adding the url to PowerBI) the rest of the magic is done in the functions inside PowerBI.

Please send a copy that would be very helpful.

See bottom for the code**

 

This code defines a Power Query custom function that takes a single argument RelPath (as text), which represents the relative path to a resource on a SuccessFactors OData v2 API endpoint. The function retrieves data from the endpoint, which is paginated, and returns the retrieved data as a table.

 

The function begins by defining a variable Token that holds the value of an authentication token that will be used to authenticate the API request. It then sets the baseurl variable to the base URL of the API endpoint.

 

The next step is to use the Web.Contents() function to make a GET request to the API endpoint, passing in the baseurl and RelPath variables as arguments. The Headers argument is used to set the Accept and Authorization headers of the request, and the RelativePath argument is used to set the relative path to the resource. The response from the API is parsed as JSON using the Json.Document() function, and the d property of the resulting record is assigned to the initReq variable. The __next property of initReq is used to extract the URL of the next page of results, which is assigned to the nextUrl variable. The results property of initReq is assigned to the initValue variable.

 

The function then defines a nested function called gather that takes two arguments: a data list and a url string. The data list is used to accumulate the results of successive API requests, and the url string is the URL of the next page of results to be retrieved. The gather function is called recursively until there are no more pages of results to retrieve. At each iteration, gather makes a new API request to the specified URL, extracts the __next URL and the results data from the response, and appends the results data to the data list. It then checks if there is a __next URL in the response, and if there is, it calls itself with the data list and the __next URL as arguments. If there is no __next URL, gather returns the final data list.

After defining the gather function, the function converts the initReq record to a table using the Record.ToTable() function, and pivots the table using the Table.Pivot() function to create a table with columns for each distinct property of the API response. The resulting table is assigned to the Pivot_Columns variable.

The function then checks if the Pivot_Columns table contains a __next column using the Table.ColumnNames() and List.Contains() functions. If there is a __next column, the function calls the gather function with the initValue list and the nextUrl as arguments, and assigns the resulting list to the outputList variable. If there is no __next column, the initValue list is assigned to outputList.

 

Finally, the function converts outputList to a table using the Table.FromRecords() function, and returns the resulting table as the output of the function.

 

For this to work, the function assume that you will always use the $inlinecount=allpages in the RelPath. Example input for RelPath: Position?$inlinecount=allpages

 

 

// Define a Power Query custom function that retrieves data from a SuccessFactors OData v2 API endpoint
(RelPath as text) =>
    // Define a variable to hold the authentication token
    let
        Token = Authentication{0}[Value],
        // Define the base URL of the API endpoint
        baseurl = "https://api2.successfactors.eu/odata/v2/",
        // Store the relative path to the resource
        RelPath = RelPath,
        // Make an initial API request to the specified resource and parse the response as JSON
        initReq  = Json.Document(Web.Contents(baseurl, [Headers=[#"Accept"="application/json", Authorization=Token],RelativePath=RelPath]))[d],
        // Extract the URL of the next page of results and the initial results data from the response
        nextUrl = Text.AfterDelimiter(initReq[__next],"v2/"),
        initValue= initReq[results],
        // Define a nested function to recursively gather all pages of results
        gather=(data as list, url)=>
            let
                // Define the base URL of the API endpoint
                baseurl = "https://api2.successfactors.eu/odata/v2/",
                // Make an API request to the specified URL and parse the response as JSON
                newReq=Json.Document(Web.Contents(baseurl,[Headers=[#"Accept"="application/json", Authorization=Token],RelativePath=url]))[d],
                // Extract the URL of the next page of results and the results data from the response
                newNextUrl = Text.AfterDelimiter(newReq[__next],"v2/"),
                newData= newReq[results],
                // Append the results data to the accumulated data list
                data=List.Combine({data,newData}),
                // Convert the response to a table
                Converttotable = Record.ToTable(newReq),
                // Pivot the table to create columns for each property of the API response
                Pivot_Columns = Table.Pivot(Converttotable, List.Distinct(Converttotable[Name]), "Name", "Value", List.Count),
                // Check if the response contains a "next" URL
                Column_Names=Table.ColumnNames(Pivot_Columns),
                Contains_Column=List.Contains(Column_Names,"__next"),
                // If there is a "next" URL, recursively call the "gather" function with the accumulated data list and the new "next" URL
                check = if Contains_Column = true then @gather(data, newNextUrl) else data
            in
                check,
        // Convert the initial response to a table
        Converttotable = Record.ToTable(initReq),
        // Pivot the table to create columns for each property of the API response
        Pivot_Columns = Table.Pivot(Converttotable, List.Distinct(Converttotable[Name]), "Name", "Value", List.Count),
        // Check if the response contains a "next" URL
        Column_Names=Table.ColumnNames(Pivot_Columns),
        Contains_Column=List.Contains(Column_Names,"__next"),
        // If there is a "next" URL, call the "gather" function with the initial results and the initial "next" URL
        outputList= if Contains_Column= true then @gather(initValue,nextUrl) else initValue,
        // Convert the accumulated results to a table
        expand=Table.FromRecords(outputList)
    in
        // Return the final table as the output of the function
        expand

 

 

In addition, you can use the following code to create the function that will get and refresh the Authorization Token - if you are like me an prefer to keep this part seperate - this will authenticate using the OAUTH2, where you need to include the header parameters in a seperate variable refrerred to as insertion below

 

let
  // Set the URL for the API endpoint
  path = "https://api2.successfactors.eu",
  
  // Try to authenticate using the specified parameters
  authenticationResponse = try Json.Document(Web.Contents(path, [Headers=[#"Content-Type"="application/x-www-form-urlencoded"],Content=Text.ToBinary(#"insertion"), RelativePath="/oauth/token"])) otherwise null,
  
  // Convert the authentication response to a table and transform its column types
  transformedColumns = Table.TransformColumnTypes(Record.ToTable(authenticationResponse), {{"Value", type text}}),
  
  // Replace any error values in the table with null
  replacedErrors = Table.ReplaceErrorValues(transformedColumns, {{"Value", null}}),
  
  // Promote the headers of the table to become column names and change its column types
  changedColumnType = Table.TransformColumnTypes(Table.PromoteHeaders(Table.Transpose(replacedErrors), [PromoteAllScalars = true]), {{"access_token", type text}, {"token_type", type text}, {"expires_in", Int64.Type}}),
  
  // Add a custom column to the table
  addedCustom = Table.AddColumn(changedColumnType, "Value", each [token_type] & " " & [access_token]),
  
  // Remove all columns except the custom "Value" column and transform its column types
  #"Transform columns" = Table.TransformColumnTypes(Table.SelectColumns(addedCustom, {"Value"}), {{"Value", type text}}),
  
  // Replace any error values in the table with null
  #"Replace errors" = Table.ReplaceErrorValues(#"Transform columns", {{"Value", null}})
in
  // Return the resulting table
  #"Replace errors"

 

 

Anonymous
Not applicable

We are looking for this connectivity as well.

Would be really helpful if we have any connector out there for us to connect to success factors seemlessly.

Anonymous
Not applicable

I would be extremely interested in this option.

For now we are using a semi-direct connection, using BizX Online Report Desginer that exports semi-processed data to SFTP and than PBI takes it from there.

 

However I do not think this is possible at all, if you look at Plateau Report Designer, it cannot be connected to SF Production instance, so I guess the same rule applies for 3rd party applications

Helpful resources

Announcements
July 2024 Power BI Update

Power BI Monthly Update - July 2024

Check out the July 2024 Power BI update to learn about new features.

Top Solution Authors
Top Kudoed Authors