{"id":16924,"date":"2020-10-15T09:33:15","date_gmt":"2020-10-14T20:33:15","guid":{"rendered":"http:\/\/help.psoda.com\/?page_id=16924"},"modified":"2024-01-31T01:16:11","modified_gmt":"2024-01-31T01:16:11","slug":"microsoft-bi-integration","status":"publish","type":"page","link":"https:\/\/www.psoda.com\/help\/microsoft-bi-integration\/","title":{"rendered":"Microsoft BI integration"},"content":{"rendered":"
Psoda data can be used in Microsoft BI to create interactive visualizations and business intelligence capabilities with their interface.\r\n
\r\n
\r\nTo get data into Microsoft BI, first we need the API key<\/b> of a user who has access to the data you want.\r\n
\r\n
\r\nTo do this navigate to that user and select the three-dot options menu. In there, click on \u201cAPI key\u201d<\/b> as shown in Figure 1.\r\n\r\n

\"\"<\/a><\/p>\r\n

Figure 1 – User options menu API key<\/strong><\/p>\r\n\r\n

\"\"<\/a><\/p>\r\n

Figure 2 – User API key popup<\/strong><\/p>\r\n\r\nCopy or note down<\/b> the API key found in the popup (keep this API key safe. It could be used by a malicious user to access all of your data).\r\n
\r\n
\r\nNext, we need the object ID<\/b> of the organisation\/programme\/project that you want to use the data from. Navigate to the organisation\/programme\/project that you want to use and go to the details tab.<\/b> There you will find the object ID as shown in Figure 3.\r\n
\r\n
\r\nCopy or note down<\/b> this object ID.\r\n\r\n

\"\"<\/a><\/p>\r\n

Figure 3 – Organisation details tab object ID<\/strong><\/p>\r\n\r\nOpen Microsoft BI on your computer.\r\n
\r\n
\r\nClick the \u201cGet data\u201d<\/b> dropdown and select \u201cWeb\u201d<\/b> as shown in Figure 4.\r\n\r\n

\"\"<\/a><\/p>\r\n

Figure 4 – Microsoft BI “get data” dropdown<\/strong><\/p>\r\n\r\nClick on \u201cAdvanced\u201d<\/b> in the popup.\r\n\r\n

\"\"<\/a><\/p>\r\n

Figure 5 – Microsoft BI “get data” popup<\/strong><\/p>\r\n\r\nFigure 5 shows what we need to enter in this popup to get Psoda data into Microsoft BI.\r\n
\r\n
\r\nFirst, we need to create the URL of the data we want from Psoda. Use this URL as a guide: \r\n
\r\n
\r\nhttps:\/\/www.psoda.com\/rest_api\/v1\/objects\/INSERT_OBJECT_ID<\/b>\/descendants.json?classes=lesson<\/b>&fields=obj_id,description,category<\/b><\/span>\r\n
\r\n
\r\nCopy and paste<\/b> the above URL into \u201cURL parts\u201d<\/b> on the Microsoft BI popup as shown in Figure 5.\r\n
\r\n
\r\nIn the URL, where it says \u201cINSERT_OBJECT_ID\u201d, change that text to the object ID noted down earlier.<\/b>\r\n
\r\n
\r\nIn the URL, where it says \u201clesson\u201d, change that text to whichever type of data<\/b> you would like to get. For example, this could be \u201caction\u201d and that would return all the actions on this organisation\/programme\/project.\r\n
\r\n
\r\nIn the URL after it says \u201cfields\u201d, there is a list of fields<\/b> that will be included for each of the data. Currently it will get all the lessons and each of their object IDs, descriptions, and categories. You can change these to whatever you like. For example, if you want the title of the lesson you can add \u201c,title\u201d to the URL.\r\n
\r\n
\r\nOnce you are happy with what data you want, the last step is to include the API key noted down earlier. Under the option \u201cHTTP request header parameters\u201d in the Microsoft BI popup, enter the text \u201cAPI-Key\u201d<\/b> as shown in Figure 5. In the field next to that, paste the API key<\/b> noted down earlier.\r\n
\r\n
\r\nClick \u201cOk\u201d and you will now be able to use your Psoda data in Microsoft BI!\r\n\r\n

Pagination using API V2<\/h3>\r\n
\r\nTo use version 2 of our API a little more configuration is required.\r\n
\r\n
\r\nTo start, create a new blank query.\r\n
\r\n

\"\"<\/a><\/p>\r\n

Figure 6 – Blank Query button in PowerBI<\/strong><\/p>\r\n
\r\n
\r\nPaste the text below into the blank query, replace YOUR_API_REQUEST_HERE with your API request URI. Replace YOUR_API_KEY_HERE with your API key. Name this function something related to the query, in this case we will use fGetDescendants.\r\n
\r\n
\r\n= (Pagesize as number)=>\r\n
\r\nlet\r\n
\r\n Source = Json.Document(Web.Contents("YOUR_API_REQUEST_HERE"&"&pageSize="&Number.ToText(Pagesize), [Headers=[#"API-Key"="YOUR_API_KEY_HERE"]]))\r\n
\r\nin\r\n
\r\n Source\r\n
\r\n
\r\n

\"\"<\/a><\/p>\r\n

Figure 7 – Standard request query<\/strong><\/p>\r\n
\r\n
\r\nCreate another blank query, and paste the text below in. Replace YOUR_API_KEY_HERE with your API key. Set the name of this query to fNextPage<\/b>.\r\n
\r\n
\r\n= (Link as text)=>\r\n
\r\nlet\r\n
\r\n Source = Json.Document(Web.Contents(Link, [Headers=[#"API-Key"="YOUR_API_KEY_HERE"]]))\r\n
\r\nin\r\n
\r\n Source\r\n
\r\n
\r\n

\"\"<\/a><\/p>\r\n

Figure 8 – Blank Query button inside query editor<\/strong><\/p>\r\n
\r\n
\r\n

\"\"<\/a><\/p>\r\n

Figure 9 – Next Page Query with correct function name<\/strong><\/p>\r\n
\r\n
\r\nNow create one more blank query, this will be the one that retrieves all of your data from Psoda. Paste the text below in, replace FIRST_FUNCTION_HERE with the first function you have created. In this case it will be fGetDescendants\r\n
\r\n
\r\n= List.Generate(()=>\r\n
\r\n[Result = if List.IsEmpty(FIRST_FUNCTION_HERE(50)[data]) then null else FIRST_FUNCTION_HERE(50)[data], Next= FIRST_FUNCTION_HERE(50)[links][next]],\r\n
\r\neach [Result]<>null,\r\n
\r\neach [Result = if [Next]=null then null else if List.IsEmpty(fNextPage([Next])[data]) then null else fNextPage([Next])[data], Next= if [Next]=null then null else if fNextPage([Next])[links] is list then null else fNextPage([Next])[links][next]],\r\n
\r\neach [Result])\r\n
\r\n
\r\n

\"\"<\/a><\/p>\r\n

Figure 10 – Final request and all data returned from API<\/strong><\/p>\r\n
\r\n
\r\nYou should now be getting all of your data from our API.<\/div>\r\n

Related topics<\/h4>\r\n