Setting up an Odata connection for Cornerstone API Reporting connector

[juankypanky]

Dear Power BI team.

I'm trying to connect Power BI with the API Cornerstone HCM using OAUth 2.0. This vendor provides a TXT file to import in Power BI desktop with the setup config to make the connection. I have all the necessary data: the id client, the secret token, API's endpoint, etc. I followed the steps of this article in their support site: 

https://csod-external.force.com/supportcentral/s/article/How-do-we-integrate-Power-BI-with-the-Repor...

Something was wrong in point 9 (Option 2) beacuse if I check "Ignore privacy levels" the screen appears over and over again, and it seems like there is a lack of data or configuration that the article does not include. I opened a support ticket, but the answer is that there are no support for third-party vendors.

Anybody has tryed to make a similar connection to the Cornerstone API or similar?

Thanks in advance! 

[V-lianl-msft]

Troubleshooting Tips

1. sessionToken in the URL is case sensitive.

2. Make sure the API and sessionToken are active and enabled via the Edge API page.

3. If you get the error: "Expression.Error: The 'Authorization' header is only supported when connecting anonymously. These headers can be used with all authentication types: Accept, Accept-Charset, Accept-Language, Cache-Control, If-Modified-Since, Referer" this means the OData authentication type is not set to Anonymous. To modify the setting, navigate to File > Options and settings > Data source settings. Find your data source URL from the list, click Edit, edit the credential type to be Anonymous, and click Done.  Try again.

See if it will help:https://github.com/cornerstoneondemand/CSOD-Power-BI-Integration-Utility/blob/master/README.md 

[juankypanky]

Good afternoon.

Thank you very much for your help. All three points were tested before and is not working yet. The github repository is related to an old API call with username and password, but now with OAuth 2.0 a token is generated with the idclient and an idsecret. The script is working but fails at the last line: 

Source=OData.Feed(odata_url,[#"Authorization" = authToken],null),

and a screen asking for credentials "we were unable to authenticate you with the credentials provided. Try again". If I click "connect" this screen appears over and over again.

[RichardR]

I was able to connect succesfully using the method in the first article you mentioned, https://csod-external.force.com/supportcentral/s/article/How-do-we-integrate-Power-BI-with-the-Repor....

I originally had the same error but fixed it by ensuring the scopes and permissions are set correctly in Cornerstone. In my case I was missing the scope for the Metadata.

https://apiexplorer.csod.com/apiconnectorweb/apiexplorer#/apidoc/7f1beda8-ec8a-41ad-a615-417d27d8e56...

[juankypanky]

Hi Richard,

I thought that the problem was the same as you mention in your message... if you try to connect to the reporting API, you should specify the "read" term at the end of the view i.e. vw_rpt_users:read.

Unfortunately I still can't connect... I can connect and obtain data using the simulation of the "Webservice practice lab" at https://csodapi.com/... but not trhough Power BI... maybe I need to try with other scopes in other environment. I will tell you soon if I can advance with the correct way.

Thank you very much for your help,

JC

[RichardR · Accepted solution]

Can you confirm you have the two scopes below selected for the App on the Cornerstone side. My issue was similar where it was working in Postman but not in PowerBi until I enabled the scope reportingmetadata:view. Another thing you could try for troubleshooting is setting the app to a user that has full permissions in Cornerstone with all the scopes enabled. If it works then it would just be narrowing it down to the permissions you are missing.

[juankypanky]

Yeah!! Good news!!

Now I can retreive the views of the scope correctly. As you told me, the "reportingmetadata:view" is mandatory to get access to the views of the scope. I really appreciate your help Richard, so thank you very much to solve a problem that CSOD neither PowerBI solved it.

Kind regards,

JC

[juankypanky]

Adding some information to the message. I have this custom view:

let
    client_id = "xxxxxxxxx",
    client_secret = "yyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyy",
    corporation_host = "zzzzzzz",
 
    //do not edit section below
    base_url = "https://" & corporation_host & ".csod.com/services/api",
    token_url = base_url & "/oauth2/token",
    odata_url = base_url & "/x/odata/api/views/",
 
    query = [
        clientId = client_id,
        clientSecret = client_secret,
        grantType = "client_credentials",
        scope="all"
        ],
    oauthRequest = Json.FromValue(query),
    Response = Web.Contents(token_url, [
        Content = oauthRequest,
        Headers=[#"Content-type" = "application/json",#"Accept" = "application/json"], ManualStatusHandling = {400}
        ]
    ),
    Parts = Json.Document(Response),
    authToken = if (Record.HasFields(Parts, {"error", "error_description"})) then
                    error Error.Record(Parts[error], Parts[error_description], Parts)
                else
                    "Bearer " & Parts[access_token],
 
    Source=OData.Feed(odata_url,[#"Authorization" = authToken],null),
    //end of do not edit section
    User_View = Source{[Name="vw_rpt_applicant",Signature="table"]}[Data]
in
    User_View

I get the error in line "Source=Odata..." with a message like "we were unable to authenticate you with the credentials provided. Try again".

The "authToken line" is correct (I can see it reviewng the steps), and the scope to do the search inside "vw_rpt_applicant" table, but I don't know why I get the error (anonymous credentials).

Thanks!

JC