none
Power BI/SSRS report using u-SQL table

    Question

  • Hi Guys,

    I have multiple CSV files and we have loaded that to data lake store via ADF and used U-SQL create table and loaded the required data after transformation.

    Now we can to show that table data as report; what tools can be used to expose my table to end user to play around  with the data?

    I tried with powerbi, but it can fetch only Raw CSV file; but was unable to connect to actual data the table which i created using u-sql; So do i need to store back table transformation as output file to other csv and expose through powerbi else there is some mechanism through which the u-sql  table can be exposed to use in SSRS or Powerbi?

    Regards,

    Navin


    Navin.D http://dnavin.wordpress.com

    Tuesday, March 7, 2017 5:42 AM

Answers

All replies

  • Hi Navin,

    Currently the only way is to use the raw CSV files like you already found out. Fortunately there is work planned by the team according to this idea: https://feedback.azure.com/forums/327234-data-lake/suggestions/10584072-output-to-powerbi

    The following idea might also interest you and get your vote: https://feedback.azure.com/forums/327234-data-lake/suggestions/11266677-support-interactive-workloads-within-adl-analytics


    Jorg Klein's Microsoft Business Intelligence Blog

    • Marked as answer by Navind Wednesday, March 8, 2017 8:26 PM
    Tuesday, March 7, 2017 8:59 AM
  • Hi Jorg,

    Is it possible to load the csv to an on premises SQL server or Azure SQL as final tables? And use them in turn in powerbi or SSRS?

    Regards,

    Navin


    Navin.D http://dnavin.wordpress.com

    Tuesday, March 7, 2017 6:45 PM
  • Yes, you can use Data Factory for that. 

    How we typically do this in Data Factory is in three stages:

    1. Copy source data (if required) into the Data Lake Store

    2. Run U-SQL script with Data Factory

    3. Copy processed csv-file to Azure SQL or On-prem SQL

    For step 1 and 3 I advise to look at the Copy Wizard for Data Factory. Then you won't have to code all of this.

    • Marked as answer by Navind Wednesday, March 8, 2017 8:26 PM
    Wednesday, March 8, 2017 6:45 AM
  • Thank you Helge,  We already did that? But if we have direct access we do not need this extra step.

    Navin.D http://dnavin.wordpress.com

    Wednesday, March 8, 2017 8:26 PM
  • Hi Navin,

    If I understood your issue correctly, when you connect to Data Lake store from Power BI, you are able to see only csv or text files and if you use that csv file from Power BI then you get only the metadata of the file, for ex: Content, name, extension, date accessed, and bunch of other fields, but you want to visualize the actual data contained in the csv file rather than the file properties. 

    If this is the case then once you connect to the csv/txt file then you can click on edit queries and then from the Content column you can click on Binary and choose if it is csv/txt/JSON/HTML/XML file type, (in your case CSV) it will then show the actual data from the CSV file, please note that you will have to rename the column names though, as all the column names would be defaulted to Column0, Column1, column2, etc..

    Please check below link 

    https://docs.microsoft.com/en-us/azure/data-lake-store/data-lake-store-power-bi

    Unless you are filtering or transforming records from your data lake store file, I guess you would not have to export/output the data to any other store to be used in PowerBI.

    Cheers!!

    Raghu 

    Thursday, March 9, 2017 8:21 AM