Skip to main content

How do I build a data warehouse?


The steps are as follows:
  1. Start with understanding the process followed by the org. / company for which you’re thinking of creating a Datawarehouse. Decide whether you need just a Database or a Data Federation or a complete Datawarehouse.
  2. Create a list of questions that you want to answer for the business (that’ll help the business analyse data better & make better decisions using the data) through the datawarehouse.
  3. Find out the Data Source Points & the kind of systems that the data resides in (For example, some data might be in files whereas some might be in Websites like twitter / facebook or databases such as Oracle / SQL Server / MySQL).
  4. Build a Requirement Specification Document & get it approved / reviewed by the Business Owners.
  5. Perform Data Modelling to create a set of tables that you’d need in the Datawarehouse / Database. Also define the kind of dimensions (Slowly Changing, Degenerated, Conformed, Static, etc) & facts (Early Arriving) in case you’re planning to go for Dimensional Data Modelling.
  6. Prepare a Technical Design Document (&/or Mapping Sheet). This should ideally have the exact information on how each column in your datawarehouse is loaded & what exactly are the business rules.
  7. Choose the ETL Tool (or programming language) that is best suited to your company’s budget & datawarehouse requirements.
  8. Create ETL Jobs / Mappings / Pipelines & schedule them depending on how often you want the data to be refreshed in the DataWarehouse.
  9. Re-iterate the process if there are changes (the model where changes are expected soon, is called the Agile Model; the other one where no changes are required anytime soon, is called the Waterfall Model). Don’t forget to prepare a timeline at each step & decide estimated time before you enter the first / third step.
  10. Optimise any step(s) implemented.
(B) Which are the different kinds of building?
There are 2 approaches to data warehousing:
1. Kimball or Bottom-up Approach
In this approach, the data marts facilitating reports and analysis are created first; these are then combined together to create a broad data warehouse.
2. Inmon or Top-down Approach
In this approach, a normalized data model is designed first. Then the dimensional data marts, which contain data required for specific business processes or specific departments are created from the data warehouse.
Note: William H. Inmon (Bill Inmon) is recognized by many as the father of the datawarehouse
(C) What is the process involved using an ETL tool like Informatica?
When using an ETL Tool like Informatica, ETL Developers use GUI components of Informatica to build mappings while referring the mapping sheet or technical design document. The advantage that these tools provide are 1. Visual flow 2. Structured system design 3. Operational resilience 4. Data-lineage and impact analysis 5. Advanced data profiling and cleansing 6. Performance. For detailed info. see: The 7 biggest benefits of ETL tools
(D) How do you test it?
The ETL Developers perform testings such as Unit Testing & Peer Review before sending the code to QA / Testing Team.
ETL Testers can either test it using GUI based ETL Testing Tools like Data Validation Option (DVO) or using SQL Queries or via Manual validations all three of these following the steps as mentioned below:
  1. Analyse Business Requirements & Understand the Business Process using documents like Business Requirement Specification Document, Technical Design Document / Mapping Sheet, etc.
  2. Prepare Test Cases
  3. Implement Test Cases
  4. Share Results with the team & Perform regression testings whenever required.
(E) How can you build data mart using this?
Data Marts are built depending on the requirements of the teams who want a specialised view on data for a specific purpose. Read this classic e-book on Data Marts for details: http://ftp://ftp.wiley.com/public/computer_books/updates/guide.pdf
F. Is data mart going to be OLTP or OLAP? I believe , DWH will be and has to be OLAP.
OLTP (Online Transaction Processing) are the systems from which a Datawarehouse is built. A Datawarehouse is an OLAP (Online Analytical Processing) & so are the Data Marts built from it.
The difference between these systems can be read from: What is the difference between OLTP and OLAP?
(F) What will the DWH be, when it is in star schema?
When a DWH is in Star Schema, it’ll look like the diagram below:-
See details on the following link: Star schema
Hope this answer would be read completely atleast by one. If possible, next time please use a different thread for each of such questions :)
Labels:

Comments

Post a Comment

Popular posts from this blog

How to find and remove duplicate files from PC uplicate files can cause all kinds of problems on your computer, taking up precious storage room, confusing your photo or media manager apps, and generally getting in the way of searches and other operations when you'd rather they didn't. You don't have to accept duplicate files as an inevitability of running a PC, though: Here's how to get rid of them. Duplicate files can crop up for all kinds of reasons: Maybe you downloaded a file, forgot it was there, and then downloaded it again; maybe you copied a folder of images and then never got around to getting rid of the originals. Whatever the reason, they clog up your disk space and shouldn't be left to gather dust. For the purposes of this guide we're going to use DupeGuru-it's Lifehacker's pick as the best duplicate file finder for Windows, and it's available for Mac and Linux as well. If you're not taken with DupeGuru, then some of the best al...
Post a Comment
Read more

Gmail Automation

Gmail Automation: 5 Useful Google Scripts to Automate Your Gmail 1. Auto delete emails after X number of days Very often, after we read the email, we will just keep it in our inbox, regardless whether it is useful or not. While Google gives you tons of space to store your emails, you might still want to clean up your inbox and get rid of those useless emails. The following script can check emails with the “Delete Me” label and delete them after “x” number of days. 1. Go to  Google Scripts  and create a blank project (make sure you are logged into your Google account). Paste the following script and save it. function auto_delete_mails ( ) { var label = GmailApp.getUserLabelByName ( "Delete Me" ) ; if ( label == null ) { GmailApp.createLabel ( 'Delete Me' ) ; } else { var delayDays = 2 // Enter # of days before messages are moved to trash var maxDate = new Date ( ) ; maxDate.setDate ( maxDate.getDate ( ) -delay...
Post a Comment
Read more

iPhone 7 and iPhone 7 Plus review: The best iPhones Apple has made

iPhone 7 and iPhone 7 Plus review: The best iPhones Apple has made 's tough meeting expectations years after year. And yet, Apple keeps doing it, although no longer as effortlessly as it would do earlier in this decade. The company launched the iPhone 7 and the iPhone 7 Plus on October 7 in the Indian market, a month after the global announcement of the two phones. These are the phones that look like incremental update to the iPhone 6S and the iPhone 6S Plus. But looks can be deceiving. And they surely are in this case. Of late, the iPhone is facing tough challenge from others in the market. The Galaxy S7 arguably has a better design. The Samsung phone also has better screen and, in most cases, better camera. Google's Nexus phones have better software. The HTC offers better sound in its high-end phones. The Sony phones are waterproof. All of this puts the iPhone at a back-foot. The good thing is that Apple knows these challenges and is meeting them head-on with t...
Post a Comment
Read more