Hello,
I am currently developing a custom connector using the Connector SDK. I am interacting with a REST API which returns data in XML only. Also no oData or ODBC capability, just straight REST API calls.
Our API allows us to call for a database schema via something like:
https://mydomain/[ApplicationID]/api/GetAppSchema&key=********** -- "Applicatoin ID" is asked for along with an API key via the connector.
This returns some XML which gives us all the database tables for this particular applicationID. Something like:
<app>
<tables>
<table>
<tablename>table1</tanlename>
<tableID>xxxxxxx</tableID>
</table>
<table>
<tablename>table2</tanlename>
<tableID>yyyyyyy</tableID>
</table>
...
</tables>
</app>
Then we create the nav table by parsing through this response. Then we create the tables, querying each one for it's data, something like:
url = https://mydomain/[TableID]/api/GetAllRecordsandColumns
for each table
My developer and I are having the following issues:
1) We can't seem to figure out how to how to supply via the connector a 'light' query for the nav view, but then query for all of the data ONLY in the tables the user selects in the table navigator. Is this possible? In larger applications with many tables, and tables with lots of rows X columns, it takes several minutes for the table navigator to load, and we really just want to give the users a preview of the data before the decide if they want to select it or not.
2) We are seeing our API queries get hit twice for every table in the database
- Once for NavTable/CreateTable
- Once when I select tables, and click "Load" -- it queries every table again, even if I didn't select them. We were scratching our heads for a while on this until we discovered that selecting the table was seen in Power Query as a navigation step from the source, which its determining is the entire application! So we have quite the dependency between our invididual tables, and the entire database... So it makes sense to us why it does this, but is there some way to build our queries to alleviate this dependency? Any advice how we could tell the table query to directly be the source of that data OR cache the source calls since they have already been made?
*At this time I cannot limit my tables/queries/columns. So I have to get all the tables from my schema call, and I need to query every table for all it's rows and columns. I need to exhaust this exercise first
**Disabling data the preview in the background/data load option doesn't do anything
^^Just wanted to get those out of the way as I see them suggested in a lot of posts. What I'm after is actually undetstanding how the connectors work intimately, to see the best ways to optimize what I'm doing.
Any ideas would be much appreciated!
Carlos
