1️⃣ GET-Data from the ITONICS Innovation OS

The ITONICS Innovation OS allows you to synchronize its data with any other application. Via the ITONICS API, you can extract data and, for instance, send it to a business intelligence tool, and embed a report again in your Innovation OS.

Synchronizing data from your ITONICS Innovation OS with the other tools in your landscape eases the flow of information and provides you with the option to analyze your data in great detail. Basically, you connect the information from ITONICS with other providers, providing seamless data and tool integration.

Read on to learn:

• 1 | Setting up the external access token in ITONICS
• 2 | GET data from the ITONICS OData API (with query examples)
• 3 | Example: GET data into PowerBI for custom reports
• 4 | Example: Embed your PowerBI report in ITONICS again

Setting up the 'External Access Token' in ITONICS

To extract content, you need to create an External Access Token first for the workspace from which you want to GET data. 

As an Application Admin, navigate to your user profile in the bottom left corner. Find the entry “Embed & External Access” in the menu.

By clicking on it, you land on the organization settings page. In the Embed & External Access tab (1), click on the Create button (2) in the bottom right corner. Add an API Key Name (3) that best represents the connection you want to build, e.g., "Power BI".

Now, choose the Workspaces (4) from which you want to GET data. As a last step, select the one of the following two types (5):

• embed: With embed, you will be able to embed reports into the ITONICS Innovation OS.
• or external integration: With external integration, you will be able to extract data from the ITONICS Innovation OS.

Once you hit “create”, you will see a table, consisting of the names of all external access token(s), types, API keys, status, workspace allocation(s), and the validity.

GET data from the ITONICS OData API (with query examples)

To extract data, you need:

• the API key (column 3 in the list of the external access tokens as specified above) - this ensures that only authorized users can retrieve data, and
• the URL of the OData API (column 5 in the list of the external access tokens as specified above) - this defines the system and the workspace from that you extract data
• the filter query - this defines the information that you want to extract

The typical structure of an API query will have a form as follows:

https://{systemurl}.io/rest/external/odata/v1/{spaceUri}/Elements?$filter={Query}

• systemurl: this is the identifier of your ITONICS Innovation OS
• spaceURi: this is the identifier of your workspace
• Elements?: this is the identifier of content as an information carrier - different to, for instance, user data 
• query: the syntax of your filter query (see below)

The information until the Elements?$filter part, you can copy from column 5 in the list of the external access tokens as specified above.

Examples of filter queries

A filter query typically consists of three parts:

• the ITONICS property you want to filter for (this is defined individually by the Workspace administrator),
• the operator how the results should match, and
• the values

1. List of operators, meaning, and an example:

• eq, equals, $filter=elementType eq 'Idea' (gets all content of type idea)
• ne, not equals, $filter=elementType ne 'Risk' (gets all content that is not of type risk)
• contains, $filter=contains(elementType,'tech'), gets all content that is of any type containing 'tech' in their titles 
• gt, greater than, $filter=createdOn gt '2023-06-25 16:00' (all content created after '2023-06-25 16:00) 
• ge, greater than or equal, $filter=createdOn lt '2023-06-25 16:00' and createdOn ge '2023-06-03 12:00' (all content created before 2023-06-25 16:00 and at or after 2023-06-03 12:00)
• lt, less than, $filter=createdOn lt '2023-06-25 16:00' (all content created before '2023-06-25 16:00) 
• le, less than or equal, $filter=createdOn lt '2023-06-25 16:00' (all content created at or before '2023-06-25 16:00) 

A list of more operators can be found here.

You will see that you can also combine multiple filter criteria by using logical operators, such as, and, or, not 

2. List of properties, meaning, and an example:

• elementType, type of the element, $filter=elementType eq 'Idea' (gets all content of type idea)
• createdBy, the content creator, $filter=createdBy eq 'jane.innovation@itonics-innovation.com' (all content from the user jane.innovation@itonics-innovation.com)
  
• updatedBy, the person last updated the content, $filter=updatedBy ne 'jane.innovation@itonics-innovation.com' (all content that was not updated by the user jane.innovation@itonics-innovation.com)
• createdOn, the time content was created, $filter=createdOn gt '2023-06-25 16:00' (all content created after '2023-06-25 16:00) 
• updatedOn, the time content was last updated, $filter=updatedOn gt '2023-04-05 16:12' (all content last updated after '2023-06-25 16:00) 
• status, the publication status (archived/draft/published), $filter=status eq 'draft' (all content with the publication status draft)
• label, the title of content, $filter=contains(label,'Technology') (all content that has 'Technology' in its title)

3. Curation options

Further, you have the option to manipulate the data extraction by sorting, pagination, and result limitation.

• Sorting Results: You can sort the results of your filter queries using the $orderby parameter. By default, sorting is in ascending order, but you can specify the order explicitly: $filter=elementType eq 'Technology'&$orderby=label asc

• Pagination: Results are returned in batches of 24 by default. To navigate through paginated results, use the $skiptoken provided in the response. This token will direct you to the next batch of results: {"nextLink": "https://{systemurl}.io/rest/external/odata/v1/{spaceUri}/Elements?$skiptoken=abc123"}

• Result Limits: If you need more than the default 24 results, use the $top parameter to specify the number of records to retrieve: $filter=elementType eq 'Idea' and createdOn gt '2023-06-15 12:00' and status eq 'archived'&$top=100

3 | Example: GET data into PowerBI for custom reports

To GET the data to Power BI, open the PowerBI desktop App. Start creating a new blank report. To further process the data, you need to connect PowerBI with your ITONICS Innovation OS.

In this step, you will need:

• the API key (column 3 in the list of the external access tokens as specified above) - this ensures that only authorized users can retrieve data, and
• the URL of the OData API (column 5 in the list of the external access tokens as specified above) - this defines the system and the workspace from which you extract data
• the filter query - this defines the information that you want to extract

To enter the credentials, find the entry "Get data" in the PowerBI home menu (1) and select "blank query" (2).

PowerBI opens now the Power Query Editor. Here, you need to set the three parameters as described above. To do so, open "Manage Parameters". Add the following three as new parameters.

1. Add the URL: Provide the name (URL), and a description (OData API Endpoint), set the type to any, and enter the URL (column 5) from the external access token list. Make sure to add /Elements? to the URL. Your URL should look like this: https://{systemurl}.io/rest/external/odata/v1/{spaceUri}/Elements?

2. Add the filter query: Provide the name (FILTER_QUERY), and a description (The filter query to get the element data), set the type to any, and enter your filter (e.g., $filter=createdOn gt 0 to retrieve all content from the workspace)

3. Add the API key: Provide the name (API_KEY), and a description (The API key used for authorization), set the type to any, and enter the key (column 3) from the external access token list. Use “ApiKey” as Prefix. Your API Key Parameter should be like this: ApiKey hC6fwSXYEQqQK9g6iqWcOS68qcE42KOOaM20suimqO4AgQ0i2  

Once you set your parameters, select Query 1 from your sidebar (note: we renamed this to ITONICS (3) in the screenshot below). Enter the following predefined M query template into the empty field labeled 'f(x)' and hit enter (4). 

We have created this M query template to help you integrate data from an OData API into a Power BI Query. This template will pick up the three parameters we have just set (URL, FILTER_QUERY, and API_KEY) as variables. If you have configured these parameters correctly, you should not need to alter the query itself. 

ITONICS M Query Template

= let TablesWithDate = Table.FromList(Json.Document( Web.Contents(   URL & FILTER_QUERY,   [       Headers = [       Accept = "application/json",           Authorization = API_KEY       ]   ] ))[elements],         Splitter.SplitByNothing(), null, null, ExtraValues.Error), RecordTable = TablesWithDate[Column1], GetRecordFields = List.Distinct(List.Accumulate(RecordTable, {}, (s,c)=> s&Record.FieldNames(c))), CombinedTableExpanded = Table.ExpandRecordColumn(TablesWithDate, "Column1",GetRecordFields, GetRecordFields) in CombinedTableExpanded

The ITONICS element should show up in the preview as soon as you confirmed the query. Once you have extracted the values you need for your report you can click on "Close & Apply" on the upper left corner.

Your extracted properties should now show up in the Data section and you can start building your Reports. (5)

Example: Embed your PowerBI report in ITONICS again

After creating a report based on the ITONICS data in any other application, you can also embed this report back into your ITONICS Innovation OS.

To embed the report, click "Report" in the main navigation of your ITONICS Innovation OS.

If you want to embed your first report, hit “Embed Report”.

If there is already a report created, you can add another one by clicking on the name of the currently shown report. A dropdown menu opens and you will find “Embed new Report” (1). 

In the embed report modal, you need to enter a title and a report URL. The URL either needs to come from Power BI, Google Data Studio, Oracle, or Tableau. If you want to embed from another Business Intelligence Platform, please contact us via support@itonics.io. 

You will also find the option to "Make publicly available". When you share with everyone, every workspace user with permission granted to view embedded reports finds the name of this saved view in their main menu under reports and can access it.

Per default, all user roles have permission granted to view reports.

Click on “Embed Report” to create the report.