Data warehouse Concepts - Day 2 i

What is datawarehouse?

A data warehouse is built on 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.
In addition to a relational database, a data warehouse environment includes an extraction, transportation, transformation, and loading (ETL) solution, an online analytical processing (OLAP) engine

Characteristics of a data warehouse

Subject Oriented
Integrated
Nonvolatile
Time Variant

Different Types of Data

Metadata
Dimension data
Fact Data
Aggregated Data

Data warehouse Architectures



Dimension Modeling


Identify a Business Process
Identify Grain
Identify elements which describe the process (Dimensions)
Identify elements which measure process(facts)10

Read more »

Data warehouse Concepts - Day 2

dimension table:
A dimensional table is a collection of hierarchies and categories along which the user can drill down and drill up. it contains only the textual attributes.

LookUp table

A LookUp table is the one which is used when updating a warehouse. When the lookup is placed on the target table (fact table / warehouse) based upon the primary key of the target, it just updates the table by allowing only new records or updated records based on the lookup condition.

Aggregate table

Aggregate table contains the summary of existing warehouse data which is grouped to certain levels of dimensions.Retrieving the required data from the actual table, which have millions of records will take more time and also affects the server performance.To avoid this we can aggregate the table to certain required level and can use it.This tables reduces the load in the database server and increases the performance of the query and can retrieve the result very fastly

What is ODS?

1. ODS means Operational Data Store.
2. A collection of operation or bases data that is extracted from operation databases and standardized, cleansed, consolidated, transformed, and loaded into an enterprise data architecture. An ODS is used to support data mining of operational data, or as the store for base data that is summarized for a data warehouse. The ODS may also be used to audit the data warehouse to assure summarized and derived data is calculated properly. The ODS may further become the enterprise shared operational database, allowing operational systems that are being reengineered to use the ODS as there operation databases.

Read more »

Data warehouse Concepts - Day 1

Data warehouse :
A Data warehouse is a repository of integrated information, available for queries and analysis. Data and information are extracted from heterogeneous sources as they are generated. This makes it much easier and more efficient to run queries over data that originally came from different sources". Another definition for data warehouse is: " A data warehouse is a logical collection of information gathered from many different operational databases used to create business intelligence that supports business analysis activities and decision-making tasks, primarily, a record of an enterprise's past transactional and operational information, stored in a database designed to favour efficient data analysis and reporting (especially OLAP)". Generally, data warehousing is not meant for current "live" data, although 'virtual' or 'point-to-point' data warehouses can access operational data. A 'real' data warehouse is generally preferred to a virtual DW because stored data has been validated and is set up to provide reliable results to common types of queries used in a business

Data Warehouse is a repository of integrated information, available for queries and analysis. Data and information are extracted from heterogeneous sources as they are generated....This makes it much easier and more efficient to run queries over data that originally came from different sources.
Typical relational databases are designed for on-line transactional processing (OLTP) and do not meet the requirements for effective on-line analytical processing (OLAP). As a result, data warehouses are designed differently than traditional relational databases


Data Warehousing is concerned with the activities involved with creating a DataRepository where you store all the facts you know about some large enterprise or activity, and then analyse the data in different ways,to look for patterns on the data. Typically this is done by large retail organisations, trying to identify patterns in buying behaviour so they can sell more stuff to the poor unsuspecting customer.

Read more »

Repository tables

In BusinessObjects 6.5 Repository have 57 tables.


Security Domain tables 25


1. OBJ_M_ACTOR
2. OBJ_M_USRATTR
3. OBJ_M_DOCCST
4. OBJ_M_GENPAR
5. OBJ_M_MAGICID
6. OBJ_M_RESLINK
7. OBJ_M_UNIVCST
8. OBJ_M_UNIVSLC
9. OBJ_M_ACTORDOC
10. OBJ_M_DOCUMENTS
11. OBJ_M_UNIVERSES
12. OBJ_M_ACTORLINK
13. OBJ_M_TIMESTAMP
14.OBJ_M_UNIVDBCST
15.OBJ_M_CONNECTION
16.DS_USER_LIST
17.OBJ_M_REPOSITORY
18.OBJ_M_CATEG
19.OBJ_M_CONNECTDATA
20.OBJ_M_OBJSLICE
21.OBJ_M_RESERVATION
22.DS_PENDING_JOB
23.OBJ_M_DOCCATEG
24.OBJ_M_DOCAT
25.OBJ_M_DOCATVAR


Document Domain have 1 tables

1.OBJ_X_DOCUMENTS

Universe Domain have 31 tables

1. UNV_JOIN
2. UNV_AUDIT
3. UNV_CLASS
4. UNV_RELATIONS
5. UNV_DIM_OBJ
6. UNV_OBJECT
7. UNV_OBJ_TAB
8. UNV_TAB_OBJ
9. UNV_CONTEXT
10. UNV_CTX_JOIN
11. UNV_TAB_PROP
12. UNV_JOIN_DATA
13. UNV_PROP_DATA
14. UNV_PROPERTY
15. UNV_UNIVERSE
16. UNV_CLASS_DATA
17. UNV_DIMENSION
18. UNV_PROP_TAB
19. UNV_OBJECT_DATA
20. UNV_CONTEXT_DATA
21. UNV_JOINCONTENT
22. UNV_UNIVERSE_DATA
23. UNV_OBJCONTENT
24. UNV_TABLE
25. UNV_TABLE_DATA
26. UNV_OBJECT_KEY
27. UNV_JOIN_OBJECT
28. UNV_COLUMNS
29. UNV_COLUMN_DATA
30. UNV_OBJ_COLUMN
31. UNV_X_UNIVERSES

Read more »

What is a Chasm Trap

What is a Chasm Trap

Simply put: A chasm occurs when a series of joins crosses a many >- one -<>

A >- B -<>

If I were to write a query that spanned all three of those tables the data from table A and C would be duplicated and measure values would be inflated. The inflation factor can be determined by counting the number of rows from the left that match a particular key and multiplying by the number of rows on the right....... for more details

Read more »