August 12, 2014

Data Migration from SQL Server to SQL Azure

Data Migration from SQL Server to SQL Azure
You can transfer data to Microsoft Azure SQL Database by using the following:
  • SQL Server 2008 Integration Services (SSIS) 
  • The bulk copy utility (BCP.exe)
  • System.Data.SqlClient.SqlBulkCopy class
  • Scripts that use INSERT statements to load data into the database
Microsoft Azure SQL Database does not support:
  • The RESTORE statement.
  • Attaching a database to the Azure SQL Database server.

Azure SQL Database General Guidelines and Limitations

Azure SQL Database General Guidelines and Limitations

Please do following checks when you create database object script using "Generate Script" wizard.

1. Delete all instances of SET ANSI_NULLS ON.

2. SQL Azure hides all hardware-specific access and information. There is no concept of PRIMARY or file groups because disk space is handled by Microsoft, so this option isn’t required.

Delete all instances of ON [PRIMARY].

3. Delete all instance of PAD_INDEX = OFF as well as ALLOW_ROW_LOCKS = ON and ALLOW_PAGE_LOCKS = ON.

4. The NEWSEQUENTIALID() function isn’t supported in SQL Azure because there is no CLR support in SQL Azure, and thus all CLR-based types aren’t supported. The NEWSEQUENTIALID() return value is one of those typesIn the Users table, modify the rowguid column, changing DEFAULT NEWSEQUENTIALID() to NULL.

5. In the stored procedure, remove the ENCRYPTION clause.

6. SQL Azure currently supports only clustered tables. SQL Azure doesn’t support heap tables. A heap table is one without a clustered index.

7.Microsoft Azure SQL Database supports only SQL Server Authentication. Windows Authentication (integrated security) is not supported. Users must provide credentials (login and password) every time they connect to Microsoft Azure SQL Database.

8.Microsoft Azure SQL Database does not support distributed transactions, which are transactions that affect several resources

9.Microsoft Azure SQL Database does not allow setting the collation at the server level.

10.Microsoft Azure SQL Database provides two database editions: Web Edition and Business Edition. Web Edition databases can grow up to a size of 5 GB and Business Edition databases can grow up to a size of 150 GB.

Multiple Ways to Improve SQL Query Performance

Multiple Ways to Improve SQL Query Performance

1. Re-writing SQL Query
Re-writing SQL Query by following best practices for getting the fastest response like  avoid writing a SQL query using multiple joins ( JOIN containing more than 4 tables). ( Pls refer Nested Loop http://avishkarm.blogspot.in/2014/08/what-is-nested-loops-join.html)
Try to remove cursors from the query and use set-based query.
Remove non-correlated scalar sub query as a separate query instead of part of the main query and store the output in a variable.
Avoid Multi-statement TVFs are more costly than inline TFVs.

2. Indexes
Index can magically reduce the data retrieval time and help to improve SQL query performance and give you best query response time.
Keep in mind that clustered index should be created on a unique column.
A non-clustered index is most useful if the ratio of qualifying number of rows/total number of rowsis around 5% or less, which means if the index can eliminate 95% of the rows from consideration. As a best practice, most selective columns should be placed leftmost in the key of a non-clustered index.
Dropping unused indexes can help to speed up data modifications without affecting data retrieval.

3. Statistic Creation and Updates

4.Revisit Your Schema Definitions
Availability of the right constraint(FORIGEN KEY, NOT NULL and CEHCK constraints ) on the right place always helps to improve the query performance, like FORIGEN KEY constraint helps to simplify joins by converting some outer or semi-joins to inner joins and CHECK constraint also helps a bit by removing unnecessary or redundant predicates.

What is nested loops join?

What is nested loops join?

The nested loops join uses one join input as the outer input table (shown as the top input in the graphical execution plan) and one as the inner (bottom) input table. The outer loop consumes the outer input table row by row. The inner loop, executed for each outer row, searches for matching rows in the inner input table.
In the simplest case, the search scans an entire table or index; this is called a naive nested loops join. If the search exploits an index, it is called an index nested loops join. If the index is built as part of the query plan (and destroyed upon completion of the query), it is called a temporary index nested loops join. All these variants are considered by the query optimizer.
A nested loops join is particularly effective if the outer input is small and the inner input is preindexed and large. In many small transactions, such as those affecting only a small set of rows, index nested loops joins are superior to both merge joins and hash joins. In large queries, however, nested loops joins are often not the optimal choice.

SELECT * FROM customer, orders 
WHERE customer.customer_num=orders.customer_num 
AND order_date>"01/01/1997";
 

What is Hash Join?

What is Hash Join?
The optimizer usually uses a hash join when at least one of the two join tables does not have an index on the join column or when the database server must read a large number of rows from both tables. No index and no sorting is required when the database server performs a hash join.

A hash join consists of two activities: first building the hash table (build phase) and then probing the hash table (probe phase). Figure 48 shows the hash join in detail.

In the build phase, the database server reads one table and, after it applies any filters, creates a hash table. Think of a hash table conceptually as a series of buckets, each with an address that is derived from the key value by applying a hash function. The database server does not sort keys in a particular hash bucket.

Smaller hash tables can fit in the virtual portion of database server shared memory. The database server stores larger hash files on disk in the dbspace specified by the DBSPACETEMP configuration parameter or the DBSPACETEMP environment variable.

In the probe phase, the database server reads the other table in the join and applies any filters. For each row that satisfies the filters on the table, the database server applies the hash function on the key and probes the hash table to find a match.

August 08, 2014

In-memory BI

In-memory BI gained popularity largely due to the success of QlikTech, provider of the in-memory-based QlikView BI product. Following QlikTech’s lead, many other BI vendors have jumped on the in-memory “hype wagon,” including the software giant, Microsoft, which has been aggressively marketing PowerPivot, their own in-memory database engine.

The concept of in-memory business intelligence is not new. It has been around for many years. The only reason it became widely known recently is because it wasn’t feasible before 64-bit computing became commonly available. Before 64-bit processors, the maximum amount of RAM a computer could utilize was barely 4GB, which is hardly enough to accommodate even the simplest of multi-user BI solutions. Only when 64-bit systems became cheap enough did it became possible to consider in-memory technology as a practical option for BI.

When we talk about whether a database is disk-based or in-memory, we are talking about where the data resides while it is actively being queried by an application: with disk-based databases, the data is queried while stored on disk and with in-memory databases, the data being queried is first loaded into RAM.

Relational databases are great as the backbone of operational applications such as CRM, ERP or Web sites, where transactions are frequently and simultaneously inserted, they are a poor choice for supporting analytic applications which usually involve simultaneous retrieval of partial rows along with heavy calculations.

The “traditional best-practice” BI has been based on the following architecture:

We start with business applications that gather the data we would like to analyze.
We create a copy that’s typically called an “operational data store” or ODS, then we use ETL (extraction, transformation, and loading) technology to load data into database structures optimized for business intelligence – a data mart or data warehouse.
To provide better interactivity, an additional data cache is often created for a particular report or cube.
Because this architecture is slow and unwieldy, organizations often create extra data marts for a particular business need.
The result is a vacuum tube: it works, and it’s the best alternative we have right now, but it’s slow, complex, and expensive.



The “In Memory” BI has been based on the following architecture:

“In Memory” BI store all the required data in-memory.
Operations run with the support of massively parallel processing.
Column databases are very well adapted to parallelization: because each column is stored in a separate area of memory, aggregates can be efficiently handed off to a separate processor, or even partitioned across several processorsWith column database, data loading times are no longer a problem.
64 bit addressing has radically increased how easy data to be accessed.



QlikView is a relatively non-invasive, business-focused product that uses in-memory technology to present data in an associative architecture. QlikView is a technology aimed at helping analysts and business people get insights from information as part of a highly visual process. When business users explore data in QlikView, they use associations and patterns that are more like those in the human mind than in a traditional database, releasing users from the limitations of those databases. QlikView’s claim to value is that it gets business users working quickly, collaboratively and flexibly, without making business intelligence an IT project in and of itself. The idea is that, through associative search, and displays that gray out (but still show) data that doesn’t fit in the framework of the query, users can not only answer questions quickly, but can even find the answers to the questions they didn’t ask — but should have. Its accelerated and accessible brand of business intelligence is called “business discovery.”

The beauty of it is that with QlikView:

Metadata management is optional and pervasive. QlikView customers use metadata only when and where it adds value. QlikView creates metadata automatically. Whether or not it is used is up to the designers and developers.
Our focus is on QlikView itself. QlikView’s metadata focus is on helping stakeholders understand and manage the QlikView environment. Developers and designers get a clear picture of how well their QlikView applications were built and gain insights that help them maintain their applications.
Developers can introduce metadata usage over time. With QlikView, developers do not have to create a metadata layer ahead of time. They can define and collect metadata after they have created, tested, and even deployed applications. Project teams can instead focus on getting the right business information and analytical tools in the hands of the right people at the right time.


August 04, 2014

Self-Service Business Intelligence and Analytics

Self-Service Business Intelligence and Analytics


Self-service means different things to different people, who want to interact and analyze data to discover new insights on their own and ability to consume it. Match self-service capabilities to your users: information consumers will be interested in reporting with little to no interactivity; more data-savvy users will want drill-down analysis, data querying, and report authoring capabilities; and analysts will want visual data discovery that helps them answer new questions as they arise.

Traditional BIs Vs QlikView :
Traditional BI suites usually remind us of rigid and complex UIs involving extensive usage of SQLs and resemble visual front-end extensions of databases.
Unlike traditional BI’s, QlikView sits on your existing data warehouses and infuses a broad set of self-service BI capabilities.Most traditional BI platforms don't fit into this. Controllers are usually not offered alongside models and views, that's why they are considered as visual extensions of your databases.Traditional report centric BI vendors have been vying to fit in the self service capability into their systems for a long time. Traditional BI architectures typically require mobile reporting layers.In contrast, QlikView offers a unified architecture for delivering cross platform Business discovery capabilities on mobile allowing users to benefit from a consistent experience across devices. Making full use of rich HTML5 features, QlikView offers appealing layouts, compelling visualizations and a bag full of touch interactions on new age mobile devices, thus delivering an user experience on par with native apps.



How QlikView offer Self-Service BI?

QlikView enables IT pros to deliver self-service BI, providing a competitive advantage to the business. With QlikView, IT allows business users to create their own analyses so they can arrive at innovative decisions. QlikView lets IT serve the business like never before—all while assuring strict data security, quality, and governance. Combined with the direct data access capabilities offered by QlikView’s Direct Discovery, virtually unlimited data can be addressed. It lets IT deliver a consistent, highly-responsive user experience, while freeing it up to focus on core competencies.

 QlikView enables IT professionals to deliver far more analytics and BI to the business than with any other approach. With its rapid application development capability, requests from the business for new apps can be answered much more quickly, changes to existing applications made easily and with its self-service approach, business users can get answers to their own questions without turning back to IT for a new report. This all contributes to the success of QlikView and makes IT groups who provide it to an organization true ‘champions to the business’.



ho want to interact and analyze data to discover new insights on their own - See more at: http://www.logianalytics.com/blog/5-keys-self-service-success#sthash.im9sszXi.dpuf
ho want to interact and analyze data to discover new insights on their own - See more at: http://www.logianalytics.com/blog/5-keys-self-service-success#sthash.im9sszXi.dpuf
Self-service means different things to different people - See more at: http://www.logianalytics.com/blog/5-keys-self-service-success#sthash.im9sszXi.dpuf
Self-service means different things to different people - See more at: http://www.logianalytics.com/blog/5-keys-self-service-success#sthash.im9sszXi.dpuf

Stop Memorizing SQL Part 3 : A Practical SQL Cheat Sheet for Beginners

 A Practical SQL Cheat Sheet for Beginners When learning SQL, students often struggle to remember the correct syntax during exams or practic...