What is Data Warehousing?

A data warehousing is where anyone can store data from multiple data sources to be used for historical and trend analysis reporting.
Its acts as a central repository for many subject areas and contains the `single version of truth`.

Benefits of a Data Warehouse
A data warehouse maintains a copy of information from the source transaction systems.
This architectural complexity provides the opportunity for:
  • Maintain data history
  • Integrate data from multiple systems
  • Improve data quality
  • Present the organisation's information consistently
  • Provide a single common data model for all data of interest regardless of the data's source
  • Restructure the data so that it makes sense to the business users
  • Restructure the data so that it delivers excellent query performance, even for complex analytic queries
  • Add values to operational business applications

Types of Data Warehouses
Three types of data warehouses
  1. EDW - Enterprise Data Warehouse
  2. ODS - Operational Data Store
  3. Data Mart
  • Enterprise Data Warehouse - An enterprise data warehouse provides a central database for decision support throughout the enterprise
  • Operational Data Store - This has a broad enterprise wide scope, but unlike the real enterprise data warehouse, data is refreshed n near real time and used for routine business activity.
  • Data Mart - Data mart is a subset of data warehouse and it supports a particular region, business unit or business function.

How to build a Data Warehouse
  • Design - Top Down, Bottom Up
  • Customer Interviews and requirement gathering
  • Data profiling
  • Extract Transform Load data from source to data warehouse
  • Create Facts and Dimensions
  • Put a BI tool on top
  • Develop reports
  • Data Governance

Data Warehouse Roles
  • Business User - Client
  • Business User Support
  • Data Administrator
  • Data Analysts
  • Meta Data Administrator
  • Database Administrator
  • Developers (ETL / BI - Reports, Queries)

One Liner - a large store of data accumulated from a wide range of sources within a company and used to guide management decisions.

Comments

Post a Comment

Popular posts from this blog

GP - Kerberos errors and resolutions

Issue:1. Ticket granting has expired with the error message, ERROR: psql: GSSAPI continuation error: Unspecified GSS failure. The Minor code may also display the error message, GSSAPI continuation error: Ticket expired. Solution: Create a new ticket on the Master server using the following command: kinit -k -t /home/gpadmin/gpdb-kerberos.keytab db_user Note: -t is the keytab file and db_user is the user. Issue:2. The library required to run the kinit command does not exist in the LD_LIBRARY_PATH  set in the Greenplum binaries, producing the following error message: ERROR: kinit: relocation error: kinit: symbol krb5_get_init_creds_opt_set_fast_ccache_name, version krb5_3_MIT not defined in file libkrb5.so.3 with link time reference Solution: Unset the LD_LIBRARY_PATH. IMPORTANT NOTE: The above fix will cause the following error when you try to run Postgres commands:  ERROR: psql: error while loading shared libraries: libpq.so.5: cannot open shared object ...
Read more

How to set Optimizer at database level in greenplum

How can I turn ON optimizer at Database level? You can set the optimier ON at database level using below command; ALTER DATABASE databasename SET OPTIMIZER = ON; Once this is done, kindly do reconnect to Database to see the change. How do I check if optimizer is set (ON/OFF) in a given database? You can use the show command as below to check psql -d databasename -c 'show optimizer'
Read more

GP - SQL Joins

Image
Available Join Types? Row Elimination Minimize Data Movement Join Implementation Seen in Query Plans Join Types: Inner Join Resulting data set is obtained from joining two tables on a common column Each row in left table is compared with each row in right table Matching rows will be present in result set Most commonly used Also know has Simple Join or EQUI-Join Left Outer Join Returns all rows from the left table even if there is no matching rows in right table. Will also get the matching rows Rows that are not matched in the right table will not be included in the result set Right Outer Join This is similar to left outer join, just need to flip the join Full Outer Join Will return all rows from both the tables where there is a match We will receive a NULL for rows which don’t match Cross Join Which is also called has Cartesian products Result set will have all rows on the left table paired with all rows on the right table Exam...
Read more