How to use data stored in ADLS Gen 2

[oKo]

Hi Everyone,

After successfully connect power bi with Azure Data Lake Storage (ADLS Gens 2) and create dataflow to ETL data to the storage. I need to load that data into Power BI and other Applications.  However, Power BI Dataflow connection does not support direct query to apply greate functions such as Aggregations.

Is it possible to use ADLS Gen 2 as Azure SQL DataWarehouse External Data Source? If yes, please help me with set up. If no, Are there any better way to use that data?

Thanks all

[v-yulgu-msft · Accepted solution]

Hi @oKo ,

Based on research, below link talks about how ADLS Gen 2 integrates with Azure SQL DataWarehouse. 

The prepared data is also loaded into staging tables in an Azure SQL Data Warehouse, where it is transformed into a dimensional model.  Reference: New tutorial explores data sharing between Power BI and Azure

Besides, please have a look at this blog which shows Access Data from Azure Data Lake Store using Polybase with Azure Data Warehouse.

Best regards,

Yuliana Gu

[oKo]

Thank @v-yulgu-msft  for your support,

I am successfully connected ADLS with Data WareHouse via Polybase External Data Souce.

However, every time dataflow refreshed, a new snapshot created without deleting the old one.

When query Azure SQL DataWarehouse gets all data in the folder => data is duplicated.

Is it possible to delete old snapshots after data refreshed or how to query only the newest snapshot.

Thanks & Best Regards

[gacecilidw]

Hi @oKo - what format is your data stored in in adls? I expected it to look like described here: https://docs.microsoft.com/en-us/common-data-model/data-lake (CDM entity folders with csv files inside) - but all my files and folders except model.json are stored with the extension .snapshot, and not able to be downloaded...