What is our primary use case?
We are developing our SQL Data Warehouse locally, using SQL Server 2016 on our private cloud, and we will be migrating it to Azure once it is ready.
The company for which I am working deals with products in the leisure industry, such as museums and swimming pools. They use the data warehouse solution for big data reporting. Currently, we are developing a multi-platform data warehouse that can support the reporting requirements for all of the customers. Whatever analytics they want to perform will be done with the data warehouse, and not the SQL database application.
My role is to design the data warehouse, and once that is complete, we need to build the data pipelines so that the data can be moved from the application databases into the warehouse. Then we will design the OLAP servers and OLAP cubes, vertically, to give us a presentation layer on top of the data warehouse. This layer is what the customers will be using.
What is most valuable?
The most valuable feature is the incremental load because we do not need to refresh the entire data on a daily basis. We have a nightly load where all of the data is updated, and it is a very good feature.
The transaction log shipping gives us the ability to replicate the database onto different servers for backup and synchronization.
What needs improvement?
The major concern is that I have a hard time with having to version control the data warehouse all the time. As it is now, I have to open all of the ports and push everything onto the server. I would like to see version control implemented into the data warehouse. This would make the tool perfect.
For how long have I used the solution?
I have been using this Microsoft Azure SQL Data Warehouse for about two and a half years.
What do I think about the stability of the solution?
The version that we have been using, 2016, is stable. We have not used the 2019 version in production yet, but there is a team that is evaluating that version right now. I'm not sure of the reason for it not being in production, whether it is related to stability or not.
What do I think about the scalability of the solution?
The scalability is good. I think we are already using a high availability cluster for 2016, so I assume that it will be the same with the 2019 version. We are also using it with our data recovery system, so it is not an issue.
We have approximately twelve to fifteen developers and approx. 900+ customers who will be using the data warehouse. We are acquiring customers on a monthly or quarterly basis, so it is a pretty big user base and we expect that our usage will continue to increase.
How are customer service and technical support?
We don't have too many technical issues, so overall, I think that it's good enough.
Which solution did I use previously and why did I switch?
I began with Microsoft SQL Server and have been on the Microsoft platform ever since.
How was the initial setup?
The initial setup is pretty straightforward. It has been a long time since we first implemented this solution, but since that point, we have created a custom handbook that we can follow in order to set it up.
What other advice do I have?
There are certain design concepts that can be used to design a data warehouse. My advice to anybody who is implementing this solution is to first study the user's needs, and then go with that approach. The design will be specific to the needs.
I would rate this solution an eight out of ten.
Which deployment model are you using for this solution?