topic Re: How to import Warehouse View in Dataflow Gen2? in Dataflow

How to import Warehouse View in Dataflow Gen2?

rvp_ordix — Wed, 03 Jul 2024 12:52:53 GMT

My data is stored various tables of a Fabric Warehouse, and now I want to build a Dataflow to transform that data. To do that, however, I need to create a control table from the various Views of INFORMATION_SCHEMA within that Warehouse and I haven't found a way to achieve that. This is the query I want to implement in Fabric.

Pressing "Get Data" and then selecting the Warehouse only shows tables, not the views I need. I can't construct the table via Warehouse's Query Editor either, since inserting anything from those Views would always return the error "The query references an object that is not supported in distributed processing mode."

How am I meant to do this?

Re: How to import Warehouse View in Dataflow Gen2?

Anonymous — Thu, 04 Jul 2024 06:52:14 GMT

Hi rvp_ordix ,

You can create the storage table with the following sql statement.

CREATE PROCEDURE PopulateControlTable AS BEGIN -- Insert data from 'datamart' schema INSERT INTO ControlTable (TABLE_NAME) SELECT TABLE_NAME FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_TYPE = 'BASE TABLE' AND TABLE_SCHEMA = 'datamart'; -- Insert data from 'core' schema INSERT INTO ControlTable (TABLE_NAME) SELECT TABLE_NAME FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_TYPE = 'BASE TABLE' AND TABLE_SCHEMA = 'core'; -- Insert data from 'staging' schema INSERT INTO ControlTable (TABLE_NAME) SELECT TABLE_NAME FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_TYPE = 'BASE TABLE' AND TABLE_SCHEMA = 'staging'; END;

Best Regards,
Adamk Kong

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Re: How to import Warehouse View in Dataflow Gen2?

rvp_ordix — Thu, 04 Jul 2024 09:23:30 GMT

I've already tried that, as mentioned in my post, and it doesn't work. Running that procedure throws the error "The query references an object that is not supported in distributed processing mode".