4

I work for the insurance company little more than a year, my SQL experience around 2-3 years including SSIS, SSRS. We have approximately 1 TB of data.

Is it possible to build data warehouse by myself? Should I do that with my experience?

There are lots of materials and SQL groups I can get help from. But still, is it too complicated?

Thanks

1
  • Why are you building a data warehouse? If the answer is not "the business unit asked us to do so" you are looking at a failed project
    – billinkc
    Apr 28, 2017 at 18:49

3 Answers 3

9

It's a lot like asking, "Can I build a house by myself?"

It depends a lot on what your definition of "house" is. Talk with your internal users to build a set of requirements first - that's going to need to happen regardless of who builds it.

When you're done building requirements, then you can start sketching out what you'll need to build in order to satisfy those requirements.

Then, you can start building time estimates for what needs to be built.

As you work through this process, you'll have a better understanding of what the house looks like and whether you can build it yourself with your existing toolbox and skills.

7

Along the same lines as @Brent's statement of, "It depends a lot on what your definition of 'house' is", you need to find out what "Data Warehouse" means to those who requested it. The term "Data Warehouse" is sometimes used when it shouldn't be. Do they want a system that aggregates data back to the beginning of time and is loaded nightly and is missing current day transactional data? Do they want real-time reporting including current transactional data with a time delay of no more than 1 - 5 minutes?

I can tell you, from (not-so-pleasant) experience, that:

  1. True data warehousing is a different way of looking at the world and how data is stored and queried. Do not make the mistake of thinking that you will simply be working with larger tables. Several years of experience working on the transactional side, even working with SSIS and SSRS, does not enable one to magically create a real data warehouse. (conversely, being great at data warehousing does not magically translate into being any good on the transactional side!)
  2. You should start reading up on data warehouse concepts to get a sense of what you are looking at as it will help facilitate getting a proper answer to "what do you mean by data warehouse". If you don't know what that term means, then asking those who are asking you for this won't be very productive.
  3. Talk to someone who has been working with data warehouses for years to get better pointers than I am giving here as to where to start your educational process. Especially try to get at what makes a data warehouse a data warehouse.
  4. Be clear with those making this request of you that there are trade-offs built into the design of a proper data warehouse and so changing the requirements later (such as to take a historical reporting system and then introduce near-real-time data a year later) will result in either taking a very long time to modify the system to account for the new requirements, or possibly destabilizing the system by forcing it to do conflicting features such that the code base becomes harder and harder to maintain over time until the entire project becomes a liability due to even simple requests taking weeks to accomplish and/or breaking existing functionality each time.
0
2

Another avenue would be to use a data warehouse as a service product. Many vendors are offering this now, including Oracle, Snowflake, Microsoft and Amazon. These products typically have features that will guide you in building your data warehouse. Redshift is a big player in this area, and now there’s Amazon Athena, a serverless option, which seems to be for smaller data needs. That may be another way to go.

One of the first questions would be, what’s the difference? Why would a business choose one over another? Well, to begin with; they all share these features:

• Cloud-based

• Some level of administrative/management interface

• The ability to scale up and down as needed

• No hardware on premise

• Limited or no software to purchase (IMO, this greatly depends on the tools you want or need for analyzing your data.)

Other considerations will be unique to each business:

• Data set size, and estimated growth.

• Data complexity.

• Administrator resources and capabilities.

• Technical resources available/IT staff.

• User population and demographics. Yes, these are cloud-based solutions but getting data up there requires appropriately sized and stable Internet connections.

• Data integration long term goals. What is the purpose of your data warehouse? Who are your main users? What are your anticipated future data needs? For example, if acquisitions are a strong possibility then incorporating dissimilar data sets should be considered.

Then look at your chosen vendors/products and see how they match up. Make sure you have your deal breakers identified. Are there any tradeoffs that would favor one solution over another? That should get you to a list of finalists to review closely. Do your due diligence and you can get to a solution that works technically, financially and from a business standpoint.

There are many options today, so do your research. http://searchdatamanagement.techtarget.com/definition/data-warehouse-as-a-service-DWaaS

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.