Forum Discussion

LB-Tech's avatar
LB-Tech
Helper I
1 year ago
Solved

Suggest an efficient automated pipeline idea

Hi,

I am currently working with two softwares, Meditos (Lab) and Wondersoft Shopaid (Pharmacy), which provide automated backups at specific intervals in the form of SQL and .bak files respectively.

I want to set up a real-time data pipeline to integrate these backups with Power BI for reporting.

Could you suggest me some best ways to 

     1. connect these backups to Power BI in real-time*?
     2. Any techniques, or workflows (e.g., ETL tools, connectors) that can help automate this process effectively(cost effectively)?
 

data engineering
data pipeline
  • v-prasare's avatar
    v-prasare
    1 year ago

    Hi  LB-Tech ,

    Thanks for reaching out to Microsoft Fabric community support.

     

    from above question your trying to set up a real-time data pipeline that integrates SQL and .bak backups. please follow below mentioned steps and let me know if this helps:

     

    1. Setup a Centralized Database
    1. Automate Backup Processing

    For SQL Files:

    • Write a script (Python, PowerShell, or Bash) to Detect new SQL files in the backup folder. Execute the SQL files to update the database.

    For .bak Files:

    • Use SQL Server commands or tools to automate .bak file restoration. please refer to similar community post

    Solved: Re: Is it possible to connect to a .BAK file? - Microsoft Fabric Community

     

    Automate this process using:

    • Windows Task Scheduler or CRON jobs, ADF (Azure Data Factory) or custom scripts for orchestration.
    1. Data Transformation
      • Use an ETL tool or custom scripts toClean the data, Join and aggregate data, Apply business rules for reporting
    1. Real-Time Data Integration
      • DirectQuery for Real-Time Reporting: Connect Power BI directly to the centralized database. Changes in the database are reflected in Power BI in near-real-time.
      • Scheduled Refresh: Use Power BI Import mode for pre-aggregated data. Set a refresh schedule to pull the latest data.

     

    Thanks,

    Prashanth Are

     

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

6 Replies

  • spencer_sa's avatar
    spencer_sa
    Impactful Individual
    1 year ago

    I'm assuming you're missing a footnote regarding real-time.  What do you mean by 'real-time'?  Actual real time or polling at intervals?  Actual real time is likely to be Event Stream and Kusto driven.

  • v-prasare's avatar
    v-prasare
    Community Support
    1 year ago

    Hi  LB-Tech ,

    Thanks for reaching out to Microsoft Fabric community support.

     

    from above question your trying to set up a real-time data pipeline that integrates SQL and .bak backups. please follow below mentioned steps and let me know if this helps:

     

    1. Setup a Centralized Database
    1. Automate Backup Processing

    For SQL Files:

    • Write a script (Python, PowerShell, or Bash) to Detect new SQL files in the backup folder. Execute the SQL files to update the database.

    For .bak Files:

    • Use SQL Server commands or tools to automate .bak file restoration. please refer to similar community post

    Solved: Re: Is it possible to connect to a .BAK file? - Microsoft Fabric Community

     

    Automate this process using:

    • Windows Task Scheduler or CRON jobs, ADF (Azure Data Factory) or custom scripts for orchestration.
    1. Data Transformation
      • Use an ETL tool or custom scripts toClean the data, Join and aggregate data, Apply business rules for reporting
    1. Real-Time Data Integration
      • DirectQuery for Real-Time Reporting: Connect Power BI directly to the centralized database. Changes in the database are reflected in Power BI in near-real-time.
      • Scheduled Refresh: Use Power BI Import mode for pre-aggregated data. Set a refresh schedule to pull the latest data.

     

    Thanks,

    Prashanth Are

     

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

    • LB-Tech's avatar
      LB-Tech
      Helper I
      1 year ago

      Can we setup a On-Prem Gateway for this task?

      • v-prasare's avatar
        v-prasare
        Community Support
        1 year ago

        Yes, setting up an On-Premises Data Gateway is a practical solution for integrating your on-premises data with Power BI in near real-time.

        for more info related refer to this doc: Power BI implementation planning: Data gateways - Power BI | Microsoft Learn 

         

         

         

        Thanks,

        Prashanth Are

        MS Fabric community team

         

        If this post helps, then please consider Accept it as the solution to help the other members find it more quickly and give Kudos if helped you resolve your query.

  • v-prasare's avatar
    v-prasare
    Community Support
    1 year ago

    Hi LB-Tech ,
    Hope your doing well.

    As we haven’t heard back from you, we wanted to kindly follow up to check if the solution helps you? or let us know if you need any further assistance here?

     

     

    Your feedback is important to us, Looking forward to your response. 

     

    Thanks,

    Prashanth Are

    MS Fabric community support.

     

    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly and give Kudos if helped you resolve your query.