IDENT_CURRENT,@@IDENTITY and SCOPE_IDENTITY

IDENT_CURRENT returns the last identity value generated for a specific table in any session and any scope.

@@IDENTITY returns the last identity value generated for any table in the current session, across all scopes.

SCOPE_IDENTITY returns the last identity value generated for any table in the current session and the current scope.

An INSERT trigger is defined on T1. When a row is inserted to T1, the trigger fires and inserts a row in T2. This scenario illustrates two scopes: the insert on T1, and the insert on T2 by the trigger.

Assuming that both T1 and T2 have identity columns, @@IDENTITY and SCOPE_IDENTITY will return different values at the end of an INSERT statement on T1. @@IDENTITY will return the last identity column value inserted across any scope in the current session. This is the value inserted in T2. SCOPE_IDENTITY() will return the IDENTITY value inserted in T1. This was the last insert that occurred in the same scope. The SCOPE_IDENTITY() function will return the null value if the function is invoked before any INSERT statements into an identity column occur in the scope.

Failed statements and transactions can change the current identity for a table and create gaps in the identity column values. The identity value is never rolled back even though the transaction that tried to insert the value into the table is not committed. For example, if an INSERT statement fails because of an IGNORE_DUP_KEY violation, the current identity value for the table is still incremented.

Be cautious about using IDENT_CURRENT to predict the next generated identity value. The actual generated value may be different from IDENT_CURRENT plus IDENT_INCR because of insertions performed by other sessions.


CREATE TABLE T1 (
ID int IDENTITY(1,1)PRIMARY KEY,
Name varchar(20) NOT NULL
)

INSERT T1
VALUES ('A')
INSERT T1
VALUES ('B')
INSERT T1
VALUES ('C')

CREATE TABLE T2 (
T2ID int IDENTITY(1,1)PRIMARY KEY,
ID int
)

CREATE TRIGGER Trig_T1
ON T1
FOR INSERT AS
BEGIN
INSERT into T2
select ID from inserted
END

FIRE the trigger and determine what identity values you obtain with the @@IDENTITY,IDENT_CURRENT and SCOPE_IDENTITY functions.*/
INSERT T1 VALUES ('Rosalie')

SELECT SCOPE_IDENTITY() AS [SCOPE_IDENTITY]
GO
SELECT @@IDENTITY AS [@@IDENTITY]
GO
SELECT IDENT_CURRENT ('T1') AS T1_Identity;
GO
SELECT IDENT_CURRENT ('T2') AS T2_Identity;

Hive - Data Warehousing & Analytics on Hadoop

Hive is an open source, peta-byte scale date warehousing framework based on Hadoop that was developed by the Data Infrastructure Team at Facebook that facilitates easy data summarization, ad-hoc queries, and the analysis of large datasets stored in Hadoop compatible file systems. Hive provides a mechanism to project structure onto this data and query the data using a SQL-like language called HiveQL. At the same time this language also allows traditional map/reduce programmers to plug in their custom mappers and reducers when it is inconvenient or inefficient to express this logic in HiveQL.


Hive architecture:
Hive organizes data in tables and partitions. A good partitioning scheme allows Hive to prune data while processing a query and that has a direct impact on how fast a result of the query can be produced. Behind the scenes, Hive stores partitions and tables into directories in Hadoop File System (HDFS).


Hive comprises of the following major components:

-Metastore: To store the meta data.
-Query compiler and execution engine: To convert SQL queries to a sequence of map/reduce jobs that are then executed on Hadoop.
- SerDe and ObjectInspectors: Programmable interfaces and implementations of common data formats and types.
-UDF and UDAF: Programmable interfaces and implementations for user defined functions (scalar and aggregate functions).
-Clients: Command line client similar to Mysql command line and a web UI.

Data Flow into Hadoop Cloud:




For more Information:
http://www.vldb.org/pvldb/2/vldb09-938.pdf

Business Analytics: Good Data and Poor Data

Today, most organizations use data in two ways:
Transactional/Operational use (“running the business”), and Analytic use (“improving the business”).

Good business demands GoodData.
Business analytics can provide amazing insights into how an organization is operating -- in hindsight, with insight and with foresight. But one must be attentive to the quality of the data being analyzed and put first things first. Step one is to check the validity of the data, ensure its quality and completeness. Step two is to ask those key questions that help provide the information needed to make informed decisions. Internal auditors, armed with analytic technologies of their own, can provide a huge amount of assistance in determining data quality and addressing the risk of drawing incorrect conclusions based on bad data.

It is of great value to any enterprise risk management program to incorporate a program that includes processes for assessing, measuring, reporting, reacting to, and controlling different aspects of risks associated with poor data quality.

Data quality is a critical prerequisite to effective business analytics. Poor data quality jeopardizes the performance and efficiency of operational systems. It undermines the value of analytic and business intelligence systems upon which
organizations rely to make key decisions. Decisions based on poor data can result in direct financial loss.

Business leaders need to pay serious attention to the accuracy, quality and reliability of their data. The most obvious cause for poor data quality is data entry. If an organization has no standards or IT controls for how data is entered into a system, the data will quickly reflect their lack. It is in this way that duplicate entries are made in master data.

While Business Intelligence tools can create beautiful and compelling dashboards and graphics, if the data that the tools rely on is of poor quality, their results are
meaningless, or at least potentially badly flawed.
Data cleansing and data quality are important in order to ensure quality results from business intelligence analytics.

You can never fully predict how business users will want to analyze their data, so give them complete freedom to drill down in any direction they choose.Deliver them something good in a week as opposed to something great in six months.Good data is attained by integrating multiple data sources, deriving a ‘single version of the truth,’ and putting that good data (and unstructured content) into a data warehouse where the BA/BI tools can perform their magic. DDD (data-driven decision making) begins and ends with good data.Analytics applications that nicely present dashboards, scorecards, historical trends, predictive analys, and give me actionable insights, can all benefit from good data. Good data begins with data integration, data quality, and a good data warehouse.

A Whole New Mind: Why Right-Brainers Will Rule the Future charts the rise of right-brain thinking in modern economies and describes the six abilities individuals and organizations must master in an outsourced, automated age

Daniel Pink references three prevailing trends pointing towards the future of business and the economy:
1. Abundance (consumers have too many choices, nothing is scarce)
2. Asia (everything that can be outsourced, is)
3. Automation (computerization, robots, technology, processes).

This brings up three crucial questions for the success of any business:

1. Can a computer do it faster?
2. Is what I'm offering in demand in an age of abundance?
3. Can someone overseas do it cheaper?

When these questions are present, creativity becomes the competitive difference that can differentiate commodities.
The future belongs to a different kind of person with a different kind of mind: artists, inventors, storytellers-creative and holistic "right-brain" thinkers whose abilities mark the fault line between who gets ahead and who doesn't. Drawing on research from around the world, Pink outlines the six fundamentally human abilities that are absolute essentials for professional success and personal fulfillment-and reveals how to master them

1. Design - Moving beyond function to engage the sense.
2. Story - Narrative added to products and services - not just argument. Best of the six senses.
3. Symphony - Adding invention and big picture thinking (not just detail focus).
4. Empathy - Going beyond logic and engaging emotion and intuition.
5. Play - Bringing humor and light-heartedness to business and products.
6. Meaning - the purpose is the journey, give meaning to life from inside yourself.


The book A Whole New Mind: Why Right-Brainers Will Rule the Future by Daniel H. Pink, left- directed thinking leads to a analytical person, whereas right-directed thinking leads to creative person.

Left-directed thinking:
America is currently organized around a cadre of accountants, doctors, engineers, executives and lawyers. These "knowledge workers" excel at the ability to acquire and marry facts to data, and these abilities are typically accrued through a series of standardized tests such as the PSAT, SAT, GMAT, LSAT and MCAT.

Right directed thinking:
Design, empathy, play, and other "soft" aptitudes have become the focal point for individuals and companies that want to stand out above the others in a crowded marketplace. Look no further than Apple's design-triumph, the physically appealing and emotionally compelling iPod, for quick confirmation of this notion!

Dan advocates to leave the left directed thinking to Asia as it is available in abundance and cheap, and direct educational system towards right directed thinking to turn out creative people like Steve Jobs and Bill Gates.

Oracle Databases with SQL Server Reporting Services

Internally, SSRS uses the Oracle Data Provider for .NET (ODP.NET) to interact with Oracle databases. Hence, the ODP.NET's restrictions apply to SSRS as well.


Unlike SQL Server, Oracle returns resultant rows in a cursor. To use a stored procedure for data retrieval within SSRS, consider the following things:

1.The rows must be returned with an OUT REF CURSOR.
2.Only one OUT REF CURSOR can be returned from a stored procedure. In case of multiple OUT REF CURSORs, SSRS considers only the first one and simply ignores the rest.

Note: ADO.NET provides the capability to interact with multiple OUT CURSORs. However, SSRS abstracts a lot of the required plumbing code and hence is not able to provide this facility.

3.By default, the data source type is set to OLE DB. You should change this to Oracle to use ODP.NET features.
4.Once the fields are retrieved, SSRS automatically creates report parameters that match the stored procedure parameters. You should map these to the appropriate values based on your business logic. You can map these stored procedure parameters to the report parameters in the expression editor.

Once you've taken care of these things, SSRS generates a dataset that can be used to format a report. You can utilize other features, such as manipulation of the dataset fields and report formatting, just as you would with any other data source.

CREATE OR REPLACE PACKAGE CURSPKG AS
TYPE T_CURSOR IS REF CURSOR;
PROCEDURE OPEN_TWO_CURSORS (EMPCURSOR OUT T_CURSOR,
DEPTCURSOR OUT T_CURSOR);
END CURSPKG;

CREATE OR REPLACE PACKAGE BODY CURSPKG AS
PROCEDURE OPEN_TWO_CURSORS (EMPCURSOR OUT T_CURSOR,
DEPTCURSOR OUT T_CURSOR)
IS
BEGIN
OPEN EMPCURSOR FOR SELECT * FROM DEMO.EMPLOYEE;
OPEN DEPTCURSOR FOR SELECT * FROM DEMO.DEPARTMENT;
END OPEN_TWO_CURSORS;
END CURSPKG;


To start, create a new reporting services project in Visual Studio. Use the Report Project option and not the wizard (this will help you understand things better). Next, right-click the reports folder and, from the Add Menu, select "New Item" and add a report (.rdl) to it as "TestOracle.rdl".

Now, add a report file (.rdl) named "TestOracle.rdl" to the report.

Open the rdl file in the Visual Studio editor. On the data tab, select from the dataset dropdown. This should open up the property pages for the connection.

On the Provider tab, select Microsoft OLE DB for Oracle and click "Next." On the Connection tab, enter the Oracle service name as the server name and the username and password. (You can opt for saving the password here.)
When done, test the connection with the test connection button at the bottom and click OK.

This should create a new dataset as DataSet1 in the dataset dropdown. Select the new dataset and click the ellipse button beside it. This brings up the property pages for the Dataset configuration. Click the ellipse besides the data source to verify that the data source type is set to "Oracle." This ensures that SSRS uses the ODP.NET for underlying connectivity. The data source type is normally defaulted to OLE DB.

On the query tab of the dataset property pages, select the Command Type as - Stored Procedure. Compared with ADO.NET, this step is the same as the Command type parameter you would specify while creating a command object for fetching data from a stored procedure. The query string text would contain only the stored procedure name, possibly qualified by its package name in Oracle. Click OK on this screen to fetch the available fields.
The out param type should be Ref Cursor only.
Then put the name of the procedure PackageName.GetReportData in RS. Then ran it in
the RS. CommandType :Stored Proc
RS then automatically listed the params (only 2 according to signature of sp; out param will not be listed). Data Source type is Oracle.

Allocation units in SQL server

In Microsoft SQL Server, the database is basically divided into different allocation units, viz. Index Allocation Map or IAM pages, Global Allocation Map or GAM pages, Shared Global Allocation Map or SGAM, and or Page Free Space or PFS pages.
SQL Server uses the IAM pages to find the extents allocated to the object.
For each extent, SQL Server searches the PFS pages to see if there is a page
with enough free space to hold the row.If SQL has to allocate new space, the GAM and/or SGAM is used. This would
occur AFTER SQL has determined that there is not enough space to insert the
new row on pages already allocated to the object.
All of these allocation units are required to be in healthy condition for the successful functioning of your MS SQL database. However, corruption in any of these allocation units or pages can result in stopping SQL database mounting; thereby, resulting in the inaccessibility of databases elements like tables, views, stored procedures, indexes, triggers, etc.
Various factors can be responsible for such corruption in SQL Server allocation pages. Naming a few of them – wrong system or application shutdown, MS SQL Server damage, Trojan infections or virus attacks on the system, software malfunctioning, human errors, media errors, etc. However, in the above situation, the major possibility of damage is due to the corruption in any of GAM, SGAM, or PFS pages of SQL database Server Meta structure.

For resolving the issue and for regaining access to your SQL database elements again, you can do the following measures:
* If corruption has occurred due to physical damage, then you must replace that system component which is damaged by a new component
* However, if the problem is as a result of some logical damage, then you can run the ‘DBCC CHECKDB' command with correct repair clause
* In case above resolutions fail, try to restore your damaged or deleted SQL database from an updated backup

Data Normalization

Data Normalization
The process of modeling data into relational tables is known as normalization.
Common normal forms: 1NF, 2NF and 3NF.
Data stored in a relational database and accessed with SQL should be normalized to 3NF.
PK: Attribute of set of attributes which uniquely identify a record.

Books Table:
ISBN(PK) Title Authors Publisher
123 Oracle 10g Auth1,Auth2 Pub1,Stree1,City1, State1
124 MS SQL Auth1,Auth3 Pub2,Stree2,City2, State1

Problem with above schema:
1. Insertion anomaly: It is impossible to enter details of authors whose books are not published as there is no ISBN .
2. Deletion anomaly: It is impossible to delete book without losing publisher details.
3. Update anomaly: If some publisher address changes, it is necessary to update every book that publisher has published.
4. Data inconsistency errors due to incorrect entry of publisher address is any of the row.
5. Author value is multi valued, so finding books written by Auth1 need to search all values in Authors column.
6. Storage inefficiency as replication of address of each publisher across rows.


1NF:
Remove the repeating groups, i.e. Author column, pull them out into a separate table, Authors.
Books Table:
ISBN(PK) Title Publisher
123 Oracle 10g Pub1,Stree1,City1, State1
124 MS SQL Pub2,Stree2,City2, State1

Authors:
ISBN Author Name
123 Auth1
123 Auth2
124 Auth1
124 Auth3

Problems solved by 1NF:
1. Insertion anomaly: No need to insert unpublished book in Books table and can insert Author record in Authors table.
2. Query to find no. of books written by Auth1 or find author who written maximum books is now simplified.
2NF:
Remove columns which are not dependent on PK of table. Here publisher address is not dependent on ISBN in books table.
Books Table:
ISBN(PK) Title Publisher
123 Oracle 10g Pub1
124 MS SQL Pub2

Publishers Table:
Publisher (PK) Street City State
Pub1 Stree1 City1 State1
Pub2 Stree2 City2 State1

Problems solved by 2NF:
1. Update anomaly: Need to update only 1 record if publisher details changed.
2. Storage efficiency: Solves problem of storing publisher details many times.
3. Data consistency errors caused by incorrect entry in publisher column.
3NF:
Remove all columns that are interdependent. In Publishers table, street -- > City and City --> State, i.e. street can belongs to only 1 city and 1 city belongs to only 1 state. This can be achieved by adding address code column in Publishers table and have separate table for city, state and street.
Publisher (PK) Address code
Pub1 AC1
Pub2 AC2

Address Table:
Address code (PK) Street City State
AC1 Stree1 City1 State1
AC2 Stree2 City2 State1

Normalized Books database:
Table Primary Key Foreign Key
Books ISBN Publisher
Authors ISBN + Author Name ISBN
Publishers Publisher Address code
Address Address Code