Forum Discussion

hubix2000's avatar
hubix2000
New Member
10 years ago

PowerBI with Azure Data Lake Store

Hi,   we already run all our operations on Azure SQL databases. Now we are going to invest into Azure Data Lake Store and like to integrate it with PowerBI. Currently there is no external data sour...
desertislesql's avatar
desertislesql
Most Valuable Professional
10 years ago

I would like to use an Azure Data Lake for a Power BI source as well. Any information on this would be appreciated.

desertislesql's avatar
desertislesql
Most Valuable Professional
10 years ago

I found out that ir you are looking to use an Azure Data Lake as your data store, you cannot directly connect to it online. If you want to use an Azure Data Lake as a data source, you will need to use an ODBC data source in Power BI Desktop.

  • gerardcuijpers's avatar
    gerardcuijpers
    New Member
    10 years ago

    I managed to get data from a Data Late Store into PowerBI desktop, but not the way I would like it:

    • PowerBI Desktop, Version: 2.32.4307.641 64-bit (February 2016)
    • Get Data / Azure / Microsoft Azure Data Lake Store (Beta)
    • Provide the adl:// or swebhdfs:// path
    • It will show you a preview with columns like "content, name, extension, .."
    • Click on "Edit"
    • Click on the link "Binary" in the first column ("Content")
    • Now you can see the the data as it appears in the file itself.

     

    • desertislesql's avatar
      desertislesql
      Most Valuable Professional
      10 years ago

      That's a very interesting solution. Thanks for sharing. Here's a question, are you able to refresh the data extracted from the datastore in the personal gateway?

       

      • gerardcuijpers's avatar
        gerardcuijpers
        New Member
        10 years ago

        I published the report to PowerBI.com and I could refresh the data there. Don't need the gateway, there's no local to cloud data movement involved here. (Direct connection). I just needed to re-enter my credentials for the DataLake reference.

    • shabbirmala's avatar
      shabbirmala
      Frequent Visitor
      9 years ago

      What happens when we have files stored in multiple sub folders by timestamp?

       

      When I browse binary data and check the scripts inside Power BI advance editor I get follow:

       

      let
      Source = DataLake.Contents("https://xxxxxxxx.azuredatalakestore.net"),
      Content = Source[Content],
      Content1 = Content{0},
      #"2017" = Content1{[Name="2017"]}[Content],
      #"04" = #"2017"{[Name="04"]}[Content],
      #"20" = #"04"{[Name="20"]}[Content],
      #"14_0_f44e6b5c95fa49dfa3b78c89878777d1 json" = #"20"{[Name="14_0_f44e6b5c95fa49dfa3b78c89878777d1.json"]}[Content],
      #"Imported CSV" = Csv.Document(#"14_0_f44e6b5c95fa49dfa3b78c89878777d1 json",[Delimiter=",", Columns=17, Encoding=1252, QuoteStyle=QuoteStyle.None]),

       

      which tells I went all the way to 2017-04-20 and picked that day file. 

       

      Can I setup this to load all files as they come?

       

      Shabbir

       

    • catch93's avatar
      catch93
      New Member
      8 years ago

      how can you we load all the files for all the files.. I am not sure clicking Binary would work if I want to work with all of the data? 

      Would it 

  • jefflaw's avatar
    jefflaw
    Frequent Visitor
    9 years ago

    If I have to use an ODBC data source as I want to publish, is there any reference as to how to set this up?

    • DrGigabit's avatar
      DrGigabit
      Regular Visitor
      9 years ago

      Not sure why we talk about ODBC here, since PowerBI has out of the box support for Azure Data Lake Store.

      • jefflaw's avatar
        jefflaw
        Frequent Visitor
        9 years ago

        It was a follow up to desertislesql post above in this thread...

        I found out that ir you are looking to use an Azure Data Lake as your data store, you cannot directly connect to it online. If you want to use an Azure Data Lake as a data source, you will need to use an ODBC data source in Power BI Desktop.

         

        I am testing out PowerBI with our AppInsights data. One of the options is to move the data into DataLake and use that as a source. I can access everything fine from PowerBI, but when I publish, the connection doesnt work. I keep getting prompted to login.

         

        Doing a search, I found this reference which aligns with my experiance. Having gotten the data into DataLake, I am trying any option I can find to get the data into PowerBI.

         

        I would have thought using ODBC was a bit of hack, and if there is a way to get this working "natively" then I would like to use it.