cis 356

What are the steps in the development of data warehouse

-Start with business requirements to gather what it needs to do and what questions to answer and how to use -Doing local development aka ERD (will look different for analytical and operational) -Building the actual relational tables, convert them to SQL ---In parallel can develop indirect access (application to hit database) ---In parallel ETO process to understand how to convert from operational to format organizational structure that matched relational tables developed -Deployment (test it) -Maintain it (backup and perform index)

What is a constellation of stars

A dimensional model with multiple fact tables

What is the role of dimension tables in the dimensional model

Contain descriptions of the business, organization, or enterprise to which the subject of the analysis belongs

What is the role of fact tables in the dimensional model

Contain measures related to the subject of analysis. They contain foreign keys

What is the difference between a dependents and independent data mart

Dependent: does not have its own source system, data comes from data warehouse Independent: Stand alone data mart created in the same fashion as data warehouse

What does granularity of the fact table refer to

Describes what is depicted by one row in the fact table

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

Detailed: each record refers to a single fact Aggregated: each record summarized multiple facts

Describe the redundancy difference between operational and analytical data

Eliminating redundancy is not as critical in analytical databases as it is in operational Reducing the redundancy of information is a major goal of operational databases. One of the main reasons for minimizing the redundancy in operational databases is to eliminate the possibility of update anomalies. However, the updating of data by the end users is not allowed in analytical databases, therefore there is no particular danger of update anomalies.

Why is a data warehouse create as a separate data store

If run data analytics against operation it will be slowed down because can't handle the huge data The data in the operational systems are useful. The issue is that the data is not organized in an efficient manner to perform analytics against the operational systems

How does the use of a 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 (non snowflaked) dimensions provide simpler analysis Normalization is usually not necessary for analytical databases Because it is not necessary (no danger of update anomalies) and it would only needlessly complicate analytical queries.

Describe the data time-level-of-detail between operational and analytical data

Operational reflects detailed data (every individual transaction is recorded) On the other hand, analytical data is both detailed and summarized.

Describe the data time horizon difference between operational and analytical data

Operational systems (day to day) have short time horizon of data than analytical systems. Operational the horizon could weeks or months where analytical could be a span over years of data.

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

Operational typically process smaller amounts of data and are typically issued much more often and by more users than analytical

What are the major components of a data warehousing system

Source Systems, Extraction-transformational Load (ETL) infrastructure, and the data warehouse itself

What are the most common approaches for handling slowly changing dimensions

Type 1: New dimension value and replaces the old, no history Type 2: Keep track of all changes, additional tables required Type 3: Keep current and last only