Data ingestion taking a lot of time

Question

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.

Answer 1

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.

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.

Answer 2

@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