BITS WILP Data Warehousing Quiz-1 2017-H2

BITS WILP Data Warehousing Quiz-1 2017-H2

Question 1.

Answer 1.

From “Data Warehouse Toolkit: The Complete Guide to Dimensional Modeling - By Ralph Kimball, Margy Ross”

...
Question 2.

Answer 2.
Knowledge management (KM) is the process of creating, sharing, using and managing the knowledge and information of an organisation. (Wikipedia)

Question 3.

Answer 3.

URL: https://www.tutorialspoint.com/dwh/dwh_quick_guide.htm

Data Warehouse Features

The key features of a data warehouse are discussed below:

• Subject Oriented - A data warehouse is subject oriented because it provides information around a subject rather than the organization's ongoing operations. These subjects can be product, customers, suppliers, sales, revenue, etc. A data warehouse does not focus on the ongoing operations, rather it focuses on modelling and analysis of data for decision making.
• Integrated - A data warehouse is constructed by integrating data from heterogeneous sources such as relational databases, flat files, etc. This integration enhances the effective analysis of data.
• Time Variant - The data collected in a data warehouse is identified with a particular time period. The data in a data warehouse provides information from the historical point of view.
• Non-volatile - Non-volatile means the previous data is not erased when new data is added to it. A data warehouse is kept separate from the operational database and therefore frequent changes in operational database is not reflected in the data warehouse.

Note: A data warehouse does not require transaction processing, recovery, and concurrency controls, because it is physically stored and separate from the operational database.

Question 4.

Answer 4.

URL: https://christianvdheever.wordpress.com/2012/11/01/surrogate-keys-and-foreign-keys-in-a-dimensional-data-model/

As Per Ralph Kimball : Surrogate keys “ One of the primary benefits of surrogate keys is that they buffer the data warehouse environment for operational changes” Ok so what is he saying – imagine you have used the Product code as key and the operation system re-uses product code 1 what do you now do with the rest of the old data?
So do not use a business bound soft coded values (Like product code or CIF number) as a Key this will become a major flaw in you design
Surrogate keys value

·         Enables ETL Updates to do slowly changing dimensions (Separate blog entry)

·         Binds table together in Dimensional Model

This key can also be the primary key (U-key) on the table

Question 5.

Answer 5.

URL: https://www.ibm.com/support/knowledgecenter/en/SS9UM9_8.5.0/com.ibm.datatools.dimensional.ui.doc/topics/c_dm_design_cycle_2_idgrain.html

When you identify the grain, you specify exactly what a fact table record contains. The grain conveys the level of detail that is associated with the fact table measurements. When you identify the grain, you also decide on the level of detail you want to make available in the dimensional model. If more detail is included, the level of granularity is lower. If less detail is included, the level of granularity is higher.

Question 6.

Answer 6.

From “Data Warehousing Fundamentals - By Paulraj Ponniah”

Question 7.

Answer 7.

URL: https://www.allinterview.com/viewpost/411901/conformed-dimensions-either-identical-strict-mathematical-subsets-most-granular-.html

Conformed dimensions are either identical or strict mathematical subsets of the most granular, detailed dimension. Dimension tables are not conformed if the attributes are labeled differently or contain different values. Conformed dimensions come in several different flavors. At the most basic level, conformed dimensions mean exactly the same thing with every possible fact table to which they are joined. The date dimension table connected to the sales facts is identical to the date dimension connected to the inventory facts.

Question 8.

Answer 8.

URL: http://etldatabase.com/etl-transform/

Multi-stage data transformation – this approach follows the classic extract, transform, load process. Extracted data is moved to a staging area where transformations occur prior to loading it into the warehouse.

URL: https://archive.org/stream/IT_For_Business/unit_17_djvu.txt

Data transformation involves many forms of combining pieces of data from the

different sources. In some cases, data from a single source record or related data elements from many source records are combined. In other situations, data transformation may also involve purging source data that is not useful and/or separating out source records into new combinations. During data

transformation sorting and merging of data takes place on a large scale in the data staging area. 

Question 9.

Answer 9.

From “The Data Warehouse Toolkit: The Complete Guide to Dimensional Modeling - By Ralph Kimball, Margy Ross”

Question 10.

Answer 10.

“Processing business transactions (e.g., generate invoice, payments, orders, etc.)” is a day-to-day business operation and no strategic information is required for carrying it out.