Data ingestion taking a lot of time

Koteswara Pentakota 71 Reputation points
2022-04-22T17:28:24.97+00:00

We are doing a lot of data ingestion into our system. Currently we get 1000's of records. With power bi analytics feature we are getting requests to get more number of records. We have a customer who is ready to give 5 lakh worth of records and we tried performance analysis and observed that it is taking 50+ minutes to process a lot of records. Is there a way we can optimize this by using any in built azure solutions. The use case is to process an excel and insert data into multiple tables with some business logic.

Azure SQL Database
Azure ISV (Independent Software Vendors) and Startups
Azure ISV (Independent Software Vendors) and Startups
Azure: A cloud computing platform and infrastructure for building, deploying and managing applications and services through a worldwide network of Microsoft-managed datacenters.ISV (Independent Software Vendors) and Startups: A Microsoft program that helps customers adopt Microsoft Cloud solutions and drive user adoption.
89 questions
0 comments
Accepted answer
  1. ShaikMaheer-MSFT 37,971 Reputation points Microsoft Employee
    2022-04-25T09:24:36.69+00:00

    Hi @Koteswara Pentakota ,

    Could you please explain more about What you mean when you say create a relationship based on previous tables? If it means create primary key and foreign key relations, by running SQL script, then we can do that using "pre-copy script: field of copy activity or using script activity as one of the step in your pipeline.
    Click here to know more about script activity. This video also explains more about script activity usage.
    196092-image.png

    Azure data factory cannot encrypt data out of box directly. We should either consider using Custom activity or Azure Function activity. Please check below Q&A thread where similar discussion is available.
    https://learn.microsoft.com/en-us/answers/questions/280549/how-to-configure-the-adf-pipe-line-to-encrypt-and.html

    Hope this helps. Please let us know if any further queries.

    ------------------

    Please consider hitting Accept Answer button. Accepted answers help community as well.

1 additional answer

Sort by: Most helpful
Most helpful Newest Oldest
  1. Sudipta Chakraborty - MSFT 1,096 Reputation points Microsoft Employee
    2022-04-22T18:19:51.617+00:00

    @Koteswara Pentakota :

    Azure Data Factory can be used for this purpose of copying data from Excel and storing it in multiple Tables like SQL Database tables.

    Using the ForEach functionality in ADF V2 you can perform mass copying of tables without having to specify a target Linked Services and datasets for each table.
    Using the Copy Data tool (and the Pipeline Editor), you can specify your target data source, then select multiple source tables. When you specify the target data source, Data Factory generates a ForEach activity that in turn calls a Copy activity that’s parameterized for each source table.
    To get started we need to have an Azure Data Factory created, along with a Source and Target. You can use a Excel File as data source and Azure SQL Database as destination.

    The details around the same has been provided in the following link :
    https://learn.microsoft.com/en-us/azure/data-factory/quickstart-create-data-factory-portal

    The process of creating a pipeline with Copy Data task is provided in the following link : https://learn.microsoft.com/en-us/azure/data-factory/quickstart-create-data-factory-portal#create-a-pipeline