Data Warehouse Interview

Front 1

Constraint

Back 1

Rules imposed on the data. Placed on the data type

Front 2

What are different types of Constraints?

Back 2

Primary Key, Unique, not null, foreign key, composite foreign key

Front 3

Unique Constraint

Back 3

Way to impose duplicate values on a column, but will contain null

Front 4

Index

Back 4

To increase speed on retrieval

Front 5

Sequence

Back 5

Sequence is a database object to generate unique number

Front 6

Normalization

Back 6

A way to remove data redundancy

Front 7

Data Model Repository

Back 7

Where Metadats is stored

Front 8

Forward Engineering

Back 8

DDL scripts generated from data model on data modeler. With these scripts, dats model can be created

Front 9

Reverse Engineering

Back 9

Data Modeling tool where connected to a database to create data model

Front 10

Star Schema

Back 10

Usually denormalized. Where you have fact table connected to dimensional tables

Front 11

Snow Flake Schema

Back 11

Like Star Schema but on 3rd NF, so mofd dimension tables branching out

Front 12

Data Warehouse

Back 12

Is like a relational database designed for analytical needs. It is a central location for consolidating multiple source databases

Front 13

Data Staging Area in Data Warehouse is stored in

Back 13

normalized tables and Flat Files

Front 14

Slicing and dicing

Back 14

Dw users want to separate combine or filter the database

Front 15

Data Integration

Back 15

The combination of multiple data sources into one. Typically on to s staging area

Front 16

Granularity

Back 16

Lowest level of detail you can find in a table

Front 17

Difference between data warehouse and an operational database

Back 17

Data warehouse is for analysis where operational database is for running or operating the business

Front 18

OLAP

Back 18

Online Analytical Processing. Class of applications that lets you analyze a dw

Front 19

Dimension Table

Back 19

Entities that gives context to the fact table ao you can slice and dice the data

Front 20

Attributes

Back 20

Columns

Front 21

Fact Table

Back 21

Grain of business event.. contains measure of the dimension

Front 22

Grain

Back 22

Lowest Level at the business event occur

Front 23

Additive Fact

Back 23

A measure in a fact table that can be fully summed across any dimensions associated with it (total sales per month or product)

Front 24

Seni additive fact

Back 24

A measure in a fact table that can be summed in some dimension (daily average balance of a bank account)

Front 25

Non additive fact

Back 25

Facts that cant be added averaged (discount)

Front 26

Factless fact table

Back 26

A fact table that doesnt have a measure to the dimensions. Just has dimension ID

Front 27

Conformed dinension

Back 27

A dimension that can be used across multiple data marts or departments

Front 28

Aggregate table

Back 28

A table that is produced from already created dw by slice and dice the data to a table

Front 29

Summary information

Back 29

Where predefined aggregation are kept

Front 30

Data Mart

Back 30

A smaller version of data warehouse which deals with a single subject

Front 31

Meta Data

Back 31

Data about data

Shows information about attributes

Shows the data flow

Front 32

Data mining

Back 32

Analyzing dimensional data or datasets to see patterns

Front 33

Types of OLAP servers:

Back 33

MOLAP - multi dimensional OLAP

ROLAP- Relational OLAP

HOLAP- Hybrid OLAP

Front 34

MOLAP

Back 34

Multidimensional OLAP processes and storesthe data directly to multidimensional database. Can perform complex calculations quickly but limited to disc space

Front 35

Relational OLAP

Back 35

Performs analysis of multidimensional data stored in a relational database. Great amount pf data can be saved but processing power