Data warehousing Basics
Why Data Warehouses?
What is the DSS?
OLTP vs DW
DW Implementations
Centralized approach
Data mart centric
DW Architecture
Operational data store
Data mart centric
Enterprise data warehouse architectures.
Usage of Staging database
De-normalization concept
Dimensions and Facts in Data Warehousing world
Star Schema
Snow Flake Schema
Dimensional Modeling
SCD1 – Business scenario and usage
SCD2 – Business scenario and usage
SCD3 – Business scenario and usage
Facts
Fact Table
Fact Less Fact
Measure Types (Fully, Semi and Non Additive)
Different types of loading process (Design and execution tips)
Full Load
Delta Load
ETL (Extract, Transform and Loading)
Extract process
Transformation process
Loading process
BI (Business Intelligence)
OLAP and the types of OLAP
Static Reporting
Ad hoc Reporting
Cubes and usage
Data Mining
Hierarchies
Drill up, drill down, drill across and drill through
BI Metadata.
Meta Data in Data warehousing
Showing posts with label Datawarehouse. Show all posts
Showing posts with label Datawarehouse. Show all posts
Monday, March 31, 2008
Monday, February 25, 2008
ETL and Datawarehouse Basics with added advantages
ETL and Datawarehouse Basics with added advantages
Are the dimensions independent? Or is there a statistical correlation between them?
Weak statistical correlation and big dimensions
Even if there is a weak correlation, build the DW as if the dimensions are independent, especially for large dimensions
100 stores, 1 million products, 100K millions for combining the two, too large
If the statistical correlation is significant, build a fact table for the correlation, and there may be many such correlations, e.g., merchandizing correlation, pricing-strategy correlation, changing-seasonality correlation
Leave the dimensions simple and independent if the dimensions are large and the statistical correlation is weak
Strong correlations and small resulting dimensions
If the correlation is strong, e.g., product always has a brand, leave the brand OUT of the fact table, because the product always rolls up to a single brand and COMBINE the product and the brand into a single dimension
Arbitrary bound: 100K records is not small dimension
When the same dimension is attached to a fact table multiple times
Sale has an order date, payment date, a shipping date, a return date
A claim transaction has multiple internal people, a claim intake, a medical nurse, a claim adjuster, a payer, etc
End-user might be confused when they do drill-down into the dimension as to what the dimension represents, so it helps to have the dimension have different names
Solution: Create views over the dimensions for each of the dimensional role to help with the name recognition
For very large dimensions (locations) might be appropriate to create physical copies of the dimension table to help with the name confusion
Degenerate dimensions
When a parent-child relationship exists and the grain of the fact table is the child, the parent is kind of left out in the design process
Example:
grain of the fact able is the line item in an order
the order number is significant part of the key
but we don’t create a dimension for the order number, because it would be useless
we insert the order number as part of the key, as if it was a dimension, but we don’t create a dimension table for it
When the DW receives notification that some record in a dimension has changed, there are three basic responses:
Type 1 slow changing dimension (Overwrite)
Type 2 slow changing dimension (Partitioning History)
Type 3 slow changing dimension (Alternate Realities)
Overwrite one or more values of the dimension with the new value
Use when
the data are corrected
there is no interest in keeping history
there is no need to run previous reports or the changed value is immaterial to the report
Type 1 Overwrite results in an UPDATE SQL statement when the value changes
If a column is Type-1, the ETL subsystem must
Add the dimension record, if it’s a new value or
Update the dimension attribute in place
Must also update any Staging tables, so that any subsequent DW load from the staging tables will preserve the overwrite
This update never affects the surrogate key
But it affects materialized aggregates that were built on the value that changed (will be discussed more next week when we talk about delivering fact tables)
Beware of ETL tools Update else Insert statements, which are convenient but inefficient
Some developers use UPDATE else INSERT for fast changing dimensions and INSERT else UPDATE for very slow changing dimensions
Better Approach: Segregate INSERTS from UPDATES, and feed the DW independently for the updates and for the inserts
No need to invoke a bulk loader for small tables, simply execute the SQL updates, the performance impact is immaterial, even with the DW logging the SQL statement
For larger tables, a loader is preferable, because SQL updates will result into unacceptable database logging activity
Turn the logger off before you update with SQL Updates and separate SQL Inserts
Or use a bulk loader
Prepare the new dimension in a staging file
Drop the old dimension table
Load the new dimension table using the bulk loader
Standard
When a record changes, instead of overwriting
create a new dimension record
with a new surrogate key
add the new record into the dimension table
use this record going forward in all fact tables
no fact tables need to change
no aggregates need to be re-computed
Perfectly partitions history because at each detailed version of the dimension is correctly connected to the span of fact tables for which that version is correct
The natural key does not change
The job attribute changes
We can constraint our query
the Manager job
Joe’s employee id
Type-2 do not change the natural key (the natural key should never change)
With a Type-2 change, you might want to include the following additional attributes in the dimension
Date of change
Exact timestamp of change
Reason for change
Current Flag (current/expired)
The source system does not notify changes and does not time/date-stamp its own updates
For small tables, use a brute force approach of comparing every incoming field with every field in the DW to see if anything changes
For larger tables, use the CRC, or cyclic-redundancy checksum, a number of about 20 digits, like this:
Treat the entire incoming record as a string
Compute the CRC value of the string (some numeric value)
Compare the CRC value of today’s record with the CRC value of yesterday’s record
If the CRC values match, today’s record is identical to yesterday’s record
If the CRC values do not match, do a field by field comparison to see what has changed
Depending on whether the changed field is a Type-1, Type-2 or Type-3 change, do the necessary updates
Most ETL packages and the Internet include CRC computation and comparison code
To identify deletions
Read the source log file, if one exists
Determine that the CRC number of a record in the DW does not match some incoming record
Use the MINUS set operator to compare the DW dimension with the incoming dimension (usually works if the ETL staging table with today’s dimension and the DW are part of the same database)
Delete the record if this is valid for the business, most cases you don’t want to delete anything from the DW
Applicable when a change happens to a dimension record but the old record remains valid as a second choice
Product category designations
Sales-territory assignments
Instead of creating a new row, a new column is inserted (if it does not already exist)
The old value is added to the secondary column
Before the new value overrides the primary column
Example: old category, new category
Usually defined by the business after the main ETL process is implemented
Please move Brand X from Men’s Sportswear to Leather goods but allow me to track Brand X optionally in the old category
The old category is described as an Alternate reality
Automated data movement across data stores and the analytical area in support of a data warehouse, data mart or ODS effort
Extensible, robust, scalable infrastructure
Standardization of ETL processes across the enterprise
Reusability of custom and pre-build functions
Better utilization of existing hardware resources
Faster change-control & management
Integrated meta-data management
Complete development environment, work as you think design metaphor
Execute stored procedures at the source to extract data, perhaps filtering some
Store the extracted data into staging tables at the source
Generate a file with the source staging data
Transfer the file into the destination
Load the data into staging tables at the destination
Execute stored procedures at the destination to read the incoming staged data and compute what changed since the last time by comparing with the current value of the data warehouse
Populate the data warehouse with new changes
Supports incremental updates to sources
Supports simple data cleansing
Logs warning/errors
DataStage
big player, high ability to execute, gets good results from Gartner, visionary
Informatica
another big player, gets good results from Gartner, visionary with high ability to execute
most expensive
SAS ETL Server
fast becoming a major player, very positive results from Gartner
low exposure as an ETL tool (SAS a significant statistical analysis vendor)
Information Builder's Data Migrator/ETL Manager tool suite
part of Enterprise Focus/WebFocus
not a major player but industrial strength language, data connectors, etc
Sunopsis
cheap
relies on native RDBMS functionality
CIGNA people exposed to it at conferences liked it
Vendor tools promote standardization of the ETL process, reusability of custom and pre-built functions, lowering the time (and cost) of additional ETL efforts
Vendor ETL tools are somewhat self-documenting
Many tools can connect to a variety of sources (RDBMSs, non-relational, different OS, ERP, PeopleSoft, etc) without exposing the ETL developer to the differences
Vendor tools deal with changes in the source systems or the necessary transformations better, reducing long term ETL maintenance
Meta-data management is a huge advantage, especially when sharing data from many applications
Vendor ETL tools place discipline in the extraction and transformation process
ETL prices have not dropped much over the years, but there is increased functionality, performance, and usability from vendor tools
A conflict between
getting the data from the operational systems as fast as possible
having the ability to restart without repeating the process from the beginning
Reasons for staging
Recoverability: stage the data as soon as it has been extracted from the source systems and immediately after major processing (cleaning, transformation, etc).
Backup: can reload the data warehouse from the staging tables without going to the sources
Auditing: lineage between the source data and the underlying transformations before the load to the data warehouse
The staging area is owned by the ETL team
no indexes, no aggregations, no presentation access, no querying, no service level agreements
Users are not allowed in the staging area for any reason
staging is a construction site
Reports cannot access data in the staging area
tables can be added, or dropped without modifying the user community
Only ETL processes can read/write the staging area (ETL developers must capture table names, update strategies, load frequency, ETL jobs, expected growth and other details about the staging area)
The staging area consists of both RDBMS tables and data files
Flat files
fast to write, append to, sort and filter (grep) but slow to update, access or join
enables restart without going to the sources
XML Data Sets (not really used in Staging)
Relational Tables
Metadata, SQL interface, DBA support
Dimensional Model Constructs: Facts, Dimensions, Atomic Facts tables, Aggregate Fact Tables (OLAP Cubes)
Surrogate Key Mapping Tables
map natural keys from the OLTP systems to the surrogate key from the DW
can be stored in files or the RDBMS (but you can use the IDENTITY function if you go with the RDBMS approach)
Best Practices about these data structures:
perform impact analysis, capture metadata, use naming conventions,
Effectively integrate data from
different DBMS, OS, H/W, communication protocols
need a logical map, data movement view documents, data lineage report
have a plan
identity source candidates
analyze source systems with a data profiling tool
receive walk-through of data lineage and business rules (from the DW architect and business analyst to the ETL developer)
data alterations during data cleansing, calculations and formulas
measure twice, cut once
standard conformance to dimensions and numerical facts
receive walk-through of the dimensional model
Target table and column, table type (Dimension, Fact)
Slow-changing dimension type per target column
Type 1, overwrite (Customer first name)
Type 2, retain history (Customer last name)
Type 3, retain multiple valid alternative values
Source database, table, column
Transformations (the guts of the document)
Where do you capture this information? Which tool? How do you maintain this metadata?
Data modelers and ETL developers should maintain a documentation of the source systems including
subject area, application name, business name, department name
priority
business owner and technical owner
DBMS, production server
DB size, #users, complexity, #transactions per day
comments
Determine the system of record
Dealing with derived data (derive from base facts or accepted calculated columns from the source systems?)
if calculations are done by the DW infrastructure, the ETL developer is responsible for them
and what if the numbers don’t match?
recommendations: stay true to the definition of system-of-record
The further downstream you go from the originating data source, the more you increase the risk of extracting corrupt data. Barring rare exceptions, maintain the practice of sourcing data only from the system-of-record.
Analyze your source system
get a ER-model for the system or reverse engineering one (develop one by looking at the metadata of the system)
reverse engineering is not the same as forward engineering , i.e., given the ER-models of the source systems derive the dimensional schema of the data warehouse
Reverse engineering of the understanding of a source system
unique identifiers and natural keys
data types
relationships between tables (1-to-1, many-to-1, many to many), problematic when source database does not have foreign keys defined
discrete relationships (static data, reference tables)
Data content analysis
NULL values, especially in foreign keys, NULL result in lossy joins
In spite of the most detailed analysis, we recommend using outer join logic when extracting from relational source systems, simply because referential integrity often cannot be trusted on remote systems
Dates in non-date fields
What is the standard for the enterprise?
ODBC, OLE DB, JDBC, .NET
access databases from windows applications, so that applications are portable
performance is major drawback
every DBMS has an ODBC driver, even flat files
Adds two layers of interaction between the ETL and the database
Mainframe
COBOL copybooks give you the datatypes
EBCDIC and not ASCII character set (FTP does the translation between the mainframe and Unix/Windows)
Working with redefined fields (To save space the same field is used for different types of data)
Extracting from IMS, IDMS, Adabase
you need special adapters or you get someone in those systems to give you a file
XML sources, Web Log Files: doable, if you undestand the structure of those sources
Enterprise-Resource-Planning ERP Systems (SAP, PeopleSoft, Oracle)
Don’t treat it as a relational system -- it’s a mess
Use adapters
Using Audit Columns
Use the last update timestamp, populated by triggers or the front-end application
Must ensure that the timestamp is dependable, that is if the front-end modifies it, a batch job does not override it
Index the timestamp it if it’s dependable
Database Log Scrapping or sniffing
Take the log of the source file and try to determine the transactions that affect you
Sniffing does it real time
Timed extracts
Retrieve all records from the source that were modified today
POTENTIALLY dangerous -- what if the process fails today? When it runs tomorrow, you’d have lost today’s changes
Process of elimination
Preserve yesterday’s data in the stage area
Bring today’s entire data in the stage area
Perform a comparison
Inefficient, but the most reliable
Initial and Incremental Loads
Create two tables, previous-load and current-load
Load into the current-load, compare with the previous-load, when you are done drop the previous-load, rename the current-load into previous-load, create a new curent-log
Constrain on indexed columns
Retrieve only the data you need
Use DISTINCT sparingly
Use the SET operations sparingly
Use HINT (HINT tells the DBMS to make sure it uses a certain index)
Avoid NOT
Avoid functions in the where clause
Avoid subqueries
When a dimension is populated by several distinct systems, it is important to include the unique identifier from each of those systems in the target dimension in the data warehouse. Those identifiers should be viewable by end users to ensure peace of mind that the dimension reflects their data that they can tie back to in their transaction system.
Primary key (PK)
Meaningless, unique integer
Aka as surrogate key
Joins to Fact Tables
Is a Foreign Key to Fact Tables
Natural key (NK)
Meaningful key extracted from source systems
1-to-1 relationship to the PK for static dimensions
1-to-many relationship to the PK for slowly changing dimensions, tracks history of changes to the dimension
Descriptive Attributes
Primary textual but numbers legitimate but not numbers that are measured quantities
100 such attributes normal
Static or slow changing only
Product price -- either fact or dimension attribute
Via triggers in the DBMS
Read the latest surrogate key, generate the next value, create the record
Disadvantages: severe performance bottlenecks
Via the ETL process, an ETL tool or a 3-rd party application generate the unique numbers
A surrogate key counter per dimension
Maintain consistency of surrogate keys between dev, test and production
Using Smart Keys
Concatenate the natural key of the dimension in the source(s) with the timestamp of the record in the source or the Data Warehouse.
Tempting but wrong
By definition
Surrogate keys are supposed to be meaningless
Do you update the concatenate smart key if the natural key changes?
Performance
Natural keys may be chars and varchars, not integers
Adding a timestamp to it makes the key very big
The dimension is bigger
The fact tables containing the foreign key are bigger
Joining facts with dimensions based on chars/varchars become inefficient
Heterogeneous sources
Smart keys work for homogeneous environments, but most likely than not the sources are heterogeneous, each having the own definition of the dimension
How does the definition of the smart key changes when there is another source added? It doesn’t scale very well.
One advantage: simplicity in the ETL process
The definition of the key of the dimension in business terms, what does the dimension represent
Challenge: analyze the source systems so that a particular set of fields in that source corresponds to the grain of the dimension
Verify that a given source (file) implements the intended grain
Nothing should be returned by this from the source system/file
If something is returned by this, the fields A, B and C do not represent the grain of the dimension
Simple Case: the dimension is loaded as a lookup table
Typical Case
Data cleaning
Validate the data, apply business rules to make the data consistent, column validity enforcement, cross-column value checking, row de-duplication
Data conforming
Align the content of some or all of the fields in the dimension with fields in similar or identical dimensions in other parts of the data warehouse
Fact tables: billing transactions, customer support calls
IF they use the same dimensions, then the dimensions are conformed
Data Delivery
All the steps required to deal with slow-changing dimensions
Write the dimension to the physical table
Creating and assigning the surrogate key, making sure the natural key is correct, etc.
Dimensions are de-normalized flat tables
It is normal to have multiple embedded hierarchical structures in the same dimension
Eg. Store dimension
Geographic hierarchy: location, city, state, country
Merchandizing hierarchy: location, district, region
Can co-exist in the same dimension IF every attribute is single valued in the presence of the dimension’s primary key
If some attribute takes multiple values, it is not part of the dimension
E.g., a store can have multiple cash-registers, I.e., if the grain of the dimension is the store, the cash-register cannot be part of the store dimension, to include the cash register in the dimension the grain must be changed to be the cash-register, not the store
Normalized structures (3NF)
ok to exist in the staging area
should be flattened (2NF) before the delivery phase, using a simple query over the normalized structures
Dimensions that remain normalized
Looks like a snowflake, if the levels of the hierarchy obey perfect many-to-1 relationships
There is no semantic difference, the information content between the two approaches are the same
Problems with snow-flakes
If the hierarchical model changes, the normalized table schema and the declared joins must change, as well as the resulting end-user queries
More confusing to the end user, flat dimensions mask the complexity of the data
However, appropriate snow-flakes do exist (as sub-dimensions of dimensions)
Virtually everywhere: measurements are defined at specific times, repeated over time, etc.
Most common: calendar-day dimension with the grain of a single day, many attributes
Doesn’t have a conventional source:
Built by hand, speadsheet
Holidays, workdays, fiscal periods, week numbers, last day of month flags, must be entered manually
10 years are about 4K rows
Note the Natural key: a day type and a full date
Day type: date and non-date types such as inapplicable date, corrupted date, hasn’t happened yet date
fact tables must point to a valid date from the dimension, so we need special date types, at least one, the N/A date
How to generate the primary key?
Meaningless integer?
Or 10102005 meaning Oct 10, 2005 ? (reserving 9999999 to mean N/A?)
This is a close call, but even if meaningless integers are used, the numbers should appear in numerical order (why? Because of data partitioning requirements in a DW, data in a fact table can be partitioned by time)
Also typically needed are time dimensions whose grain is a month, a week, a quarter or a year, if there are fact tables in each of these grains
These are physically different tables
Are generated by eliminating selected columns and rows from the Date dimension, keep either the first of the last day of the month
Do NOT use database views
A view would drag a much larger table (the date) into a month-based fact table
How about a time dimension based on seconds?
There are over 31 million seconds in a year!
Avoid them as dimensions
But keep the SQL date-timestamp data as basic attributes in facts (not as dimensions), if needed to compute precise queries based on specific times
Older approach: keep a dimension of minutes or seconds and make it based on an offset from midnight of each day, but it’s messy when timestamps cross days
Might need something fancy though if the enterprise has well defined time slices within a day such as shift names, advertising slots -- then build a dimension
BIG
Examples: Customer, Product, Location
Millions or records with hundreds of fields (insurance customers)
Or hundreds of millions of records with few fields (supermarket customers)
Always derived by multiple sources
These dimensions should be conformed
SMALL
Examples: Transaction Type, Claim Status
Tiny lookup tables with only a few records and one ore more columns
Build by typing into a spreadsheet and loading the data into the DW
These dimensions should NOT be conformed
JUNK dimension: a tactical maneuver to reduce the number of FKs from a fact table by combining the low-cardinality values of small dimensions into a single junk dimension, generate as you go, don’t generate the cartesian product
Are the dimensions independent? Or is there a statistical correlation between them?
Weak statistical correlation and big dimensions
Even if there is a weak correlation, build the DW as if the dimensions are independent, especially for large dimensions
100 stores, 1 million products, 100K millions for combining the two, too large
If the statistical correlation is significant, build a fact table for the correlation, and there may be many such correlations, e.g., merchandizing correlation, pricing-strategy correlation, changing-seasonality correlation
Leave the dimensions simple and independent if the dimensions are large and the statistical correlation is weak
Strong correlations and small resulting dimensions
If the correlation is strong, e.g., product always has a brand, leave the brand OUT of the fact table, because the product always rolls up to a single brand and COMBINE the product and the brand into a single dimension
Arbitrary bound: 100K records is not small dimension
When the same dimension is attached to a fact table multiple times
Sale has an order date, payment date, a shipping date, a return date
A claim transaction has multiple internal people, a claim intake, a medical nurse, a claim adjuster, a payer, etc
End-user might be confused when they do drill-down into the dimension as to what the dimension represents, so it helps to have the dimension have different names
Solution: Create views over the dimensions for each of the dimensional role to help with the name recognition
For very large dimensions (locations) might be appropriate to create physical copies of the dimension table to help with the name confusion
Degenerate dimensions
When a parent-child relationship exists and the grain of the fact table is the child, the parent is kind of left out in the design process
Example:
grain of the fact able is the line item in an order
the order number is significant part of the key
but we don’t create a dimension for the order number, because it would be useless
we insert the order number as part of the key, as if it was a dimension, but we don’t create a dimension table for it
When the DW receives notification that some record in a dimension has changed, there are three basic responses:
Type 1 slow changing dimension (Overwrite)
Type 2 slow changing dimension (Partitioning History)
Type 3 slow changing dimension (Alternate Realities)
Overwrite one or more values of the dimension with the new value
Use when
the data are corrected
there is no interest in keeping history
there is no need to run previous reports or the changed value is immaterial to the report
Type 1 Overwrite results in an UPDATE SQL statement when the value changes
If a column is Type-1, the ETL subsystem must
Add the dimension record, if it’s a new value or
Update the dimension attribute in place
Must also update any Staging tables, so that any subsequent DW load from the staging tables will preserve the overwrite
This update never affects the surrogate key
But it affects materialized aggregates that were built on the value that changed (will be discussed more next week when we talk about delivering fact tables)
Beware of ETL tools Update else Insert statements, which are convenient but inefficient
Some developers use UPDATE else INSERT for fast changing dimensions and INSERT else UPDATE for very slow changing dimensions
Better Approach: Segregate INSERTS from UPDATES, and feed the DW independently for the updates and for the inserts
No need to invoke a bulk loader for small tables, simply execute the SQL updates, the performance impact is immaterial, even with the DW logging the SQL statement
For larger tables, a loader is preferable, because SQL updates will result into unacceptable database logging activity
Turn the logger off before you update with SQL Updates and separate SQL Inserts
Or use a bulk loader
Prepare the new dimension in a staging file
Drop the old dimension table
Load the new dimension table using the bulk loader
Standard
When a record changes, instead of overwriting
create a new dimension record
with a new surrogate key
add the new record into the dimension table
use this record going forward in all fact tables
no fact tables need to change
no aggregates need to be re-computed
Perfectly partitions history because at each detailed version of the dimension is correctly connected to the span of fact tables for which that version is correct
The natural key does not change
The job attribute changes
We can constraint our query
the Manager job
Joe’s employee id
Type-2 do not change the natural key (the natural key should never change)
With a Type-2 change, you might want to include the following additional attributes in the dimension
Date of change
Exact timestamp of change
Reason for change
Current Flag (current/expired)
The source system does not notify changes and does not time/date-stamp its own updates
For small tables, use a brute force approach of comparing every incoming field with every field in the DW to see if anything changes
For larger tables, use the CRC, or cyclic-redundancy checksum, a number of about 20 digits, like this:
Treat the entire incoming record as a string
Compute the CRC value of the string (some numeric value)
Compare the CRC value of today’s record with the CRC value of yesterday’s record
If the CRC values match, today’s record is identical to yesterday’s record
If the CRC values do not match, do a field by field comparison to see what has changed
Depending on whether the changed field is a Type-1, Type-2 or Type-3 change, do the necessary updates
Most ETL packages and the Internet include CRC computation and comparison code
To identify deletions
Read the source log file, if one exists
Determine that the CRC number of a record in the DW does not match some incoming record
Use the MINUS set operator to compare the DW dimension with the incoming dimension (usually works if the ETL staging table with today’s dimension and the DW are part of the same database)
Delete the record if this is valid for the business, most cases you don’t want to delete anything from the DW
Applicable when a change happens to a dimension record but the old record remains valid as a second choice
Product category designations
Sales-territory assignments
Instead of creating a new row, a new column is inserted (if it does not already exist)
The old value is added to the secondary column
Before the new value overrides the primary column
Example: old category, new category
Usually defined by the business after the main ETL process is implemented
Please move Brand X from Men’s Sportswear to Leather goods but allow me to track Brand X optionally in the old category
The old category is described as an Alternate reality
Automated data movement across data stores and the analytical area in support of a data warehouse, data mart or ODS effort
Extensible, robust, scalable infrastructure
Standardization of ETL processes across the enterprise
Reusability of custom and pre-build functions
Better utilization of existing hardware resources
Faster change-control & management
Integrated meta-data management
Complete development environment, work as you think design metaphor
Execute stored procedures at the source to extract data, perhaps filtering some
Store the extracted data into staging tables at the source
Generate a file with the source staging data
Transfer the file into the destination
Load the data into staging tables at the destination
Execute stored procedures at the destination to read the incoming staged data and compute what changed since the last time by comparing with the current value of the data warehouse
Populate the data warehouse with new changes
Supports incremental updates to sources
Supports simple data cleansing
Logs warning/errors
DataStage
big player, high ability to execute, gets good results from Gartner, visionary
Informatica
another big player, gets good results from Gartner, visionary with high ability to execute
most expensive
SAS ETL Server
fast becoming a major player, very positive results from Gartner
low exposure as an ETL tool (SAS a significant statistical analysis vendor)
Information Builder's Data Migrator/ETL Manager tool suite
part of Enterprise Focus/WebFocus
not a major player but industrial strength language, data connectors, etc
Sunopsis
cheap
relies on native RDBMS functionality
CIGNA people exposed to it at conferences liked it
Vendor tools promote standardization of the ETL process, reusability of custom and pre-built functions, lowering the time (and cost) of additional ETL efforts
Vendor ETL tools are somewhat self-documenting
Many tools can connect to a variety of sources (RDBMSs, non-relational, different OS, ERP, PeopleSoft, etc) without exposing the ETL developer to the differences
Vendor tools deal with changes in the source systems or the necessary transformations better, reducing long term ETL maintenance
Meta-data management is a huge advantage, especially when sharing data from many applications
Vendor ETL tools place discipline in the extraction and transformation process
ETL prices have not dropped much over the years, but there is increased functionality, performance, and usability from vendor tools
A conflict between
getting the data from the operational systems as fast as possible
having the ability to restart without repeating the process from the beginning
Reasons for staging
Recoverability: stage the data as soon as it has been extracted from the source systems and immediately after major processing (cleaning, transformation, etc).
Backup: can reload the data warehouse from the staging tables without going to the sources
Auditing: lineage between the source data and the underlying transformations before the load to the data warehouse
The staging area is owned by the ETL team
no indexes, no aggregations, no presentation access, no querying, no service level agreements
Users are not allowed in the staging area for any reason
staging is a construction site
Reports cannot access data in the staging area
tables can be added, or dropped without modifying the user community
Only ETL processes can read/write the staging area (ETL developers must capture table names, update strategies, load frequency, ETL jobs, expected growth and other details about the staging area)
The staging area consists of both RDBMS tables and data files
Flat files
fast to write, append to, sort and filter (grep) but slow to update, access or join
enables restart without going to the sources
XML Data Sets (not really used in Staging)
Relational Tables
Metadata, SQL interface, DBA support
Dimensional Model Constructs: Facts, Dimensions, Atomic Facts tables, Aggregate Fact Tables (OLAP Cubes)
Surrogate Key Mapping Tables
map natural keys from the OLTP systems to the surrogate key from the DW
can be stored in files or the RDBMS (but you can use the IDENTITY function if you go with the RDBMS approach)
Best Practices about these data structures:
perform impact analysis, capture metadata, use naming conventions,
Effectively integrate data from
different DBMS, OS, H/W, communication protocols
need a logical map, data movement view documents, data lineage report
have a plan
identity source candidates
analyze source systems with a data profiling tool
receive walk-through of data lineage and business rules (from the DW architect and business analyst to the ETL developer)
data alterations during data cleansing, calculations and formulas
measure twice, cut once
standard conformance to dimensions and numerical facts
receive walk-through of the dimensional model
Target table and column, table type (Dimension, Fact)
Slow-changing dimension type per target column
Type 1, overwrite (Customer first name)
Type 2, retain history (Customer last name)
Type 3, retain multiple valid alternative values
Source database, table, column
Transformations (the guts of the document)
Where do you capture this information? Which tool? How do you maintain this metadata?
Data modelers and ETL developers should maintain a documentation of the source systems including
subject area, application name, business name, department name
priority
business owner and technical owner
DBMS, production server
DB size, #users, complexity, #transactions per day
comments
Determine the system of record
Dealing with derived data (derive from base facts or accepted calculated columns from the source systems?)
if calculations are done by the DW infrastructure, the ETL developer is responsible for them
and what if the numbers don’t match?
recommendations: stay true to the definition of system-of-record
The further downstream you go from the originating data source, the more you increase the risk of extracting corrupt data. Barring rare exceptions, maintain the practice of sourcing data only from the system-of-record.
Analyze your source system
get a ER-model for the system or reverse engineering one (develop one by looking at the metadata of the system)
reverse engineering is not the same as forward engineering , i.e., given the ER-models of the source systems derive the dimensional schema of the data warehouse
Reverse engineering of the understanding of a source system
unique identifiers and natural keys
data types
relationships between tables (1-to-1, many-to-1, many to many), problematic when source database does not have foreign keys defined
discrete relationships (static data, reference tables)
Data content analysis
NULL values, especially in foreign keys, NULL result in lossy joins
In spite of the most detailed analysis, we recommend using outer join logic when extracting from relational source systems, simply because referential integrity often cannot be trusted on remote systems
Dates in non-date fields
What is the standard for the enterprise?
ODBC, OLE DB, JDBC, .NET
access databases from windows applications, so that applications are portable
performance is major drawback
every DBMS has an ODBC driver, even flat files
Adds two layers of interaction between the ETL and the database
Mainframe
COBOL copybooks give you the datatypes
EBCDIC and not ASCII character set (FTP does the translation between the mainframe and Unix/Windows)
Working with redefined fields (To save space the same field is used for different types of data)
Extracting from IMS, IDMS, Adabase
you need special adapters or you get someone in those systems to give you a file
XML sources, Web Log Files: doable, if you undestand the structure of those sources
Enterprise-Resource-Planning ERP Systems (SAP, PeopleSoft, Oracle)
Don’t treat it as a relational system -- it’s a mess
Use adapters
Using Audit Columns
Use the last update timestamp, populated by triggers or the front-end application
Must ensure that the timestamp is dependable, that is if the front-end modifies it, a batch job does not override it
Index the timestamp it if it’s dependable
Database Log Scrapping or sniffing
Take the log of the source file and try to determine the transactions that affect you
Sniffing does it real time
Timed extracts
Retrieve all records from the source that were modified today
POTENTIALLY dangerous -- what if the process fails today? When it runs tomorrow, you’d have lost today’s changes
Process of elimination
Preserve yesterday’s data in the stage area
Bring today’s entire data in the stage area
Perform a comparison
Inefficient, but the most reliable
Initial and Incremental Loads
Create two tables, previous-load and current-load
Load into the current-load, compare with the previous-load, when you are done drop the previous-load, rename the current-load into previous-load, create a new curent-log
Constrain on indexed columns
Retrieve only the data you need
Use DISTINCT sparingly
Use the SET operations sparingly
Use HINT (HINT tells the DBMS to make sure it uses a certain index)
Avoid NOT
Avoid functions in the where clause
Avoid subqueries
When a dimension is populated by several distinct systems, it is important to include the unique identifier from each of those systems in the target dimension in the data warehouse. Those identifiers should be viewable by end users to ensure peace of mind that the dimension reflects their data that they can tie back to in their transaction system.
Primary key (PK)
Meaningless, unique integer
Aka as surrogate key
Joins to Fact Tables
Is a Foreign Key to Fact Tables
Natural key (NK)
Meaningful key extracted from source systems
1-to-1 relationship to the PK for static dimensions
1-to-many relationship to the PK for slowly changing dimensions, tracks history of changes to the dimension
Descriptive Attributes
Primary textual but numbers legitimate but not numbers that are measured quantities
100 such attributes normal
Static or slow changing only
Product price -- either fact or dimension attribute
Via triggers in the DBMS
Read the latest surrogate key, generate the next value, create the record
Disadvantages: severe performance bottlenecks
Via the ETL process, an ETL tool or a 3-rd party application generate the unique numbers
A surrogate key counter per dimension
Maintain consistency of surrogate keys between dev, test and production
Using Smart Keys
Concatenate the natural key of the dimension in the source(s) with the timestamp of the record in the source or the Data Warehouse.
Tempting but wrong
By definition
Surrogate keys are supposed to be meaningless
Do you update the concatenate smart key if the natural key changes?
Performance
Natural keys may be chars and varchars, not integers
Adding a timestamp to it makes the key very big
The dimension is bigger
The fact tables containing the foreign key are bigger
Joining facts with dimensions based on chars/varchars become inefficient
Heterogeneous sources
Smart keys work for homogeneous environments, but most likely than not the sources are heterogeneous, each having the own definition of the dimension
How does the definition of the smart key changes when there is another source added? It doesn’t scale very well.
One advantage: simplicity in the ETL process
The definition of the key of the dimension in business terms, what does the dimension represent
Challenge: analyze the source systems so that a particular set of fields in that source corresponds to the grain of the dimension
Verify that a given source (file) implements the intended grain
Nothing should be returned by this from the source system/file
If something is returned by this, the fields A, B and C do not represent the grain of the dimension
Simple Case: the dimension is loaded as a lookup table
Typical Case
Data cleaning
Validate the data, apply business rules to make the data consistent, column validity enforcement, cross-column value checking, row de-duplication
Data conforming
Align the content of some or all of the fields in the dimension with fields in similar or identical dimensions in other parts of the data warehouse
Fact tables: billing transactions, customer support calls
IF they use the same dimensions, then the dimensions are conformed
Data Delivery
All the steps required to deal with slow-changing dimensions
Write the dimension to the physical table
Creating and assigning the surrogate key, making sure the natural key is correct, etc.
Dimensions are de-normalized flat tables
It is normal to have multiple embedded hierarchical structures in the same dimension
Eg. Store dimension
Geographic hierarchy: location, city, state, country
Merchandizing hierarchy: location, district, region
Can co-exist in the same dimension IF every attribute is single valued in the presence of the dimension’s primary key
If some attribute takes multiple values, it is not part of the dimension
E.g., a store can have multiple cash-registers, I.e., if the grain of the dimension is the store, the cash-register cannot be part of the store dimension, to include the cash register in the dimension the grain must be changed to be the cash-register, not the store
Normalized structures (3NF)
ok to exist in the staging area
should be flattened (2NF) before the delivery phase, using a simple query over the normalized structures
Dimensions that remain normalized
Looks like a snowflake, if the levels of the hierarchy obey perfect many-to-1 relationships
There is no semantic difference, the information content between the two approaches are the same
Problems with snow-flakes
If the hierarchical model changes, the normalized table schema and the declared joins must change, as well as the resulting end-user queries
More confusing to the end user, flat dimensions mask the complexity of the data
However, appropriate snow-flakes do exist (as sub-dimensions of dimensions)
Virtually everywhere: measurements are defined at specific times, repeated over time, etc.
Most common: calendar-day dimension with the grain of a single day, many attributes
Doesn’t have a conventional source:
Built by hand, speadsheet
Holidays, workdays, fiscal periods, week numbers, last day of month flags, must be entered manually
10 years are about 4K rows
Note the Natural key: a day type and a full date
Day type: date and non-date types such as inapplicable date, corrupted date, hasn’t happened yet date
fact tables must point to a valid date from the dimension, so we need special date types, at least one, the N/A date
How to generate the primary key?
Meaningless integer?
Or 10102005 meaning Oct 10, 2005 ? (reserving 9999999 to mean N/A?)
This is a close call, but even if meaningless integers are used, the numbers should appear in numerical order (why? Because of data partitioning requirements in a DW, data in a fact table can be partitioned by time)
Also typically needed are time dimensions whose grain is a month, a week, a quarter or a year, if there are fact tables in each of these grains
These are physically different tables
Are generated by eliminating selected columns and rows from the Date dimension, keep either the first of the last day of the month
Do NOT use database views
A view would drag a much larger table (the date) into a month-based fact table
How about a time dimension based on seconds?
There are over 31 million seconds in a year!
Avoid them as dimensions
But keep the SQL date-timestamp data as basic attributes in facts (not as dimensions), if needed to compute precise queries based on specific times
Older approach: keep a dimension of minutes or seconds and make it based on an offset from midnight of each day, but it’s messy when timestamps cross days
Might need something fancy though if the enterprise has well defined time slices within a day such as shift names, advertising slots -- then build a dimension
BIG
Examples: Customer, Product, Location
Millions or records with hundreds of fields (insurance customers)
Or hundreds of millions of records with few fields (supermarket customers)
Always derived by multiple sources
These dimensions should be conformed
SMALL
Examples: Transaction Type, Claim Status
Tiny lookup tables with only a few records and one ore more columns
Build by typing into a spreadsheet and loading the data into the DW
These dimensions should NOT be conformed
JUNK dimension: a tactical maneuver to reduce the number of FKs from a fact table by combining the low-cardinality values of small dimensions into a single junk dimension, generate as you go, don’t generate the cartesian product
Tuesday, February 19, 2008
Control M Job Scheduling Tool
Srl NO # Subject Complexity Questions Answers
1 Control M Average What is Control M? Control-M is a multi-platform job scheduling and control system. It executes as a background activity and schedules, submits, tracks and follows up the execution of jobs. Used in conjunction with Enterprise Control Station (Enterprise/CS) it provides centralised workstation control of the job scheduling production environment. An instance of Control-M resides on each of the machines for which scheduling is required. Enterprise/CS resides on a Sun workstation and can control multiple instances of Control-M.
2 Control M Complex Integerated Components of Enterprise/CS Enterprise/CS is comprised of three integrated components which work in unison to provide centralised control of the entire job scheduling environment. 1. The first component, the data-centre, consists of one or more computers which are managed by one Control-M monitor. Enterprise/CS can control many data-centres of the same or different types of platforms. 2.The second component, a gateway, provides communication between data-centres and the Enterprise/CS workstation(s). 3.The third component, the Enterprise/CS Workstation, provides an easy-to-use graphical user interface and local SQL database, used to maintain all the data-centres.
3 Control M Simple Owner of the production file controlled 'prod' user.
4 Control M Average General Structure of the HOME dorectory of the 'prod' user runlib - Storage area for all scripts and binaries used by a Control M controlled application overlib - Temporary area for emergency fixes rellib - Used as holding area prior to release into run library oldlib - Contains the previous version of any updated files. adhoc - Emergency one off runs INIT - Initialisation files : ksh.env, csh.env, INIT, pcp related files, etc.
5 Control M Simple What the environment variable CTM_ODATE mean? Order date for the job.
6 Control M Average Stages of execution 1. Control M checks the permissions of the file, comparing it with the job owner field. It copies the file, making a number of changes to the script. These changes are made so that Control-M can trace and post-process the job output. 2. Control-M switches user to the job owner, changes directory to the job owners HOME directory and executes the .profile/.cshrc scripts. 3. Main script startup 4. The Parameter Construction Program (PCP) is used to create a file of options, parameters and other configuration information, some of which may change depending on the date. 5. Main script execution 6.Control M - Final processing
7 Control M Simple PCP The Parameter Construction Program should be used to create any variable configuration, parameters or startup code required by a group of scripts.
8 Control M Average How do you call a sql? Call the oracle login instances and giving the sql script as a parameter
9 Control M Average How do you call a Informatica session? Using pmcmd command in the script
10 Control M Simple What is RIT? It's a Run time Information Template. It tell us the information about the control M job reg. Its scheduling time, Inputs reqired, database which it is using, Whether it is doing Insert/ Update / Delete
1 Control M Average What is Control M? Control-M is a multi-platform job scheduling and control system. It executes as a background activity and schedules, submits, tracks and follows up the execution of jobs. Used in conjunction with Enterprise Control Station (Enterprise/CS) it provides centralised workstation control of the job scheduling production environment. An instance of Control-M resides on each of the machines for which scheduling is required. Enterprise/CS resides on a Sun workstation and can control multiple instances of Control-M.
2 Control M Complex Integerated Components of Enterprise/CS Enterprise/CS is comprised of three integrated components which work in unison to provide centralised control of the entire job scheduling environment. 1. The first component, the data-centre, consists of one or more computers which are managed by one Control-M monitor. Enterprise/CS can control many data-centres of the same or different types of platforms. 2.The second component, a gateway, provides communication between data-centres and the Enterprise/CS workstation(s). 3.The third component, the Enterprise/CS Workstation, provides an easy-to-use graphical user interface and local SQL database, used to maintain all the data-centres.
3 Control M Simple Owner of the production file controlled 'prod' user.
4 Control M Average General Structure of the HOME dorectory of the 'prod' user runlib - Storage area for all scripts and binaries used by a Control M controlled application overlib - Temporary area for emergency fixes rellib - Used as holding area prior to release into run library oldlib - Contains the previous version of any updated files. adhoc - Emergency one off runs INIT - Initialisation files : ksh.env, csh.env, INIT, pcp related files, etc.
5 Control M Simple What the environment variable CTM_ODATE mean? Order date for the job.
6 Control M Average Stages of execution 1. Control M checks the permissions of the file, comparing it with the job owner field. It copies the file, making a number of changes to the script. These changes are made so that Control-M can trace and post-process the job output. 2. Control-M switches user to the job owner, changes directory to the job owners HOME directory and executes the .profile/.cshrc scripts. 3. Main script startup 4. The Parameter Construction Program (PCP) is used to create a file of options, parameters and other configuration information, some of which may change depending on the date. 5. Main script execution 6.Control M - Final processing
7 Control M Simple PCP The Parameter Construction Program should be used to create any variable configuration, parameters or startup code required by a group of scripts.
8 Control M Average How do you call a sql? Call the oracle login instances and giving the sql script as a parameter
9 Control M Average How do you call a Informatica session? Using pmcmd command in the script
10 Control M Simple What is RIT? It's a Run time Information Template. It tell us the information about the control M job reg. Its scheduling time, Inputs reqired, database which it is using, Whether it is doing Insert/ Update / Delete
DataWarehousing FAQ
1 DataWarehousing simple what is a Data Warehouse? A data warehouse is a relational database that is designed for query and analysis rather than for transaction processing. It usually contains historical data derived from transaction data, but it can include data from other sources. It separates analysis workload from transaction workload and enables an organization to consolidate data from several sources.
19 DataWarehousing Complex What is the conventional definition of a DWH? Explain each term. Subject-oriented, Integrated, Time-variant, Non-volatile.
17 DataWarehousing Medium Draw the architecture of a Datawarehousing system.
35 What are the goals of the Data warehouse?
18 DataWarehousing Medium What are the approaches in constructing a Datawarehouse and the datamart? Top-down and Bottom-up approaches.
2 DataWarehousing simple Data Mart A data structure that is optimized for access. It is designed to facilitate end-user analysis of data. It typically supports a single, analytic application used by a distinct set of workers.
25 Datawarehousing Medium Can a datamart be independent? Yes.
30 Datawarehousing simple What are the sources for a datawarehouse? OLTP system, Legacy system, Files, Other sources.
14 DataWarehousing simple What the difference is between a database, a data warehouse and a data mart? " A database is an organized collection of information.
-- A data warehouse is A very large database with special sets of tools to extract and cleanse data from operational systems
and to analyze data.
-- A data mart is A focused subset of A data warehouse that deals with A single area of data and is organized for quick
analysis."
4 DataWarehousing simple OLAP (On-Line Analytical Processing) A method by which multidimensional analysis occurs.
5 DataWarehousing average/simple what do you mean by Multidimensional Analysis? The ability to manipulate information by a variety of relevant categories or “dimensions” to facilitate analysis and understanding of the underlying data. It is also sometimes referred to as “drilling-down”, “drilling-across” and “slicing and dicing”
42 What is the difference between OLAP, ROLAP, MOLAP ,DOLAP?
40 Difference between OLAP & OLTP?
20 OLAP Medium What are the different types of OLAP? Give an eg. ROLAP eg.BO, MOLAP eg.Cognos, HOLAP, DOLAP
24 Data modelling Medium Which is the suitable data model for a datawarehouse? Why? MultiDimensional Model. optimized for data warehouse, data mart and online analytical processing (OLAP) applications. The main advantage of this database is query performance.
7 DataWarehousing simple Star Schema A means of aggregating data based on a set of known dimensions. It stores data multidimensionally in a two dimensional Relational Database Management System (RDBMS), such as Oracle.
13 DataWarehousing average/simple What are the benefits of STAR SCHEMA? "Because it’s denormalized structure , i.e., Dimension Tables are denormalized. Why to denormalize means the first (and often only) answer is : speed. OLTP structure is designed for data inserts, updates, and deletes, but not data retrieval. Therefore, we can often squeeze some speed out of it by denormalizing some of the tables and having queries go against fewer tables.
These queries are faster because they perform fewer joins to retrieve the same recordset. Joins are also confusing to many End users. By denormalizing, we can present the user with a view of the data that is far easier for them to understand.
Benefits of STAR SCHEMA:
· Far fewer Tables.
· Designed for analysis across time.
· Simplifies joins.
· Less database space.
· Supports “drilling” in reports.
· Flexibility to meet business and technical needs.
"
16 DataWarehousing complex What are Additive Facts? Or what is meant by Additive Fact? The fact tables are mostly very huge and almost never fetch a single record into our answer set. We fetch a very large number of records on which we then do, adding, counting, averaging, or taking the min or max. The most common of them is adding. Applications are simpler if they store facts in an additive format as often as possible. Thus, in the grocery example, we don’t need to store the unit price. We compute the unit price by dividing the dollar sales by the unit sales whenever necessary.
8 DataWarehousing simple Snowflake Schema An extension of the star schema by means of applying additional dimensions to the dimensions of a star schema in a relational environment.
32 Datawarehousing Complex What is Galaxy schema? A set of fact tables with some mutual dimension tables is called galaxy schema.
36 What is Dimension & Fact ?
41 Different types of Dimensions Confirmed, Degenerate, Junk, Mini, Multivalued, Too-Many, Dirty customer, Demographic mini dimensions
23 Normalization Twisted Are the dimensional tables normalized? If so when? Yes. In snowflake schema, they are normalized.
37 What is Transaction fact table & Centipede Fact table?
38 Different types of Facts? Additive, semi-additive, non-additive
48 What are the types of Factless fact tables? Event tracking & coverage
43 What is Granularity? "Granularity
The level of detail of the facts stored in a data warehouse."
22 Normalization simple Is the Fact table normalized? Yes
12 DataWarehousing average/simple Can 2 Fact Tables share same dimensions Tables? Yes
21 Project-related Complex Give egs. of the fact, dimensional tables, datamarts/DWH used in your project. Explain what data each contains.
31 Datawarehousing Medium What are fact constellations? Multiple fact tables sharing the dimension tables.
15 DataWarehousing simple What is a Fact less fact table ? "A factless fact table captures the many-to-many relationships between dimensions, but contains no numeric or textual facts. They are often used to record events or coverage information.
( A fact which does not have any measures )
Common examples of factless fact tables include:
o Identifying product promotion events (to determine promoted products that didn’t sell)
o Tracking student attendance or registration events
o Tracking insurance-related accident events
o Identifying building, facility, and equipment schedules for a hospital or University.
"
27 Datawarehousing simple What is metadata? Data that describes data and other structures, such as objects, business rules, and processes.
9 DataWarehousing simple What is data quality? Data quality (information quality) is defined as standardizing and consolidating customer and/or business data. By cleansing/enhancing the data and combining related records to avoid duplicate entries, you’re able to create a single record view. Within Informatica, this all takes place prior to the initial load to the target database as well as during the on-going data maintenance and updating processes.
28 ETL simple How do you achieve data quality? By Cleansing - The process of resolving inconsistencies and fixing the anomalies in source data, typically as part of the ETL process.
46 What is Parsing & Data Mining.
29 Datawarehousing Medium What are surrogate keys? System generated, artificial primary keys in alternative to the natural keys like SSN etc.
26 Datawarehousing simple Name a few data modelling tools. Erwin, Embarcedaro, Rational Rose, Oracle Designer.
33 Datawarehousing Medium Materialized views? Views storing pre-computed results are called materialized views.
34 Datawarehousing Medium Can you insert into materialized views? No.
44 Definition of Adhoc Queries?
45 What is ODS (Operational Data Store), DSS (Decision support System), Data Staging Area, Data Presentation Area.
47 What is Market-Basket analysis?
49 DataWarehousing average/simple SCD Types "Type 1 : Keep Most recent Values in Target
Type 2 : Keep a full history of changes in the target
Type 3 : Keep the current and previous values in the target.
"
6 DataWarehousing simple what is a Hypercube? A means of visually representing multidimensional data.
11 DataWarehousing complex/average Explain the performance improvement techniques in DW? Partition, aggregation, indexing..
Explain slice and dice ? To slice and dice is to break a body of information down into smaller parts or to examine it from different viewpoints so that you can understand it better. This term can be compared to drill down, which is the process of dividing an information area up into finer and finer layers in a hierarchy, but with the purpose of narrowing in to one small area or item.
19 DataWarehousing Complex What is the conventional definition of a DWH? Explain each term. Subject-oriented, Integrated, Time-variant, Non-volatile.
17 DataWarehousing Medium Draw the architecture of a Datawarehousing system.
35 What are the goals of the Data warehouse?
18 DataWarehousing Medium What are the approaches in constructing a Datawarehouse and the datamart? Top-down and Bottom-up approaches.
2 DataWarehousing simple Data Mart A data structure that is optimized for access. It is designed to facilitate end-user analysis of data. It typically supports a single, analytic application used by a distinct set of workers.
25 Datawarehousing Medium Can a datamart be independent? Yes.
30 Datawarehousing simple What are the sources for a datawarehouse? OLTP system, Legacy system, Files, Other sources.
14 DataWarehousing simple What the difference is between a database, a data warehouse and a data mart? " A database is an organized collection of information.
-- A data warehouse is A very large database with special sets of tools to extract and cleanse data from operational systems
and to analyze data.
-- A data mart is A focused subset of A data warehouse that deals with A single area of data and is organized for quick
analysis."
4 DataWarehousing simple OLAP (On-Line Analytical Processing) A method by which multidimensional analysis occurs.
5 DataWarehousing average/simple what do you mean by Multidimensional Analysis? The ability to manipulate information by a variety of relevant categories or “dimensions” to facilitate analysis and understanding of the underlying data. It is also sometimes referred to as “drilling-down”, “drilling-across” and “slicing and dicing”
42 What is the difference between OLAP, ROLAP, MOLAP ,DOLAP?
40 Difference between OLAP & OLTP?
20 OLAP Medium What are the different types of OLAP? Give an eg. ROLAP eg.BO, MOLAP eg.Cognos, HOLAP, DOLAP
24 Data modelling Medium Which is the suitable data model for a datawarehouse? Why? MultiDimensional Model. optimized for data warehouse, data mart and online analytical processing (OLAP) applications. The main advantage of this database is query performance.
7 DataWarehousing simple Star Schema A means of aggregating data based on a set of known dimensions. It stores data multidimensionally in a two dimensional Relational Database Management System (RDBMS), such as Oracle.
13 DataWarehousing average/simple What are the benefits of STAR SCHEMA? "Because it’s denormalized structure , i.e., Dimension Tables are denormalized. Why to denormalize means the first (and often only) answer is : speed. OLTP structure is designed for data inserts, updates, and deletes, but not data retrieval. Therefore, we can often squeeze some speed out of it by denormalizing some of the tables and having queries go against fewer tables.
These queries are faster because they perform fewer joins to retrieve the same recordset. Joins are also confusing to many End users. By denormalizing, we can present the user with a view of the data that is far easier for them to understand.
Benefits of STAR SCHEMA:
· Far fewer Tables.
· Designed for analysis across time.
· Simplifies joins.
· Less database space.
· Supports “drilling” in reports.
· Flexibility to meet business and technical needs.
"
16 DataWarehousing complex What are Additive Facts? Or what is meant by Additive Fact? The fact tables are mostly very huge and almost never fetch a single record into our answer set. We fetch a very large number of records on which we then do, adding, counting, averaging, or taking the min or max. The most common of them is adding. Applications are simpler if they store facts in an additive format as often as possible. Thus, in the grocery example, we don’t need to store the unit price. We compute the unit price by dividing the dollar sales by the unit sales whenever necessary.
8 DataWarehousing simple Snowflake Schema An extension of the star schema by means of applying additional dimensions to the dimensions of a star schema in a relational environment.
32 Datawarehousing Complex What is Galaxy schema? A set of fact tables with some mutual dimension tables is called galaxy schema.
36 What is Dimension & Fact ?
41 Different types of Dimensions Confirmed, Degenerate, Junk, Mini, Multivalued, Too-Many, Dirty customer, Demographic mini dimensions
23 Normalization Twisted Are the dimensional tables normalized? If so when? Yes. In snowflake schema, they are normalized.
37 What is Transaction fact table & Centipede Fact table?
38 Different types of Facts? Additive, semi-additive, non-additive
48 What are the types of Factless fact tables? Event tracking & coverage
43 What is Granularity? "Granularity
The level of detail of the facts stored in a data warehouse."
22 Normalization simple Is the Fact table normalized? Yes
12 DataWarehousing average/simple Can 2 Fact Tables share same dimensions Tables? Yes
21 Project-related Complex Give egs. of the fact, dimensional tables, datamarts/DWH used in your project. Explain what data each contains.
31 Datawarehousing Medium What are fact constellations? Multiple fact tables sharing the dimension tables.
15 DataWarehousing simple What is a Fact less fact table ? "A factless fact table captures the many-to-many relationships between dimensions, but contains no numeric or textual facts. They are often used to record events or coverage information.
( A fact which does not have any measures )
Common examples of factless fact tables include:
o Identifying product promotion events (to determine promoted products that didn’t sell)
o Tracking student attendance or registration events
o Tracking insurance-related accident events
o Identifying building, facility, and equipment schedules for a hospital or University.
"
27 Datawarehousing simple What is metadata? Data that describes data and other structures, such as objects, business rules, and processes.
9 DataWarehousing simple What is data quality? Data quality (information quality) is defined as standardizing and consolidating customer and/or business data. By cleansing/enhancing the data and combining related records to avoid duplicate entries, you’re able to create a single record view. Within Informatica, this all takes place prior to the initial load to the target database as well as during the on-going data maintenance and updating processes.
28 ETL simple How do you achieve data quality? By Cleansing - The process of resolving inconsistencies and fixing the anomalies in source data, typically as part of the ETL process.
46 What is Parsing & Data Mining.
29 Datawarehousing Medium What are surrogate keys? System generated, artificial primary keys in alternative to the natural keys like SSN etc.
26 Datawarehousing simple Name a few data modelling tools. Erwin, Embarcedaro, Rational Rose, Oracle Designer.
33 Datawarehousing Medium Materialized views? Views storing pre-computed results are called materialized views.
34 Datawarehousing Medium Can you insert into materialized views? No.
44 Definition of Adhoc Queries?
45 What is ODS (Operational Data Store), DSS (Decision support System), Data Staging Area, Data Presentation Area.
47 What is Market-Basket analysis?
49 DataWarehousing average/simple SCD Types "Type 1 : Keep Most recent Values in Target
Type 2 : Keep a full history of changes in the target
Type 3 : Keep the current and previous values in the target.
"
6 DataWarehousing simple what is a Hypercube? A means of visually representing multidimensional data.
11 DataWarehousing complex/average Explain the performance improvement techniques in DW? Partition, aggregation, indexing..
Explain slice and dice ? To slice and dice is to break a body of information down into smaller parts or to examine it from different viewpoints so that you can understand it better. This term can be compared to drill down, which is the process of dividing an information area up into finer and finer layers in a hierarchy, but with the purpose of narrowing in to one small area or item.
Subscribe to:
Posts (Atom)
