Challenge: Modern data warehouse solution vs traditional data warehouse

[rajkm42]

Dear all,

    I am new to Azure synapse analytics I mean know a little bit.

We have D365 CE data exported to ADLS gen2. When I open Synapse studio, I can Built-in serverless sql pool. 

We currently have traditional datawarehouse(SQL server). ETL pushes data from various sources(Operational, csv, excel, third party apps) to sql server.

How can I move to azure synapse analytics workspace to save costs and performance.

Serverless pool is cheaper but it doesn't hold data physically in a table. It is a reference to csv file(Common Data Model/normal third party csv files). I have seen performance is not good when querying a table with 800k rows.

I am googling to find how Azure Synapse analytics workspace replaces traditional dw. I am getting confused with the results.

Basically, I need to include all the data sources in serverless pool what I used to get into my SSMS(as mentioned above).

And also, I need to apply business logic(Stored procedures or views) before feeding to reporting platform. Target views will be a mixture of more than 1 sources of data in SSMS.

I am very confused, How a serverless pool is best for me? 

Any reply is highly appreciable. 

[#2]

Hi @rajkm42 

> How can I move to azure synapse analytics workspace to save costs and performance.

Not neccererily moving will lead to save costs and gain performance. There are a lot of advantages in migrating to cloud solutions and not less disadvantages according to the scenario and your specific case.

You should test it well to be sure. It is highly recommend to get some estimated about manage costs for serverless SQL pool and check the Azure Synapse Analytics pricing calculator here.

Not any service fit any scenario. It is up to you to check the limitations and cost according to your specific case.

> I am very confused, How a serverless pool is best for me?

Who told you that it is better for your specific case?!? Why do you assume that it is better for scenario?!? There is a good reason for other services and products to exists 

You question based on assumptions and as such makes small sense.

Without examine your specific case and get a lot more information, I cannot advise you what fits for you, but in first glance i would recommend you to check the option of using azure synapse dedicated sql pool as a database warehouse solution.

[#3]

Hi @Ronen_Ariely 

Thanks for the reply.

My question is actually

1. What is the best way to migrate to Azure Synapse based on my current process

    a. Using SSIS to pull data from datalake (Common Data Model) to Azure SQL DB

    b. Synapse is serverless pool

    c. What is the best way to implement business logic like Sql Views, Sql SPs

    d. Serverless pool offers $5 to process 1TB of data

2. How do I merge or query data between 2 datalakes(D365 CE and D365 FinOps)

    a. I mean cross database reference

    b. Is the serverless pool performance good with massive data files in lake

Regards