Forum Discussion

rvp_ordix's avatar
rvp_ordix
Helper I
2 years ago

How to import Warehouse View in Dataflow Gen2?

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?

Dataflows
Need Help

2 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable
    2 years ago

    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.

    • rvp_ordix's avatar
      rvp_ordix
      Helper I
      2 years ago

      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".