CODEWITHSUNDEEP
sqlsql-serverclouddata-warehouse
AJY
AJY

Reputation: 188

Historical solution for a point in time database - Possible data warehouse solution

My company is a membership based organization and we are in a dire need to record granular membership data at the end of every month. Our membership base is continually changing and historical information is not well kept. We hope to reference and compare our membership base on a month to month basis. The issue is, we do no have a solution to do so.

We're looking to push a maximum of 10GB per month onto the cloud.

I'm considering services such as DOMO (that leverages AWS) to aggregate 700K rows of data on a monthly basis.

Our database is an MSSQL database and I am sure we will incur discounts for Azure.

Cost is also a factor on this.

What are my options in term of solutions?

Upvotes: 0

Views: 440

Answers (1)

Marcus D
Marcus D

Reputation: 1144

You basically need a datawarehouse (DWH) there really isn't any way around that.

It is important to separate your transaction (membership) system from your historic data repository (DWH) which you perform reporting and analytics. Companies very often try and save money by doing long term reporting on a transaction system, instead of building a purpose built DWH.

I would disagree with @destination-data about PowerBI. PowerBI is a visualisation tool and not a DWH.

Read about Dimensional Modelling to get an idea of grain, dimensions, facts, etc.

Thinking about your solution, I would suggest you should go for a daily loaded DWH that tracks the new records and changes to your membership base, along with their attendance, usage, transactions, etc. It is important to start small since DWH projects fail quite often because they aim too large and the technology/methodology isn't well understood. Buy a copy of Kimballs' DWH Toolkit, it is excellent!

This will allow you to report on any daily event and produce like for like reporting, month to date reporting, management KPIs, etc.

As far as cost goes, it is going to cost some money, you can't get around that. I would suggest a solution like MS Azure / DWH built on SQL Server Standard Virtual Machine / SSRS reporting / PowerBI Dashboards / Excel Analytics. This can be sliced many ways, but when we looked at the price of AWS compared to Azure there wasn't much in it from a price point of view... This will cost you £100-300/month for the relatively small data volumes you are talking about.

Some of the tricky bits come in when you want to make it secure and add in firewalls / webservers / backups / redundancy etc.

Upvotes: 1

Related Questions