Home » Microsoft » 70-767 » Which data model should you use?
Note: This question-is part of a series of questions that use the same or similar answer choices. An answer choice may be correct for more than one question-in the series. Each question-is independent of the other questions in this series. Information and details provided in a question-apply only to that question.
You are implementing a Microsoft SQL Server data warehouse with a multi-dimensional data model. Sales information is stored in a single table. The table includes the order date, expected shipping date, promotion ID, currency, and sales office columns. A dimension table exists for each attribute.
Business users must be able to examine the sales data based on dates, promotions, currency, and the office of the sale.
You need to design the data model.
Which data model should you use?
A. star schema
B. snowflake schema
C. conformed dimension
D. slowly changing dimension (SCD)
E. fact table
F. semi-additive measure
G. non-additive measure
H. dimension table reference relationship
Correct Answer: A
Explanation/Reference:
Explanation:
The star schema is the simplest type of Data Warehouse schema. It is known as star schema as its structure resembles a star. In the Star schema, the center of the star can have one fact tables and numbers of associated dimension tables. It is also known as Star Join Schema and is optimized for querying large data sets.
Characteristics of Star Schema:
Every dimension in a star schema is represented with the only one-dimension table.
The dimension table should contain the set of attributes.
The dimension table is joined to the fact table using a foreign key
The dimension table are not joined to each other
Fact table would contain key and measure
Incorrect Answers:
Snowflake schema:
A Snowflake Schema is an extension of a Star Schema, and it adds additional dimensions. It is called snowflake because its diagram resembles a Snowflake.
The dimension tables are normalized which splits data into additional tables. In the following example, Country is further normalized into an individual table.
References:
https://www.guru99.com/star-snowflake-data-warehousing.html