CIS 356 Exam 3

Why is a data warehouse created as a separate data store?

A data warehouse is created within an organization as a separate data store whose primary purpose is data analytics. If you run data analytics against operational data systems, you slow them down Data is not stored in an operational warehouse in a way that is useful for data analytics.

What is a constellation of stars?

A dimensional model with multiple fact tables.

What is the role of dimension tables in the dimensional model?

Contains descriptions related to the business, organization, or enterprise to which the subject of analysis belongs.

What is the role of fact tables in the dimensional model?

Contains measures related to the subject of analysis.

What does granularity of the fact table refer to?

Describes what is depicted in one row of the fact table.

What is the difference between a detailed fact table and an aggregated fact table?

Detailed fact tables- each record refers to a single fact. Aggregated fact table- each record summarized multiple facts.

Describe the data redundancy difference between operational and analytical data.

Eliminating data redundancy is not as critical in analytical databases as it is in operational databases.

What are the steps in the development of data warehouses?

Gather business requirements Logistical development- ERD Building relational tables (data warehouse) (In parallel, use indirect access (application program for database, not access to underlying tables) for application development In parallel, ETL) Deployment Maintaining- administrative processes

What is the difference between a dependent and independent data mart?

Independent data marks can stand alone. They don't require a data warehouse. A dependent data warehouse must have a data warehouse. Like a Kroger gas station.

How does the use of dimensional model simplify analytical queries?

It reduces the number of tables that have to be considered and provides a singular place for analysis, which eliminates the need for data preparation on behalf of the analyst.

Why is snowflaking usually not used in dimensional modeling?

Not-normalized dimensions provide for a simpler analysis. A normalized star schema adds a lot of complexity to the analysis.

Describe the data level-of-detail difference between operational and analytical data.

Operational data depicts fine detailed data Analytical can contain both detailed and summarized data which are physically stored.

Describe the difference in queried amounts of data and frequency of queries between operational and analytical data.

Operational queries typically process much smaller amounts of data. Operational queries are used much more often and by more users. Analytical queries typically process much more data. Analytical queries are used much less often by fewer users.

Describe the data time-horizon difference between operational and analytical data.

Operational typically displays the current state Analytical can display both the current state and snapshots of the past.

What are the major components of a data warehousing system?

Source systems Extraction-transformational-load infrastructure Data warehouse

What are the most common approaches for handling slowly changing dimensions?

Using type 1 option (new value replaces old, no history), type 2 option (keeps track of all changes, more tables required), type 3 option (keep current and last only), or some combination of the 3.