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

Fix error 0xc000007b “The Application Was Unable To Start Correctly”

Fix error 0xc000007b “The Application Was Unable To Start Correctly” Windows is one of the most popular OS ( Operating System ) used today by several users. There are lot of versions available for the Windows. The most popular version of the window is Window 7.0. Some user switches to the newest version but still there are lot of people who is using the older version. There is one of the most common error  “The application was unable to start correctly 0xc00007b”  is faced by the all windows user. Today we are going to show you how to solve this error.  Before we get to the solutions of this error let’s take a look at the reason behind this error. There could be any reason of this error but we listed some of the most common reason for this error. The 32-bit app tries to execute on a 64-bit system. You might be trying to play a Game on your PC and after launching the application, error code  0xc00007b  may flash on your screen. You might be...
Post a Comment
Read more
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

Dynamic Host Configuration Protocol (DHCP)

The Dynamic Host Configuration Protocol (DHCP) is a network service that enables host computers to be automatically assigned settings from a server as opposed to manually configuring each network host. Computers configured to be DHCP clients have no control over the settings they receive from the DHCP server, and the configuration is transparent to the computer's user. The most common settings provided by a DHCP server to DHCP clients include: IP address and netmask IP address of the default-gateway to use IP adresses of the DNS servers to use However, a DHCP server can also supply configuration properties such as: Host Name Domain Name Time Server Print Server The advantage of using DHCP is that changes to the network, for example a change in the address of the DNS server, need only be changed at the DHCP server, and all network hosts will be reconfigured the next time their DHCP clients poll the DHCP server. As an added advantage, it is also ...
Post a Comment
Read more