Storing static datasets in Dataflows?

[13havya]

Hi,

Just trying to figure out what the best way is, to store static datasets in dataflows? 

We have legacy payroll records that are stored in excel files, which have a bunch of transformations applied to accomodate it to our data model. These records are no longer modified, as we have an API connector feeding into a datalake, which we use for the payroll information, but we still require the legacy reports to be connected to our datasets (to display historical data). 

As the legacy records contain over 1000 files over a period of 3 years, it is not feasible to refresh them along with other dataflows. What's the best way to store this data, to minimise/completely remove refresh time 

[v-ssriganesh]

Hi @13havya,

May I ask if you have resolved this issue? If so, please mark the helpful reply and accept it as the solution. This will be helpful for other community members who have similar problems to solve it faster.

Thank you.

[v-ssriganesh]

Hi @13havya,

Could you please confirm if your query have been resolved by the solution provided by @nilendraFabric? If so, please mark it as the solution. This will be helpful for other community members who have similar problems to solve it faster.

Thank you.

[nilendraFabric · Accepted solution]

Hi @13havya 

There are a few approaches you can consider for storing static datasets like your legacy payroll records in Microsoft Fabric:
1. Use a Lakehouse:
• Import the Excel files into a Lakehouse as delta tables.
• This allows you to store the data in an optimized format without needing frequent refreshes.
• You can then reference this data in your dataflows or directly in reports.
2. Create a static dataflow:
• Import the Excel files into a dedicated dataflow for static data.
• Set the refresh schedule to manual or very infrequent (e.g., monthly) to minimize refresh time.
• Use the “Enter Data” option to manually input small static datasets directly in the dataflow.
3. Use parameters in dataflows:
• Create parameters to control which data gets refreshed.
• Set up logic to skip refreshing the static historical data during regular refreshes.
4. Leverage incremental refresh:
• If your data has date columns, use incremental refresh to only update new data.
• Configure the refresh policy to exclude the date range of your static historical data.

Please accept this solution and give kudos if this is helpful 

Thanks

[13havya]

Thanks @nilendraFabric