Difference between Data mart and Data Warehouse

Read More

ETL

An ETL tool is a tool that:

• Extracts data from various data sources (usually legacy)

• Transforms data
• from -> being optimized for transaction
to -> being optimized for reporting and analysis
• synchronizes the data coming from differ databases
• data cleanses to remove errors

• Loads data into a data warehouse

Why use an ETL tool?

• ETL tools save time and money when developing a data
warehouse by removing the need for “hand-coding”.
“Hand Coding” is still the most common way of integrating data
today.

• It requires hours and hours of development and expertise
to create a Business-Intelligence-System.

• It is very difficult for data base administrators to connect
between different brands of databases without using an external
tool.

In the event that databases are altered or new databases need to be integrated, a lot of “hand-coded” work needs to be completely redone.

Most ETL Tool used? Why?

• Informatica and Pentaho have very good products.

• Informatica has a far more extensive range of products, but compared to Pentaho is very expensive.

• Pentaho has proved that it can handle small to large scale systems.

• Pentaho is gaining fast momentum with businesses that would not have considered using open source products before.

Read More

Data processing

Data processing types:

    Transactional: High-volume and easy-processing collections of knowledge that generate by organizations a day.

    Analytical: Analysis of accumulated information frequently by end users; additionally remarked as business intelligence, includes decision call support systems (DSSs), enterprise info systems, internet applications, querying, and alternative end-user activities.

Multidimensional information Model:

• The third-dimensional information model is an integral a part of On-Line Analytical process, or OLAP. as a result of OLAP is on-line, it should offer answers quickly.

• The third-dimensional information model is meant to unravel complicated queries in real time.

• The third-dimensional information model is vital as a result of it enforces simplicity.

• The central attraction of the dimensional model of a business is its simplicity.... that simplicity is that the basic key that permits users to grasp databases, and permits software package to navigate databases expeditiously.

• The relative implementation of the third-dimensional information model is usually a star schema,or a snowflake schema.

Star schema:

• The star schema consists of 1 or additional truth tables and one or additional dimension tables that ar connected through foreign keys.

• dimensions ar pessimistic with every dimension being diagrammatic by one table

Snowflake schema:

• a kind of star schema within which the dimension tables ar partially or totally normalized.

• Dimensions ar normalized into multiple connected tables.

Data warehouse:

• A DW could be a subject homeward, integrated, time variant and non volatile assortment of knowledge in support of management’s deciding} process. {a information|a knowledge|an information} warehouse is that the main repository of the organization's historical data, its company memory

• these days organizations use DW to store analytical information so as to put strategic info within the hands of call manufacturers to assist productivity and empower users for creating higher choices, resulting in larger competitive advantage.

Data retail store definition:

• information marts ar analytical information stores designed to specialize in specific business functions for a selected community among a company. information marts ar typically derived from subsets {of information|of knowledge|of information} in an exceedingly data warehouse.

Data retail store types:

1-Dependent information Marts:

Allows you to unite your organization's information in one information warehouse. this offers you the standard blessings of centralization.

2-Independent information Marts

Is created while not the employment of a central information warehouse. this might be fascinating for smaller teams among a company.

3-Hybrid information Marts

Allows you to mix input from sources apart from an information warehouse.

Read More

RDBMS Meets the Cloud

• The traditional relational database can be a poor fit in these cloud environments. The promise of the cloud is that you can increase computing resources by allocating additional virtual machines.

• As a result, cloud providers offer simpler, nonrelational databases that can provide elastic on-demand scalability. The major offerings are:

• SimpleDB within Amazon AWS

• BigTable within Google App Engine

• Microsoft’s Azure Table Services and SQL Data Services

• These cloud databases differ in implementation detail, but share common core features:

• A hierarchical structure resembling a B-tree index or hash table. Rapid lookup via a single key value is provided.

• Flexible. Each “row” often can contain different “columns,” and columns may have multiple values or include a more complex embedded structure.

• Automatic geo-redundancy. Elements stored in the database are guaranteed to be replicated across multiple data centers.

• Automatic partitioning across multiple hosts and automatic scale-out as the size of – or demand on – the data store exceeds the capability of a single host.

• However, these databases are missing many of the features we’ve come to expect in a relational solution:

• Joins and complex queries must be implemented in procedural code.

• Transactions are not supported. You can’t create a set of changes that must succeed or fail as a single unit.

• “Eventual” consistency. When you make a change to a data item, it will be visible eventually, but not immediately, in all locations where a copy of that data is maintained.

• Will cloud databases disrupt the RDBMS market? Probably not. But the cloud environments available from Amazon, Google and Microsoft offer significant advantages over traditional hosting or in-house hardware models. Applications that exploit the cloud will find the scalability and zero-maintenance models of these cloud databases appealing. As these cloud applications emerge into the mainstream, the cloud database probably will establish a permanent and successful segment of the database taxonomy.

Read More

Comparison between DBMS

Many of you may asked this question "What are the differences between all of the different types of SQL? I hear of PostgreSQL, SQLite, MySQL, SQL, .... What are the differences between them?"

here you will find a comparison between DBMS like :

• Oracle.

• Mysql.

• Microsoft SQL Server .

• Postgresql.

• Hbase.

• DB2.

• Teradata.

Read More

Transparency

Distribution Transparency

• Distribution transparency allows user to perceive database as single, logical entity.

• If DDBMS exhibits distribution transparency, user does not need to know:

• Data is fragmented (fragmentation transparency),.

• Location of data items (location transparency).

• Otherwise call this local mapping transparency.

• With replication transparency, user is unaware of replication of fragments.

Naming Transparency:

• Each item in a DDB must have a unique name.

• DDBMS must ensure that no two sites create a database object with same name.

• One solution is to create central name server. However, this results in:

• loss of some local autonomy;

• central site may become a bottleneck;

• low availability; if the central site fails, remaining sites cannot create any new objects.

• Alternative solution - prefix object with identifier of site that created it.

• For example, Branch created at site S₁ might be named S1.BRANCH.

• Also need to identify each fragment and its copies.

• Thus, copy 2 of fragment 3 of Branch created at site S₁ might be referred to as S1.BRANCH.F3.C2.

• However, this results in loss of distribution transparency.

• An approach that resolves these problems uses aliases for each database object.

• Thus, S1.BRANCH.F3.C2 might be known as LocalBranch by user at site S₁.

• DDBMS has task of mapping an alias to appropriate database object.

Transaction Transparency:

• Ensures that all distributed transactions maintain distributed database’s integrity and consistency.

• Distributed transaction accesses data stored at more than one location.

• Each transaction is divided into number of subtransactions, one for each site that has to be accessed.

• DDBMS must ensure the indivisibility of both the global transaction and each of the subtransactions.

Concurrency Transparency:

• All transactions must execute independently and be logically consistent with results obtained if transactions executed one at a time, in some arbitrary serial order.

• Same fundamental principles as for centralized DBMS.

• DDBMS must ensure both global and local transactions do not interfere with each other.

• Similarly, DDBMS must ensure consistency of all subtransactions of global transaction.

Performance Transparency:

• DDBMS must perform as if it were a centralized DBMS.

• DDBMS should not suffer any performance degradation due to distributed architecture.

• DDBMS should determine most cost-effective strategy to execute a request.

Read More