Data Semantic Model Best Practices in SQL Server Analysis Services

Best Practices for Creating Data Semantic Models

Prev Question Next Question

Question

You need to create data semantic models in SQL Server Analysis Services.

There are some recommended best practices for data modeling that one should follow.

Which of the following practices are considered as the best practices that you would mind while creating data semantic models? (Select three options)

Answers

A. Never create a dimension model snowflake as well as a star while ingesting data from various sources.

B. Create a dimension model snowflake or/and star, even if you need to ingest data from various sources.

C. Only include the integer surrogate keys or value encoding in the model and exclude all the natural keys from the dimension tables.

D. Only include the natural keys in the model and exclude the integer surrogate keys or value encoding.

E. Decrease cardinality to reduce the uniqueness of the values and allow much better compression.

F. Increase the cardinality to reduce the uniqueness of the values and allow much better compression.

Show Answer

Explanations

Click on the arrows to vote for the correct answer

A. B. C. D. E. F.

Correct Answers: B, C and E

The below image describes the best practices that can be considered while creating data semantic models in, Azure Analysis Services, SQL Server Analysis Services, or Power BI:

Create a dimension model star and/or snowflake, even if you are ingesting data from different sources.

Ensure that you create integer surrogate keys on dimension tables. Natural keys are not best practice and can cause
issues if you need to change them at a later date. Natural keys are generally strings, so larger in size and can perform
poorly when joining to other tables. The key point in regards to performance with tabular models is that natural keys
are not optimal for compression. The process with natural keys is that they are:
¢ ¢ Encoded, hash/dictionary encoding.

© Foreign keys encoded on the fact table relating to the dimension table, again hash/dictionary encoding.

© Build the relationships.

This has an impact on performance and reduces the available memory for data as a proportion, which will be needed
for the dictionary encoding.

Only bring into the model the integer surrogate keys or value encoding and exclude any natural keys from the
dimension tables.

Only bring into the model the foreign keys or integer surrogate keys on the fact table from the dimension tables.

Only bring columns into your model that are required for analysis, this may be excluding columns that are not
needed or filter on data to only bring the data in that is being analyzed.

Reduce cardinality so that the values uniqueness can be reduced, allowing for much greater compression.
Add a date dimension into your model.

Ideally, we should run calculations at the compute layer if possible.

Option A is incorrect.

It is recommended to create a dimension model even if you need to ingest data from various sources.

Option B is correct.

You need to create a dimension model snowflake or/and star, even if you need to ingest data from various sources.

Option C is correct.

The best practices ask to only include the integer surrogate keys or value encoding in the model and exclude all the natural keys from the dimension tables.

Option D is incorrect.

The best practices ask to only include the integer surrogate keys or value encoding in the model and exclude all the natural keys from the dimension tables.

Option E is correct.

You should decrease the cardinality to reduce the uniqueness of the values and allow much better compression.

Option F is incorrect.

Decreasing the cardinality will help in reducing the uniqueness of the values and allowing much better compression.

To know more about Data models, please visit the below-given link:

When creating data semantic models in SQL Server Analysis Services, it is important to follow best practices for data modeling. The following are some of the recommended best practices:

B. Create a dimension model snowflake or/and star, even if you need to ingest data from various sources. Dimensional modeling is a popular approach used for data modeling in SQL Server Analysis Services. The two common dimensional modeling techniques are the star schema and the snowflake schema. The star schema is a denormalized model where all the data is stored in a single fact table with associated dimension tables, while the snowflake schema is a normalized model where dimension tables are further normalized into smaller tables. It is recommended to use either of these techniques depending on the use case, even if you need to ingest data from various sources.

C. Only include the integer surrogate keys or value encoding in the model and exclude all the natural keys from the dimension tables. Surrogate keys are unique identifiers that are assigned to dimension tables instead of using natural keys. Using surrogate keys improves query performance and simplifies data maintenance, as they are typically smaller than natural keys and do not change. Value encoding is another technique used to improve query performance by encoding dimension table values into integers. It is recommended to use either of these techniques and exclude natural keys from dimension tables.

E. Decrease cardinality to reduce the uniqueness of the values and allow much better compression. Cardinality refers to the number of unique values in a dimension table column. By reducing the cardinality, you can decrease the uniqueness of the values and allow for better compression, which can improve query performance and reduce storage requirements.

Therefore, the correct options for the best practices that should be considered while creating data semantic models are B, C, and E.

Prev Question Next Question