Data Warehousing 2015

1. Which of the following implements Multidimensional OLAP?

Answers:

• Microsoft SSAS

• Oracle Essbase

• IBM TM1

• All of the Above

2. What is a Data Mart?

Answers:

• an online, open exchange in which organizations can trade business information

• a schema that organizes data into facts and dimensions

• a central repository where separate organizations can securely backup data

• an access layer comprising a subset of a data warehouse

3. When is it appropriate to use a Joiner Transformation during ETL?

Answers:

• All of the Above

• When the two sources have a primary-key to foreign-key relationship

• When the two sources are heterogeneous

• When the two sources have multiple matching columns

4. Which system is appropriate for recording customer transactions in real time?

Answers:

• Star Schema

• OLAP

• Snowflake Schema

• OLTP

5. Which of the following is true about Snowflake schema?

Answers:

• It is a variation of the star schema

• All of the above

• It is represented by centralized fact tables

• Its a logical arrangement of tables in a multidimensional database

6. Which of the following uses the idea of a data cube to represent the dimensions of data ?

Answers:

• OLTP

• Relational Database

• OLAP or Multidimensional Database

• ODS

7. Which of the following is true about Data Warehousing?

Answers:

• It is copy of transaction data specifically structured for query and analysis

• All of the given options are true

• It is designed to facilitate reporting and analysis

• It is a non-volatile time-variant repository

8. Data Warehouse (OLAP) is based on which of the following?

Answers:

• All of given options are valid

• Fact Constellation Schema

• Snowflake Schema

• Star Schema

9. What is the process by which raw data is migrated into a data warehouse?

Answers:

• Extract Test Language

• Extract, Transform, Load

• Export, Transmit, Load

• Export, Translate, Load

10. Which of the following are valid Logical Extraction methods?

Answers:

• Incremental Extraction

• Online extraction

• Full Extraction

• Both Full Extraction and Incremental Extraction

11. Which of the following is TRUE about MOLAP?

Answers:

• MOLAP cubes are built for fast data retrieval

• Data is stored in a multidimensional cube.

• MOLAP cubes are optimal for slicing and dicing operations

• All of the given options are valid

12. Which of the following is VALID about time-variant characteristic of a data warehouse?

Answers:

• Time horizon of a data warehouse is significanlty longer than that of operational systems

• All of the given options are valid

• Data warehouses contain data that is generally loaded from the operational databases on a regular interval

• Data Warehouse maintains both historical and (nearly) current data

13. Which of the following is true about Data mining?

Answers:

• It is the process of finding correlations or patterns among various fields

• It is the process of analyzing data from different perspectives and summarizing it into useful information

• All of the given options are valid

• It allows users to analyze data from many different dimensions or angles

14. Which of the following systems is optimized for multidimensional analysis?

Answers:

• ETL

• OLAP

• SQL

• OLTP

15. Operational Database(OLTP) is based on which of the following?

Answers:

• Snowflake Schema

• Star Schema

• Fact Constellation Schema

• Entity Relationship Model

16. Which of the following is TRUE about ROLAP?

Answers:

• All of the given options are valid

• Can handle large amounts of data

• Performance can be slow

• Can leverage functionalities inherent in the relational database

17. What table is at the center of a star schema?

Answers:

• metadata table

• fact table

• dimension table

• core table

18. What is an example of an OLAP dice operation?

Answers:

• randomizing the year and region

• randomizing the year

• specifying a particular year

• specifying a particular year and region

19. What is the "grain" of a fact table?

Answers:

• the direction along which additive measures can be combined

• the most atomic level at which the facts may be defined

• the raw data from which the facts are derived

• the ratio of facts to dimensions

20. A data warehouse is built as a separate repository of data, different from the operational data of an enterprise because?

Answers:

• It is necessary to keep the operational data free of any warehouse operations

• It contains data derived from multiple sources

• A data warehouse contains summarized data whereas the operational database contains transactional data

• A data warehouse cannot afford to allow corrupted data within it

21. Which MDX operation returns all possible combinations of one or more sets?

Answers:

• Distinct

• AllMembers

• Crossjoin

• Leaves

22. Which of the following is true about Specialized SQL servers?

Answers:

• It uses array-based multidimensional storage engines

• It uses the two level of data storage representation to handle dense and sparse data sets

• It provides advanced query language and query processing support for SQL queries over star and snowflake schemas

• It facilitates OLTP operations in SQL

23. Which of the following operations navigate data from less detailed data to highly detailed data?

Answers:

• Roll-up

• Roll-down

• Drill-up

• Drill-down

24. Which of the following is true about 'Pivot' operation?

Answers:

• It is also known as rotation

• Two consecutive slice operations in two different dimensions

• All of the given options are valid

• It rotates the data axes in view in order to provide an alternative presentation of data

25. Where does cleansing take place during the ETL phase of data bound for a MOLAP based data warehouse?

Answers:

• Cleanroom Table

• Staging Area

• No distinct cleansing phase, data cleansed during MDX queries

• ODS

26. The ‘Slice’ operation deals with?

Answers:

• Merging the cells of all but one dimension

• Merging the cells along one dimension

• Selecting all but one dimension of the data cube.

• Selecting the cells of any one dimension of data cube

27. Which language is appropriate for querying a non-relational OLAP database?

Answers:

• MDX

• Fortran

• SPSS

• SQL

28. Which of the following is a method for delta detection during the ETL process?

Answers:

• CRC

• audit columns

• (both of these choices)

• (none of these choices)

29. Choose the most correct statement:

Answers:

• Fact Constellation schema contains two Fact tables

• Star and snowflake schema contains two Fact tables

• Star schema contains two Fact tables

• Snowflake schema contains two Fact tables

30. Which of the following is true about Data Scrubbing?

Answers:

• It involves data cleansing

• Standardization of data cannot be considered as Data Scrubbing

• Harmonization of data cannot be considered as Data Scrubbing

• It is same as data validation

31. Modifying an OLAP Cube to view information from only a single year is an example of which operation?

Answers:

• pivot

• dice

• slice

• join

32. What is an OLAP Cube?

Answers:

• None of the Above

• an array in which data is stored and characterized by multiple dimensions

• a three-dimensional array for Online Analytical Processing

• a dimensional-reduction operation that summarizes data

33. Which of the following environments has a higher amount of Joins

Answers:

• OLAP

• OLTP

• Data Warehousing env

• Database

34. Which of the following would not be a part of the fact table of a star schema representing sales data?

Answers:

• Units sold

• None of the Above

• Store UID when a Store dimension table exists

• Product name when a Product dimension table exists

35. Which of the following is NOT a benefit of data warehousing?

Answers:

• To maintain data history

• To integrate data from multiple source systems

• To collate data from multiple sources into a multiple databases

• To enhance data quality

36. Which of the following is not a VALID characteristic of data warehouse?

Answers:

• Volatile

• Nonvolatile

• Integrated

• Subject Oriented

37. Which of the following contains historical data?

Answers:

• OLTP

• Both OLAP and OLTP

• Neither OLAP nor OLTP

• OLAP

38. Which of the following is an example of a non-aggregatable measure?

Answers:

• costs

• gross profits

• probability of default

• transaction volume

39. Which line is not a valid MDX comment?

Answers:

• // this line

• /* this line */

• -- this line

• ## this line

40. Which of the following can present summary data from an OLAP server?

Answers:

• All of the Above

• normalization

• primary keys

• pivot tables

41. Which of the following is a valid disadvantage of a dimensional approach?

Answers:

• Loading the data warehouse with data from different operational systems is complicated

• The retrieval of data from the data warehouse tends to become slow

• Data warehouse gets complicated for the user to understand and to use

• Dimensional structures are difficult to understand for business users

42. Which of the following enviroments are in third normal form 3NF?

Answers:

• OLTP

• All of the options are correct

• OLAP

• Data Warehousing env

43. Which of the following operation is performed by introducing new dimensions?

Answers:

• Roll-down

• Roll-up

• Drill-up

• Drill-down

44. Which of the following is true about 'Dice' operation?

Answers:

• It forms a new sub-cube by selecting one or more dimensions

• The Dice operation performs selection of one dimension on a given cube

• The Dice operation performs selection of two or more dimension on a given cube

• It navigates the data from less detailed data to highly detailed data

45. Which operation on a multidimensional database is most equivalent to a relational join?

Answers:

• rotating

• dice

• slice

• drill-across

46. What Amazon Reshift compression encodings would be most appropriate for the columns (Date, City, Description), respectively?

Answers:

• Byte-dictionary, LZO, Delta

• Delta, Byte-dictionary, LZO

• MOSTLY8, Runlength, Raw

• LZO, Delta, Raw

47. Choose the most correct statement:

Answers:

• Snowflake schema contains normalized Dimension table

• Star schema contains normalized Dimension table

• Snowflake schema contains denormalized Dimension table

• Fact Constellation schema contains denormalized Dimension table

48. Which of the following is true about multidimensional models?

Answers:

• It uses just one level of data storage representation to handle sparse data sets

• It uses one level of data storage representation to handle both dense and sparse data sets

• It uses just one level of data storage representation to handle dense data sets

• It uses two level of data storage representation to handle dense and sparse data sets

49. Which of the following is not a type of OLAP?

Answers:

• Analytical

• Hybrid

• Multidimensional

• Relational

50. Which of the following schema is generally the most denormalized?

Answers:

• Reverse Snowflake

• Star

• Snowflake

• Hub and Spoke

51. In which of the data storing approaches does transaction data get partitioned into facts?

Answers:

• Both dimensional and normalized approach

• dimensional approach

• Neither dimensional nor normalized approach

• normalized approach

52. In a 3-tier architecture of the data warehouse, the OLAP engine is related to:

Answers:

• The third layer of the architecture

• The first layer of the architecture

• The Second layer of the architecture

• The fourth layer of the architecture

53. In order to populate the data warehouse, which of the following set of operations are appropriate?

Answers:

• Create and edit

• Refresh and load

• Insert and delete

• Query and update

54. Which of the following applies to Fact Tables?

Answers:

• Completely normalized

• Completely denormalized

• Partially normalized

• Partially denormalized

55. What is the principal disadvantage of Type 3 Slowly Changing Data management?

Answers:

• Stores only a finite number of historical changes

• Significantly increases storage requirements per record

• Does not track historical data

• Updates obsolete data with new values infrequently

56. Which of the following is the most appropriate data structure for indexing a MOLAP Cube?

Answers:

• Bitmap index

• Index table

• Huffman encoding

• B-Tree

57. Which of the following is a property of Data Warehouse data?

Answers:

• Write Only

• Read and Write

• Read Only

• Write Deconditional

58. Which of the following is not a property of a perspective?

Answers:

• Can securely restrict access to objects in a cube

• Can hide any number of dimensions from a cube

• Can provide business-specific viewpoints of a cube

• Can display hierarchies from a cube

59. Which of the following operations performs aggregation on a data cube by dimension reduction?

Answers:

• Drill-up

• Roll-down

• Drill-down

• Roll-up

60. Which of the following is not TRUE about subject oriented characteristics of a data warehouse?

Answers:

• Data is manipulated to provide information about a particular subjec

• Data warehouse has a defined scope

• Data that are never deleted once they have been added

• Data should not change once it is entered into the warehouse

61. ‘ROLAP’ is preferred over ‘MOLAP’ in which of the following cases?

Answers:

• When fast data retrieval is required

• A data warehouse and relational database are separable

• When there is a requirement to perform complex calculation

• When slicing and dicing is required

62. Which of the following is not an advisable implementation of division in MDX?

Answers:

• measures.[Value A]/measures.[Value B]

• iff(measures.[Value A]=0, null, measures.[Value A]/measures.[Value B])

• Divide(measures.[Value A]/measures.[Value B], null)

• None of the Above

63. Among the following, which is NOT the objective of clustering?

Answers:

• It facilitates OLAP operations

• To initiate hypothesis about the data

• To find consistent and valid organizations of the data

• To cover natural groupings

64. An ETL Joiner Transformation is performed on Source A with 10,000 rows and Source B with 25,000 rows. Which source should be the master?

Answers:

• None of the Above

• Source A to speed up the join process

• Source B to prevent rows from being omitted

• There is no master-detail distinction, Joiner Transformations gives both sources parity

65. Which of  following stages involve data update in the warehouse for every transaction performed on the source data?

Answers:

• Integrated data warehouse

• Offline operational data warehouse

• On time data warehouse

• Offline data warehouse

66. When is a column-based engine a more appropriate architecture for a Data Warehouse than a row-based engine?

Answers:

• small number of known indexes to be created, static query patterns

• most columns in a table will be required for a query

• a column-based engine is only appropriate for OLTP applications

• dynamic, unpredictable query patterns

67. Which of the following is not a valid context for the MDX From clause?

Answers:

• None of the Above

• a subcube (nested MDX query)

• a cube

• a perspective (subset of measures and dimensions

68. What is true of querying normalized data in a relational database for the purposes of business analysis?

Answers:

• All of the Above

• The data must first be placed into a data warehouse via the ETL process

• Aggregating relevant data requires the creation of new primary keys

• Aggregating relevant data requires multiple table joins

69. What are the following is not a requirement of First Normal Form?

Answers:

• None of the Above

• No columns that are not dependent on a primary key

• No duplicate columns within a single table

• Separate tables for each group of related data with a unique identifier for each row

70. Choose the most correct statement:

Answers:

• Data mining and data warehousing are totally unrelated

• Data mining and data warehousing are the same

• Data warehousing is the application of data mining

• OLAP is more flexible then OLTP

71. What is a slice in the context of Amazon Redshift?

Answers:

• A client-facing partition that isolates different parts of the data warehouse for different types of users in an organization

• A partition allowing each of core of a multi-processor node to process the node's workload in parallel

• A logical partition of the data layer shared between distributed compute nodes

• A parallel operation isolating a single layer of a Redshift cube

72. Which of the following environments has more indexes?

Answers:

• Data Warehousing env

• OLAP

• Database

• OLTP

73. Without deliberate security measures, which of the following MDX clauses is susceptible to injection attack?

Answers:

• FROM

• WHERE

• WITH

• All of the Above

74. Which of the following is an appropriate method for mitigating the database explosion associated with sparse dimensions?

Answers:

• isolate the sparse dimension in a separate cube

• populate the sparse dimension with filler values

• apply Type 6 updating to the sparse dimension

• create a composite to stand-in for the sparse dimension

75. Which of the following is a required property of information passed to the Microsoft Sequence Clustering Algorithm?

Answers:

• star schema with key sequence columns on each dimension table

• sequence information stored as a nested table

• normalized data with key sequence columns on each dimension table

• sequence information stored as a flat table

76. Online extraction process falls under which type of data extraction method?

Answers:

• Logical Extraction

• Incremental Extraction

• Physical Extraction

• Full Extraction

77. A data warehouse is an ‘integrated’ collection of data because?

Answers:

• It is a collection of data of derived from multiple sources

• It is a collection of data of different types

• It is a relational database

• It contains summarized data

78. What property do Router Transformations and Filter Transformations have in common?

Answers:

• single input group transformation

• All of the Above

• will not block input rows

• single output group transformation

79. All else being equal, which of the following elements of a single MDX WHERE clause will evaluate first?

Answers:

• MEMBER [Measures].[A Squared] AS [Measures].[Measure A] * [Measures].[Measure A], SOLVE_ORDER = 2

• MEMBER [Measures].[C Less One] AS [Measures].[Measure C] - 1, SOLVE_ORDER = 1

• MEMBER [Measures].[B Twice] AS [Measures].[Measure B] + [Measures].[Measure B], SOLVE_ORDER = 4

• All elements evaluate simultaneously

80. Where is the OLAP page pool stored in memory in an Oracle Database 10g and later?

Answers:

• User Global Area

• Program Global Area

• SQL Work Area

• Private SQL Area

81. Choose the most correct statement:

Answers:

• Star schema contains two Fact tables

• Fact Constellation schema contains two Fact tables

• Star and snowflake schema contains two Fact tables

• Snowflake schema contains two Fact tables

82. Choose the most correct statement:

Answers:

• Snowflake schema contains denormalized Dimension table

• Fact Constellation schema contains denormalized Dimension table

• Star schema contains normalized Dimension table

• Snowflake schema contains normalized Dimension table

83. Choose the most correct statement:

Answers:

• Data mining and data warehousing are the same

• Data warehousing is the application of data mining

• Data mining and data warehousing are totally unrelated

• OLAP is more flexible then OLTP