The Cost of Gold

Huskers 32 - Michigan 28
Go Big Red!

*

Internet Sales Show Big Gains Over Holidays

Online retailers, whose growth was expected to level off after a decade of dizzying gains, experienced a stellar holiday season, according to two preliminary reports released yesterday, as traditional stores like Wal-Mart and Target cemented their place on the Web.

Wal-Mart was the third most popular site this season, trailing Amazon and eBay, with Target, Best Buy and Circuit City close behind. For the first time, Neiman Marcus and L. L. Bean said they received more orders from their Web sites than telephone orders through their catalogs.

*

The Cost of Gold
Articles in this series examine gold mining around the world.

30 Tons an Ounce
Behind Gold's Glitter: Torn Lands and Pointed Questions

Treasure of Tanacocha
Tangled Strands in Fight Over Peru Gold Mine

The Hidden Payroll
Below a Mountain of Wealth, a River of Waste

Water Worries
A Drier and Tainted Nevada May Be a Legacy of a Gold Rush

*

Don't Think Twice, It's All Right
Apparently, introspection is bad.

10 Greatest Gadget Ideas of the Year

Cancer and Quantum Mechanics

Slowly, Cancer Genes Tender Their Secrets

Using microarrays, or gene chips - small slivers of glass or nylon that can be coated with all known human genes - scientists can now discover every gene that is active in a cancer cell and learn what portions of the genes are amplified or deleted. With another method, called RNA interference, investigators can turn off any gene and see what happens to a cell. And new methods of DNA sequencing make it feasible to start asking what changes have taken place in what gene.

The National Cancer Institute and the National Human Genome Research Institute recently announced a three-year pilot project to map genetic aberrations in cancer cells.

There are roughly 10 pathways that cells use to become cancerous and that involve a variety of crucial genetic alterations. There are genetic changes that end up spurring cell growth and others that result in the jettisoning of genes that normally slow growth. There are changes that allow cells to keep dividing, immortalizing them, and ones that allow cells to live on when they are deranged; ordinarily, a deranged cell kills itself. Still other changes let cancer cells recruit normal tissue to support and to nourish them. And with some changes, cancer cells block the immune system from destroying them. In metastasis, when cancers spread, the cells activate genes that normally are used only in embryo development, when cells migrate, and in wound healing.

Dr. Bert Vogelstein of Johns Hopkins University
piece together the molecular pathways that lead to cancer
black box
new technology

*

Quantum Trickery: Testing Einstein's Strangest Theory

This fall scientists announced that they had put a half dozen beryllium atoms into a "cat state." To a physicist, a "cat state" is the condition of being two diametrically opposed conditions at once, like black and white, up and down, or dead and alive.

The discovery that individual events are irreducibly random is probably one of the most significant findings of the 20th century

quantum mechanics
entanglement
spooky action at a distance
Erwin Schrödinger
uncertainty principle - Werner Heisenberg
Bohr
E.P.R. argument
locality
realism
prediction of a measurement

Sealand

The Principality of Sealand is a micronation (a self-declared but unrecognized state-like entity) that claims as its territory Roughs Tower, a former Maunsell Sea Fort located in the North Sea 10 km (six miles) off the coast of Suffolk, England, at 51°53′40″N, 1°28′57″E, as well as territorial waters in a twelve-nautical-mile radius. Sealand is occupied by the family and associates of Paddy Roy Bates. The population of the facility rarely exceeds five, and its inhabitable area is 550 m². It is also possibly the world's best-known micronation, and although its claims to sovereignty and legitimacy are not recognized by any country, it is nevertheless sometimes cited in debates as an interesting case study of how various principles of international law can be applied to a territorial dispute.

Hopefully one day I will have my own unrecognized micronation.

Alas, ANWR Remains Unsullied

Coleman votes for Alaska oil drilling bill, measure defeated

* In 2002, then-candidate Coleman pledged to oppose efforts to drill in ANWR.

* In December 2005, Coleman voted to support efforts to drill in ANWR.

In other words, Coleman opposed drilling ANWR before he supported it.
Flip-flop.

If other news, I voted for drilling in ANWR. Here are my reasons:

Top Ten Reasons to Support Development in ANWR

HR2863
That's the defense appropriations bill.
I can't find the ANWR addition.

News from US Senator Ted Stevens, Alaska.
Thump the tub.

Forever War

United States President George W. Bush issued an executive order authorizing the National Security Agency (NSA) as of 2002 to conduct warrantless domestic phone-taps of persons believed to be linked to al-Qaeda or its affiliates. The NSA subsequently performed wiretaps on international communications that included a U.S. participant. The authorization was kept secret until December 2005, when it was reported in the The New York Times, engendering serious controversy over both the legality of the blended international/domestic wiretaps and the revelation of this highly-classified program, especially in a time of war.

The controversial wiretaps arguably constitute a violation of the Fourth Amendment to the United States Constitution which makes search and seizure without a warrant illegal, and may also be a criminal violation of the wiretapping provisions of the Foreign Intelligence Surveillance Act (FISA) or Title III of the Omnibus Crime Control Act.

President Bush has maintained he acted within "legal authority derived from the constitution" and that Congress "granted [him] additional authority to use military force against al Qaeda". Allegedly, the necessary statutory authority to override FISA's warrant provisions is provided by the authorization to use "all necessary force" in the employment of military resources to protect the security of the United States; the use of wiretapping is a qualifying use of force (under the terms of the authorization for the use of military force against al-Qaida as found in Senate Joint Resolution 23, 2001).

World Cup 2006

I recently looked at the groups for the FIFA World Cup 2006, which will be hosted by Germany.

Congratulations to Ghana, who qualified for its first World Cup. Ghana is grouped with the US, Italy, and Czech Republic. Tough. Matches begin in June.

Bolivia Presidential Elections

It appears Evo Morales has won more than fifty percent of the vote and will become Bolivia's next president. Results showed Morales' Movement Toward Socialism party was just one seat shy of control in the lower house of Congress — and is without control of the Senate.

What will happen in Bolivia over the next few years? Can we expect radical changes that will rapidly enhance economic development and reduce poverty. No.

Mr. Morales has vowed to nationalize the natural gas industry and roll back the eradication of coca. I imagine the US is pleased.

Picture of the Week

Man standing by large tree.

Doctor Al-Rashid

Ziyad and his business partner have created a limited liability company. You can find it on the web at www.azchemicalsllc.com.

Minnesota Vikings

Many of you are probably aware of the escapade involving several Minnesota Vikings players and a few morally loose women on a boat cruising Lake Minnetonka. Last week the public was finally indulged with the details of the criminal charges against four players, and I was lucky enough to be watching local news at five when the fourth estate began to masturbate.

We warn our viewers: the following content may be offensive.

* Dante Culpepper received a lap dance while he sat by the bar
* Mo Williams fondled a woman while he received a lap dance
* Bryan McKinnie picked up a woman, placed her on the bar, and performed oral sex on her
* Fred Smoot used sex toys to perform sex acts on multiple women simultaneously

Local news at 5:00pm.
Hilarious.

It gets better.

The Hennepin County Sheriff's Office had a press conference - a press conference about misdemeanor crimes, which are punishable by 90 days in jail or a $1,000 fine. If you are found guilty of a misdemeanor for the first time, jail time is highly unlikely. The Sheriff's Office has spent over 300 hours investigating cock sucking, clitoris licking, and the rubbing of private parts. Wow, that is $10,000 well spent. Congratulations for making us all safer.

Too Much Information

If you read about the topics below, you will know what has been consuming my time and impeding my ability to post - that, and the fact Jude and I are having a contest to see who can go the longest without writing. Start small.

Table of Contents
-Business Intelligence
-Balanced Scorecard
-ETL
-Informatica
-Operational Data Store (ODS)
-Database
-Data Warehouse
-Star Schema
-Snowflake Schema
-Dimension
-Foreign Key
-Primary Key
-Microsoft SQL Server
-SQL
-Cognos

Business intelligence

The phrase business intelligence (BI) may refer to:
1. a set of business processes
2. the technology used in these processes, and
3. the information obtained from these processes.

Organizations typically gather such information in order to assess the business environment, and cover fields such as marketing research, industry or market research, and competitor analysis. Competitive organizations accumulate business intelligence in order to gain sustainable competitive advantage, and may regard such intelligence as a valuable core competence in some instances.

Persons involved in business intelligence processes may use application software and other technologies to gather, store, analyze, and provide access to data (also known as business intelligence). Some observers regard BI as the process of enhancing data into information and then into knowledge. The software aims to help people make "better" business decisions by making accurate, current, and relevant information available to them when they need it.

Generally, BI-collectors glean their primary information from internal business sources. Such sources help decision-makers understand how well they have performed. Secondary sources of information include customer needs, customer decision-making processes, the competition and competitive pressures, conditions in relevant industries, and general economic, technological, and cultural trends.

Each business-intelligence system has a specific goal, which derives from an organizational goal or from a vision statement. Both short-term goals (such as quarterly numbers to Wall Street) and long term goals (such as shareholder value, target industry share / size, etc) exist.

Industrial espionage may provide business intelligence by using covert techniques. A gray area exists between "normal" business intelligence and industrial espionage.

Some people use the term "BI" interchangeably with "briefing books" or with "executive information systems". One can regard a business intelligence system as a decision-support system (DSS).

Business performance management offers software-oriented business intelligence systems that some see as a new generation of business intelligence, though most people in the field use the terms interchangeably.

Contents
• 1 History
• 2 Metrics / Key Performance Indicators
• 3 Application software types
• 4 Designing and implementing a business intelligence programme
• 5 See also (companies)
• 6 See also
History

An early reference to non-business intelligence occurs in Sun Tzu's The Art of War. Sun Tzu claims that to succeed in war, one should have full knowledge of one's own strengths and weaknesses and full knowledge of one's enemy's strengths and weaknesses. Lack of either one might result in defeat. A certain school of thought draws parallels between the challenges in business and those of war, specifically:

• collecting data
• discerning patterns and meaning in the data (generating information)
• responding to the resultant information

Prior to the start of the Information Age in the late 20th century, businesses sometimes took the trouble to struggle to collect data from non-automated sources. Businesses then lacked the computing resources to properly analyze the data, and often made commercial decisions primarily on the basis of intuition.

As businesses started automating more and more systems, more and more data became available. However, collection remained a challenge due to a lack of infrastructure for data exchange or to incompatibilities between systems. Reports on the data gathered sometimes took months to generate. Such reports allowed informed long-term strategic decision-making. However, short-term tactical decision-making continued to rely on intuition.
In modern businesses, increasing standards, automation, and technologies have led to vast amounts of data becoming available. Data warehouse technologies have set up repositories to store this data. Improved ETL and even recently Enterprise Application Integration tools have increased the speedy collecting of data. OLAP reporting technologies have allowed faster generation of new reports which analyze the data. Business intelligence has now become the art of sieving through large amounts of data, extracting information and turning that information into actionable knowledge.

In 1989 Howard Dresner, a Research Fellow at Gartner Group popularized "BI" as a umbrella term to describe a set of concepts and methods to improve business decision-making by using fact-based support systems. Dresner left Gartner in 2005 and joined Hyperion Solutions as its Chief Strategy Officer.

Metrics / Key Performance Indicators

BI often uses Key performance indicators (KPIs) to assess the present state of business and to prescribe a course of action. More and more organizations have started to make data available more promptly. In the past, data only became available after a month or two, which did not help managers to adjust activities in time to hit Wall Street targets. Recently, banks have tried to make data available at shorter intervals and have reduced delays. For example, for businesses which have higher operational/credit risk loading (for example, credit cards and "wealth management"), A large multi-national bank makes KPI-related data available weekly, and sometimes offers a daily analysis of numbers. This means data usually becomes available within 24 hours, necessitating automation and the use of IT systems.

Application software types

People working in business intelligence have developed tools that ease the work, especially when the intelligence task involves gathering and analyzing large quantities of unstructured data.

Tool categories commonly used for business intelligence include:
• OLAP (Online Analytical Processing) sometimes simply called "Analytics" (based on dimensional analysis and the so-called "hypercube" or "cube")
• Scorecarding, Dashboarding and Information visualization
• Data warehouses
• DM - Data mining
• Business performance management
• Document warehouses
• Text mining
• EIS - Executive Information Systems
• DSS - Decision Support Systems
• MIS - Management Information Systems
• GIS - Geographic Information Systems

Designing and implementing a business intelligence programme

When implementing a BI programme one might like to pose a number of questions and take a number of resultant decisions, such as:

• Goal Alignment queries: The first step determines the short and medium-term purposes of the programme. What strategic goal(s) of the organization will the programme address? What organizational mission/vision does it relate to? A crafted hypothesis needs to detail how this initiative will eventually improve results / performance (i.e. a strategy map).

• Baseline queries: Current information-gathering competency needs assessing. Does the organization have the capability of monitoring important sources of information? What data does the organization collect and how does it store that data? What are the statistical parameters of this data, e.g. how much random variation does it contain? Does the organization measure this?

• Cost and risk queries: The financial consequences of a new BI initiative should be estimated. It is necessary to assess the cost of the present operations and the increase in costs associated with the BI initiative? What is the risk that the initiative will fail? This risk assessment should be converted into a financial metric and included in the planning?

• Customer and Stakeholder queries: Determine who will benefit from the initiative and who will pay. Who has a stake in the current procedure? What kinds of customers/stakeholders will benefit directly from this initiative? Who will benefit indirectly? What are the quantitative / qualitative benefits? Is the specified initiative the best way to increase satisfaction for all kinds of customers, or is there a better way? How will customers' benefits be monitored? What about employees,... shareholders,... distribution channel members?

• Metrics-related queries: These information requirements must be operationalized into clearly defined metrics. One must decide what metrics to use for each piece of information being gathered. Are these the best metrics? How do we know that? How many metrics need to be tracked? If this is a large number (it usually is), what kind of system can be used to track them? Are the metrics standardized, so they can be benchmarked against performance in other organizations? What are the industry standard metrics available?

• Measurement Methodology-related queries: One should establish a methodology or a procedure to determine the best (or acceptable) way of measuring the required metrics. What methods will be used, and how frequently will the organization collect data? Do industry standards exist for this? Is this the best way to do the measurements?

How do we know that?

• Results-related queries: Someone should monitor the BI programme to ensure that objectives are being met. Adjustments in the programme may be necessary. The programme should be tested for accuracy, reliability, and validity. How can one demonstrate that the BI initiative (rather than other factors) contributed to a change in results? How much of the change was probably random?

*

Balanced scorecard

In 1992, Robert S. Kaplan and David Norton introduced the balanced scorecard (BSC), a method for measuring a company's activities in terms of its vision and strategies. It gives managers a comprehensive view of the performance of a business.

It is a management tool that continuously reveals whether a company and its employees achieve the results set forth by the strategy. But it is also a tool that helps the company express the necessary objectives and initiatives to support the strategies.

Contents

• 1 A comprehensive view of business performance
• 2 Purpose of the balanced scorecard
• 3 Adoption results survey
• 4 See also
• 5 References
A comprehensive view of business performance

The scorecard seeks to measure a business from the following perspectives:

• Financial perspective - measures reflecting financial performance, for example number of debtors, cash flow or return on investment. The financial performance of an organization is fundamental to its success. Even non-profit organizations must make the books balance. Financial figures suffer from two major drawbacks:

o They are historical. Whilst they tell us what has happened to the organization they may not tell us what is currently happening, or be a good indicator of future performance.

o It is common for the current market value of an organization to exceed the market value of its assets. Tobin's-q measures the ratio of the value of a company's assets to its market value. The excess value can be thought of as intangible assets. These figures are not measured by normal financial reporting.

• Customer perspective - measures having a direct impact on customers, for example time taken to process a phone call, results of customer surveys, number of complaints or competitive rankings.

• Business process perspective - measures reflecting the performance of key business processes, for example the time spent prospecting, number of units that required rework or process cost.

• Learning and growth perspective - measures describing the companies learning curve, for example number of employee suggestions or total hours spent on staff training.

The specific measures within each of the perspectives will be chosen to reflect the drivers of the particular business. The method can facilitate the separation of strategic policymaking from the implementation, so that organizational goals can be broken into task oriented objectives which can be managed by front-line staff. It can also help detect correlation between activities. For example, we might find that the internal business objective of implementing a new telephone system can help the customer objective of reducing response time to telephone calls, leading to increased sales from repeat business.

In many senses, the objectives chosen are leading indicators of future performance. Effort we make today is reflected in the future profits of the company. In this way, current expenditure can be viewed as investment in the future of the company.

Purpose of the balanced scorecard

Kaplan and Norton found that companies are using the scorecard to:
• Clarify and update strategy
• Communicate strategy throughout the company
• Align unit and individual goals with strategy
• Link strategic objectives to long term targets and annual budgets
• Identify and align strategic initiatives
• Conduct periodic performance reviews to learn about and improve strategy

Adoption results survey

In 1997 Kurtzman found that 64% of companies questioned were measuring performance from a number of perspectives in a similar way to the balanced scorecard.

It is difficult to interpret the impressive survey based adoption statistics for the Balanced Scorecard, however, without being clear on how the term was both defined and understood by those participating in the survey. In practice, it appears, there are wide variations in understanding between organisations. In 2002, Cobbold and Lawrie developed a classification of Balanced Scorecard designs based upon intended method of use within an organisation. They describe how Balanced Scorecard can be used to support two distinct management activities, management control and strategic control, and asserts that due to differences in the performance data requirements of these applications, planned use should influence the type of Balanced Scorecard design adopted. They also describe characteristics of Balanced Scorecards appropriate for each purpose, and suggests a framework to help select between them.

Later that year the same authors reviewed the evolution of the Balanced Scorecard as a strategic management tool, recognising three distinct generations of Balanced Scorecard design. In their paper, they relate the empirically driven developments in Balanced Scorecard thinking with literature concerning strategic management within organisations. Cobbold and Lawrie argue that over the dozen years that have passed since its introduction significant changes have been made to the physical design, application and the design processes used to implement the tool within organisations. This Balanced Scorecard evolution can largely be attributed to empirical evidence of changes driven primarily by weaknesses in earlier design processes, rather than in the architecture of the original idea they write. They conclude that it is these changes, in what they refer to as 3rd Generation Balanced Scorecard that have enhanced the utility of Balanced Scorecard as a strategic management tool.

*

Extract, transform, load

Extract, transform, and load (ETL) is a process in data warehousing that involves
• extracting data from outside sources,
• transforming it to fit business needs, and ultimately
• loading it into the data warehouse.

ETL is important, as it is the way data actually gets loaded into the warehouse. This article assumes that data is always loaded into a data warehouse, whereas the term ETL can in fact refer to a process that loads any database.

Contents

• 1 Extract
• 2 Transform
• 3 Load
• 4 Challenges
• 5 Tools
o 5.1 Some ETL tools
• 6 See also
• 7 External links
Extract

The first part of an ETL process is to extract the data from the source systems. Most data warehousing projects consolidate data from different source systems. Each separate system may also use a different data organization / format. Common data source formats are relational databases, and flat files, but other source formats exist. Extraction converts the data into records and columns (aka fields).

Transform

The transform phase applies a series of rules or functions to the extracted data to derive the data to be loaded. Some data sources will require very little manipulation of data. However, in other cases any combination of the following transformations types may be required:

• Selecting only certain columns to load (or if you prefer, null columns not to load)
• Translating coded values (e.g. If the source system stores M for male and F for female but the warehouse stores 1 for male and 2 for female)
• Encoding free-form values (e.g. Mapping "Male" and "M" and "Mr" onto 1)
• Deriving a new calculated value (e.g. sale_amount = qty * unit_price)
• Joining together data from multiple sources (e.g. lookup, merge, etc)
• Summarizing multiple rows of data (e.g. total sales for each region)
• Generating Surrogate_key values
• Transposing(turning multiple columns into multiple rows or vice versa)

Load

The load phase loads the data into the data warehouse. Depending on the requirements of the organization, this process ranges widely. Some data warehouses merely overwrite old information with new data. More complex systems can maintain a history and audit trail of all changes to the data.

Challenges

ETL processes can be quite complex, and significant problems can occur. Improperly designed ETL systems or an unexpected change in format of one of the source systems can cause serious problems in the ETL process potentially destroying or corrupting significant amounts of data in the target system. An additional difficulty is making sure the data being uploaded is relatively consistent. Since multiple source databases all have different update cycles (some may be updated every few minutes, while others may take days or weeks), an ETL system may be required to hold back certain data until all sources are synchronized.

Tools

While an ETL process can be created using almost any programming language, creating them from scratch is quite complex. Increasingly, companies are buying ETL tools to help in the creation of ETL processes.

A good ETL tool must be able to communicate with the many different relational databases and read the various file formats used throughout an organization. ETL tools have started to migrate into Enterprise Application Integration, or even Enterprise Service Bus, systems that now cover much more than just the extraction transformation and loading of data. Many ETL vendors now have data profiling, data quality and metadata capabilities.

*

Informatica

Informatica PowerCenter

Unlock the Value of Your Strategic Data Assets

Informatica PowerCenter provides a single enterprise data integration platform to help organizations access, transform, and integrate data from a large variety of systems and deliver that information to other transactional systems, real-time business processes, and people. PowerCenter supports the activities of a business' integration competency center (ICC) and other integration experts by serving as the foundation for data warehousing, data migration, consolidation, “single-view,” metadata management, and synchronization. By enabling enterprises to create a single, consistent enterprise-wide information resource, PowerCenter helps them reduce IT costs and complexity, harness new technologies, and empower the business.

Your business turns to IT to support its needs, whether the issue is mergers and acquisitions, compliance, customer profitability, or any other strategic initiative. But IT often can't respond effectively. Hindered by a complex environment of systems that were not designed to share data, they respond slowly and often ineffectively, potentially compromising your business. Organizations must integrate data and manage their metadata using a robust enterprise data integration platform.

With Informatica PowerCenter you can:
Integrate data to provide business users holistic access to enterprise data—data is comprehensive, accurate, and timely
Scale and respond to business needs for information—deliver data in a secure, scalable environment that provides immediate data access to all disparate sources
Simplify design, collaboration, and re-use to reduce developers' time to results—unique metadata management helps boost efficiency to meet changing market demands

Ingredients for Success

Enterprise-level data integration

Informatica PowerCenter helps organizations respond to the business in a more coordinated, strategic way by bringing together all enterprise data and ensuring it's trustworthy and timely. Leverage of standards, metadata, and near-universal mainframe data access enables you to unlock the value of information from your disparate applications and databases. PowerCenter ensures accuracy of data through a single environment for transforming, profiling, integrating, cleansing, and reconciling data and managing metadata. It also helps ensure the right data reaches the right people at the right time, through real-time, on-demand, or periodic data updates.

Scalability

As your integrated IT resource becomes even more mission-critical, it must consistently provide secure, scalable, on-demand data. PowerCenter ensures security through complete user authentication, granular privacy management, and secure transport of your data. Linear scalability optimizes use of available resources—including 64-bit processors and Linux systems. Open APIs make it easier to add new data sources for extensibility. Processing options—including data-smart parallelism, partitioning, and the ability to scale out to heterogeneous grids—deliver flexibility in meeting increased demands. Interoperable and extensible, PowerCenter is portable across platforms without re-coding.

Developer productivity

Your development teams must be able to respond promptly to your business' changing needs, rapidly designing, collaborating on, and building solutions that address your latest requirements. PowerCenter simplifies design processes by making it easy to search and profile data, reuse objects across teams and projects, and leverage metadata. It facilitates collaboration across teams, sites, and projects by providing granular version control and automated configuration. To minimize risk and increase speed of deployment, PowerCenter maximizes re-use of code and provides impact analysis and data lineage information to assess the consequences of each change before you implement.

PowerCenter Editions

PowerCenter is available in two editions:
PowerCenter Standard Edition—The industry's leading software for accessing, integrating, and delivering data, PowerCenter Standard Edition cost-effectively leverages data from any system, to any system. PowerCenter Standard Edition allows installation in under thirty minutes.
PowerCenter Advanced Edition—In addition to all the features of PowerCenter Standard Edition, PowerCenter Advanced Edition provides broad enterprise data integration with a single platform, robust metadata analysis and rich reporting capabilities, cost-effective grid computing and team-based development capabilities. With PowerCenter Advanced Edition, organizations can realize the benefits of a unified platform that addresses the full data integration lifecycle-helping to drive productivity, lower maintenance costs, and gain a substantial cost advantage with a rapid out-of-the-box experience. PowerCenter Advanced Edition allows installation all from one CD, all in under an hour.

Informatica PowerExchange

Unlock Complex Data. On Demand.

Informatica PowerExchange, based on a services-oriented architecture (SOA), provides on-demand access to data in all critical enterprise data systems, including mainframe, midrange, and file-based systems. Available as a standalone service or tightly integrated with Informatica PowerCenter, PowerExchange helps organizations leverage mission-critical operational data by making it available to people and processes without requiring manual coding of data extraction programs. Its SQL access to native database APIs provides high-performance extraction, conversion, and filtering of data without intermediary staging and programming. Shared services offer data delivery options that enable IT organizations to flexibly and efficiently manage processing demands.

Organizations today demand immediate access to accurate information for quick decision making and high-speed operations. At the same time, the volume and variety of data is exploding, stretching the capacity of IT resources and infrastructures. To leverage the full value of their information, organizations must be able to integrate data from a wide variety of transactional applications and systems for easy access and "right time" delivery.

PowerExchange provides on-demand access to immediate, accurate, and understandable data. With Informatica PowerExchange you can:
Access and deliver data in "right time"
Extend existing IT investments
Unlock complex systems without coding

Access data on demand

PowerExchange offers several options for capturing data and making it available to a range of targets. It can capture data from relational and non-relational data sources either as whole data sets or as incremental changed data, in real-time or in a scheduled batch process. PowerExchange enables organizations to schedule data delivery to multiple targets weekly, daily, hourly—even at the sub-second. Informatica PowerExchange provides a single architecture that allows a seamless transition from batch and bulk, to batch and changed data capture, to real-time changed data capture delivery. PowerExchange also eliminates the need for multiple-step processes with extraction, file transfer, and load scripts for batch data processing.

Extend existing IT investments

Based on an extensible, service-oriented architecture, PowerExchange supports a variety of platforms. As a business decides to make more of its data sources available to other applications across the enterprise, it can add platforms easily. SQL access to native database APIs delivers high performance: PowerExchange extracts, converts, filters, and makes data available to target systems without intermediary staging and program coding.

Unlock complex systems without coding

Unlocking the value of legacy systems once meant costly new system development and data migration, or complex hand coding to access critical data. Informatica PowerExchange significantly reduces the time and resources required to leverage existing investments, streamlining access to legacy systems and delivering data to a range of business applications. It masks the complexity of source systems from developers and offers an intuitive GUI with SQL-like access, eliminating the need for lengthy training and implementation.

PowerExchange Architecture

Supported Platforms
See the complete list of supported platforms for PowerExchange, including options for batch, real-time and changed data capture.

*

Operational data store

An operational data store (or "ODS") is a database designed to integrate data from multiple sources to facilitate operations, analysis and reporting. Because the data originates from multiple sources, the integration often involves cleaning, redundancy resolution and business rule enforcement. An ODS is usually designed to contain low level or atomic (indivisible) data such as transactions and prices as opposed to aggregated or summarized data such as net contributions. Aggregated Data usually is stored in the

Database

A database is an organized collection of data. The term originated within the computer industry, but its meaning has been broadened by popular use, to the extent that the European Database Directive (which creates intellectual property rights for databases) includes non-electronic databases within its definition. This article is confined to a more technical use of the term; though even amongst computing professionals, some attach a much wider meaning to the word than others.

One possible definition is that a database is a collection of records stored in a computer in a systematic way, such that a computer program can consult it to answer questions. For better retrieval and sorting, each record is usually organized as a set of data elements (facts). The items retrieved in answer to queries become information that can be used to make decisions. The computer program used to manage and query a database is known as a database management system (DBMS). The properties and design of database systems are included in the study of information science.

The central concept of a database is that of a collection of records, or pieces of knowledge. Typically, for a given database, there is a structural description of the type of facts held in that database: this description is known as a schema. The schema describes the objects that are represented in the database, and the relationships among them. There are a number of different ways of organizing a schema, that is, of modelling the database structure: these are known as database models (or data models). The model in most common use today is the relational model, which in layman's terms represents all information in the form of multiple related tables each consisting of rows and columns (the true definition uses mathematical terminology). This model represents relationships by the use of values common to more than one table. Other models such as the hierarchical model and the network model use a more explicit representation of relationships.

Strictly speaking, the term database refers to the collection of related records, and the software should be referred to as the database management system or DBMS. When the context is unambiguous, however, many database administrators and programmers use the term database to cover both meanings.

Many professionals would consider a collection of data to constitute a database only if it has certain properties: for example, if the data is managed to ensure its integrity and quality, if it allows shared access by a community of users, if it has a schema, or if it supports a query language. However, there is no agreed definition of these properties.

Database management systems are usually categorized according to the data model that they support: relational, object-relational, network, and so on. The data model will tend to determine the query languages that are available to access the database. A great deal of the internal engineering of a DBMS, however, is independent of the data model, and is concerned with managing factors such as performance, concurrency, integrity, and recovery from hardware failures. In these areas there are large differences between products.

Contents

• 1 History
• 2 Database models
o 2.1 Flat model
o 2.2 Network model
o 2.3 Relational model
 2.3.1 Relational operations
o 2.4 Dimensional model
o 2.5 Object database models
• 3 Database Internals
o 3.1 Indexing
o 3.2 Transactions and concurrency
o 3.3 Replication
• 4 Applications of databases
• 5 Common Database Brands
• 6 See also
• 7 References
History

The earliest known use of the term data base was in June 1963, when the System Development Corporation sponsored a symposium under the title Development and Management of a Computer-centered Data Base. Database as a single word became common in Europe in the early 1970s and by the end of the decade it was being used in major American newspapers. (Databank, a comparable term, had been used in the Washington Post newspaper as early as 1966.)

The first database management systems were developed in the 1960s. A pioneer in the field was Charles Bachman. Bachman's early papers show that his aim was to make more effective use of the new direct access storage devices becoming available: until then, data processing had been based on punched cards and magnetic tape, so that serial processing was the dominant activity. Two key data models arose at this time: CODASYL developed the network model based on Bachman's ideas, and (apparently independently) the hierarchical model was used in a system developed by North American Rockwell, later adopted by IBM as the cornerstone of their IMS product.

The relational model was proposed by E. F. Codd in 1970. He criticized existing models for confusing the abstract description of information structure with descriptions of physical access mechanisms. For a long while, however, the relational model remained of academic interest only. While CODASYL systems and IMS were conceived as practical engineering solutions taking account of the technology as it existed at the time, the relational model took a much more theoretical perspective, arguing (correctly) that hardware and software technology would catch up in time. Among the first implementations were Michael Stonebraker's Ingres at Berkeley, and the System R project at IBM. Both of these were research prototypes, announced during 1976. The first commercial products, Oracle and DB2, did not appear until around 1980. The first successful database product for microcomputers was dBASE for the CP/M and PC-DOS/MS-DOS operating systems.

During the 1980s, research activity focused on distributed database systems and database machines, but these developments had little effect on the market. Another important theoretical idea was the Functional Data Model, but apart from some specialized applications in genetics, molecular biology, and fraud investigation, the world took little notice.

In the 1990s, attention shifted to object-oriented databases. These had some success in fields where it was necessary to handle more complex data than relational systems could easily cope with, such as spatial databases, engineering data (including software engineering repositories,) and multimedia data. Some of these ideas were adopted by the relational vendors, who integrated new features into their products as a result; the independent object database vendors largely disappeared from the scene.

In the 2000s, the fashionable area for innovation is the XML database. As with object databases, this has spawned a new collection of startup companies, but at the same time the key ideas are being integrated into the established relational products. XML databases aim to remove the traditional divide between documents and data, allowing all of an organization's information resources to be held in one place, whether they are highly structured or not.

Database models

Various techniques are used to model data structure. Most database systems are built around one particular data model, although it is increasingly common for products to offer support for more than one model. For any one logical model various physical implementations may be possible, and most products will offer the user some level of control in tuning the physical implementation, since the choices that are made have a significant effect on performance. An example of this is the relational model: all serious implementations of the relational model allow the creation of indexes which provide fast access to rows in a table if the values of certain columns are known.

A data model is not just a way of structuring data: it also defines a set of operations that can be performed on the data. The relational model, for example, defines operations such as selection, projection, and join. Although these operations may not be explicit in a particular query language, they provide the foundation on which a query language is built.

Flat model

Some would disagree that this qualifies as a data model, as defined above.

The flat (or table) model consists of a single, two-dimensional array of data elements, where all members of a given column are assumed to be similar values, and all members of a row are assumed to be related to one another. For instance, columns for name and password that might be used as a part of a system security database. Each row would have the specific password associated with an individual user. Columns of the table often have a type associated with them, defining them as character data, date or time information, integers, or floating point numbers. This model is, incidentally, a basis of the spreadsheet.

Network model

The network model (defined by the CODASYL specification) organizes data using two fundamental constructs, called records and sets. Records contain fields (which may be organized hierarchically, as in COBOL). Sets (not to be confused with mathematical sets) define one-to-many relationships between records: one owner, many members. A record may be an owner in any number of sets, and a member in any number of sets.

The operations of the network model are navigational in style: a program maintains a current position, and navigates from one record to another by following the relationships in which the record participates. Records can also be located by supplying key values.

Although it is not an essential feature of the model, network databases generally implement the set relationships by means of pointers that directly address the location of a record on disk. This gives excellent retrieval performance, at the expense of operations such as database loading and reorganization.

Relational model

The relational model was introduced in an academic paper by E. F. Codd in 1970 as a way to make database management systems more independent of any particular application. It is a mathematical model defined in terms of predicate logic and set theory.

The products that are generally referred to as relational databases (for example, Ingres, Oracle, DB2, and SQL Server) in fact implement a model that is only an approximation to the mathematical model defined by Codd. The data structures in these products are tables, rather than relations: the main differences being that tables can contain duplicate rows, and that the rows (and columns) can be treated as being ordered. The same criticism applies to the SQL language which is the primary interface to these products. There has been considerable controversy, mainly due to Codd himself, as to whether it is correct to describe SQL implementations as "relational": but the fact is that the world does so, and the following description uses the term in its popular sense.

A relational database contains multiple tables, each similar to the one in the "flat" database model. Relationships between tables are not defined explicitly; instead, keys are used to match up rows of data in different tables. A key is a collection of one or more columns in one table whose values match corresponding columns in other tables: for example, an Employee table may contain a column named Location which contains a value that matches the key of a Location table. Any column can be a key, or multiple columns can be grouped together into a single key. It is not necessary to define all the keys in advance; a column can be used as a key even if it was not originally intended to be one.

A key that can be used to uniquely identify a row in a table is called a unique key. Typically one of the unique keys is the preferred way to refer to row; this is defined as the table's primary key.

A key that has an external, real-world meaning (such as a person's name, a book's ISBN, or a car's serial number), is sometimes called a "natural" key. If no natural key is suitable (think of the many people named Brown), an arbitrary key can be assigned (such as by giving employees ID numbers). In practice, most databases have both generated and natural keys, because generated keys can be used internally to create links between rows that cannot break, while natural keys can be used, less reliably, for searches and for integration with other databases. (For example, records in two independently developed databases could be matched up by social security number, except when the social security numbers are incorrect, missing, or have changed.)

Relational operations

Users (or programs) request data from a relational database by sending it a query that is written in a special language, usually a dialect of SQL. Although SQL was originally intended for end-users, it is much more common for SQL queries to be embedded into software that provides an easier user interface. (Many web sites — including MediaWiki which is the engine that runs Wikipedia — perform SQL queries when generating pages.)

In response to a query, the database returns a result set, which is just a list of rows containing the answers. The simplest query is just to return all the rows from a table, but more often, the rows are filtered in some way to return just the answer wanted.

Often, data from multiple tables gets combined into one, by doing a join. Conceptually, this is done by taking all possible combinations of rows (the "cross-product"), and then filtering out everything except the answer. In practice, relational database management systems rewrite ("optimize") queries to perform faster, using a variety of techniques.

The flexibility of relational databases allows programmers to write queries that were not anticipated by the database designers. As a result, relational databases can be used by multiple applications in ways the original designers did not foresee, which is especially important for databases that might be used for decades. This has made the idea and implementation of relational databases very popular with businesses.

Dimensional model

The dimensional model is a specialized adaptation of the relational model used to represent data in data warehouses in a way that data can be easily summarized using OLAP queries. In the dimensional model, a database consists of a single large table of facts that are described using dimensions and measures. A dimension provides the context of a fact (such as who participated, when and where it happened, and its type) and is used in queries to group related facts together. Dimensions tend to be discrete and are often hierarchical; for example, the location might include the building, state, and country. A measure is a quantity describing the fact, such as revenue. It's important that measures can be meaningfully aggregated - for example, the revenue from different locations can be added together.

In an OLAP query, dimensions are chosen and the facts are grouped and added together to create a summary.

The dimensional model is often implemented on top of the relational model using a star schema, consisting of one table containing the facts and surrounding tables containing the dimensions. Particularly complicated dimensions might be represented using multiple tables, resulting in a snowflake schema.

A data warehouse can contain multiple star schemas that share dimension tables, allowing them to be used together. Coming up with a standard set of dimensions is an important part of dimensional modeling.

Object database models

In recent years, the object-oriented paradigm has been applied to database technology, creating a new programming model known as object databases. These databases attempt to bring the database world and the application programming world closer together, in particular by ensuring that the database uses the same type system as the application program. This aims to avoid the overhead (sometimes referred to as the impedance mismatch) of converting information between its representation in the database (for example as rows in tables) and its representation in the application program (typically as objects). At the same time object databases attempt to introduce the key ideas of object programming, such as encapsulation and polymorphism, into the world of databases.

A variety of ways have been tried for storing objects in a database. Some products have approached the problem from the application programming end, by making the objects manipulated by the program persistent. This also typically requires the addition of some kind of query language, since conventional programming languages do not have the ability to find objects based on their information content. Others have attacked the problem from the database end, by defining an object-oriented data model for the database, and defining a database programming language that allows full programming capabalities as well as traditional query facilities.

Object databases suffered because of a lack of standardization: although standards were defined by ODMG, they were never implemented well enough to ensure interoperability between products. Nevertheless, they have been used successfully in many applications: usually specialized applications such as engineering databases or molecular biology databases rather than mainstream commercial data processing. However, object database ideas were picked up by the relational vendors and influenced extensions made to these products and indeed to the SQL language.

Database Internals

Indexing

All of these kinds of database can take advantage of indexing to increase their speed, and this technology has advanced tremendously since its early uses in the 1960s and 1970s. The most common kind of index is a sorted list of the contents of some particular table column, with pointers to the row associated with the value. An index allows a set of table rows matching some criterion to be located quickly. Various methods of indexing are commonly used; B-trees, hashes, and linked lists are all common indexing techniques.

Relational DBMSs have the advantage that indices can be created or dropped without changing existing applications, the application which indices to use. The database chooses between many different strategies based on which one it estimates will run the fastest.

Relational DBMSs utilize many different algorithms to compute the result of an SQL statement. The RDBMs will produce a plan of how to execute the query, which is generated by analysing the run times of the different algorithms and selecting the quickest. Some of the key algorithms that deal with joins are Nested Loops Join, Sort-Merge Join and [[Hash Jo

Transactions and concurrency

In addition to their data model, most practical databases ("transactional databases") attempt to enforce a database transaction model that has desirable data integrity properties. Ideally, the database software should enforce the ACID rules, summarized here:

• Atomicity - Either all the tasks in a transaction must be done, or none of them. The transaction must be completed, or else it must be undone (rolled back).

• Consistency - Every transaction must preserve the integrity constraints -- the declared consistency rules -- of the database. It cannot place the data in a contradictory state.

• Isolation - Two simultaneous transactions cannot interfere with one another. Intermediate results within a transaction are not visible to other transactions.

• Durability - Completed transactions cannot be aborted later or their results discarded. They must persist through (for instance) restarts of the DBMS after crashes.

In practice, many DBMS's allow most of these rules to be selectively relaxed for better performance.

Concurrency control is a method used to ensure that transactions are executed in a safe manner and follow the ACID rules. The DBMS must be able to ensure that only serializable, recoverable schedules are allowed, and that no actions of committed transactions are lost while undoing aborted transactions.

Replication

Replication of databases is closely related to transactions. If a database can log its individual actions, it is possible to create a duplicate of the data in realtime. The duplicate can be used to improve Performance or Availability of the whole database system. Common replication concepts include:

• Master/Slave Replication: All write requests are performed on the master and then replicated to the slaves

• Quorum: The result of Read and Write requests is calculated by quering a "majority" of replicas.

• Multimaster: Two or more replicas sync each other via a transaction identifier.

Applications of databases

Databases are used in many applications, spanning virtually the entire range of computer software. Databases are the preferred method of storage for large multiuser applications, where coordination between many users is needed. Even individual users find them convenient, though, and many electronic mail programs and personal organizers are based on standard database technology. Software database drivers are available for most database platforms so that application software can use a common application programming interface (API) to retrieve the information stored in a database. Two commonly used database APIs are JDBC and ODBC.

*

Data warehouse

A data warehouse is, primarily, a record of an enterprise's past transactional and operational information, stored in a database designed to favor efficient data analysis and reporting (especially OLAP). Data warehousing is not meant for current "live" data.

Data warehouses often hold large amounts of information which are sometimes subdivided into smaller logical units called dependent data marts.

Usually, two basic ideas guide the creation of a data warehouse:

• Integration of data from distributed and differently structured databases, which facilitates a global overview and comprehensive analysis in the data warehouse.

• Separation of data used in daily operations from data used in the data warehouse for purposes of reporting, decision support, analysis and controlling.

Periodically, one imports data from enterprise resource planning (ERP) systems and other related business software systems into the data warehouse for further processing. It is common practice to "stage" data prior to merging it into a data warehouse. In this sense, to "stage data" means to queue it for preprocessing, usually with an ETL tool. The preprocessing program reads the staged data (often a business's primary OLTP databases), performs qualitative preprocessing or filtering (including denormalization, if deemed necessary), and writes it into the warehouse.

Business Intelligence reports (e.g., MI reports) may then be generated from the data written to the warehouse. In this way the data warehouse supplies the data for and supports the business intelligence tools that an organization might use.

Dimensions and Measures

A data warehouse is created by analyzing ways to categorize data using dimension (data warehouse)s and ways to summarize data using measure (data warehouse)s. Dimensions can be used to filter data by excluding results or by displaying data in different cells of a presentation. Measures are used to create averages and totals using precomputed aggregates.

*

Star schema

The star schema (sometimes referenced as star join schema) is the simplest data warehouse schema, consisting of a single "fact table" with a compound primary key, with one segment for each "dimension" and with additional columns of additive, numeric facts.

The star schema makes multi-dimensional database (MDDB) functionality possible using a traditional relational database. Because relational databases are the most common data management system in organizations today, implementing multi-dimensional views of data using a relational database is very appealing. Even if you are using a specific MDDB solution, its sources likely are relational databases. Another reason for using star schema is its ease of understanding. Fact tables in star schema are mostly in third normal form (3NF), but dimensional tables in de-normalized second normal form (2NF). If you want to normalize dimensional tables, they look like snowflakes (see snowflake schema) and the same problems of relational databases arise - you need complex queries and business users cannot easily understand the meaning of data. Although query performance may be improved by advanced DBMS technology and hardware, highly normalized tables make reporting difficult and applications complex.

*

Snowflake schema

The snowflake schema is a more complex data warehouse model than a star schema, and is a type of star schema. It is called a snowflake schema because the diagram of the schema resembles a snowflake.

Snowflake schemas normalize dimensions to eliminate redundancy. That is, the dimension data has been grouped into multiple tables instead of one large table. For example, a product dimension table in a star schema might be normalized into a products table, a product_category table, and a product_manufacturer table in a snowflake schema. While this saves space, it increases the number of dimension tables and requires more foreign key joins. The result is more complex queries and reduced query performance.

*

Dimension table

A dimension table is a data warehousing concept. It is one of the set of companion tables to a fact table.

The fact table contains business facts or measures and foreign keys which refer to candidate keys (normally primary keys) in the dimension tables.

The dimension tables contain attributes or (fields) used to constrain and group data when performing data warehousing queries.

Over time, the attributes of a given row in a dimension table may change. For example, the shipping address for a company may change. Kimball refers to this phenomena as Slowly Changing Dimensions. Strategies for dealing with this kind of change are divided into three categories:

• Type One - Simply overwrite the old value(s).
• Type Two - Add a new row containing the new value(s), and distinguish between the rows using Tuple-versioning techniques.
• Type Three - Add a new attribute to the existing row.

*

Foreign key

A foreign key (FK) is a field or group of fields in a database record that point to a key field or group of fields forming a key of another database record in some (usually different) table. Usually a foreign key in one table refers to the primary key (PK) of another table. This way references can be made to link information together and it is an essential part of database normalization. Foreign keys that refer back to the same table are called recursive foreign keys.

For example, a person sending an e-mail need not include the entire text of a book in the e-mail. Instead, they can include the ISBN of the book, and interested persons can then use the number to get information about the book - or even the book itself. The ISBN is the primary-key of the book, and it is used as a foreign-key in the e-mail.

Note that using a foreign key often assumes its existence as a primary key somewhere else. Improper foreign key/primary key relationships are the source of many database problems. Further, a foreign key constraint is where data that serves as a foreign key in one database record cannot be removed as there is still data in another record that would need to be deleted.

*

Primary key

In database design, a primary key is a value that can be used to identify a unique row in a table. Attributes are associated with it. Examples are names in a telephone book (to look up telephone numbers) and words in a dictionary (to look up definitions).
In the relational model of data, a primary key is a candidate key chosen as the main method of uniquely identifying a tuple in a relation. Practical telephone books and dictionaries can not use names or words or Dewey Decimal System numbers as candidate keys because they do not uniquely identify telephone numbers or words.

In some design situations it is impossible to find a natural key that uniquely identifies a tuple in a relation. A surrogate key can be used as the primary key. In other situations there may be more than one candidate key for a relation, and no candidate key is obviously preferred. A surrogate key may be used as the primary key to avoid giving one candidate key artificial primacy over the others.

In addition to the requirement that the primary key be a candidate key, there are several other factors which may make a particular choice of key better than others for a given relation:

• The primary key should be immutable, meaning that its value should not be changed during the course of normal operations of the database. (Recall that a primary key is the means of uniquely identifying a tuple, and that identity, by definition, never changes.) This avoids the problem of dangling references or orphan records created by other relations referring to a tuple whose primary key has changed. If the primary key is immutable, this can never happen.

• Candidate keys have the advantage of uniquely identifying a row without requiring additional storage space. However, it is exceedingly rare that a candidate key remains immutable throughout the life of the database, and changing keys may necessitate significant application reprogramming. As a result, a surrogate key taken from a randomly generated 32-bit integer and validated for redundancy works exceedingly well. They require only four bytes of storage space each, permit just over four billion combinations, and, since they have no meaning in and of themselves, need never be changed.

• The primary key should generally be short to minimize the amount of data that needs to be stored by other relations that reference it. If no single domain of the relvar qualifies as a key, a compound key may be appropriate. Physical constraints on the implementation may suggest adding a shorter surrogate key, in order to reduce the redundant storage used. (However, this is a physical design consideration, and some database management systems may be better than others in this regard.)

*

Microsoft SQL Server

Microsoft SQL Server is a relational database management system produced by Microsoft. It supports Microsoft's version of Structured Query Language (SQL), the most common database language. It is commonly used by businesses for small- to medium-sized databases, and - in the past five years - large enterprise databases. Microsoft SQL Server competes with other relational database products for this market segment.

Contents

• 1 History
• 2 Versions for Windows
• 3 Description
• 4 Variants
• 5 Sub Products
• 6 See also
• 7 Further reading
• 8 External links
History

The code base for Microsoft SQL Server originated in Sybase SQL Server, and was Microsoft's entry to the enterprise-level database market, competing against Oracle, IBM, and Sybase. Microsoft, Sybase and Ashton-Tate teamed up to create and market the first version named SQL Server 4.2 for OS/2 (about 1989) which was essentially the same as Sybase SQL Server 4.0 on Unix, VMS, etc. Microsoft SQL Server for NT v4.2 was shipped around 1992 (available bundled with Microsoft OS/2 version 1.3) and was a simple port from OS/2 to NT. Microsoft SQL Server v6.0 was the first version of SQL Server that was architected for NT and did not include any direction from Sybase.

About the time Windows NT was coming out, Sybase and Microsoft parted ways and pursued their own design and marketing schemes. Microsoft negotiated exclusive rights to all versions of SQL Server written for Microsoft operating systems. Later, Sybase changed the name of its product to Adaptive Server Enterprise to avoid confusion with Microsoft SQL Server. Until 1994 Microsoft's SQL Server carried three Sybase copyright notices as an indication of its origin.

Several revisions have been done independently since with improvements for SQL Server. SQL Server 7.0 was the first true GUI based database server, and a variant of SQL Server 2000 was the first commercial database for the Intel IA64 architecture. During this time there was a rivalry between Microsoft and Oracle's servers for winning the market over enterprise customers.

The current version, Microsoft SQL Server 2005, was released in November of 2005. The launch took place alongside Visual Studio 2005 and BizTalk Server 2006. The SQL Server 2005 Express edition is currently available for free download.

Versions for Windows

• 1993 - SQL Server 4.21 for Windows NT
• 1995 - SQL Server 6.0, codenamed SQL95
• 1996 - SQL Server 6.5, codenamed Hydra
• 1999 - SQL Server 7.0, codenamed Sphinx
• 1999 - SQL Server 7.0 OLAP, codenamed Plato
• 2000 - SQL Server 2000 32-bit, codenamed Shiloh
• 2003 - SQL Server 2000 64-bit, codenamed Liberty
• 2005 - SQL Server 2005, codenamed Yukon

Description

MS SQL Server uses a variant of SQL called T-SQL, or Transact-SQL, an implementation of SQL-92 (the ISO standard for SQL, certified in 1992) with some extensions. T-SQL mainly adds additional syntax for use in stored procedures, and affects the syntax of transactions support. (Note that SQL standards require (ACID) Atomic, Consistent, Isolated, Durable transactions.) MS SQL Server and Sybase/ASE both communicate over networks using an application-level protocol called Tabular Data Stream (TDS). The TDS protocol has also been implemented by the FreeTDS project ([1]) in order to allow more kinds of client applications to communicate with MS SQL Server and Sybase databases. MS SQL Server also supports Open Database Connectivity (ODBC).

Variants

A stripped-down version of Microsoft SQL Server known as MSDE (Microsoft SQL Server Desktop Engine) is distributed with products such as Visual Studio, Visual FoxPro, Microsoft Access, MS Web Matrix, and other products. MSDE has some restrictions: a limit of 2 GB databases, and it comes with no GUI tools to administer it. It also has a workload governor which reduces its speed once you exceed 8 concurrent workloads on the engine.

Microsoft recently released the successor to MSDE, dubbed SQL Server Express. Similar to MSDE, SQL Express includes all the core functionality of SQL Server and the workload governor was removed, but places restrictions on the scale of databases. It will only utilize a single CPU, 1 GB of RAM, and imposes a maximum size of 4 GB per database (log's size doesn't count). Microsoft provides a separate download ("feature pack") for the Express edition that includes less feature rich version of Reporting Services. SQL Express also doesn't include enterprise features such as Analysis Services, Data Transformation Services, and Notification Services. Unlike MSDE, SQL Express includes a management console, called SQL Server Management Studio Express.

Sub Products

• SQL Server Integration Services
• SQL Server Analysis Services
• SQL Server Reporting Services
• SQL Server Notification Services

*

SQL

SQL (commonly expanded to Structured Query Language - see History for the term's derivation) is the most popular computer language used to create, modify and retrieve data from relational database management systems. The language has evolved beyond its original purpose to support object-relational database management systems. It is an ANSI/ISO standard.

Contents

• 1 History
• 2 Scope
• 3 SQL keywords
o 3.1 Data retrieval
o 3.2 Data manipulation
o 3.3 Data transaction
o 3.4 Data definition
o 3.5 Data control
o 3.6 Other
• 4 Database systems using SQL
• 5 Criticisms of SQL
• 6 Alternatives to SQL
• 7 External links
o 7.1 Tutorials
• 8 References
History

A seminal paper, "A Relational Model of Data for Large Shared Data Banks", by Dr. Edgar F. Codd, was published in June, 1970 in the Association for Computing Machinery (ACM) journal, Communications of the ACM. Codd's model became widely accepted as the definitive model for relational database management systems (RDBMS).

During the 1970s, a group at IBM's San Jose research center developed a database system "System R" based upon Codd's model. Structured English Query Language ("SEQUEL") was designed to manipulate and retrieve data stored in System R. The acronym SEQUEL was later condensed to SQL due to a trademark dispute (the word 'SEQUEL' was held as a trademark by the Hawker-Siddeley aircraft company of the UK). It should be noted that although SQL was influenced by Dr. Codd's work, it was not designed by Dr. Codd himself; the SEQUEL language design was due to Donald D. Chamberlin and Raymond F. Boyce at IBM.[1], and their concepts were published to increase interest in SQL.

The first non-commercial non-SQL relational database was developed in 1974.(Ingres from U.C. Berkeley.)

In 1978, methodical testing commenced at customer test sites. Demonstrating both the usefulness and practicality of the system, this testing proved to be a success for IBM. As a result, IBM began to develop commercial products that implemented SQL based on their System R prototype, including the System/38 (announced in 1978 and commercially available in August 1979), SQL/DS (introduced in 1981), and DB2 (in 1983).[2]
At the same time Relational Software, Inc. (now Oracle Corporation) saw the potential of the concepts described by Chamberlin and Boyce and developed their own version of a RDBMS for the Navy, CIA and others; and in the summer of 1979, Relational Software, Inc. introduced Oracle V2 (Version2) for VAX computers, as the first commercially available implementation of SQL. Oracle is often incorrectly cited as beating IBM to market by two years, but in a great public relations coup, beat IBM's release of the System/38 by only a few weeks. Considerable public interest then developed; soon many other vendors developed versions, and Oracle's future was ensured.

It is often suggested that IBM was slow to develop SQL and relational products, possibly because it wasn't available initially on the mainframe and Unix environments, and that they were afraid it would cut into lucrative sales of their IMS database product, which used navigational database models instead of relational. But at the same time as Oracle was being developed, IBM was developing the System/38, which was intended to be the first relational database system, and was thought by some at the time, because of its advanced design and capabilities, that it might have become a possible replacement for the mainframe and Unix systems.

SQL was adopted as a standard by the ANSI (American National Standards Institute) in 1986 and ISO (International Organization for Standardization) in 1987. ANSI has declared that the official pronunciation for SQL is /ɛs kjuː ɛl/, although many English-speaking database professionals still pronounce it as sequel. Another widespread misconception is that "SQL" is an initialism that stands for "Structured Query Language" — this is not the case.

The SQL standard has gone through a number of revisions.

Scope

The SQL standard is not freely available. SQL:2003 may be purchased from ISO or ANSI. A late draft is available as a zip archive from Whitemarsh Information Systems Corporation. The zip archive contains a number of PDF files that define the parts of the SQL:2003 specification.

Although SQL is defined by both ANSI and ISO, there are many extensions to and variations on the version of the language defined by these standards bodies. Many of these extensions are of a proprietary nature, such as Oracle Corporation's PL/SQL or Sybase, IBM's SQL PL(SQL Procedural Language) and Microsoft's Transact-SQL. It is also not uncommon for commercial implementations to omit support for basic features of the standard, such as the DATE or TIME data types, preferring some variant of their own. As a result, in contrast to ANSI C or ANSI Fortran, which can usually be ported from platform to platform without major structural changes, SQL code can rarely be ported between database systems without major modifications. There are several reasons for this lack of portability between database systems:

• the complexity and size of the SQL standard means that most databases do not implement the entire standard.

• the standard does not specify database behavior in several important areas (e.g. indexes), leaving it up to implementations of the standard to decide how to behave.

• the SQL standard precisely specifies the syntax that a conformant database system must implement. However, the standard's specification of the semantics of language constructs is less well-defined, leading to areas of ambiguity.

• many database vendors have large existing customer bases; where the SQL standard conflicts with the prior behavior of the vendor's database, the vendor may be unwilling to break backward compatibility.

• some believe the lack of compatibility between database systems is intentional in order to ensure vendor lock-in.

SQL is designed for a specific, limited purpose — querying data contained in a relational database. As such, it is a set-based, declarative computer language rather than an imperative language such as C or BASIC which, being programming languages, are designed to solve a much broader set of problems. Language extensions such as PL/SQL are designed to address this by turning SQL into a full-fledged programming language while maintaining the advantages of SQL. Another approach is to allow programming language code to be embedded in and interact with the database. For example, Oracle and others include Java in the database, while PostgreSQL allows functions to be written in a wide variety of languages, including Perl, Tcl, and C.

One joke about SQL is that "SQL is neither structured, nor is it limited to queries, nor is it a language." This is founded on the notion that pure SQL is not a classic programming language since it is not Turing-complete. On the other hand, however, it is a programming language because it has a grammar, syntax, and programmatic purpose and intent. The joke recalls Voltaire's remark that the Holy Roman Empire was "neither holy, nor Roman, nor an empire."

SQL contrasts with the more powerful database-oriented fourth-generation programming languages such as Focus or SAS, however, in its relative functional simplicity and simpler command set. This greatly reduces the degree of difficulty involved in maintaining SQL source code, but it also makes programming such questions as 'Who had the top ten scores?' more difficult, leading to the development of procedural extensions, discussed above. However, it also makes it possible for SQL source code to be produced (and optimized) by software, leading to the development of a number of natural language database query languages, as well as 'drag and drop' database programming packages with 'object oriented' interfaces. Often these allow the resultant SQL source code to be examined, for educational purposes, further enhancement, or to be used in a different environment.

SQL keywords

SQL keywords fall into several groups.

Data retrieval

The most frequently used operation in transactional databases is the data retrieval operation.

• SELECT is used to retrieve zero or more rows from one or more tables in a database. In most applications, SELECT is the most commonly used DML command. In specifying a SELECT query, the user specifies a description of the desired result set, but they do not specify what physical operations must be executed to produce that result set. Translating the query into an efficient query plan is left to the database system, more specifically to the query optimizer.

o Commonly available keywords related to SELECT include:

 FROM is used to indicate which tables the data is to be taken from, as well as how the tables join to each other.

 WHERE is used to identify which rows to be retrieved, or applied to GROUP BY.

 GROUP BY is used to combine rows with related values into elements of a smaller set of rows.

 HAVING is used to identify which of the "combined rows" (combined rows are produced when the query has a GROUP BY keyword or when the SELECT part contains aggregates), are to be retrieved.

 ORDER BY is used to identify which columns are used to sort the resulting data.

Example:

SELECT * FROM my_table WHERE id > 10;

Data manipulation

First there are the standard Data Manipulation Language (DML) elements. DML is the subset of the language used to add, update and delete data.

• INSERT is used to add zero or more rows (formally tuples) to an existing table.
• UPDATE is used to modify the values of a set of existing table rows.
• MERGE is used to combine the data of multiple tables. It is something of a combination of the INSERT and UPDATE elements. It is defined in the SQL:2003 standard; prior to that, some databases provided similar functionality via different syntax, sometimes called an "upsert".
• DELETE deletes all data from a table (non-standard, but common SQL command).
• TRUNCATE removes zero or more existing rows from a table.

Example:
INSERT INTO my_table (field1, field2, field3) VALUES ('test', 'N', NULL);
UPDATE my_table SET field1 = 'updated value' WHERE field2 = 'N';
DELETE FROM my_table WHERE field2 = 'N';

Data transaction

Transaction, if available, can be used to wrap around the DML operations.

• BEGIN WORK (or START TRANSACTION, depending on SQL dialect) can be used to mark the start of a database transaction, which either completes completely or not at all.

• COMMIT causes all data changes in a transaction to be made permanent.

• ROLLBACK causes all data changes since the last COMMIT or ROLLBACK to be discarded, so that the state of the data is "rolled back" to the way it was prior to those changes being requested.

COMMIT and ROLLBACK interact with areas such as transaction control and locking. Strictly, both terminate any open transaction and release any locks held on data. In the absence of a BEGIN WORK or similar statement, the semantics of SQL are implementation-dependent.

Example:
UPDATE inventory SET quantity = quantity - 3 WHERE item = 'pants';

Data definition

The second group of keywords is the Data Definition Language (DDL). DDL allows the user to define new tables and associated elements. Most commercial SQL databases have proprietary extensions in their DDL, which allow control over nonstandard features of the database system.

The most basic items of DDL are the CREATE and DROP commands.

• CREATE causes an object (a table, for example) to be created within the database.

• DROP causes an existing object within the database to be deleted, usually irretrievably.

Some database systems also have an ALTER command, which permits the user to modify an existing object in various ways -- for example, adding a column to an existing table.

Example:
CREATE TABLE my_table
(
my_field1 INT UNSIGNED,
my_field2 VARCHAR(50),
my_field3 DATE NOT NULL,
PRIMARY KEY (my_field1, my_field2)
)

Data control

The third group of SQL keywords is the Data Control Language (DCL). DCL handles the authorisation aspects of data and permits the user to control who has access to see or manipulate data within the database.

Its two main keywords are:

• GRANT — authorises a user to perform an operation or a set of operations e.g. grant all privileges to user identified by passwd?

• REVOKE — removes or restricts the capability of a user to perform an operation or a set of operations.

Example:
GRANT ALL ON my_db TO someone@localhost IDENTIFIED BY 'somepass'

Other

ANSI-standard SQL supports -- as a single line comment identifier (some extensions also support curly brackets for multi-line comments).

Example:
SELECT * FROM inventory -- Retrieve everything from inventory table

Database systems using SQL

• List of relational database management systems
• List of object-relational database management systems

Criticisms of SQL

Technically, SQL is a declarative computer language for use with "relational databases". Theorists note that many of the original SQL features were inspired by, but in violation of, tuple calculus. Recent extensions to SQL achieved relational completeness, but have worsened the violations, as documented in The Third Manifesto.

In addition, there are also some criticisms about the practical use of SQL:
• The language syntax is rather complex (sometimes called "COBOL-like").
• It does not provide a standard way, or at least a commonly-supported way, to split large commands into multiple smaller ones that reference each other by name. This tends to result in "run-on SQL sentences" and may force one into a deep hierarchical nesting when a graph-like (reference-by-name) approach may be more appropriate and better repetition-factoring.
• Implementations are inconsistent and, at times, incompatible between vendors.
• It is at times too difficult a syntax for DBAs (DataBase Administrators) to extend.
• Over-reliance on "NULLs", which some consider a flawed or over-used concept.
• For larger statements, it is often difficult to factor repeated patterns and expressions into one or fewer places to avoid repetition and avoid having to make the same change to different places in a given statement.
• Unexplained difference between value-to-column assignments in UPDATE and INSERT syntax.

Alternatives to SQL

A distinction should be made between alternatives to relational and alternatives to SQL. The list below are proposed alternatives to SQL, but are still (allegedly) relational. See navigational database for alternatives to relational.

*

Cognos

Cognos TSX: CSN NASDAQ: COGN is an Ottawa, Ontario based company which makes business intelligence (BI) and performance planning software. Founded in 1969, Cognos employs over 3,300 people and serves more than 23,000 customers in over 135 countries. Cognos was originally known as Quasar but changed its name in 1982. It has since acquired NoticeCast Software, Adaytum, and Frango.

Cognos' Metrics Manager won the Intelligent Enterprise Award for the Best Business Performance Monitoring Solution 2004. Metrics Manager, which is a next-generation scorecarding technology, is aimed at corporate performance management. It is tightly integrated with its own Business Intelligence series of products which consist of tools like ReportNet, PowerPlay and others, and also with the Enterprise Planning Solutions.

With its latest product Cognos 8 BI, launched in September 2005, Cognos unites its former products ReportNet, PowerPlay, Metrics Manager, Noticecast, and Decision Stream.