question

CloudRock avatar image
0 Votes"
CloudRock asked AndreiCalinJuganaru-2206 answered

What is the difference between : Azure Synapsis Analytics - Azure Databricks - Azure HD insight

Hello Everybody,

I'm running a project where we need to propose an azure-based architecture to import data from an on-premises data warehouse (databases) to azure-based data platform.

Data are aimed to be exposed to company operators through a web visualization UI (with some analytics capabilities).

Data needs to be captured from on-premises, transformed from RAW to daily curated and exposed to end-user, using vizualisation tool.

I need your opinions to discuss benefits of using each of below services:

  • Azure Databricks OR Azure Synapsis Analytics OR Azure HD insight

  • Azure Analysis services

  • Azure SQL Database OR Azure Cosmos DB

  • Azure Data Factory OR Azure Data Migration Assistant

  • Azure Data Lake OR Azure Blob Storage

Here some constraints:

  • As compliance with modularity and service segregation principles, we need to separate jobs into different nodes.

  • Data migration will be performed by Azure Data Factory, but I need to know if Azure Data Migration assistant is also suitable in this case ?

  • Data should be stored in a resource that permit transformation, is Data Lake the best answer for this ?

  • For data transformation jobs from RAM to daily curated, would Databricks be the best solution for this ?

  • Would Azure Analysis Services be the best azure tool to interface with end-user visualization UI to perform some analytics ?


azure-data-factoryazure-synapse-analyticsazure-databricksazure-data-lake-storageazure-hdinsight
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

VaibhavChaudhari avatar image
2 Votes"
VaibhavChaudhari answered KL-9434 commented

You have identified the Azure services almost correctly.

If it is one time migration like lift and shift database from On-premise SQL or any other source to Azure SQL etc, you can use Data migration assistant - datamigration.microsoft.com

In your case I think you will most probably have to use Azure data factory where you set up a data ingestion flow and schedule the daily runs.

ADLS Gen2 is the resource where you can store the data and this data can be transformed

Azure databricks can be used for data processing, AAS can be used to create tabular model which will later be connected to PBI for visualization

Below is Modern data warehouse architecture proposed by Microsoft.

Reference -
modern-data-warehouse
data-warehousing

16050-image.png


===============================================
If the response helped, do "Accept Answer" and upvote it -- Vaibhav



image.png (54.5 KiB)
· 2
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

Good response and diagram. I could still use some clarification on the role of Azure Synapse Analytics here - i understand that it is a combo of enterprise data warehouse + big data analytics. So what is the incremental functionality value of Synapse versus Databricks to the left and Analysis Services to the right? Thanks in advance!

2 Votes 2 ·
KL-9434 avatar image KL-9434 ChadTursiWipro-5447 ·

I would be as well interested...

0 Votes 0 ·
IvanTavaresJunior-0080 avatar image
7 Votes"
IvanTavaresJunior-0080 answered IvanTavaresJunior-0080 edited

3-explore-azure-data-services-warehousing

Compare Analysis Services with Synapse Analytics:

Azure Analysis Services has significant functional overlap with Azure Synapse Analytics, but it's more suited for processing on a smaller scale.

  • Use Azure Synapse Analytics for:

Very high volumes of data (multi-terabyte to petabyte sized datasets).
Very complex queries and aggregations.
Data mining, and data exploration.
Complex ETL operations. ETL stands for Extract, Transform, and Load, and refers to the way in which you can retrieve raw data from multiple sources, convert this data into a standard format, and store it.
Low to mid concurrency (128 users or fewer).

  • Use Azure Analysis Services for:

Smaller volumes of data (a few terabytes).
Multiple sources that can be correlated.
High read concurrency (thousands of users).
Detailed analysis, and drilling into data, using functions in Power BI.
Rapid dashboard development from tabular data.

  • Combine Analysis Services with Synapse Analytics:

Many scenarios can benefit from using Synapse Analytics and Analysis Services together. If you have large amounts of ingested data that require preprocessing, you can use Synapse Analytics to read this data and manipulate it into a model that contains business information rather than a large amount of raw data. The scalability of Synapse Analytics gives it the ability to process and reduce many terabytes of data down into a smaller, succinct dataset that summarizes and aggregates much of this data. You can then use Analysis Services to perform detailed interrogation of this information, and visualize the results of these inquiries with Power BI.








5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

AndreiCalinJuganaru-2206 avatar image
0 Votes"
AndreiCalinJuganaru-2206 answered

@CloudRock I guess you had some time to experiment/decide. I am interested as well in one of your questions:
Azure Databricks OR Azure Synapse Analytics OR Azure HD insight?

I understand the difference between Analysis Services and Synapse Analytics.
However, it is still unclear to me what is the best choice between Databricks vs. Synapse vs. HD Insight.

Any input is highly appreciated! Thanks!

5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.