none
PowerBI integration with Data Lake Analytics Store

    Question

  • Hi,

    we are thinking of using PowerBI as a font end for our data in ADLS. Currently there is no service in PowerBI available (just a beta connector in PowerBI desktop) to query the data.

    Do you have any information when a connector might be available? The guys of the PowerBI community don't react. :-(

    Thanks,
    Jörg

    Tuesday, January 26, 2016 2:28 PM

Answers

  • Hi Jörg,

    At the moment I would recommend using Azure Data Factory (ADF) to write the data back to an Azure SQL Database and then use Power BI with that if you want to push filtering and other compute intensive logic down to a database engine.

    Alternatively, depending on the size of the data, you can write down the data to a file, or set of files, in the Azure Data Lake Store (ADLS). You may want to pre-aggregate it if it won't take away from the analytics side of things. You could then connect to the ADLS files directly through Power BI, apply filtering and transformations through the Power BI Query Editor, and build a Power BI data model on top of this. Since the data will effectively be cached within the Power BI data model once loaded, the performance when building and interacting with Power BI reports and dashboards will still be very fast (generally 0-5 second response times).

    You may find the following links helpful:

    https://azure.microsoft.com/en-gb/documentation/articles/data-factory-usql-activity/

    https://azure.microsoft.com/en-us/blog/creating-big-data-pipelines-using-azure-data-lake-and-azure-data-factory/

    Edit: The M query generated by the Power BI Query Editor (or coded by hand) can be refactored at a later point to pull data from another data store if the need arises, and without breaking the reports built on top (as long as it exposes the same data structure to the model).


    Regards,

    Michael Amadi

    Please use the 'Mark as answer' link to mark a post that answers your question. If you find a reply helpful, please remember to vote it as helpful :)

    Website: http://www.nimblelearn.com, Twitter: @nimblelearn

    Thursday, January 28, 2016 6:53 PM

All replies

  • Hi Jörg,

    There is a feature suggestion on the Data Lake feedback website (https://feedback.azure.com/forums/327234-data-lake/suggestions/10584072-output-to-powerbi). You may wish to up vote and comment on this, or if it doesn't fit with your requirements, add another feature suggestion. Also, if you have any additional feature requests, this is a great place to let the ADL team know and allow the community to vote on.


    Regards,

    Michael Amadi

    Please use the 'Mark as answer' link to mark a post that answers your question. If you find a reply helpful, please remember to vote it as helpful :)

    Website: http://www.nimblelearn.com, Twitter: @nimblelearn

    Tuesday, January 26, 2016 5:38 PM
  • Hi Michael,

    there is already a comment that the ADLS is on the roadmap for PowerBI. Can you help to get some rough suggestion when a public beta might start?

    Thanks,
    Jörg

    Tuesday, January 26, 2016 5:57 PM
  • Hi Jörg,

    That's a question for the ADL team. Hopefully a member of the team will be able to give some kind of time-frame even if it's a broad one.

    Regards,

    Michael Amadi

    Please use the 'Mark as answer' link to mark a post that answers your question. If you find a reply helpful, please remember to vote it as helpful :)

    Website: http://www.nimblelearn.com, Twitter: @nimblelearn

    Tuesday, January 26, 2016 6:37 PM
  • Hi,

    The connector to ADLS is available in PowerBI Desktop as noted. It is also available on PowerBI.com. However, for that to work, the user would need to upload a PBIX file with queries which pull from files in ADLS. 

    If you are talking about connecting from PowerBI.com to ADLS without a model from PowerBI desktop, i.e. Direct Query or Live Query, that is not available. Primarily because there is no query engine on ADLS to process the requests.

    Going ahead PowerBI could support Direct/Live Query with ADLA. If you want this, please file it or upvote it on https://feedback.azure.com/forums/327234-azure-data-lake .

    Thanks,
    Sachin Sheth
    Program Manager
    Azure Data Lake.

    • Proposed as answer by Michael Amadi Wednesday, January 27, 2016 6:17 AM
    Wednesday, January 27, 2016 12:50 AM
  • Hi Sachin,

    thanks for your effort to help us - but this last reply wasn't something I like to hear. ;-)

    I guess querying data from ADSL is not yet on the roadmap. And that means it will take a lot of time to finish such a feature once planned.

    I'm feared we have to rethink our decision using ADSL together with PowerBI.

    In another issue I ask if it's possible to write data back to Azure SQL - which is not - to connect from PowerBI to Azure SQL to query our results.

    Do you have any other idea how to implement a workaround to use U-SQL to transform data from Azure SQL such a way that they can be used in PowerBI?

    Thanks for your help.
    Jörg

    Wednesday, January 27, 2016 6:37 AM
  • Hi Jörg,

    At the moment I would recommend using Azure Data Factory (ADF) to write the data back to an Azure SQL Database and then use Power BI with that if you want to push filtering and other compute intensive logic down to a database engine.

    Alternatively, depending on the size of the data, you can write down the data to a file, or set of files, in the Azure Data Lake Store (ADLS). You may want to pre-aggregate it if it won't take away from the analytics side of things. You could then connect to the ADLS files directly through Power BI, apply filtering and transformations through the Power BI Query Editor, and build a Power BI data model on top of this. Since the data will effectively be cached within the Power BI data model once loaded, the performance when building and interacting with Power BI reports and dashboards will still be very fast (generally 0-5 second response times).

    You may find the following links helpful:

    https://azure.microsoft.com/en-gb/documentation/articles/data-factory-usql-activity/

    https://azure.microsoft.com/en-us/blog/creating-big-data-pipelines-using-azure-data-lake-and-azure-data-factory/

    Edit: The M query generated by the Power BI Query Editor (or coded by hand) can be refactored at a later point to pull data from another data store if the need arises, and without breaking the reports built on top (as long as it exposes the same data structure to the model).


    Regards,

    Michael Amadi

    Please use the 'Mark as answer' link to mark a post that answers your question. If you find a reply helpful, please remember to vote it as helpful :)

    Website: http://www.nimblelearn.com, Twitter: @nimblelearn

    Thursday, January 28, 2016 6:53 PM
  • Qubole a Microsoft ISV Gold Platform partner has a published PowerBI connector that allows direct query of the Azure Data Lake.  Here is the blog on this capability: https://www.qubole.com/blog/qubole-brings-federated-queries-to-microsoft-power-bi-with-presto/.  Qubole will provide free hands on workshops and free pilot in your Azure subscription to test out these capabilities and see if they meet your needs.  Contact dlassiter@qubole.com
    Wednesday, March 27, 2019 1:48 PM