Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
1 year ago

How to Create a Copy of an Existing Data Flow for Testing in Power BI Workspaces

Hi,

I'm working on a Power BI data flow that resides in the Power BI workspaces. The data flow connects to an Excel workbook stored in SharePoint to build a semantic model, which is used to generate production reports used by everyone in the company. The Excel workbook is live and updated daily by multiple users.

 

Currently, there is a need to create a test pipeline that mirrors the existing setup to test changes(like renaming columns or modifying data) without affecting the production pipeline. For context:

The original pipeline connects to an Excel file (e.g., MainWorkbook.xlsx) to build the semantic model and the production report.

I have a copy of the excel file (e.g., TestWorkbook.xlsx) stored in a different location, and I want to use it to create a seperate test pipeline while keeping the dashboards and report content intact.

 

I tried changing the data source for the test report to point to the new Excel file, but it resulted in multiple errors despite the file structure being identical. I need guidance on:

  1. How to properly duplicate the pipeline, including the semantic model, to work with the test data source.
  2. How to ensure that the visuals, transformations, and other elements in the test report remain consistent with the prodution report.
  3. Best practices for maintaining the separation between the test and the production pipelines.

Any insights or recommendations would be greatly appreaciated!

Thank you!

Need Help

2 Replies

  • rajendraongole1's avatar
    rajendraongole1
    Icon for Super User rankSuper User
    1 year ago

    Hi Anonymous - Creating a test pipeline that mirrors the production setup in Power BI while keeping visuals, transformations, and the semantic model intact involves several steps.

    Below is the process:

    In the Power BI Service, go to the workspace where the production dataflow resides.
    Select the dataflow and choose Export JSON to download the definition.
    Import the Dataflow as a Test Version:

    Go to the workspace where you want the test pipeline.
    Choose Create > Dataflow > Import JSON and upload the previously exported JSON.
    Rename the dataflow to indicate it is for testing (e.g., Test_Dataflow).
    Update the Data Source in the Test Dataflow:

    Open the test dataflow in the Power Query Editor.
    Edit the connection settings to point to the TestWorkbook.xlsx file in the alternate SharePoint location.
    Verify that the schema matches the original data source and adjust transformations if needed.
    Save and refresh the test dataflow.
    Step 2: Duplicate the Dataset (Semantic Model)
    Download the Dataset:

    Open the Power BI Desktop file connected to the production dataflow.
    Save a copy of the .pbix file and name it Test_Model.pbix.
    Switch the Data Source in Power BI Desktop:

    Open the Test_Model.pbix file in Power BI Desktop.
    Go to Transform Data > Data Source Settings.
    Locate the dataflow connection and select Change Source.
    Point it to the test dataflow created in Step 1.
    Verify that all tables and transformations load correctly, and fix any errors caused by differences in data.
    Publish the Test Dataset:

    Publish the test dataset to the test workspace in Power BI Service.
    Step 3: Duplicate the Reports
    Duplicate the Reports:

    In Power BI Service, locate the production report.
    Use Save As to create a copy of the report and save it in the test workspace (e.g., Test_Report).
    Relink the Test Dataset:

    Open the test report in Power BI Service.
    Go to Settings > Datasets and change the dataset to point to the test dataset published in Step 2.
    Verify all visuals and ensure they work correctly.

     

    Hope this details helps.

  • Poojara_D12's avatar
    Poojara_D12
    Icon for Super User rankSuper User
    1 year ago

    Hi Anonymous 

     

    1. Duplicate the Dataflow and Point It to the Test Workbook

    • Duplicate the Dataflow:
      • Go to the Power BI workspace and duplicate the existing dataflow.
      • In the new dataflow, edit the query to replace the connection to MainWorkbook.xlsx with TestWorkbook.xlsx (ensure SharePoint paths are correct).
    • Verify Schema Consistency:
      • Ensure the schema (column names, data types) of the test file matches the production file exactly.
      • If needed, preprocess the test Excel file to align with the production file.

    2. Duplicate the Semantic Model

    • Export the existing dataset (semantic model):
      • In Power BI Desktop, connect to the production dataset and save it as a .pbix file.
    • Modify the data source in Power BI Desktop:
      • Open the .pbix file, go to Transform Data > Data Source Settings, and change the connection to point to the test dataflow.
      • Ensure all relationships and transformations are preserved during the change.
    • Publish the modified .pbix file to a test workspace or a dedicated test environment.

    3. Maintain Report Consistency

    • Duplicate the reports:
      • In Power BI Service, make a copy of the existing report and connect it to the test dataset.
      • Verify that visuals, measures, and calculations render correctly using the test data.
    • Check for errors:
      • Review any broken visuals or calculations caused by schema changes and fix them.

    4. Best Practices for Test vs. Production Separation

    • Separate Workspaces:
      • Use dedicated workspaces for test and production environments to avoid accidental overlaps.
    • Naming Convention:
      • Clearly name your test dataflows, datasets, and reports to distinguish them from production (e.g., Test_Dataflow, Test_SemanticModel, etc.).
    • Permissions:
      • Restrict access to the test workspace to ensure changes are controlled.
    • Version Control:
      • Maintain a record of changes (e.g., column renaming, DAX updates) to easily replicate them in production after testing.

    Additional Recommendations

    • Validation Workflow:
      • After testing, validate the changes using test users before applying them to production.
    • Refresh Schedules:
      • Set different refresh schedules for test and production pipelines to avoid conflicts.

    By following these steps, you can establish a test pipeline that mirrors your production setup while ensuring separation and consistency.

     

    Did I answer your question? Mark my post as a solution, this will help others!

    If my response(s) assisted you in any way, don't forget to drop me a "Kudos" 🙂

    Kind Regards,
    Poojara
    Data Analyst | MSBI Developer | Power BI Consultant
    YouTube: https://youtube.com/@biconcepts?si=04iw9SYI2HN80HKS