1

After learning about the data warehouse concepts whole day, I couldn't found a source that can actually say when you should not do data warehousing. In most tutorials I read, it says that DW is for getting data from various sources and have it in one place(simplifying).

But my problem is that I have a single database on SQL Server, I need to just have a copy of it that I could use for analysis later on. I don't want to, in fact don't need to perform ETL etc. Just some way to have DW update after every one week or day. Can i skip these steps of designing and ETL for DW. What is the simplest solution and consequences to my problem.

Improve this question
4
  • If you just need a copy of your database, well, just copy it. Why would you complicate your life designing an ETL that does nothing?
    – Mat
    Oct 28, 2019 at 17:43
  • @Mat i don't want to but copying again and again from the beginning takes a lot of time. is there anyway that my copy of database is updated. Maybe i am missing the right path
    – tsu90280
    Oct 28, 2019 at 17:46
  • 1
    Look at what forms of replication your database engine provides, that's usually the way to start investigating.
    – Mat
    Oct 28, 2019 at 17:49
  • @Mat I am using sql server so there are three types but can't figure out which one suites my case. I didn't go through each of them. I am also concerned about the live server being brought down during such experiments.
    – tsu90280
    Oct 28, 2019 at 17:54

3 Answers 3

Reset to default
2

Presumably your database is an OLTP style database that is used as the customer/user facing part of your application(s). OLTP is optimized to support transactions.

A data warehouse is a OLAP style database structured in order to more easily support analytical queries. One aspect of an data warehouse is merging data from different sources into a single consistent schema. Since this is not in the scope of your problem this is irrelevant.

A third approach is to create an ODS. One of the main points of this kind of database is to prevent reports and extracts from interfering with your main OLTP database. These can be used as a source for a data warehouse.

Before you start creating something, you should make sure you understand what problems you have e.g. are you trying to create an source for complex analytics in which case a data warehouse could be helpful or if you are simply trying to run reports on your transactions which might indicate something like an ODS. It's possible that what you really need is something else entirely. Once you can clearly articulate the problem you wish to solve, then you can determine (perhaps in part by asking here) what solution makes sense.

Improve this answer
3
  • The scenario is that I needed access to data for analysis in python. The org cannot give access to live db. so, one option is to backup/restore(currently doing), but takes a lot of time. Looking for some other option, DW is one but it's complex and we probably don't need it, as not looking for reporting/analysis over db environment.
    – tsu90280
    Oct 29, 2019 at 1:49
  • @tsu90280 I'm not very familiar with what's available for SQLServer but there tools that can replicate a DB in real-time from the journal and/or storage primitives. You might need to pay for such a solution. As long as you aren't trying to write back to the database, this is definitely feasible.
    – JimmyJames
    Oct 29, 2019 at 14:06
  • @tsu90280: you wrote "The scenario is that I needed access to data for analysis" - come on, this is pretty vague. What kind or types of analysis? Follow the advice from this answer: clearly articulate the problem you wish to solve. That gives people a chance to answer. Moreover: how long does a backup take, and how up-to-date do you need the data for your analysis?
    – Doc Brown
    Oct 31, 2019 at 23:28
0

First, you can take a backup of your database and restore it on another server to serve as your data warehouse. This would be better than copying. Just set up a job to do this.

Second, the reason for the data warehouse is your reporting needs do not slow down your transactional needs.

Typically, database are built for either transactional or reporting. You may need additional indexes on reporting that are not required for the transactions.

Improve this answer
3
  • this is what I am doing right now, but that takes time and when i want update my backup i have to backup from the 1st row, looking for something that allows me to update from the last row in replicated/backup.
    – tsu90280
    Oct 28, 2019 at 18:12
  • Whats wrong with doing a full backup? You want all the data for the reports/queries, or are you looking for a delta.
    – Jon Raynor
    Oct 28, 2019 at 20:41
  • every time I want new data for analysis, i have to do backup and restore of full live database. Or, is there a way to start backup from the point where i last left? If not, then this process is extremely time consuming
    – tsu90280
    Oct 29, 2019 at 1:54
0

A data warehouse is usually a denormallalised copy of data from your transactional db.

So say you have customer and orders tables in your normal db. your data warehouse would have one big customerOrders table with all the columns.

Once you understand this principle you can then see that you need a transformation layer.

Once you have a transform layer, you have a place to put your selection criteria in.

So I dont want all the customer and orders, just orders since my last export and the customer that go with them. and I can specify that in my transformation/export.

MSSQL has a number of tools for this process if you cant program it. SSIS for example.

Improve this answer

Your Answer

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

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