BI tools

We’ve tried to use PowerBI, but our IT team had limited knowledge of how to connect the data source and get the data we needed.  Especially to marry live training (webinar) attendance data with a user.  It was clunky and painful and we never got to adopt it really well.  I’m definitely wanting to get some sort of a way to get better data out of the system.

We use API heavily, mostly because we adopted Docebo years ago and a lot of the integrations were not yet available. In a basic sense the way we get data into PowerBI is through a custom database which is populated by API requests to Docebo. A setup like this gives you flexibility to link up with a lot of different systems. However, it does require a coder or team of coders depending on the data. It also requires keeping up with Docebo releases that make changes to API endpoints.

Hello @Cindy McElhinney 

Can you help me to understand how does Google data studio works in Docebo?

Thnx.

@abhijeet.dalvi Thanks for your question. Currently, it’s quite a manual process for me. I export data via a report in Docebo which is then brought in to a Google Sheet which is linked to my Google Data Studio Dashboard. Obviously the process is not real-time and that means updating the google sheets which is a bit time consuming and therefore I am guessing that this is probably not a good solution for some. It works for us because our dashboard does not need to be refreshed more than once a month. Eventually though, I want to learn how to use webhooks and using something like Zapier update rows in my Google sheet via the hooks to Docebo. I have a long way to get there   Tableau may be a good option for those with technical expertise. I love Tableau but for us it was like taking a “sledgehammer to a nail” as our needs are not that sophisticated at least for now. Anyway, here is a pic of my dashboard. I am in the process of revamping some of the information so you’ll see some of the data is not showing currently but this will still give you an idea of what’s possible. I haven’t been able to spend time on it due to another big project I’m on right now. It’s kind a pretty but not sure how functional it is given that it’s a bit labor intensive. Happy to have a convo with you if you need more detail, just private message me. 

Hello @Cindy McElhinney 

Thank you for such a neat explanation. 

I would love to know how to integrate Power BI using API’s as well. Does anyone know how to add it to the API Credentials? I’m currently doing the same as @Cindy McElhinney but with excel spreadsheets. easy once it’s set up but I now want data that I can’t get from just the reports.

I think there are a few primers that are out there in the subject - but I am tossing my name in this thread? So that if someone can list them out it would be good.

A consulting partner - eSkillz just had a discussion on using the APIs.
Part of me hates that i have dedicated my gray matter to other things like instructional design and LMS administration and practices.

I’m currently trying to work out how to authenticate the Power Bi to get API’s as it would be quite useful. Microsoft Azure is registered to get API’s and I can see that I need to use the link below with our subdomain to get an authorization token. But I have no idea where to put that link in Power BI? any ideas?

https://<yoursubdomain.docebosaas.com>/oauth2/token

Putting my two bits in because I would like to know this as well.

Or better yet...@Docebo...build in the ability to create dashboards and other data elements. :)

Hi @gcrawford88 and all

I believe this is what Docebo’s new DLA (Learning Analytics) module is all about. Costs extra, but might be more cost/resource effective than developing API integrations or doing manual stuff in Excel. 

Hi there,

New to Docebo and the forum. I came across this thread yesterday while looking for a solution to this problem. This thread is a year old, but I thought I’d post what I figured out for those interested. 

I’m using Power Query in Excel at the moment, but as it’s all M, it should work just fine in Power BI too.

First thing, in Docebo, in the the Admin Menu (gear icon), go to the API and SSO section. Under API credentials you’ll want to create an entry for your application. You’ll enter your Client ID. This can be whatever you want to call your app. “App” is a pretty loose term here. I’m just using a plain old Excel workbook. I just called my app “report”.

The dialog box will automatically generate a Client Secret for you. Fill in the other required fields. Copy the Client ID and Client Secret. These will be very important in a minute.

Under the Advanced Settings section, I checked the first 3 options. I know I’m not using the 4th option, JWT, so I left that turned off. It may or may not need all of the other 3, but it works with them on, so I’m just going with that for now.

Save all of that. And then make sure to click on the check mark icon on the entry you just created. It will turn green, meaning that it’s active. You can have entries that aren’t active, but those won’t work of course. It’s just not immediately obvious that you need to click on the check mark to activate.

OK, so now that that’s set up, it’s time to build your queries. I’m splitting this up into two queries. The first one gets the OAUTH token, and the second one actually gets the data I need. (In this case I’m just getting a list of our courses.) I’m splitting them up so that I can have multiple “data” queries that all use the same “token” query.

(The Code format on here looks terrible so I’m just leaving the code as plain text. Sorry if it’s hard to read.)

Token Query:

let
    //From the API Credentials area of Docebo
    oauthSite = "https://YOUR_URL.docebosaas.com/oauth2/token",
    clientId=”YOUR_CLIENT_ID",
    clientSecret = "YOUR_CLIENT_SECRET",

    //Gets user email and password from named ranges in workbook. Pass this in any way you need to.
    email = getValue("email"),
    pwd = getValue("pwd"),

    //Assemble the URL and convert to binary
    tokenURL = "client_id=" & clientId & "&" & "client_secret=" & clientSecret & "&" & "grant_type=password&scope=api&username=" & email & "&" & "password=" & pwd,

    binaryTokenURL = Text.ToBinary(tokenURL),
    
    //Call for the token
    tokenData = Json.Document(Web.Contents(oauthSite, [Headers = [#"Content-Type"="application/x-www-form-urlencoded"], Content=binaryTokenURL])),
    
    //Extract the token from the returned table
    token = tokenData[access_token]
in
    token

Data Query:

let
    // We use a custom URL, which is what I use here. Note that it may not be the same as the “basic” url that you used above to call for the token. I’m not sure what happens if you use the base url here, or vice versa, but I know this works.

    site = "https://YOUR_URL",
    
    ver = "/learn/v1/",
    page = "courses",
    endPoint = site & ver & page,
    
    //Note that “token” in the next line is a reference to the query above. It uses that result to pass to the end point and get your data.
    Source = Json.Document(Web.Contents(endPoint, [Headers=[Authorization="Bearer " & token, Accept="application/json"]])),
    
    data = Source[data],
    items = data[items],
    theTable = Table.FromList(items, Splitter.SplitByNothing(), null, null, ExtraValues.Error)
in
    theTable

I hope you find this useful. 

Welcome to the community @DanBrill and thank you so much for sharing this!  We have been trying to find a more elegant approach to connecting PowerBI to Docebo, I’ll give this a try in our Sandbox!

Thanks @DanBrill!

I’m not a trained programmer, but I’ve been fumbling my way through Power Query and Docebo so far. 

With the “email’ and “pwd” steps, I just used my Docebo credentials right in the query editor. I’m not sure what workbook you’re referring to and the “getValue” threw errors.  Using my own super-admin credentials seemed to get past the error.  Was that the correct move?

After that, I can’t get passed the “tokenData” step.  It asks to edit credentials and gives me the options of anonymous, windows, basic, Web API, organizational account.

Anonymous won’t work even though the username and password are accurate.  And if I get in with the others, it says that I need to use the anonymous credentials.

Any advice on what I’m doing wrong?

Thanks!

Hey @gogrizz 

getValue is just a function I use all the time that gets a value from a named range in Excel. It’s really useful for getting user inputs.

Let’s say I want to get the user’s email address. On a spreadsheet I just make a cell where they put their address. I name that cell “email”. And then from Power Query I can easily get that value to be used in any query.

Save this as a query called getValue:

let

    Source = (rangeName as text) => 

    Excel.CurrentWorkbook(){[Name=rangeName]}[Content]{0}[Column1]

in

    Source

Then in any other query I could get their address with getValue(“email”). 

This part won’t work in Power BI, it’s really an Excel thing. But that’s just how I’m pulling in the data. You can also just hard-code the email and pwd right in the query editor, or get it some other way. You should have no problem with a super-admin name and password.

Once you have those then they become part of the tokenURL and they get passed as part of the request in the tokenData step. 

Use Anonymous as all the credentials to get the token are built into the request itself.

Hope that helps,

Dan

Follow-up question for Dan or anyone else who can help:

The “TokenData” step in Dan’s query above is throwing errors that I can’t get past.

Initially, it’s asking me to connect.  When I click the “Edit Credentials” button, I can connect Anonymously, via Windows, Basic, WebAPI or an Organizational Account.

• Anonymous keeps throwing “We couldn’t authenticate with the credentials provided.  Try again”
• Docebo isn’t windows based.
• When I choose “Basic’, I login with my SuperAdmin account, but then step gives the following error   Web.Contents with the Content option is only supported when connecting anonymously.  And as mentioned, I can’t connect anonymously,
• I don’t have a “Web API Key”

For programming content, I’m in over my head.  Any suggestions?

Sorry - one more thing: on the Docebo API credentials page, for the App Redirect URI, I’m just using my docebosaas.com page.  I don’t think that’s correct, but I’ve tried and can’t figure out anything else.

Hi @gogrizz 

The anonymous connection is the way to go. Make sure the Client ID and Client Secret variables in the code are the same as you’ve set here in the Oauth setup:

 

For the Redirect URI I just put in our main corporate website. You could probably put in any generic page like that.

I tend to ignore the dialog box that Power Query gives you for API connections and just edit right in the advanced editor.

And for what it’s worth, I don’t have any formal programming training. I just keep trying stuff and I’m really stubborn. :-)

Hope that helps,

Dan

So we’ve ended up side stepping a lot of the issues we had with the api’s and just scheduling reports to be sent to an inbox then using power automate to put the excel file into a teams folder and that updates the information in power BI. Much easier to setup and much easier to maintain.

Hi ​@Lucy.blake 

I’m attempting to do this now. Do you have premium power automate to use the HTTP flow or do you use anther action to download the report from the link in the email? 

Thanks for any helpp you can provide :) 

Danielle