Data warehousing definitions

Data warehouse — a repository of business information used for analysis, separate from the “active” business system files.

Data mart — business-focused data warehouse. Usually focuses on a particular business issue.

Data mining — analysis techniques designed to detect patterns in the data. Could be applied to data mart/data warehouse contents.

OLAP (On-Line Analytical Processing) — Tools and applications that support interactive analysis of multidimensional data.

Data Warehousing A subject-oriented, integrated, time-variant, and nonvolatile collection of data in support of decision-making process.

Data Marts

1. R. Kimball - “a data mart is a flexible set of data, ideally based on the most atomic (granular) data possible to extract from operational source, and presented in a symmetric (dimensional) model that is resilient when faced with unexpected user queries”
2. “in its most simplistic form a data mart represent data from a single business process” Business process = purchase order, store inventory, etc Definitions

OLAP = On-line Analytical Processing

A reporting system designed to allow different flexible analysis in real time, on-line, with data structures designed for fast retrieval, with redundancy included to support performance. Note: “On-line” doesn’t indicate data from on-line systems, rather on-the-fly

OLAP Basics

1. Drill-down - decreasing the level of aggregation
2. Drill-up/Roll-up - increasing the level of aggregation
3. Drill-across - move between different star-join schemas using conformed dimensions and joins
4. Slicing and dicing – ability to look at the database from different views, e.g. one slice shows all sales of product type within regions, another slice shows all sales by sales channel within each product type
5. Pivoting - e.g. change columns to rows, rows to columns
6. Ranking - sorting

Business Intelligence (BI)

Forrester definition: A process of transforming data into information and making it available to users in time to make a difference.

Strategic BI (Examples: Balance scorecard, Strategic Planning)

1. Who: strategic leaders
2. What: formulate strategy and monitor corporate performance.

Operational BI (Examples: Budgeting, Sales forcasting)

1. Who: operational managers
2. What: execution of strategy againts objectives.

Analytical BI (Examples: Financial and Sales Analysis, Customer Segmentation, Clickstream analysis)

1. Who: analysts, knowledge worker, controller
2. What: ad-hoc analysis.

Dimensional Modeling The process and outcome of designing logical database schemas created to support OLAP and Data Warehousing solutions. THE Definition

OLTP = On Line Transactional Processing – Each transaction must be written to the database in real time, i.e. “on line” Transactional System Emphasis

Data structures must enable consistent and fast writing.

Data Stage Designer Performance Tuning in Server jobs

1. In derivations, instead of calling routines, implement the logic in the derivation. This eliminates the overhead of the procedure call.
   
2. Implement the logic in a stage variable and then point the stage variable to the actual field.
   
3. Use Transforms rather than using routines.
   
4. While using the ODBC stage adjust the rows per transaction setting. Try setting to 1000, 5000, or 10000.
   
5. Adjust the array size setting. Try setting to 10, 100, or 1000.
   
6. If output rows are Inserts or Appends and not Updates, consider using a native bulk loader.
   
7. Eliminate unused columns.
   
8. Eliminate unused references.
   
9. Minimize using the stages like SORT, AGGREGATE which minimizes the performance of the job.
   
10. If more transformer Stages are used in sequence in a job, Enable the inter process buffering in the job properties or use the InterProcess Stage between Transformers which improves the performance.
    
11. Direct output to a sequential file compatible with the bulk loader. Then invoke the bulk loader using an after-job subroutine. The bulk loader for Oracle is SQLLDR.
    
12. Avoid using 'like' operator in user defined queries in ODBC stages
    
13. Avoid using stored procedures until and unless the functionality cannot be implemented in Data Stage jobs.
    
14. Tips while creating routines
15. Use variables in the routines.
16. Assign empty values to the variables before using them.
17. Routines will return Ans as return value. Instead of using ANS multiple times, use a variable .Implement the logic in that variable and assign that variable to ANS.
18. For Example: Ans = ''
    If ( Len(Trim(Name)) > 45) Then Ans = Ans : ',' : '24356' End
    Ans = Ans
    The above logic can be implemented using
    ErrStr = ''Ans = ''
    If ( Len(Trim(Name)) > 45) Then ErrStr := ',24356'End Ans = ErrStr
    

Useful Guidelines in Designing Ascential Data Stage Server Jobs

1. Logically create the folders / subfolders on the file server so that the files can be placed and accessed from the relevant folders/subfolders.
   
2. Group the Jobs logically into various categories / Subcategories.
   
3. Comment the Jobs by using Annotation stage which tells the users, the functionality implemented in the job.
   
4. Give descriptions in the properties of the stages used so that others can identify the functionality implemented in it.
   
5. Name passive stages with the Table/File Names they access in it. 6. Name active stages to match their function.
   
6. Name links to express the direction and type of data flowing through them.
   
7. Use job parameters where ever it is required. This makes the process easy while moving into production.
   
8. While using ODBC stages remember to remove the derivations in the columns.
   
9. Use ODBC stage to access relational tables.
   
10. Move constraints from Transform stages to input stage WHERE clauses, to reduce the number of rows the job has to process.
    
11. Use the in-built functions present in Data Stage rather than creating a new routine for implementing the same logic as of in-built function.
    

Easy way to remove the column derivation

1. Open the transformer stage
2. Copy the columns from source(ODBC ) to target(Sequential File)
3. Delete the columns from source(ODBC)
4. Copy all the columns from target to the source.
5. Close the transformer.
6. Now we find that all the derivations are cleared from ODBC stage.

While using Reference Lookups

1. Compare the number of input rows with the number of rows in the reference table. If the reference table is smaller than the number of input rows, pre-load the reference table into a hash file and then reference the hash file.
   
2. Consider moving reference lookups to a join within the input stage. All columns used to join the tables should be indexed to maximize performance.
   
3. If the number of rows in a hashed file is small, consider Pre-loading the file into memory by checking the Pre-load file to memory checkbox in the Hash File stage.
   
4. Remove unused columns from transforms. This does not apply to columns in sequential files or output to hash files.
   
5. While mapping the input records with the Hash Look ups, remember that the fields getting mapped should be of same data type and of same length.
   
6. While loading the hash files, trim the data.