1️⃣ GET-Data from the ITONICS 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.

Table of contents

1. Setting up the external access token in ITONICS
2. GET data from the ITONICS OData API (with query examples)
3. GET data into PowerBI
4. Embed a PowerBI report into ITONICS 

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}/{Endpoint}?$filter={Query}

• systemUrl: this is the identifier of your ITONICS Innovation OS
• spaceUri: this is the identifier of your workspace
• Endpoint: there are currently three endpoints, to GET data from, available
  
  • "/Elements": this is the identifier of content as an information carrier - different to, for instance, user data. This element type supports pagination and $filter 
  • "/ElementTypes": this will return all element type information for every element type defined in the workspace (query parameters are not supported for this endpoint)
  • "/File/${FILE_URI}": this will return (download) the file attached to the FILE_URI.
    It can be found via the GET-elements endpoint, if an attachment property is set, the right URL will show up in the value property of the respected field.
• 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.

Endpoint "/ElementTypes"

This endpoint provides all information in the format shown below, to the element types themselves, that exist in the workspace associated with the API key.

export interface ODataElementTypesResponse {
  context: string;
  elementTypes: Array<ODataElementTypes>;
  count: number;
}


export interface ODataElementTypes {
  uri: string;
  version: number;
  name: string;
  description: string;
  fields: Array<ODataField>;
}

export interface ODataField {
  uri: string;
  version: number;
  name: string;
  type: string;
  properties: Array<ODataFieldProperty>;
}

export interface ODataFieldProperty {
  uri: string;
  value: string;
}

Endpoint "/Elements"

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

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) 
• rawFieldValues, setting this query parameter to 1 will output the raw field values, this might be important for RTE fields.
  "&rawFieldValues=1"

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 

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)

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 100 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 100 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

Endpoint "/File"

Files uploaded to the platform can be returned (downloaded) with their fileURI.
This fileURI and the complete URL can be found with GET-elements and then in the respective property.

GET data into PowerBI

After following all steps in this section, you will have one table in PowerBI containing all requested elements with all properties.

Setting up the API credentials as PowerBI parameters

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:

• API key (authorization key), see here
• URL of the OData API (URL column of the External Access tab)
• [Optional custom] Filter query — this defines the information that is to be requested

Now in PowerBI, in the report that you want to use, follow these steps:

1.  Go to Home tab > Transform data > Transform data
   
2. In the new modal, go to Home tab > Manage Parameters > New Parameter
   Now we will set up the API authorization and the filter query, which defines what data will be requested.
   Set up the following parameters:
   1. URL
      The URL which can be found in the External Access column in the ITONICS system.
      
   2. FILTER_QUERY
      Set the filter query to define what data will be requested, to just GET every element in the workspace, copy paste the text below and paste it in the respective field.
      Current value-field: /Elements?$top=100
      
   3. API_KEY
      URL and api key represent your authorization the ITONICS system, you will need to replace the [custom_api_key] token in the screenshot below, with your API key that you created and that is stored in the External Access settings.
      The final text in the Current Value field in PowerBI could be something like this: ApiKey A7fQ9mX2ZkR4sB8NwC0EJt6DVaL1H3uP5YgKcM

      
3. fnGetElementsPage
   Now all custom parameters are set and your setup should look something like this (see image below) with only the three parameters set.
   To finish the setup we now need to create the function which creates, executes and receives the API GET request.
   
   
   1. In PowerBI: Navigate to the same window where you created the parameters.
   2. On the left side query navigation, press right-click > New Query > Blank Query, this will create a new M Power Query
   3. Change its name to: fnGetElementsPage
   4. Press right-click > Advanced Editor, an empty window, like shown in the image above, will be displayed, paste the following code in this editor window.

   5. (pageUrl as text) as nullable record =>
      let
          // 1. Identify the static Base URL. 
          BaseUrl = URL, 
          
          // 2. Break down the incoming dynamic URL (nextLink) and the Base URL
          UrlParts = Uri.Parts(pageUrl),
          BaseParts = Uri.Parts(BaseUrl),
          
          // 3. Calculate the Relative Path (The difference between the full path and base path)
          // We remove the Base Path from the Full Path to get the segment relative to the root
          RelativePath = Text.TrimStart(Text.Replace(UrlParts[Path], BaseParts[Path], ""), "/"),
          
          Response = try
              Json.Document(
                  Web.Contents(
                      BaseUrl, // <--- This is now STATIC (assuming 'URL' is a constant/parameter)
                      [
                          RelativePath = RelativePath,
                          Query = UrlParts[Query], // <--- The dynamic parameters go here
                          Headers = [
                              Accept = "application/json",
                              Authorization = API_KEY
                          ],
                          ManualStatusHandling = {400,401,403,404,500}
                      ]
                  )
              )
          otherwise
              null
      in
          Response

4. Elements
   Now we need to setup the code that iteratively executes this function up until every paginated element data page is in PowerBI. And to then transform this data into a single table.
   1. In PowerBI: Navigate to the same window where you created the parameters and the function fnGetElementsPage.
   2. On the left side query navigation, press right-click > New Query > Blank Query, this will create a new M Power Query
   3. Change its name to: Elements
   4. Press right-click > Advanced Editor, an empty window, like shown in the image above, will be displayed, paste the following code in this editor window.

   5. let
          // 1. Define URL and Query
          FirstPageUrl = URL & FILTER_QUERY,
      
          // 2. Run Pagination
          Pages = List.Generate(
              () =>
                  let
                      First = fnGetElementsPage(FirstPageUrl),
                      HasElements = First <> null and Record.HasFields(First, "elements")
                  in
                      [
                          Elements = if HasElements then First[elements] else null,
                          NextLink = if HasElements and Record.HasFields(First, "nextLink") then First[nextLink] else null,
                          ErrorOccurred = not HasElements
                      ],
              each [Elements] <> null,
              each
                  let
                      NextPage = if [NextLink] <> null then fnGetElementsPage([NextLink]) else null,
                      HasElements = NextPage <> null and Record.HasFields(NextPage, "elements")
                  in
                      [
                          Elements = if HasElements then NextPage[elements] else null,
                          NextLink = if HasElements and Record.HasFields(NextPage, "nextLink") then NextPage[nextLink] else null,
                          ErrorOccurred = not HasElements
                      ],
              each _
          ),
      
          // 3. Extract and Combine Data
          PagesTable = Table.FromRecords(Pages),
          
          // Combine all list items from all pages into one big list
          AllElementsList = List.Combine(List.RemoveNulls(PagesTable[Elements])),
      
          // 4. Convert to Table
          ElementsTable = Table.FromList(AllElementsList, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
      
          // 5. ROBUST Expansion (Scans all rows for all potential columns)
          // Get a list of all unique field names across all rows, not just the first one
          AllColumnNames = List.Distinct(List.Combine(List.Transform(ElementsTable[Column1], Record.FieldNames))),
      
          ExpandedElements = Table.ExpandRecordColumn(
              ElementsTable,
              "Column1",
              AllColumnNames
          )
      in
          ExpandedElements

      
      
   6. Press right-click on this newly created Elements Query, make sure that the following two options are checked/enabled:
      1. Enable load
      2. Include in report refresh
   7. Now you should be able to see a preview of the full dataset
5. Close & Apply
   The last step is to press Close & Apply, now the dataset should load into the regular PowerBI report editor.
   If no data is loading, go to Home tab > Refresh > Schema and data, this should reload all queries with the updated dataset.

Learn in the next section, how to embed this report into ITONICS, how to set up daily data refreshes and more.

Embed a PowerBI report into ITONICS & scheduled data refreshes

Embedding a report is as easy as pasting the report embed URL into the respective input field in the ITONICS Innovation OS. How to get to this link in the first place and setting up scheduled data updates (PowerBI paid license required), is the hard part. To only find out, where to paste the report embed URL, see the steps below, to find out how to get there (publishing & scheduled updates), see section Setting up a PowerBI report to be embeddable in ITONICS & scheduled data refreshes.

Embedding a report into ITONICS via report embed URL

Follow these steps closely.

1. Enter the specific workspace where the report should be located in.
2. Go to Report in the More Tools section
3. In the top left, select + Embed new Report
   1. Select a Title
   2. Paste the Report URL (like: https://app.powerbi.com/view?r=[...])
   3. Press Embed Report
4. Now the report should display.
5. 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.

Setting up a PowerBI report to be embeddable in ITONICS & scheduled data refreshes

To embed a report, it has to be published to a workspace in the PowerBI Cloud first. After that, to schedule daily data updates, a PowerBI Professional license has to be associated with that workspace.

1. Publishing the report to a workspace
   
   1. Make sure there (app.powerbi.com) is a workspace already set up, where the report can be published/saved to.
   2. In PowerBI: go to Home tab > Publish > select a destination (workspace)
      1. When finished press Open '[report_name].pbix' in Power BI, continue from there
2. Get the embed URL
   In the PowerBI online service (app.powerbi.com):
   
   1. In the top left navigation bar, go to File > Embed report > Website or portal
   2. Copy Link to embed this content
      1. Continue as outlined above
3. Setting up scheduled (daily) data refreshes
   In the PowerBI online service  (app.powerbi.com):
   1. Go to the respective workspace > semantic model > settings
      
   2. Data source credentials > Edit credentials
      
   3. Configure Paginated PowerBI report
      
      
      1. Authentication method: Anonymous
      2. Privacy level settings for this data source: Organizational
      3. Skip test connections: checked ✅
      4. Press Sign in
   4. Refresh cycle
      1. Go to the respective workspace > semantic model > settings > Refresh
      2. Configure semantic model refresh
         Set time zone, set frequency (weekly, daily, etc.), and recipient of failure notifications

Now the report is scheduled to receive data updates in the specified frequency, at the specified time.