Forum Discussion

mburssens's avatar
mburssens
Frequent Visitor
8 years ago
Solved

Publish and schedule refresh for ServiceNow changes

hi all, 

I have configured a data source with the ServiceNow ODBC connector for serviceNow.

This is working fine on Power BI desktop. Data is received from the cloud when refreshed. However, this makes use of a locally installed ODBC driver provided by serviceNow.

When publishing this dataset to Power BI online, the refresh is disabled due to dependency on this local ODBC connection.

 

Is there a way to configure/upload ODBC cloud connection parameters so that a published report can be refreshed?

Or another way to retrieve change data from ServiceNow in Power BI online? Both are cloud services so there should be no need to pass through an on-premise ODBC via gateway.

 

thanks,

Maarten

  • mburssens's avatar
    mburssens
    8 years ago

    Hi all,

     

    Connecting via Service Now REST API url is a solution for this need. You can use web datasource in Power BI desktop to connect to this API. 

    Following link helps you to create REST API url (version specific, you need to check your servicenow version)

    https://docs.servicenow.com/bundle/geneva-servicenow-platform/page/integrate/inbound_rest/task/t_GetStartedAccessExplorer.html

     

    eg: URL for retrieving active change requests will look like this - https://<Server Name>/api/now/change_request?sysparm_query=active=true

     

    This one doesn't need a gateway since you are using cloud version of Service Now.

     

    It may not be a good option if you need to pull more than 10k rows. Usually Service now API has a timeout setting for 10k rows.

    If you need to pull more than 10k rows you need to load data in batches which you can do by modifying the power query.

     

     

5 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable
    8 years ago

    HI mburssens,

     

    >>Is there a way to configure/upload ODBC cloud connection parameters so that a published report can be refreshed?

    Maybe you can try to use service parameter, please check below link to know more about this:

    Using the Power BI Service Parameters to change connection strings (To possibly change between Dev,

     

    BTW, you said, you are using a local odbc driver, I think you need to confirm your device who install the on-premise gateway can connect to your datasource. Otherwise it is impossible to achieve refresh operation.(please double check your odbc connection string)

     

    >>Or another way to retrieve change data from ServiceNow in Power BI online?

    Do you mean modify the content from service connect pack? It this is a case, current only owner can edit template content pack.

    Author template content packs in Power BI

     

    Regards,

    Xiaoxin Sheng

     

    • mburssens's avatar
      mburssens
      Frequent Visitor
      8 years ago

      thanks for your reply Anonymous. 

      The issue that I have is linked to the fact that the ODBC connection (stored locally) is connecting to a cloud data source, but this is not getting noticed by Power Bi during the publish step.

      I have created a small diagram to explain the issue.

       

      Microsoft has released a ServiceNow service to get incident data from serviceNow. But there is no connector for change data

      https://docs.microsoft.com/en-us/power-bi/service-connect-to-servicenow 

       

      What we need is to be able to set up a similar service connection that retrieves ServiceNow data from other tables then just incidents. So we should be able to get the connection set up in the cloud. 

      The difference is clear when you check the refresh settings for incidents (working well, making use of the microsoft connector) and changes (not working because the local ODBC connection is not well converted during publish)

      incidents working well

      changes not working well 

      Do you know how we can solve this by changing the way we publish the ODBC connection, or by finding another way to get ServiceNow cloud data to Power BI cloud without the need to use a local component?

       

      thanks upfront

      Maarten

      • Anonymous's avatar
        Anonymous
        Not applicable
        8 years ago

        Hi mburssens,

         

        You can try to use below power bi template file to connect servicenow data source.

         

        Regards,

        Xiaoxin Sheng

  • mburssens's avatar
    mburssens
    Frequent Visitor
    8 years ago

    Hi all,

     

    Connecting via Service Now REST API url is a solution for this need. You can use web datasource in Power BI desktop to connect to this API. 

    Following link helps you to create REST API url (version specific, you need to check your servicenow version)

    https://docs.servicenow.com/bundle/geneva-servicenow-platform/page/integrate/inbound_rest/task/t_GetStartedAccessExplorer.html

     

    eg: URL for retrieving active change requests will look like this - https://<Server Name>/api/now/change_request?sysparm_query=active=true

     

    This one doesn't need a gateway since you are using cloud version of Service Now.

     

    It may not be a good option if you need to pull more than 10k rows. Usually Service now API has a timeout setting for 10k rows.

    If you need to pull more than 10k rows you need to load data in batches which you can do by modifying the power query.

     

     

  • antonstorozhuk's avatar
    antonstorozhuk
    Helper V
    3 years ago

    Hello mburssens

    You may connect Servicenow to Power BI and setup scheduled refreshs with the help of our add-on - Power BI Connector for ServiceNow:

    We have a handy User and Admin Guides for your convenience, but feel free to reach out support team at [email protected] if you need any assistance.

     

    Thank you.

    Regards,

    Anton

    ===
    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.