Data Warehousing Interview Questions and Answers

Q1.What is a CUBE in Data Warehousing concept?
Ans.Cubes are logical representation of multidimensional data.The edge of the cube contains dimension members and the body of the cube contains data values.
Q2.What is the Datatype of Surrgate key?
Ans.Datatype of the surrgate key is either integer or numeric or number.
Q3.What is Data Warehousing Hierarchy?Hierarchies Hierarchies are logical structures that use ordered levels as a means of organizing data. A hierarchy can be used to define data aggregation. For example, in a time dimension, a hierarchy might aggregate data from the month level to the quarter level to the year level. A hierarchy can also be used to define a navigational drill path and to establish a family structure.Within a hierarchy, each level is logically connected to the levels above and below it. Data values at lower levels aggregate into the data values at higher levels. A dimension can be composed of more than one hierarchy. For example, in the product dimension, there might be two hierarchies--one for product categories and one for product suppliers.Dimension hierarchies also group levels from general to granular. Query tools use hierarchies to enable you to drill down into your data to view different levels of granularity. This is one of the key benefits of a data warehouse.When designing hierarchies, you must consider the relationships in business structures. For example, a divisional multilevel sales organization.Hierarchies impose a family structure on dimension values. For a particular level value, a value at the next higher level is its parent, and values at the next lower level are its children. These familial relationships enable analysts to access data quickly.LevelsA level represents a position in a hierarchy. For example, a time dimension might have a hierarchy that represents data at the month, quarter, and year levels. Levels range from general to specific, with the root level as the highest or most general level. The levels in a dimension are organized into one or more hierarchies.Level RelationshipsLevel relationships specify top-to-bottom ordering of levels from most general (the root) to most specific information. They define the parent-child relationship between the levels in a hierarchy.Hierarchies are also essential components in enabling more complex rewrites. For example, the database can aggregate an existing sales revenue on a quarterly base to a yearly aggregation when the dimensional dependencies between quarter and year are known.
Q4.What is Bus Schema?
Ans.BUS Schema is composed of a master suite of confirmed dimension and standardized definition if facts.
In a BUS schema we would eventually have conformed dimensions and facts defined to be shared across all enterprise data marts. This way all Data Marts can use the conformed dimensions and facts without having them locally. This is the first step towards building an enterprise Data Warehouse from Kimball's perspective. For (e.g) we may have different data marts for Sales, Inventory and Marketing and we need common entities like Customer, Product etc to be seen across these data marts and hence would be ideal to have these as Conformed objects. The challenge here is that some times each line of business may have different definitions for these conformed objects and hence choosing conformed objects have to be designed with some extra care.
Q5.What is fact, dimension and measure?
Ans.Fact is key performance indicator to analyze the business.Dimension is used to analyze the fact.Without dimension there is no meaning for fact.
Fact:It is a measure,EX:Sales,Accounts etc.Dimension:It deals with the details of the data.EX:Geography,Time etc.Measure:I deals with the quantity of the data.
Fact is the Key performance Indicator which handles the Numerical Value,Dimension concerned about the subjective areas of the data,Measure is the quantity we create to process
Q6.What is Dimensional Modelling?
Ans. Fact is the Key performance Indicator which handles the Numerical Value,Dimension concerned about the subjective areas of the data,Measure is the quantity we create to process
Q7.what is the difference between view and materialised view?
Ans. View - store the SQL statement in the database and let you use it as a table. Everytime you access the view, the SQL statement executes. Materialized view - stores the results of the SQL in table form in the database. SQL statement only executes once and after that everytime you run the query, the stored result set is used. Pros include quick query results.
Q8.What is the difference between star and snowflake schemas?
Ans. Star schemaA single fact table with N number of DimensionSnowflake schemaAny dimensions with extended dimensions are know as snowflake schema
Star schema is a logical structure that can be arranged with fact and dimension tables in a star formation.It looks like a star with fact table at the core of the star and the dimension tables along the spikes of the star.The dimension model is hence called a star schema
SNOWFLAKING is a method of normalizing the dimension tables in a star schema