Hi - we utilize the Microsoft Power BI service to build and host Datasets (data models) that are basically SQL Server Analysis Services cubes (OLAP). We want to use Matillion to programmatically query our models to extract measure calculations as datasets for export to other systems. We have a database query component with a SSAS plug-in installed (from CData) that we already use to query other on-premise SSAS databases. In the case of the Power BI service, I'm not sure what the proper parameter settings/configurations should be to support query; we have Power BI XMLA end points enable for external connections, however, the database query connector throws an error that the URL for the end point is not properly configured. Has anyone worked with the Power BI service in this way?
And for further reference, it is possible to query the Power BI datasets externally using tools like DAX Studio so it is possible, just need the proper configurations to work with the Matillion database query component and CData SSAS plug-in.
Hi @Singularity
Were you able to figure this one out?
@azucena.coronel I haven't tested yet but my plan is to utilize this driver from CData (https://www.cdata.com/drivers/powerbixmla/jdbc/); I previously installed an SSAS driver from CData into my Matillion instance with success and I believe that some of the core drivers in Matillion are from CData.
I'll do some testing and will report back any findings :)
Hi @Singularity ,
Have you made any progress on using CData SSAS plug in to query Power BI datasets?
@Tinju haven't yet tested the CData Power BI XMLA end point driver to connect to datasets. @azucena.coronel mentioned that she may be doing some testing. However, we do use CData's SSAS JDBC driver for connecting to a stand-alone SQL Server database running a SSAS model, and that works well (however, it would be nice if the set-up/install process were a little easier, particularly given that CData drivers are used in other areas of Matillion). Even though Power BI datasets in the Power BI Service are basically SSAS models, connecting to them through the service's XMLA end point is apparently different from connecting to a SQL Server SSAS database, and thus requires two different drivers.
Hi there . Just would like to know if this approach works. Would be keen to understand if there is any way to extract data from Power BI datasets?
Hi @Singularity , please can you share any documentation on setting up / configuring the CData SSAS driver in Matillion. I have attempted it with no success. Your assistance will be highly appreciated