FAQ Guide > Data Warehouse
14. Why is Data Modeling Important?
The data model is also detailed enough to be used by the database developers to use as a "blueprint" for building the physical database. The information contained in the data model will be used to define the relational tables, primary and foreign keys, stored procedures, and triggers. A poorly designed database will require more time in the long-term. Without careful planning you may create a database that omits data required to create critical reports, produces results that are incorrect or inconsistent, and is unable to accommodate changes in the user's requirements.
15. What does level of Granularity of a fact table signify?
It describes the amount of space required for a database. Level of Granularity indicates the extent of aggregation that will be permitted to take place on the fact data. More Granularity implies more aggregation potential and vice-versa. In simple terms, level of granularity defines the extent of detail. As an example, let us look at geographical level of granularity. We may analyze data at the levels of COUNTRY, REGION, TERRITORY, CITY and STREET. In this case, we say the highest level of granularity is STREET. Level of granularity means the upper/lower level of hierarchy, up to which we can see/drill the data in the fact table. Level of granularity means the upper/lower level of hierarchy, up to which we can see/drill the data in the fact table.
16. What is degenerate dimension table?
The values of dimension, which is stored, in fact table is called degenerate dimensions. These dimensions don't have it's own dimensions.
17. How do you load the time dimension?
In Data warehouse we manually load the time dimension, Every Data warehouse maintains a time dimension. It would be at the most granular level at which the business runs at (ex: week day, day of the month and so on). Depending on the data loads, these time dimensions are updated. Weekly process gets updated every week and monthly process, every month.
18. Difference between Snowflake and Star Schema. What are situations where Snow flake Schema is better than Star Schema to use and when the opposite is true?
Star schema and snowflake both serve the purpose of dimensional modeling when it comes to data warehouses.
Star schema is a dimensional model with a fact table (large) and a set of dimension tables (small). The whole set-up is totally denormalized.
However in cases where the dimension tables are split to many tables that are where the schema is slightly inclined towards normalization (reduce redundancy and dependency) there comes the snowflake schema.
The nature/purpose of the data that is to be feed to the model is the key to your question as to which is better.
Star schema
- contains the dimension tables mapped around one or more fact tables.
- It is a denormalized model.
- No need to use complicated joins.
- Queries results fastly.
Snowflake schema
- It is the normalized form of Star schema.
- Contains in depth joins, because the tables are splited in to many pieces. We can easily do modification directly in the tables.
- We have to use complicated joins, since we have more tables.
- There will be some delay in processing the Query.
|
|
