OIM 350 #2

Kimball pros

- first phase of data warehousing is delivered quick - logical model can be easily understood by business users - small team of developers can manage DWH

kimball cons

- may not be able to handle all enterprise reporting needs -integration of legacy data into DWH can be a complex process

inmon cons

- more etl work is needed for data marts - initial set up and delivery takes more time/resources - requires large team of specialists to manage

DB Security Measures

-authentication: users account identifier and password for logon -access privileges: ensuring that only some people have access to certain parts of the database -role-based access control: privileges are associated with certain roles

Kimball methodologies

-bottom up -deliver easy solution that supports data query - business process oriented - dimensional modeling - a few tables with redundant data -2 tier architecture integrated via conformed dimensions -fairly simple - good direct end user - active participation business driven

Inmon Pros

-dwh serves as the single source of truth - data update anomalies are avoided -dwh can easily be updated

How does the development process of a DWH differ from that of an operational DB system?

-the operational db system does not have a step to create an etl infrastruture - they have a different modeling approach -they have different types of indirect use

Inmon methodologies

-top down -deliver sound technical solution based on proven methods -subject oriented -ERD relational modeling -many tables with no redundant data -3 tier architecture integrated by enterprise data model - quite complex - good for indirect end users - passive participation it-driven

What are the main components of a data warehouse system?

1. source systems- operational databases that provide analytically useful information 2. extraction-transformation-load (ETL)- extracts and analyses data from operational data sources 3. Data warehouse itself- repository of the analytical data integrated from the source systems

Data Warehouse VS Data Mart

DATA WAREHOUSE subjects: multiple Data sources: many typical size:very big implementation time: relatively long focus: organization wide DATA MART subjects: single Data sources: fewer typical size: not as big implementation time: not as long focus: narrower than organization wide

ETL (Extraction, Transformation, Load) Process

Data are extracted from external data sources using custom ETL software, maintained in a staging area where transformed, cleansed, and integrated, then loaded into the Data Warehouse and/or data marts.

dependent data mart

Does not have its own source systems, data comes from data warehouse

MOLAP

Multidimensional Online Analytical Processing, data is stored in multidimensional cubes and the complexity is hidden from users Pros - fast analysis cons- not appropriate for holding transaction level detail data or large amounts of data

Binary Search

Non linear method, faster than linear search, divides sorted lists into two parts of the same size and then eliminated either the bottom or top half, this is repeated until it is found

functional differeneces OLTP OLAP

Operational OLTP -all users -tactical -application oriented Analytical OLAP - narrow user group -decision making -subject oriented

Backup and Recovery

The combination of procedures that can restore lost data in the event of hardware or software failure. examples are: recovery log, checkpoints and complete mirrored backups

alpha release

a data warehouse and the associated front end applications are deployed internally to the members of the development team for initial testing of its functionalities

slowly changing dimensions

a dimension that contains attributes whose values can change

materialized view

a table that actually has physically saved data

Data Quality Characteristics

accuracy, uniqueness, completeness, consistency, timeliness, conformity

DW Deployment

allowing the end user to access the newly created and populated data warehouse and its front end applications.

type 3

applicable when theres a fixed number of changes and limited history is needed. Creates a previous and current column in the dimensional table for each column where changes are anticipated

HOLAP

combines both molap and rolap for a summary type information. its leveraged cube technology for faster performance but when details are needed holap can retried the underlying relational data

dimension tables

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

fact tables

contain measures related to the subject of analysis, typically numeric. QUANTITATIVE

granularity

describes what is depicted by one row in the fact table. Fine granularity: when each record represents a single fact. Coarse granularity when each record represents a summarizations of multiple facts

data makeup differences

differences in the characteristics of the data that comprises of the operational and analytical information. data time-horizon, data level of detail and data time representation

functional differences

differences in the rational for the use of operational and analytical data. Data audience and data orientation

technical differences

differences in the way that operational and analytical data is handled and accessed by the DBMS and applications . Queried amounts of data and frequency of queries, data update, data redundancy

Detailed fact table

each record refers to a single fact

aggregated fact tables

each record summarizes multiple facts

Snow Flake schema

ease of maintenance: easier to change (no redundancy) ease of use: harder to understand (more complex queries) query performance: longer execution time (more foreign keys) when to use: in data warehouses, when dimension tables are big

Star Schema (not normalized)

ease of maintenance: harder to change (redundant data) ease of use: easier to understand (less complex) query performance: faster (less foreign keys) when to use: in data marts, when dimensional tables have a small number of rows

Query optimization

examining multiple ways of executing the same query and choosing the fastest option, making it the most efficient

When should a data warehouse designer consider choosing the inmon or Kimball approach over the other?

inmon - high rate of change sources - large team of specialists - longer start of time -higher start up cost -easy maintenance Kimball -stable source systems -small reams of generalists -urgent start time -lower start up cost -difficult maintenance

What are the main differences between the inmon and kimball approaches to modeling a data warehouse?

inmon: -top down -deliver sound solution -subject oriented -erd relational modeling -many tables no redundant data -complex -indirect end user Kimball -bottom up -easy solution -business process oriented -dimensional model -few tables redundant -simple -direct end user

Linear Search

looking up a particular record based on a value in an unsorted column. Checks elements sequentially and one at a time until its found

Regular View

not an actual table and does not have any physically saved data

data makeup differences operational and analytical

operational -shorter time horizon - fine detail -current time Analytical -longer time horizon -summarize -current and snapshots (time of presentation

technical differences operational analytical

operational (OLTP) -small data -frequent queries -read insert delete update -low redundancy Analytical (OLAP) -large data -infrequent queries - read only -high redundancy

What are the main differences between operational and analytical data?

operational information refers to the information collected and used in support of day to day operational needs analytical information refers to the information collected and used for decision support of tasks requiring data analysis

analytical data

refers to information collected and used for decision support of tasks requiring data analysis

operational data

refers to information collected and used in sport of day to day operational needs, individual tranactions

OLAP (online analytical processing)

refers to querying and presenting data from data warehouses/data marts for analytical purposes, can only engage in querying and presenting data read only

OLTP (online transaction processing)

refers to the updating, querying and presenting data from databases and other operational purposes. updating querying and presenting data routinely performs transactions that insert,modify and delete data from databases

ROLAP

relational online analytical processing translates queries in a into sql statements Pros- can handle large amounts of data cons- slow performance, limited by SQL functions

View Materialization

saving a view as an actual physical table, this is to improve performance of queries on frequently used views.

independent data mart

stand alone data mart, created in the same fashion as the data warehouse, has its own source systems and ETL infrastructure

Production release

the actual deployment of a functioning data warehousing system

beta release

the data warehouse is deployed to a selected group of users to test usability of the system

Type 1

the simplest and most used, simply changes the value in the dimensions record where the new value is the old value

type 2

used in cases that history should be preserved, creates additional dimension records using a new value for the surrogate key every time a value in a dimension record changes

Why does an organization need a separate analytical DB system in addition to an operational DB system?

• Performance of operational databases can be severely diminished if day-to-day tasks have to share computing resources with analytical queries • It is difficult to structure a database which can be efficiently used for both operational and analytical purposes