Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
6 years ago

What is the proper use case for Dataflows

My organization is fairly new to Power BI, and I'm not 100% sure we are trying to use Dataflows how they're intended.  For instance, we have a star schema and want to consume that data through Power BI.  However, instead of hitting the tables directly on DB2, we have taken all dimensions and fact tables and created Dataflows for each.  Then, we grouped the Dataflows into categorical Linked Entities and scheduled refreshes.  The only thing happening to the data during this process is friendly naming.  

 

The first hurdle we hit is that our fact tables were too large to load into the Dataflow.  Now, we are to the point where we have this massive star schema modeled and all dataflows refreshing for dimensions, but the dataset is too large to refresh due to capping out our memory.

 

In the past with other BI tools, we would have built the model hitting the tables directly.  Without truly knowing the best use case for a Dataflow, I can't help but think we're trying to use Dataflows for something they weren't intended to be used for.  We are essentially creating a copy of the database in the service hoping to gain some optimization through Dataflows.  Does this sound like a practical application of this tool?  I can't help but feel like we should have always taken the approach of using DirectQuery against the tables on DB2.

3 Replies

  • Greg_Deckler's avatar
    Greg_Deckler
    Icon for Community Champion rankCommunity Champion
    6 years ago

    Anonymous - So, in general Dataflows were created by Microsoft to make queries reuseable. So, originally, if you had 5 Power BI PBIX files you might have the same query and transformation in each one. So, if a change happened to the source data and you needed to make adjustments you ended up having to modify all 5 PBIX files. Dataflows allowed you to have a single place to make modifications and you could use that dataflow within your PBIX files.

     

    So, that's the original intent of dataflows or what they originally brought to the table. Now, if you have having issues with the size of your datasource, you have 2 options.

    1. See if you can implement incremental refresh

    2. Move to Direct Query

     

    If you move to Direct Query dataflows are basically out of the picture unless you do the preview thing where you direct query to a dataflow but that's kind of a different thing.

    • Anonymous's avatar
      Anonymous
      Not applicable
      6 years ago

      Greg_Deckler  - Thanks for the advice.  Are you saying there is an incremental refresh option for the dataset?  I know of the incremental refresh option for dataflows, but the dataflows refresh fine.  We only run into memory issues when we attempt to refresh the dataset built from the dataflows.

       

      We enabled the enhanced compute engine and tried directquery against the dataflows themselves.  We ended up using about 1/3 of the tables with directquery and 2/3 dataflows and still exceeded our memory.  We may just switch it over to directquery against DB2 and see how that works.