Explain the difference between star and snowflake schemas.

Correct Answer: Dimensional modeling is often used in Data warehousing In simpler words it is a rational or consistent design technique used to build a data warehouse DM uses facts and dimensions of a warehouse for its design A snow and star flake schema represent data modeling

Correct Answer: OLTP stands for OnLine Transaction Processing Applications that supports and manges transactions which involve high volumes of data are supported by OLTP system OLTP is based on client-server architecture and supports transactions across networks OLAP stands for OnLine Analytical Processing Business data analysis and complex calculations on low volumes of data are performed by OLAP An insight of data coming from various resources can be gained by a user with the support of OLAP

Correct Answer: Business facts or measures and foreign keys are persisted in fact tables which are referred as candidate keys in dimension tables Additive values are usually provided by the fact tables which acts as independent variables by which dimensional attributes are analyzed Attributes that are used to constrain and group data for performing data warehousing queries are persisted in the dimension tables

Correct Answer: Data mining is a method for comparing large amounts of data for the purpose of finding patterns Data mining is normally used for models and forecasting Data mining is the process of correlations, patterns by shifting through large data repositories using pattern recognition techniques Data warehousing is the central repository for the data of several business systems in an enterprise Data from various resources extracted and organized in the data warehouse selectively for analysis and accessibility

Correct Answer: Data warehousing is a process of repository of electronic data of an organization For the purpose of reporting and analysis, data warehousing is used The essence concept of data warehousing is to provide data flow of architectural model from operational system to decision support environments

Correct Answer: View: - Tail raid data representation is provided by a view to access data from its table - It has logical structure can not occupy space - Changes get affected in corresponding tables Materialized view - Pre calculated data persists in materialized view - It has physical data space occupation - Changes will not get affected in corresponding tables

Correct Answer: Dimensional modelling is very flexible for the user perspective Dimensional data model is mapped for creating schemas Where as ER Model is not mapped for creating shemas and does not use in conversion of normalization of data into denormalized form ER Model is utilized for OLTP databases that uses any of the 1st or 2nd or 3rd normal forms, where as dimensional data model is used for data warehousing and uses 3rd normal form ER model contains normalized data where as Dimensional model contains denormalized data

Correct Answer: Data Mart is a data repository which is served to a community of people who works on knowledge (also known as knowledge workers) The data resource can be from enterprise resources or from a data warehouse

Correct Answer: At the time of updating the data warehouse, a lookup table is used When placed on the fact table or warehouse based upon the primary key of the target, the update is takes place only by allowing new records or updated records depending upon the condition of lookup The materialized views are aggregate tables It contains summarized data For example, to generate sales reports on weekly or monthly or yearly basis instead of daily basis of an application, the date values are aggregated into week values, week values are aggregated into month values and month values into year values To perform this process aggregate function is used

Correct Answer: Data cleaning is also known as data scrubbing Data cleaning is a process which ensures the set of data is correct and accurate Data accuracy and consistency, data integration is checked during data cleaning Data cleaning can be applied for a set of records or multiple sets of data which need to be merged Data cleaning is performed by reading all records in a set and verifying their accuracy Typos and spelling errors are rectified Mislabeled data if available is labeled and filed Incomplete or missing entries are completed Unrecoverable records are purged, for not to take space and inefficient operations