Autotask OData Custom View For PowerBI

[pme94]

I’m new to Invantive Cloud and have the Autotask OData connector setup and working well with Power BI.

I am now looking at ways of reducing the rows I need to call.

I would like to create a custom view that Power BI can use. I have put the following code in Startup SQL and cleared the cache, but no matter what I do I cannot see the view in the Power BI datasource list of tables:

create or replace force view TicketsWithSLAs
as
select *
from   Autotask.Tickets.Tickets@at t
left 
outer
join 
Autotask.ServiceLevelAgreementResults.ServiceLevelAgreementResults@at s
on     t.id = s.ticketID

This single view will allow me to reduce rows by approximately 40% so I’d really like to get it working if possible.

[forums]

Custom views can take some time to appear on https://bridge-online.cloud due to extensive caching.

It is recommended to test it carefully on Invantive Cloud using the UniversalSQL editor as described in Using custom database views for SQL and BI tools.

Next recommended step is to reset the cache on the Invantive Bridge Online server. NOT on Invantive Cloud, since that has no effect on Invantive Bridge Online.

In case that does not resolve it, please let us know by an answer.

Sometimes the service needs to be restarted to clear in-memory caches too.

[pme94]

Thank you, I had followed both of those links previously but reset the wrong cache. Now done it in Bridge Online and works as expected.