Which Azure Technology is best for Data warehousing?

Question

Which Azure Technology is best for Data warehousing?

Amar Agnihotri 926

Hi !
I am new to azure . I have a couple of softwares running and I want to store that data in Azure because I am not using any data warehouse right now. My requirement is to store data coming from two different sources in Azure but I Don't know which technology of Azure should I go with . The data size is not very large max of 20-30 Tables . I want to store data in azure and want to perform transformation to get some data sets ready that could be used for analysis using power bi. I am not able to understand which technology of azure would be good in terms of robustness, scalability and performance wise to complete the above requirements. I also Don't want to go with very high budget and very high license cost for future requirements. Can anybody please suggest the azure technologies for the above requirement and the differences between them . May be I am not versed enough to explain my requirement completely but it would be great if anyone could suggest anyhow

Thanks

@AnuragSharma-MSFT

2 answers

Your answer

Answer 1

Martin Cairney 2,261

Hey Anurag

If you have very low volume requirements that will eventually always be surfaced in Power BI, then there is no real need to use anything else but Power BI.

Provided that you can connect to your data sources then you can import the data and model it within the Power BI workspace to get your final model ready.

The next level up may be to use Data Factory and a SQL Database to import the data to and then connect your Power BI model to the database.

Answer 2

Amar Agnihotri 926

Hey @Martin Cairney ,

First of all thanks for the reply. Actually currently we have two sources from which we want to import data to azure. One is Salesforce and the second is OpenAir. From salesforce we take daily backup using OwnBackup tool and we can create an endpoint in OwnBackup to export that backup to Azure Mysql Database. In case of Open Air, neither we can make direct connection to OpenAir in Power Bi nor we connect it directly to Azure. We have to do some kind of automation to export backup from OpenAir and import it into Azure. Yes, you are right that currently we can use Power Bi features for now but i am looking for the coming years because our organization is growing , acquisitions are taking place and that's how the data is growing rapidly. Moreover, we can use a lot of more software in future. So, I want a centralized repository of Transformed and well managed data and i want to go with azure. Since i am new to azure so i am unable to decide which technology should i go with. My clear requirements are -

1-Want to create a centralized repository in Azure for the data coming from different sources
2- Want to perform ETL and automation for data importing and transformation
3- Will prepare a data sets in azure after proper ETL and then will use that data set in Power Bi for reporting and analysing.
4- Technology should be efficient , robust, scalable and faster in execution.
5- Also the cost should be out of my hands

Hope you understand my requirement. Now I am looking forward to you for the best suggestion.
It would be great if you could give the comparative suggestion of different options in terms of cost and features and scalability.

Keeping in mind that i don't know what Datalake, databricks and Azure Data Factory are.

Thanks

Martin Cairney 2,261 Reputation points

2021-10-07T05:47:45.143+00:00

For scalability and flexibility I would suggest that you use a Data Lake built on Azure Blob Storage to land all of your data from different sources.

It sounds like you already have the solutions in place for OpenAir and Salesforce to get them into a secondary data store - this means that you can more easily use Data Factory as the ELT orchestration tool and extract the data from these data sources to a landing zone in the Data Lake.

Any new data source - if it can be connected to from Data Factory (as you can see here there are many sources that it can connect to) then you can also land it in the Data Lake first of all.

How you then prepare your data sets will depend upon scale of the data and your own skill sets. You could simply load it into another Azure SQL Database and then use T-SQL queries to transform the data into your analytic model which Power BI can consume. If the data is bigger, you could consider Synapse which again would use T-SQL queries, or you
could use Spark either in Databricks or Synapse if you are more comfortable with that language.

(continued below because there is a dumb character limit)
Martin Cairney 2,261 Reputation points

2021-10-07T05:48:14.54+00:00

(continued)

The costs will depend almost directly on the data volumes. You'll pay more for the storage as it grows and also the amount of compute resources you need for the transformations will grow and increase in costs as they are mostly all based on active time (using Databricks or On-Demand SQL or Spark pools in Synapse).

There isn't a single best way to do it, but if your data is relatively small to begin with then I'd probably go with Azure SQL Database initially and then move to Synapse if the data volumes warrant it since the changes in T-SQL code between them would be manageable.

Before you make a decision, I think you need to go and learn more about Data Lakes, Databricks, Synapse and Data Factory as you will need to know one or more of these to implement your chosen architecture
Amar Agnihotri 926 Reputation points

2021-10-07T06:36:01.777+00:00

Thanks @Martin Cairney
Now i got an idea about the way that i can go on. One more request, will you please suggest some links so that i could learn and understand the following - Azure Data Lake, Data Bricks, Synapse Analytics from scratch .

Thanks

Share via

Which Azure Technology is best for Data warehousing?

2 answers

Your answer