Here is a custom adapter for Power BI REST api, it is not fully tested, but seems to be working well for the last couple days in one of my projects. Due to the some limitations in the api, each adapter should have it’s own data set, as api doesn’t allow us (yet) to add new tables to existing data sets. Also, we are not even able to discover which tables the specific data set has (kind of weird actually, but remember, it is still in preview). Anyway, the adapter is ready for potential upgrade. As we are not able to get any information about tables in data set, we are not able to find what columns are and what their types. It will make the setup process a bit difficult, but still easy to understand. Once you connect a data source to the adapter and move to the column mapping, it will map all your source columns to the columns, which will be in Power BI. The first time you run it, it will check if the data set exists, if not, it will create one with a table and columns of type as your source columns. If you already have dataset and tables inside, just make sure that your destination columns names match those in Power BI. The clean solution would be to create a new DataSet manually, with all your tables, and after just map SSIS adapters to those tables. In this PoC we will make it simple – each adapter will create its own dataset and table inside.
Here is a connection Manager:
- Client ID. You get the client id when you setup your native app in Azure AD.
- Set FIFO to “true” if you want to remove old record automatically when it reaches 200,000 rows.
- Redirect URL. You get it from the app in Azure AD.
- Power BI datasets. You can change this one with your tenant or leave it like this.
Here is a source code: https://github.com/nicksav/SSISAdapterForPowerBI
Stay tuned as I am going to publish a really cool staff about real time dashboard for Cisco Call Manager.