Data warehouse Concepts

Central Data warehouse

A Central Data Warehouse is a repository of company data where a database is created from operational data extracts. This database adheres to a single, consistent enterprise data model to ensure consistency in decision making support across the company.

Active Metadata Warehouse

An Active Metadata Warehouse is a repository of Metadata to help speed up data reporting and analyses from an active data warehouse. In its most simple definition, a Metadata is data describing data.

Enterprise Data Warehouse:

Enterprise Data Warehouse is a centralized warehouse which provides service for the entire enterprise. A data warehouse is by essence a large repository of historical and current transaction data of an organization. An Enterprise Data Warehouse is a specialized data warehouse which may have several interpretations

Functional Data warehouse

The Enterprise Data Warehouse is broken down into Functional Data Warehouse based one functional Data. Depending on the size of the company and their financial capability, a Functional Data Warehouse may serve on department or may server more. There are also companies that have branches in many different geographic locations around the globe and their Enterprise Data Warehouse may set up differently with different clustering for Functional Data Warehouses.

Operational Data Store

An Operational Data Store (ODS) is an integrated database of operational data. Its sources include legacy systems and it contains current or near term data. An ODS may contain 30 to 60 days of information, while a data warehouse typically contains years of data.

Read more »

ODS Vs. Data Warehouse

ODS Vs. Data Warehouse

Operational Data store

- Data Focused Integration From Transaction Processing Focused Systems

- Age Of The Data is Current, Near Term (Today, Last Week’s)

- Primary Use: Day-To-Day Decisions Tactical Reporting Current Operational
Results
- Frequency Of Load: Twice Daily , Daily, Weekly

Datawarehouse

- Subject Oriented,Integrated,Non-Volatile,Time Variant

- Age of the Data is Historic (Last Month, Qtrly, Five Years)

- Primary Use: Long-Term Decisions Strategic Reporting Trend Detection

- Frequency of load : Weekly, Monthly, Quarterly

Read more »

Incompatible objects

Incompatible objects nothing but Sometimes it is not possible to use certain combinations of objects in data providers. This situation arises when objects bear no relationship to one another. These objects are called incompatible objects.

For example, the Island Resorts Marketing universe contains the
[Reservation Year] and [Revenue] objects, which are incompatible. This is
because there is no revenue associated with a reservation. Revenue is
generated only when the customer is invoiced. The underlying database
structure reflects this; you cannot build a query that aggregates revenue by
reservation year because there is no such thing as revenue by reservation
year. In other words, the aggregation context that you specified for the
[Revenue] object does not exist.

Read more »

What is a scope of analysis?

The scope of analysis for a query is extra data that you can retrieve from the
database to give more details on the data returned by each of the objects in a
query. This extra data does not appear in the initial result document, but it
remains available in the data cube, so you can pull this data in to the report to
allow you to access more detail at any time. This process of refining the data
to lower levels of detail is called drilling down on an object

In the universe, the scope of analysis corresponds to the hierachical levels
below the object selected for a query.

You can set this level when you build a query.

You can also create a custom scope of analysis by selecting specific
dimensions to the Scope of Analysis pane.

For example, a scope of analysis of one
level down for the object Year, would include the object Quarter, which
appears immediately under Year.

Read more »

Views, Oracle

What is View?
A view is a logical table based on a table or another view. A view contains no data of its own but is like a window through which data from tables can be viewed or changed. The tables on which a view is based are called base tables. The view is stored as a SELECT statement in the DD.

Advantages of views;

->View restrict access to data bcz the view can display selective columns from the table.
->View can be used to make simple queries to retrieve the results of complicated queries.For example, view can used to query information from multiple tables without the user knowing how to write a join statement.
-> Views can be used to retrieve data from several tables.

There are two types:
simple views
Complex views

A simple view is

- You can retrieve the data one table;
- don't contains functions
- you can perform DML operations through the view

A complex view

- Retrieve the data many tables
- it can be contains functions
- Doesn't allow the DML operations through the view.

syntax for view

CREATE [OR REPLACE] [FORCE|NOFORCE] VIEW view
[(alias[,alias]...)]
as subquery
[WITH CHECK OPTION [CONSTRAINT constraint]]
[WITH READ ONLY [CONSTRAINT constraint]];

in this syntax

REPLACE - re create the view if it already exists
FORCE - create the view regardless of whether or not the base table exist
NOFORCE - create the view only if the base table exist(this automatically default)
alias - you can use alias in your subquery
check - condition
with READ only - it is ensures that no DML operation can be performed on this view.

e.g CREATE VIEW EMP10 AS SELECT EMPNO,ENAME,SAL FROM EMP WHERE DEPTNO =10;

Using ALIAS names in subquery

CREATE VIEW EMP10 AS SELECT EMPNO EMP_ID, ENAME EMPLOYEE, SAL*12 A_SAL FORM EMP WHERE DEPTNO=10;

Creating Complex View;

CREATE VIEW DEP_SUM (DNAME,MINSAL,MAXSAL) AS SELECT D.DNAME,MIN(E.SAL),MAX(SAL),AVG(SAL) FROM EMP E,DEPT D WHERE
E.DEPTNO=D.DEPTNO GROUP BY D.DNAME;


We can't remove a row if the view contains the Group functons,Group by clause, DISTINCT keyword, pseudocolumn keyword.

Removing view

Drop VIEW view;

Read more »