SQL & BI

BI on SQL Azure - Yes you can

2015-09-10T05:19:00.003-07:00

Just 4 years before, it was not possible to have end to end Business Intelligence (BI) solution on SQL Azure. Please read my blog here which was actually written 4 years back.

Now things have been changed dramatically. If you go through my blog than under conclusion at last section I have mentioned that “As per current scenario Microsoft Cloud Platform seems to be positioned as one of the leading platforms for DW/BI because it demonstrates great potential in early stage. We expect a great future of SQL Azure for DW/BI if Microsoft promises to enhance it continuously to overcome its limitation.”

Microsoft shows an awesome approach to address my conclusive remark and now we can truly do BI with some constraints on SQL Azure by using Microsoft Azure Virtual Machine gallery includes images that contain SQL Server installations. To have more details on the same read here.

3 cheers to Microsoft and Azure!

Master Data Services 2016 CTP 2.2 - Explorer functionality quick fix

2015-08-28T07:42:00.001-07:00

Microsoft has recently released SQL Server 2016 CTP 2.2. I was concerned about the Master Data Services and was exploring the new features. I got surprised when I see that Explorer functionality was not working. Generally it is used to manage members for Entities and Hierarchies. It is one of the main functionality and frequently used in MDS. Please note that I have used the SQL 2016 Server CTP 2.2 from window azure environment. I feel this issue could be the same if you download and install CTP 2.2 on virtual machine.

Please refer to below screen shot:

You can conclude from above screen shot that that Explorer functionality is disabled as well as you can’t select the version of the model. If you navigate through Version Management or Integration Management, you are allowed to select the version. This is quite strange.

It was not possible to view the members at all. I have complaint about it at some site as well on twitter, but no response till yet.

Alternatively, I used Excel add in to view and modify the members and test couple of deployment scenario. But it was quite annoying. I tried to find a workaround for it and luckily I got succeeded. I am sharing this to all of other MDS users so that everybody can use and test the MDS CTP 2.2 without any interruption.

To access the explorer functionality, you just have to replace the proper value enclosed in ‘<>’ and paste into address bar.

<Your MDS Server URL>/Explore?MID=<MUID of the Model>&VID=<MUID of Version of the model>#/ExplorerEntity?MID=<MUID of the Model>&VID=<MUID of Version of the model>&EID=<MUID of Entity which you want to explore>

Once you replace ‘<>’ with proper value, Explorer functionality will be up and running. To replace the proper value for above string I created some SQL Script. Once you execute it, it will give you the exact explorer MDS URL for the specific entity. You just have to paste it into the address bar.

Execute below script against MDS database.

DECLARE @MDSServerURL VARCHAR(100) = 'http://sql2016ctp222:81/' -- <Your MDS Server URL>

DECLARE @ModelName VARCHAR(50) = 'CustomerSample' -- <Model Name>

DECLARE @EntityName VARCHAR(50) = 'Area' -- <Entity Name>

DECLARE @MDSExploreURL VARCHAR(8000)

DECLARE @MUIDModel uniqueidentifier

DECLARE @MUIDModelVersion uniqueidentifier

DECLARE @MUIDEntity uniqueidentifier

-- Get MUID for the Model

SELECT @MUIDModel = MUID

FROM mdm.tblmodel

WHERE Name = @ModelName

-- Get MUID for the version of the model

SELECT @MUIDModelVersion = v.MUID

FROM mdm.tblModelVersion v

INNER JOIN mdm.tblmodel m

ON v.Model_ID = m.Id

WHERE m.Name = @ModelName

--Get MUID for the entity you want to explore

SELECT @MUIDEntity = e.MUID

FROM mdm.tblEntity e

INNER JOIN mdm.tblmodel m

ON e.Model_ID = m.Id

WHERE m.Name = @ModelName

SET @MDSExploreURL = @MDSServerURL + '/Explore?MID='

+ CAST(@MUIDModel AS VARCHAR(255))

+ '&VID=' + CAST(@MUIDModelVersion AS VARCHAR(255))

+ '#/ExplorerEntity?MID='

+ CAST(@MUIDModel AS VARCHAR(255)) + '&VID='

+ CAST(@MUIDModelVersion AS VARCHAR(255))

+ '&EID=' + CAST(@MUIDEntity AS VARCHAR(255))

--copy the below result and paste it into address bar and you are there

SELECT @MDSExploreURL

I just copied the URL and paste it into browser and I got below screen:

Now you can choose any entity and explore J

Now it is cake time J Enjoy reading.

Entity Sync Relationship - Master Data Services 2016

2015-08-27T08:23:00.001-07:00

Entity Sync Relationship is a new feature in Master Data Services 2016. It enables you to share entity data between different models. You can keep a single version of truth in one model and reuse it in an another model.

I was wondering where should I use it? Do I have any business scenario where it can be applicable?

I have many models which use Product master data. I could have 2 ways to handle it. I can create multiple models and add Product as an entity to those models. I am just repeating Product entity in all my models and ask me genuinely , I don’t like it. If I follow the best practices than I can create other models as an entity under Product model itself. This is good way to handle it but it has its own limitation. Sometimes all other entities is independent and it just want to use the Product. So I would prefer to create independent models and want to use the Product entity across models.

Actually Entity Sync Relationship is quite useful feature. Just relating above with an example. Assume you have a model for Product where Product is itself an entity. Now you want to build a model for Customer where you have a necessity to use Product entity. In this case you have to create a new entity called Product under model name Customer and you have to add members for the same. If product table is a huge table than you might create an ETL package preferably SSIS to first bring data from Product Entity in Product Model. You also need to run this package incrementally to sync the data between Product entity from source model (Product) to target model (Customer).

Now life becomes easier due to Entity Sync Relationship.

To create an entity sync relationship

In Master Data Manager, click System Administration.

Please refer to screen shot below:

Now click on the Model View page, from the menu bar, point to Manage and click Entity Sync.

Please refer to screen shot below:

On the Entity Sync Maintenance page, click Add as shown below:

Now choose appropriate models and entity and version from source and target. Please refer to below screen shot:

Note – There are some constraints as well as prerequisites with respect to Entity Sync Relationship . To know in more detail, please refer to msdn.

Versions (Master Data Services) - Where to use

2015-08-21T05:25:00.000-07:00

Multiple versions of the master data within a model can be created in Master Data Services(MDS). When you create a model for the first time, Master Data Services (MDS) creates a default initial version of the model data.

While working with MDS, I was trying to find when to use Versions. I have googled it and I didn’t find sufficient answer, so I tried few things from my side. I have considered that I already have a model called Product which is developed and moved to Production after testing it very well. Now I want to use the Versions feature supported by MDS. So question is where it will fit in or what could be the best case to use it?

Before getting into details of scenario let us know little bit about Versions. We can create sequential or simultaneous versions of our model in MDS.

Sequential Versions - Version can be created one after another in sequence manner. For example, you can copy Version 1 of your model and name the copy Version 2.

Simultaneous versions - Create simultaneous versions of your model when you want to work on two or more versions of your data at once. It means this can also be created simultaneously, so multiple versions can exist in parallel.

Let us take the best out if simultaneous version which can help business in such a manner that one consistent version can be dedicatedly available for consumption by the all consumers of Product master data. Another version of Product master data can exist in parallel that can be used for adding or deleting or modifying members or for testing the model data.

Now the questions is how should be attain this?

Suppose you have the Product model which looks like below in Production environment.

Environment	Model	Versions	Version Flag	Status	Subscription View	Copied From Version	Comments
Production	Product	V1	F1	Open	S1	-	Version in use

Now to attend our scenario we have to follow below steps:

First create 2 flags F1 and F2 for version V1 if it does not exist
Add flag F1 to version V1
Lock V1 if it is used by multiple users
Validate V1
Commit V1
Create new version V2 from old version V1
Map Flag F1 to newly created version V2
Version V2 with Flag F1 will be current version in use
Create new version V3 from old version V1
Map Flag F2 to newly created version V3
Version V3 with flag F2 will be version where you can make modifications

For Assigning, Locking, Validating and Committing versions, please refer to MSDN. I don’t want to repeat those steps as it is already available on internet.

After following above steps the Product model will look like below in Production environment:

Environment	Model	Versions	Version Flag	Status	Subscription View	Copied From Version	Comments
Production	Product	V1		Commited	S1	-	Locked
Production	Product	V2	F1	Open	S2	V1	Version in use
Production	Product	V3	F2	Open	S3	V1	Version available for making modifications

It means now V2 with F1 is open for use for consumers of Master Data and V3 is available for making changes as well as testing the model. Now assume you are done with your changes in V3 and you want that consumers should start using V3. How you will make it happen? It is simple and you have to follow below steps:

Validate V2 and V3
Lock V2 and V3
Commit V2 and V3
Create new version V4 from old version V3
Map Flag F1 to newly created version V4
Version V4 with Flag F1 will be current version in use
Create new version V5 from old version V3
Map Flag F2 to newly created version V5
Version V5 with flag F2 will be version where you can make modifications

After following above steps the Product model will look like below in Production environment:

Environment	Model	Versions	Version Flag	Status	Subscription View	Copied From Version	Comments
Production	Product	V1		Commited	S1	-	Locked
Production	Product	V2		Commited	S2	V1	Locked
Production	Product	V3		Commited	S3	V1	Locked
Production	Product	V4	F1	Open	S4	V3	Version in use
Production	Product	V5	F2	Open	S5	V3	Version available for making modifications

I have also come across multiple constraint while experimenting above steps. It is good to know about it before you start blindly using it.

Constraints ( Very Very Important)

As the subscription views are created based on the Version Flags and not on Version. So we don’t have to change the subscription views for the newly created version. THIS IS ONLY APPLICABLE IF you add members to entity.
When you add attribute to entity then all the subscription view referring to that entity, needs to be regenerated to reflect this change. The old view will still be working but will not show the newly added attribute.
You can't delete attribute if you have already defined subscription view for the same entity and model. If you still want to delete it then you have to first delete the subscription view than delete the attribute and then recreate the subscription view.

Introducing Python in Hadoop World

2013-08-18T08:38:00.004-07:00

This blog is not for pure Python lover. In this article I will describe usefulness of Python in Big Data and Hadoop environment. To discover more about Python please visit to official website of Python (http://www.python.org/).

Python is a powerful, flexible, open-source language that is easy to learn, easy to use, and has powerful libraries for data manipulation and analysis. It’s simple syntax is very accessible to programming novices, and will look familiar to anyone with experience in C/C++, Java, or Visual Basic. Python has a unique combination of being both a capable general-purpose programming language as well as being easy to use for analytical and quantitative computing. Python is one of the most popular languages in the world and widely used by Google.

In addition to Java, we can write map and reduce functions in other languages and invoke them using an API known as Hadoop Streaming. Streaming is based on the concept of UNIX streaming, where input is read from standard input, and output is written to standard output. These data streams represent the interface between Hadoop and our applications. The Streaming interface lends itself best to short and simple applications we would typically develop using a scripting language such as Python. A major reason for this is the text-based nature of the data flow, where each line of text represents a single record.

The below example shows the execution of map and reduce functions written in Python using Hadoop streaming:

hadoop jar contrib/streaming/hadoop-streaming.jar \

-input input/dataset.txt \

-output output \

-mapper text_processor_map.py \

-reducer text_processor_reduce.py

Please refer to below link for an excellent and detailed example of MapReduce program in Python:

http://www.michael-noll.com/tutorials/writing-an-hadoop-mapreduce-program-in-python/

I will end it here because I feel exploring and learning programming syntax is programmer’s choice. Before wrapping it up, I would like to mention that Python is easy for analysts to learn and use. It is also powerful enough to tackle even the most difficult problems in any domain. It integrates well with existing IT infrastructure. The last and most important fact is that it is platform independent. The agility and the productivity of Python-based solutions are changing the world of Big Data and Hadoop.

Big Data: An Introduction with Hive

2012-12-26T21:07:00.000-08:00

Hive is a data warehouse system for Hadoop 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.

As with any database management system (DBMS), we can run our Hive queries in many ways. You can run them from a command line interface (known as the Hive shell), from a Java Database Connectivity (JDBC) or Open Database Connectivity (ODBC) application leveraging the Hive JDBC/ODBC drivers, or from what is called a HiveThrift Client. The Hive Thrift Client is much like any database client that gets installed on a user’s client machine (or in a middle tier of 3-tier architecture): it communicates with the Hive services running on the server. You can use the Hive Thrift Client within applications written in C++, Java, PHP, Python, or Ruby (much like you can use these client-side languages with embedded SQL to access a database such as DB2 or Informix.

The above sections are taken from http://manishku.blogspot.in/2012/12/big-data-and-hadoop-part-2.html

Now you will think that we already have Pig a powerful and simple language than why should be look for Hive? The downside of Pig is that it is something new and we need to learn it and then master it.

Facebook folks have developed a runtime Hadoop support structure which is called Hive which allows anyone who is already familiar with SQL to control the Hadoop platform right out of the gate. Hive is a platform where SQL developers are allowed to write HQL (Hive Query Language) which are similar to standard SQL statements. HQL is limited in the commands it understands but it is still pretty useful.

HQL statements are broken down by the Hive service into MapReduce jobs and executed across a Hadoop cluster. We can run Hive queries in many ways. We can run them from

Hive Shell (a command line interface)
Java Database Connectivity (JDBC)
Open Database Connectivity (ODBC)
HiveThrift Client (The Hive Thrift Client is much like any database client that gets installed on a user’s client machine written in C++, Java, PHP, Python, or Ruby)

Hive Thrift Client can use these client-side languages with embedded SQL to access a database such as DB2 or Informix.

Let us consider below simple example. Here we create a FBComments table, populate it, and then query that table using Hive:

CREATE TABLE FBComments(from_user STRING, userid BIGINT, commenttext STRING,

recomments INT)

COMMENT 'This is Facebook comments table and a simple example'

STORED AS SEQUENCEFILE;

LOAD DATA INPATH 'hdfs://node/fbcommentdata' INTO TABLE FBComments;

SELECT from_user, SUM(recomments)

FROM FBComments

GROUP BY from_user;

After looking above code, Hive (HQL) looks very much similar to traditional database SQL code. There exist small difference and any SQL developer can point it out.Hive is based on Hadoop and MapReduce operations, there are few key differences.

High Latency - Hadoop is intended for long sequential scans, we can expect queries to have a very high latency (many minutes). Hive would not be appropriate for applications that need very fast response times.

Not Suitable for Transaction processing - Hive is read-based and therefore not suitable for transaction processing where you expect high percentage of write operations.

Big Data - An Introduction to Pig

2012-12-25T21:59:00.000-08:00

In my previous article, I have explained Big Data and Hadoop in details. In this article I would like to go little deeper with Pig. Pig is a high-level platform for creating Map Reduce programs used with Hadoop. Pig is made up of two components: the first is the language itself, which is called PigLatin and the second is a runtime environment where PigLatin programs are executed. Pig Latin can be extended using UDF (User Defined Functions) which the user can write in Java, Python or JavaScript and then call directly from the language.

We know Pig was initially developed at Yahoo research at 2006. The whole intension behind developing Pig was to allow people using Hadoop to focus more on analyzing large data sets and spend less time to write mapper and reducer programs. Pigs eat almost anything, the Pig programming language is designed to handle any kind of data and for the very same reason Yahoo! named it Pig.

The first step in a Pig program is to LOAD the data you want to manipulate from HDFS. Then you run the data through a set of transformations (which, under the covers, are translated into a set of mapper and reducer tasks). Finally, you DUMP the data to the screen or you STORE the results in a file somewhere.

Let us talk about LOAD, TRANSFORM, DUMP and STORE in details.

LOAD

The objects that are being worked on by Hadoop are stored in HDFS. In order for a Pig program to access this data, the program must first tell Pig what file (or files) it will use, and it is done through the LOAD 'data_file' command (where 'data_file' specifies either an HDFS file or directory). If a directory is specified, all the files in that directory will be loaded into the program. If the data is stored in a file format that is not natively accessible to Pig, you can optionally add the USING function to the LOAD statement to specify a user-defined function that can read in and interpret the data.

TRANSFORM

The transformation logic is where all the data manipulation happens. Here we can FILTER out rows that are not of interest, JOIN two sets of data files, GROUP data to build aggregations, ORDER results, and much more. The following is an example of a Pig program that takes a file composed of Facebook comments, selects only those comments that are in English, then groups them by the user who is commenting, and displays the sum of the number of re-comments of that user’s comments.

L = LOAD 'hdfs//node/facebook_comment';

FL = FILTER L BY iso_language_code EQ 'en';

G = GROUP FL BY from_user;

RT = FOREACH G GENERATE group, SUM(recomments);

DUMP and STORE

If we don’t specify the DUMP or STORE command, the results of a Pig program are not generated. When we are debugging our Pig programs, we typically use the DUMP command to send the output to the screen. We simply change the DUMP call to a STORE call so that any results from running your programs are stored in a file for further processing or analysis when we go into production. Please note that DUMP command can be used anywhere in our program to dump intermediate result sets to the screen and actually we need it because it helps us in debugging.

How to run Pig program

Now when we are ready with our Pig program, than we need to run in the Hadoop environment. There are three ways to run a Pig program:

Embedded in a script
Embedded in a Java program
From the GRUNT(Pig command line)

It doesn’t matter which of the three ways we run the program. The Pig runtime environment translates the program into a set of map and reduces tasks and runs them under our behalf. I will talk about Python in my next blog, till than happy reading.

Big Data And Hadoop Part 2

2012-12-24T03:26:00.000-08:00

In my previous blog I have given more insight on Big Data and now we will take a closer look at Hadoop.

Hadoop

Hadoop is a software framework that supports data-intensive distributed applications under a free license. It enables applications to work with thousands of nodes and petabytes (a thousand terabytes) of data. It is a top-level Apache project which is written in Java. Hadoop was inspired by Google’s work on its Google (distributed) File System (GFS) and the Map-Reduce programming paradigm.

Hadoop is designed to scan through large data sets to produce its results through a highly scalable, distributed batch processing system. People, who always misunderstand Hadoop, Please take a note that

It is not about speed-of-thought response times
It is not real-time warehousing

Hadoop a computing environment built on a top of distributed cluster system designed specifically for very large scale data operation. It is about discovery and making the once near-impossible possible from a scalability and analysis perspective. The Hadoop methodology is built around a function-to-data model where analysis programs are sent to data. One of the key components of Hadoop is the redundancy built into the environment and this redundancy provides fault tolerance to it.

Hadoop Component

Hadoop has three below component:

Hadoop Distributed File System
Programming paradigm – Map Reduce pattern
Hadoop Common

Hadoop is generally seen as having two parts: a file system (the Hadoop Distributed File System) and a programming paradigm (Map-Reduce).

Components of Hadoop

Now let us take a closer look at Hadoop components.

The Hadoop Distributed File System (HDFS)

It’s possible to scale a Hadoop cluster to hundreds and even to thousands of nodes. Data in a Hadoop cluster is broken down into smaller pieces (called blocks) and distributed throughout the cluster. The map and reduce functions execute on smaller subsets of larger data sets. Hadoop uses commonly available servers in a very large cluster, where each server has a set of inexpensive internal disk drives. For higher performance, MapReduce tries to assign workloads to these servers where the data to be processed is stored. This is known as data locality. For the very same reason SAN (storage area network) or NAS (network attached storage), is not recommended in Hadoop Environment.

The strength of Hadoop is that it has built-in fault tolerance and fault compensation capabilities. This is the same for HDFS, in that data is divided into blocks, and copies of these blocks are stored on other servers in the Hadoop cluster. That is, an individual file is actually stored as smaller blocks that are replicated across multiple servers in the entire cluster.

Let us take an example we have to store the Unique Registration Numbers (URN) for everyone in the India; then people with a last name starting with A might be stored on server 1, B on server 2, C on Server 3 and so on. In a Hadoop pieces of this URN would be stored across the cluster. In order to reconstruct the entire URN information, we would need the blocks from every server in the Hadoop cluster. To achieve availability as components fail, HDFS replicates these smaller pieces on to two additional servers by default. A data file in HDFS is divided into blocks, and the default size of these blocks for Apache Hadoop is 64 MB.

All of Hadoop’s data placement logic is managed by a special server called NameNode. It keeps the directory tree of all files in the file system, and tracks where across the cluster the file data is kept. It does not store the data of these files itself.All of the NameNode's information is stored in memory, which allows it to provide quick response times to storage manipulation or read requests. Client applications talk to the NameNode whenever they wish to locate a file, or when they want to add/copy/move/delete a file. Backup Node is available from Hadoop 0.21+ version and used for HA (High Availability) in case NameNode fails.

Please take a note that there’s no need for MapReduce code to directly reference the NameNode. Interaction with the NameNode is mostly done when the jobs are scheduled on various servers in the Hadoop cluster. This greatly reduces communications to the NameNode during job execution, which helps to improve scalability of the solution. In summary, the NameNode deals with cluster metadata describing where files are stored;actual data being processed by MapReduce jobs never flows through the NameNode.

HDFS is not fully POSIX compliant because the requirements for a POSIX filesystem differ from the target goals for a Hadoop application. The tradeoff of not having a fully POSIX compliant filesystem is increased performance for data throughput. It means that commands you might use in interacting with files (copying, deleting, etc.) are available in a different form with HDFS. There may be syntactical differences or limitations in functionality. To work around this, you may write your own Java applications to perform some of the functions, or learn the different HDFS commands to manage and manipulate files in the file system.

Basics of MapReduce

MapReduce is the heart of Hadoop. It is this programming paradigm in which work is broken down into mapper and reducer tasks to manipulate data that is stored across a cluster of servers for massive parallelism. The term MapReduce actually refers to two separate and distinct tasks that Hadoop programs perform. The first is the map job, which takes a set of data and converts it into another set of data, where individual elements are broken down into tuples (key/value pairs). The reduce job takes the output from a map as input and combines those data tuples into a smaller set of tuples. As the sequence of the name MapReduce implies, the reduce job is always performed after the map job.

The people are generally confused about MapReduce so I am trying to explain it by giving a simple example but in real world it is not so simple J

Assume we have three files, and each file contains two columns {a key (city) and a value (population) in Hadoop term} for the various months. Please see below:

Mumbai, 20000

New Delhi, 30000

Bangalore, 15000

Mumbai, 22000

New Delhi, 31000

Now we want to find the minimum population for each city across all of the data files. There may be the case that each file might have the same city represented multiple times. Using the MapReduce paradigm, we can break this down into three map tasks, where each mapper works on one of the three files and the mapper task goes through the data and returns the minimum population for each city.

For example, the results produced from one mapper task for the data above would look like this:

(Mumbai, 20000) (New Delhi, 30000) (Bangalore, 15000)

Let’s assume the other 2 mapper tasks produced the following intermediate results:

(Mumbai, 20000) (New Delhi, 31000) (Bangalore, 15000)

(Mumbai, 22000) (New Delhi, 30000) (Bangalore, 14000)

All three of these output streams would be fed into the reduce tasks, which combine the input results and output a single value for each city, producing a final result set as follows:

(Mumbai, 20000) (New Delhi, 30000) (Bangalore, 15000)

The JobTracker is the service within Hadoop that farms out MapReduce tasks to specific nodes in the cluster, ideally the nodes that have the data, or at least are in the same rack.

Client applications submit jobs to the Job tracker.
The JobTracker talks to the NameNode to determine the location of the data
The JobTracker locates TaskTracker nodes with available slots at or near the data
The JobTracker submits the work to the chosen TaskTracker nodes.
The TaskTracker nodes are monitored. If they do not submit heartbeat signals often enough, they are deemed to have failed and the work is scheduled on a different TaskTracker.
A TaskTracker will notify the JobTracker when a task fails. The JobTracker decides what to do then: it may resubmit the job elsewhere, it may mark that specific record as something to avoid, and it may may even blacklist the TaskTracker as unreliable.
When the work is completed, the JobTracker updates its status.
Client applications can poll the JobTracker for information.

The JobTracker is a point of failure for the Hadoop MapReduce service. If it goes down, all running jobs are halted.All MapReduce programs that run natively under Hadoop are written in Java, and it is the Java Archive file (jar) that’s distributed by the JobTracker to the various Hadoop cluster nodes to execute the map and reduce tasks.

Hadoop Common Components

The Hadoop Common Components are a set of libraries that support the various Hadoop subprojects. To interact with files in HDFS, we need to use the /bin/hdfs dfs <args> file system shell command interface, where args represents the command arguments we want to use on files in the file system.

Here are some examples of HDFS shell commands:

cat Copies the file to standard output (stdout).

chmod Changes the permissions for reading and writing to a given file or set of files.

chown Changes the owner of a given file or set of files.

copyFromLocal Copies a file from the local file system into HDFS.

copyToLocal Copies a file from HDFS to the local file system.

cp Copies HDFS files from one directory to another.

expunge Empties all of the files that are in the trash.

ls Displays a listing of files in a given directory.

mkdir Creates a directory in HDFS.

mv Moves files from one directory to another.

rm Deletes a file and sends it to the trash

Application Development in Hadoop

In below section, we will cover three Pig, Hive, and Jaql which are top 3 application development language currently.

Pig

Pig is a high-level platform for creating Map Reduce programs used with Hadoop. Pig was originally developed at Yahoo Research for researchers to have an ad-hoc way of creating and executing map-reduce jobs on very large data sets. Pig is made up of two components: the first is the language itself, which is called PigLatin and the second is a runtime environment where PigLatin programs are executed.

Hive

As with any database management system (DBMS), we can run our Hive queries in many ways. You can run them from a command line interface (known as the Hive shell), from a Java Database Connectivity (JDBC) or Open Database Connectivity (ODBC) application leveraging the Hive JDBC/ODBC drivers, or from what is called a HiveThrift Client. The Hive Thrift Client is much like any database client that gets installed on a user’s client machine (or in a middle tier of 3-tier architecture): it communicates with the Hive services running on the server. You can use the Hive Thrift Client within applications written in C++, Java, PHP, Python, or Ruby (much like you can use these client-side languages with embedded SQL to access a database such as DB2 or Informix.

Jaql

Jaql has been designed to flexibly read and write data from a variety of data stores and formats. Its core IO functions are read and write functions that are parameterized for specific data stores (e.g., file system, database, or a web service) and formats (e.g., JSON, XML, or CSV). Jaql infrastructure is extremely flexible and extensible, and allows for the passing of data between the query interface and the application language like Java, JavaScript, Python, Perl, Ruby, etc.

Specifically, Jaql allows us to select, join, group, and filter data that is stored in HDFS, much like a blend of Pig and Hive. Jaql’s query language was inspired by many programming and query languages, including Lisp, SQL, XQuery, and Pig. Jaql is a functional, declarative query language that is designed to process large data sets. For parallelism, Jaql rewrites high-level queries, when appropriate, into “low-level” queries consisting of MapReduce jobs.

Before we move further on JAQL let us talk about JSON first. Now day’s developers prefer JSON as their choice for a data interchange format. There are 2 reasons behind that; first it’s easy for humans to read due to its structure, secondly it’s easy for applications to parse it. JSON is built on top of two types of structures. The first is a collection of name/value pairs. These name/value pairs can represent anything since they are simply text strings that could represent a record in a database, an object, an associative array, and more. The second JSON structure is the ability to create an ordered list of values much like an array, list, or sequence you might have in our existing applications.

Internally, the Jaql engines transforms the query into map and reduce tasks that can significantly reduce the application development time associated with analyzing massive amounts of data in Hadoop.

Other Hadoop Related Projects

Please see below the list of some other important Hadoop related projects, for further readings please refer to (hadoop.apache.org) or google:

ZooKeeper - It provides coordination services for distributed applications.

HBase - It is a column-oriented database management system that runs on top of HDFS. It is well suited for sparse data sets.

Cassandra – It is essentially a hybrid between a key-value and a row-oriented database.

Oozie - It is an open source project that simplifies workflow and coordination between jobs.

Lucene – It is an extremely popular open source Apache project for text search and is included in many open source projects.

Apache Avro -It is mainly used for data serialization.

Chukwa -A monitoring system specifically used for large distributed system.

Mahout - It is a machine learning library.

Flume -A flume is a channel that directs water from a source to some other location where water is needed.

If I am ending this article without putting a single word about BigQuery, than I am not giving justice to it. BigQuery for Developers is a RESTful web service that enables interactive analysis of massively large datasets working in conjunction with Google Storage. Please take a note that it is an Infrastructure as a Service (IaaS) that may be used complementary with MapReduce.

I will try to bring out more and more, hidden but known truth of Big Data and Hadoop in my subsequent articles. Till than keep reading!

Big Data and Hadoop Part 1

2012-12-14T02:12:00.001-08:00

Big Data Definition

As per wiki Big Data usually includes data sets with sizes beyond the ability of commonly-used software tools to capture, curate, manage, and process the data within a tolerable elapsed time.

Big data is an opportunity to find visions in new and emerging types of data and content. It helps to make business more agile. Sometime we know that we can find answer to questions, but we can’t because of limitation of current infrastructure or data is not being captured in the current traditional systems. We can also find an answer to those questions which were not considered previously and beyond our reach. Now, Big Data gives this opportunity. It also enables us to identify explanation and pattern across industries and sectors.

Big Data stores everything: environmental data, financial data, medical data, surveillance data, log data, Insurance data, digital pictures and videos, cell phone GPS signals, etc. This list can go on and on and on.

Characteristics of Big Data

There are three characteristics of Big Data which are described below:

Volume

Enterprise data is growing and growing from Terabytes to Petabytes and from Petabytes to Zettabytes. Twitter alone generates more than 7 terabytes (TB) of data every day, Facebook 10 TB, and some enterprises generate terabytes of data every hour.

Year - Data Stored Volume

2000 - 800,000 Petabytes (PB)

Year - Expected Data Stored Volume

2020 35 Zettabytes (ZB)

Now days, Enterprises are facing massive volumes of data. Organizations don’t know how to manage this data and to get right meaning out of it. If we use the right technology, right platform and analyze almost all of the data than it can gain a better understanding of business, customers, as well as the marketplace. We can at least use the right technology to identify the useful data for business and customers. The data volumes has changed from terabytes to petabytes and in couple of years it will shift to zettabytes, and for sure all this data can’t be stored in traditional database systems. So the question is where we will store high volume of daily data and make it meaningful for business decision and the answer is move quickly to Big Data.

Variety

Big data can store any type of data, structured, semi-structured and unstructured data. All of it can be included in part of the decision-making. The volume associated with the Big Data brings new challenges. It is a challenge for data centers how to deal with it. It is not only the volume, its variety also a matter of concern. The data associated with sensors, smart devices and social media have made enterprise data very complex. It includes not only traditional relational data, but also raw, semi-structured, and unstructured data. Examples are like data from web pages, web log files (including click-stream data), search indexes, social media forums, e-mail, documents, sensor data, and so on. The biggest struggle for traditional systems is that to store and perform the required analytic to gain understanding from the contents of these logs. Much of the information being generated doesn’t fit to traditional database technologies. Very few companies currently understand it and moving forward and starting to understand the opportunities of Big Data.

Traditional structured data is not meant to handle variety of data. Now days, organization’s success depends upon its ability to draw perceptions from traditional and nontraditional data. Traditional data is structured, neatly formatted, and relational and fits into exact schema and we do all analysis on it. But do we know that it is just the 15~20 percent of the day to day data of any business. Earlier we didn't think of 80~85 percent of data which is unstructured or semi-structured. But now world is looking into this high volume of data as this may be responsible to bring out the truth that may help in taking business decision at the right time. Twitter and Facebook are the best example of it. Twitter feeds are stored as JSON format but the actual text is unstructured and it is difficult to make meaning out of it. Facebook uses video and images too often and it is not easy to store them in traditional databases. Some event changes dynamically which will not fit right into relational databases. The biggest advantages of Big data is that it enables us to analyze structured (traditional relational databases), semi-structured and unstructured data like text, sensor data, audio, video, transactional, and more and more.

Velocity

As per our traditional understanding we say velocity is considered as how quickly the data is arrived, stored and retrieved. Now days, volume and variety of data have changed in collecting and storing manner. Sometimes even few seconds are too late for time sensitive process like catching fraud.

We need a new thinking way to define velocity. We should define velocity as the speed at which the data is flowing.

Sometimes, getting an edge over your competition can mean identifying a trend, problem, or opportunity within small fraction of time. Organizations must be able to analyze this data in near real time. Big Data scale streams computing is a new concept to go beyond traditional databases limitation of running queries against relatively static data. Let us take an example, the query “Show me all people living in the Asia Pacific Sunami region” would result in a single result set. This list can be used to give warning. With streams computing, we can execute a query like people who are currently “in the Asia Pacific Sunami region” and this can be continuously updated result set, because location information from GPS system is refreshed in real time.

Big Data requires perform analytic against the volume and variety of data while it is still in motion, not just after it is at rest. To conclude on Big Data characteristic we can say velocity has same importance as volume. It should combine both how fast data is generated as well how fast analytic is done.

Big Data & Hadoop

The Traditional warehouses are mostly ideal for analyzing structured data from various systems and producing visibility with known and relatively stable measurements. Hadoop based platform is well suited to deal with semi-structured and unstructured data, as well as when a data discovery process is needed. In addition Hadoop can be used for structured data too.

The truth and accuracy is the most desired feature of data. Traditional DW is meant to store structured data. DW departments pick and choose high-valued data and put it through rigorous cleansing and transformation processes as they know that data has a high known value per byte. In contrast, Big Data repositories rarely undergo (at least initially) the full quality control rigors of data before being injected into DW. The Unstructured data can’t be easily stored in a warehouse. A Big Data platform lets you store all of the data in its native business object format and get value out of it through massive parallelism on readily available components. Hadoop based repository scheme store the entire business entity and the fidelity of the Tweet, transaction, Facebook post. Data in Hadoop might seem of low value today but in fact in can be the key to bring out the answer to question which is not asked yet. So let data sit in Hadoop for a while until you discover its value this can be migrate to DW once its value is proven.

Please take a point that Big Data & Hadoop platform is not a replacement for warehouse. Actually both supplement each other.

When Big Data Is Suitable

Big Data solutions are ideal for analyzing not only raw structured data, but semi-structured and unstructured data from a wide variety of sources.
Big Data solutions are ideal when all, or most, of the data needs to be analyzed versus a sample of the data; or a sampling of data isn’t nearly as effective as a larger set of data from which to derive analysis.
Big Data solutions are ideal for iterative and exploratory analysis when business measures on data are not predetermined.
Is the reciprocal of the traditional analysis paradigm appropriate for the business task at hand? Better yet, can you see a Big Data platform complementing what you currently have in place for analysis and achieving synergy with existing solutions for better business outcomes?
Big Data is well suited for solving information challenges that don’t natively fit within a traditional relational database approach for handling the problem at hand. It’s important that we understand that conventional database technologies are an important, and relevant, part of an overall analytic solution. In fact, they become even more vital when used in conjunction with your Big Data platform.

Big Data - Where It Can Fit In

IT Log Analytics

Log analytic is a common use case for Big Data project. We like to refer to all those logs and trace data that are generated by the operation of your IT solutions as data exhaust. Data exhaust has concentrated value, and we need to figure out a way to store and extract value from it. Some of the value derived from data exhaust is obvious and has been transformed into value-added click-stream data that records every gesture, click, and movement made on a web site. Google and Bing search use search based log data to do various analytical reporting. It is not limited to search log, it can be applied to firewall log and to also IT logs which are unreported and uncovered.

Trying to find correlation across massive (gigabytes) of data is a difficult task, but Big Data makes it easy. Now it is possible to identify previously unreported areas for performance optimization tuning. Big Data platform can be used to analyze 1TB of log data each day, with less than 5 minutes latency. Big Data platform finally offers the opportunity to get some new and better insights into the problems at hand.

The Fraud Detection Pattern

Fraud detection comes up a lot in the financial services vertical. Online auctions, insurance claims, any sort of financial transaction is involved, there we see a potential of misusing the data and it may lead to fraud. Here Big Data can help us to identify it and fix it quickly.

Conventional technologies are facing several challenges to detect fraud .The most common and repeating challenge is to store and compute fraud patterns data. Traditional Data Mining technique is used to detect fraud but they have limitation. First it works on less data. The fraud patterns can be cyclic and that can come and go in hours/days/weeks/months. By the time we discover new patterns, it’s too late and some damage has already been done.

Traditionally, in fraud cases, samples and models are used to identify customers that characterize a certain kind of profile by profiling a segment and not the granularity at an individual transaction or person level. We can forecast based on a segment, but it is obviously better to take decision based on an individual transaction. We need to work upon a larger set of data to achieve it and that is not possible with the traditional approach.

Typically, fraud detection works after a transaction gets stored only to get pulled out of storage and analyzed. With Big Data Streams, we can apply fraud detection models as the transaction is happening. It means when data is in motion.

The Social Media

Perhaps the most talked about Big Data usage pattern is social media and customer sentiment. It is very hot and happening topic in global market now days.

We can use Big Data to figure out what customers are saying about you your competitor. Furthermore, we can use it to figure out how customer sentiment impacts the business decisions. It can also be used to review product, price, campaign and placement.

Why are people saying what they are saying and behaving in the way they are behaving? Big Data can answer this question by linking behavior and the driver of that behavior which can’t be answered by traditional data. Big data can look at the interaction of what people are doing with their behaviors, current financial trends and actual transactions. For example the number of Twitter tweets per second per topic can give different insight of customer behavior.

Call and Chat Details

The time and quality resolution metrics and trending discontent patterns for a call center can show up weeks after the fact if we just depend upon traditional DW. This latency can be reduced by using Big Data.

Call centers with text and voice support always want to find better ways to process information to address business issue with lower latency. Simply it is a Big Data use case, where we can use analytics-in-motion and analytics-at-rest. Using in-motion analytics (Streams) means that we basically build our models and find out what’s interesting based upon the conversations that have been converted from voice to text or with voice analysis as the call is happening. We can build model by using rest analytics, after that promote them back into Streams. In this fashion we can examine and analyze the calls that are actually happening in real time. Big Data can also be used not only to see the customer churn rate but also to identify the vulnerable customers and reason for churn. Business can use this to reduce customer churn rate.

Risk: Patterns for Modeling and Management

Risk modeling and management is another big opportunity and common Big Data usage pattern. As per today’s financial markets, a lack of understanding risk can have devastating wealth creation effects. In addition, newly legislated regulatory requirements affect financial institutions worldwide to ensure that their risk levels fall within acceptable thresholds.

Traditional model uses 15 to 20 percent of the available structured data in their risk modeling which is similar as in fraud detection model. Traditional model experts know that there’s a lot of data that’s potentially underutilized and it can be used to determine business rules in risk model. The issue is that they don’t know where the relevant information can be found in the rest of the data. Additionally it can be too much expensive to bring underutilized data into current infrastructure. We already know that some data won’t even fit into traditional systems, so we have to look for new approach. Let’s think about what happens at the end of a trading day in a financial firm. We get a closing snapshot of the positions at the end of the day. Companies use this snapshot, and can derive insight and identify issues and concentrations using their models within a couple of hour. This can be reported back to regulators for internal risk control.

Let’s consider that financial services trend to move their risk model and dashboards to inter-day positions rather than just close-of-day positions. This is again a new challenge to traditional systems and it can’t be solved alone. Big Data can join hands with traditional system to solve this problem.

Energy Sector

The energy sector provides many Big Data use case challenges in how to deal with the massive volumes of sensor data from remote installations.

Many companies are using only a fraction of the data being collected, because they lack the infrastructure to store or analyze the available scale of data. Take for example of a typical oil drilling platform that can have 20,000 to 40,000 sensors on board. All of these sensors are streaming data about the health of the oil rig, quality of operations, and so on. Not every sensor is actively broadcasting at all times, but some are reporting back many times per second. Only 5~10 percent of those sensors are actively utilized. So 90~95 percentage of data are not being utilized in decision-making process. Analyze million and millions of electric meter data to predict better power consumption is also a difficult task. I see Big Data has a great opportunity to deal with this kind of difficult challenge.

Vestas, Denmark is an energy sector global leader whose vision is about the generation of clean energy, are using the IBM BigInsights (Big Data) platform as a method by which they can more profitably and efficiently generate even more clean energy.

This is part 1 on Big Data and I believe it is a good start to step forward with Big Data. The part 2 of current topic will take complete inside of Hadoop and its components. Stay Tuned.

Parallel Execution in SSIS 2008 and later version

2012-09-29T04:41:00.001-07:00

SQL Server Integration Services (SSIS) execution is sequential by default. SQL Server Integration Services (SSIS) does allow parallel execution. Now question is how we can configure SSIS to run in parallel? In SQL Server 2008 and later on version Integration Services, pipeline parallelism has been enhanced with architecture improvements to the data flow engine. SQL 2005 Integration Services each execution tree in a DataFlow task within a package is generally assigned a single worker thread, and, under certain conditions, each execution tree actually shares a thread with other execution trees. This approach has certain benefits and also has some drawbacks. Comparing SQL 2005 SSIS and SQL 2008 SSIS parallelism is out of scope of this article. Any curios user who is interested to know more about it should try google/bing.

Now coming back to our original question and let us continue our talk about SSIS 2008 parallelism. The answer is simple and parallelism can be achieved in SSIS 2008 and later version in two different ways. It can be controlled by using following two properties:

MaxConcurrentExecutables – It is a property of SSIS package which defines how many tasks can run simultaneously. Its default value is -1.
EngineThreads – It is a property of the Data Flow Task that defines how many work threads the scheduler will create and run in parallel. Its default value is 5 for SQL 2008 and default value is 10 in SQL 2012 Evaluation version.

Now let us take an example: Assume we have a SSIS package with 5 Data Flow Tasks. Each task has 15 flows in the transform of “OLE DB SQL Source to SQL Server Destination”.

Just Set MaxConcurrentExecutables to 5 for package properties, then all 5 Data Flow Tasks will run simultaneously. Please see below figure:

Now all 15 flows in each individual Data Flow Task start concurrently or not, it is a good question. The answer is quite interesting. The answer is No if you are using SQL 2008 and later version. The default value of EngineThreads is 5 for SQL 2008 and it is 10 for SQL 21012 evaluation version. All 15 flows in each individual Data Flow Task can run concurrently if we modify EngineThreads properties of Data Flow Task and set it to 15. Please see below figure:

EngineThreads governs both source threads (for source components) and work threads (for transformation and destination components). Here when we say source threads than we are referring to source components and when we say work threads than we are referring to transformation and destination components. Please take a note that source threads and work threads are both engine threads created by the Data Flow’s scheduler.

RANKING Function in plain SQL

2012-06-11T04:34:00.005-07:00

Below examples will show that we can still write a pure sql query for RANK, DENSE_RANK and ROW_NUMBER without using RANK() function.

DECLARE @StudentScore TABLE
(StudentName VARCHAR(1)
,StudentScore INT)

INSERT @StudentScore VALUES ('A',85),
('B',90),
('C',90),
('D',99),
('E',88)

---RANK ----

SELECT
SS1.StudentName
,SS1.StudentScore
,(
SELECT
COUNT(*) + 1
FROM
@StudentScore SS2
WHERE
SS2.StudentScore > SS1.StudentScore) AS StudentRank
FROM
@StudentScore SS1
ORDER BY
StudentRank

--DENSE RANK --

SELECT
SS1.StudentName
,SS1.StudentScore
,StudentDenseRank = (
SELECT
(COUNT(distinct SS2.StudentScore))
FROM
@StudentScore SS2
WHERE
SS1.StudentScore <= SS2.StudentScore
)
FROM
@StudentScore SS1
ORDER BY
StudentDenseRank ASC

--ROW NUM

SELECT
SS1.StudentName
,SS1.StudentScore
,StudentRowNum = (
SELECT
COUNT(*)
FROM
@StudentScore SS2
WHERE
SS1.StudentName >= SS2.StudentName
)
FROM
@StudentScore SS1
ORDER BY
SS1.StudentName

All about Kerberos authentication

2012-05-16T21:18:00.000-07:00

http://download.microsoft.com/download/B/E/1/BE1AABB3-6ED8-4C3C-AF91-448AB733B1AF/SSRSKerberos.docx

OFFSET and FETCH Query Options in SQL Server 2012

2012-05-03T04:30:00.002-07:00

If you are tired of implementing query paging solution in old classic style than try query hints OFFSET & FETCH newly introduced in SQL Server 2012. You might have used TOP operator to return the top number of rows from a table. Probably you might also have used TOP operator to answer your complex query need for pagination. OFFSET & FETCH query clauses can give you more advantages over the TOP operator. OFFSET & FETCH query clauses also make your life easier when you want to avoid row by row operation such like cursor.

Let us say you have 150,000 records in a table and you want to query 100 rows starting from 125000. In this case you can use OFFSET and FETCH to avoid cursor or PARTITION BY clause. OFFSET and FETCH can be used together to get the exact set of data you are looking for.

OFFSET: allows you to offset the record pointer to a specific row in a table

FETCH: allows you to fetch/return number of rows you request in Fetch.

Let’s take an example of querying 5 rows from a table that has 10 records. To make this an interesting example, lets query 5 records from a 10 records table starting from the 4^threcord:

CREATE TABLE STUDENT

CREATE TABLE STUDENT

(

Id INT IDENTITY(1,1),

Name VARCHAR(20)

)

INSERT ROWS INTO STUDENT TABLE

INSERT INTO STUDENT VALUES ('Manish');

INSERT INTO STUDENT VALUES ('Kumar');

INSERT INTO STUDENT VALUES ('John');

INSERT INTO STUDENT VALUES ('Smith');

INSERT INTO STUDENT VALUES ('Vikas');

INSERT INTO STUDENT VALUES ('Sandeep');

INSERT INTO STUDENT VALUES ('Gautam');

INSERT INTO STUDENT VALUES ('Prasad');

INSERT INTO STUDENT VALUES ('Dinesh');

INSERT INTO STUDENT VALUES ('Nidhi');

GO;

QUERYING THE TABLE USING OFFSET AND FETCH

--In below query, we are offsetting the rows by 3 records (starting at 4^th record) and returning the next 5 rows.

SELECT * FROM STUDENT

ORDER BY Id

OFFSET 3 ROWS

FETCH NEXT 5 ROWS ONLY

GO;

--You will get below result

Id Name

4 Smith

5 Vikas

6 Sandeep

7 Gautam

8 Prasad

(5 row(s) affected)

--you can also use variables with offset and fetch clauses.

DECLARE @offset INT=3, @fetch INT=5

SELECT * FROM STUDENT

ORDER BY Id

OFFSET @offset ROWS

FETCH NEXT @fetch ROWS ONLY

GO;

SQL Azure Federation

2012-04-25T03:36:00.000-07:00

What is Federation?

Federation is a database object like other objects such as tables, views, stored procedures or triggers.
It is also known as Sharding.
There can be multiple federations in a database.
It is data dependent/data directed routing so it can scale out across databases.
It is a feature that you can only do on Azure; it is not supported in regular SQL Server.

Advantage of Federation

Elasticity

Ø Scale out when you need and scale back when you don’t need.

Ø Create an elastic database tier that can expand and contract with your applications workload Ø without downtime!

Unlimited Scalability

Ø Federations offer scale beyond the capacity limits of a single SQL Azure database.

Ø It can horizontally scale out to many nodes.

Ø It can go beyond the limits of a single Azure database.

Multi-Tenancy

Ø It is favorable for very small tenants and very large tenants.

Ø Tenant management is very easy with federation.

Ø Tenant placement can be changed at any time without downtime.

Online Repartitioning

Ø Federation allows online repartitioning of data through ALTER FEDERATION T-SQL commands.

Ø By repartitioning SPLIT operation data can be moved to new federation members without downtime.

Federation and Related terms difference

Federation & NoSQL - Federation and NOSQL are different. It uses SQL within each federation member. It supports Parallel Queries and Map Reduce patterns same as what NoSQL offers.

Federation & Partition - Federation and partition are similar but there exist a difference. In partitioning SQL Server gives us an automatic way to query all the partition. In federation there is no automatic way; Federation members don’t talk to each other.

Just take an example. Divide a big table into 2 tables and keeps them into a single database and query them separately that is really a federation. It means 2 tables look like to be in separated databases and they don’t talk to each other. You can’t do the join between 2 tables too.

Scenario where Federation can be used

Web Scale Database Solutions

Ø Gaming

Ø Social Networking sites

Ø Online electronic stores

Ø Travel reservation system

Ø NOSQL Applications

Applications which

Ø Need big data

Ø Big scale

Ø Massive parallelism

Ø Eventual consistency & semi-structured data

Software Vendors Implementing Multi-tenant SaaS Solutions

Very Huge Partitioned Data Mart

Federation Architecture & Design

Federation Root: It is a database that contains the federation database object. It stores federation metadata information. All application connects to federation root first. Root database is the central repository for information about distribution of scaled-out data.

Federation Member: Federation use system managed SQL Azure databases to achieve scale-out named federation members. Federation members provide the computational and storage capacity for parts of the federations workload and data. Collection of all federation members in a federation represents the collection of all data in the federation. Federation members are managed dynamically as data is repartitioned. Administrators decide how many federation members are used at any point in time using federation repartitioning operations.

Each federation member is a single database. The CREATE FEDERATION process creates a new database based on the root database. When you create a new federation you have to give the name of the federation (not the name of the physical database, which is a System-GUID), the name for the distribution key, and distribution data type on which data will be federated on. The valid distribution data types are int, bigint, uniqueidentifier and varbinary (up to 900 bytes).Varbinary can be used if you want to define string or characters federation. You need to cast string to varbinary and then you can create a string federated member. Currently only Range Distribution Type is supported. Probably in next future release we expect Azure should support HASH Distribution Type also.

SalesDB is the root database with many federations.

Federation Distribution Key: Each federation has a single key for federating or we can say partitioning data. In the federation definition, the distribution key represented by 3 properties:

A distribution key label that is used for referring to the key,
A data type to specify the valid data domain for the distribution such as uniqueidentifier or bigint, and
A distribution type to specify the method for distributing the data such as ‘range’.

Federation Atomic Unit: All data for a value of a federation key stored in the same federation member. It represents all data that belongs to a single instance of a federation key.

Federation design is de-normalized rather than usual normalized design.

There are three main types of tables we have to consider while designing federation:

Entity/Federated Table: Refer to tables that contain data that is distributed by the federation. Tables that will be split based on the federation and their federation columns.

Ø It is subset in each federation member.

Ø Must Use Entity Key FEDERATED ON CLAUSE.

Ø Federated by entity key – must be part of a primary key.

Reference table: Refer to tables that contain reference information to optimize lookup queries in federations. Reference tables are created in federation members and do not contain any FEDERATED ON annotation.

Ø Tables that will not be split, but should be referred by the federated table.

Ø These tables will be copied across all federation members.

Ø Exist in each federation member in entity.

Example would be a Product table or other dimension tables.

Important Note -When you do a split of a federation member than reference table will be replicated automatically to new federation member. If you want to insert/update reference table than you have to do it individually for each reference table in each federation member. There is no way to keep reference table in sync automatically.

Global Central Table: Refer to tables that are created in the federation root for typically low traffic or heavily cached data that does not need to be scaled-out.

Ø Tables that will not be split, and very rarely join-select with the tables in federation members.

Ø Exist in federation root – Good for storing metadata.

Limitation of Federation

Cross-database joins are not supported in SQL Azure and it is a typical behavior of SQL Azure.
SQL Azure federations don’t support Identity on a column while creating new table.
If you shard your data into N number of federation members and then decide you need to make a schema change, you’ll have to modify the schema on each federation member database.
There should be a primary key on a table of a source database if you want to do migration to SQL Azure federation.
Merge is not supported in SQL Azure federation currently; probably it should be accommodated in next future release. If you want to do merge than

First Backup the federation member database somewhere on SQL Azure.
Now drop the federation member.
Now insert backup data into specific range.

Sync Services of SQL Azure is not supported.
Azure import-export is also not supported.
SQL Server 2012 data tools (SSDT) is also not supported.
Joins, grouping, aggregation should be done by application and to do the same fan out queries can be used.
There is no automatic fan out query is currently supported in Azure.
Federation keys are immutable.

Some Useful System Views

Standard system views use in day to day federation operation

sys.federations represent the federations in a database.
sys.federation_distributions represent the definition of the federation distribution scheme for federations.
sys.federation_members represent members of the federations.
sys.federation_member_distributions provides the distribution details per federation distribution key per member.

Standard system views use in day to day to manage federation

sys.dm_federation_operations returns one row per SPLIT or DROP operation on a federation. Each row contains information on the progress and any error conditions for the operation.
sys.dm_federation_operation_errors returns rows containing information on errors that occur during SPLIT or DROP federation operations.
sys.dm_federation_operation_error_members returns the list of members involved in federation operations that failed due to errors.

Example

Connect to SQL Azure database through SSMS.

CREATE DATABASE FedDb

Above statement will create a federation root database.

Now connect to FedDB again using SSMS. You can’t switch to FedDB directly so you need to connect again.

Now you need to create a federation member with federation key. To create a federation you have to give a

name and federation key. Let us consider a scenario. Suppose you have a customer table where

CustomerKey is a primary key and table is very huge and you want to create a federation for it. As per

federation rule CustomerKey has to be a part of or whole thing of a primary key in every federated

tables.

So if we want to create a customer federation than CustomerKey should be chosen a federation key. Let us

assume that we also have a CustomerAddress table and it has a CustomerKey as a part of primary key.

Now assume we have Order and and OrderDetail table also. Order table has a CustomerKey as a part of

key, so it would be fine again. OrdeDetails table hasn’t CustomerKey so in this case we have to de-

normalize it and introduce CustomerKey into it. Now assume you have a Product table that is nowhere

related with CustomerKey, in this case product table will be created as a reference table.

CREATE FEDERATION CustomerFederation(CustId BIGINT RANGE)

Above statement will create a Federation with name CustomerFederation where CustId is a distribution key

with data type int and distribution type is range.

Before you create any table on federation member you have to execute below:

USE FEDERATION CustomerFederation (CustId=0) WITH RESET , FILTERING OFF

After executing above you can to create a customer table and syntax should be like below:

SET ANSI_NULLS ON

SET QUOTED_IDENTIFIER ON

CREATE TABLE [dbo].[Customer](

[CustomerKey] [int] NOT NULL,

[FirstName] [nvarchar](50) NULL,

[MiddleName] [nvarchar](50) NULL,

[LastName] [nvarchar](50) NULL,

CONSTRAINT [PK_Customer_CustomerKey] PRIMARY KEY CLUSTERED

(

[CustomerKey] ASC

)

) FEDERATED ON (CustId = CustomerKey)

SET ANSI_NULLS OFF

SET QUOTED_IDENTIFIER OFF

Please take a note of highlighted part. Now if you want to create a CustomerAddress table than syntax

should be like below:

SET ANSI_NULLS ON

SET QUOTED_IDENTIFIER ON

CREATE TABLE [dbo].[CustomerAddress](

[CustomerKey] [int] NOT NULL,

[AddressKey] [int] NOT NULL,

CONSTRAINT [PK_CustomerAddress_CustomerAddressKey] PRIMARY KEY CLUSTERED

(

[CustomerKey] ASC,[AddressKey] ASC

)

) FEDERATED ON (CustId= CustomerKey)

SET ANSI_NULLS OFF

SET QUOTED_IDENTIFIER OFF

Assume you have data loaded into Customer and CustomerAddress table by using some insert script.

To query federation data you should use below

USE FEDERATION CustomerFederation (CustId=0) WITH RESET , FILTERING OFF

SELECT * FROM Customer

Special Note: You connect to the root and from there you can connect to multiple federation members

means multiple databases, so you don’t need a multiple connection pool. Please remember that SQL Azure

doesn’t support linking databases as it happens in the SQL Azure Gateway.

If you want to query for a specific customer than you have to turn on FILTERING option and USE

FEDERATION statement will also change like below:

USE FEDERATION CustomerFederation (CustId=10) WITH RESET , FILTERING ON

SELECT * FROM Customer

SELECT * FROM CustomerAddress

Above query will just give you specific row with CustomerKey = 10 even though you select all from

federated tables, this is one of the beauty of federation and good example of atomic unit.

If you want to SPLIT federation you have to use following syntax:

USE FEDERATION ROOT WITH RESET

ALTER FEDERATION CustomerFederation SPLIT AT (CustId = 100)

BCP Syntax to load data in SQL Azure Federation

bcp.exe "<SysId of SQL Azure Federation Member>.<tablename>" in "<sourcefilepath>" -E -b 10000 -a 16384 -q -S <sql azure server name>.database.windows.net -U <SQL Azure User Name> -P <Password> -f "<format file path>" –E

Acknowledge

My sincere thanks to Bob Beauchemin, Cihan Biyikoglu, George Huey and msdn.

CONCAT in SQL 2012

2012-04-19T20:07:00.000-07:00

SQL Server 2012 introduces a brand new string function called CONCAT(). CONCAT() string function allows you to concatenate up to 255 string or variable values in to one single string. It requires a minimum of two input values when calling the function and takes care of implicitly converting the data values to a string by following the data type conversion rules of SQL Server 2012. This function would help eliminate the need of explicit data conversions when concatenating two values.

SYNTAX:

SELECT CONCAT (String_Value1, String_Value2, String_Value3 [, String_ValueN]) -- Between 2 and 254 values can be passed.

Example - CONCATENATION WITH CONCAT()

Declare @a Varchar(100)=Manish Kumar is '

Declare @b int=100

Declare @c varchar(200)=' times busy now.'

Select CONCAT (@a, @b, @c)

Returns:

Manish Kumar is 100 times busy now.

In above example you can see that no data conversion is required for integer variable @b.

NOTE: NULL values are implicitly converted to an empty string. If all the variables passed as parameters in CONCAT function are NULL, an empty string of type VARCHAR(1) is returned.

CONCAT function only works with SQL Server 2012 and will work for later versions.

Welcome IIF() to SQL Server Family

2012-04-18T20:33:00.000-07:00

IIF() function is new to SQL Server family of functions. It is a brand new logical function being introduced with SQL Server 2012 that allows you to perform IF..THEN..ELSE condition within a single function. This helps in simplified code and easy to read conditions.

In earlier versions of SQL Server, we have used IF..ELSE and CASE..END CASE to perform logical conditional operations. However, IIF() can be used as a shorthand way of writing conditional CASE statements in a single function. It evaluates the expression passed in the first parameter with the second parameter depending upon the evaluation of the condition and returns either TRUE or FALSE.

In this example, we will evaluate the values of two variables and return the result of a variable which is bigger in value.

DECLARE @M INT=40

DECLARE @V INT=30

SELECT IIF(@M > @V, M IS GREATER THAN V', 'V IS GREATER THAN M')

GO;

Executing the above T-SQL will return the following result:

-------------------

M IS GREATER THAN V

(1 row(s) affected)

Like CASE statements which can be nested up to 10 levels, IIF() function can also nest up to 10 levels.

MDM (Master Data Management) Business Scenario

2011-10-13T09:17:00.000-07:00

Introduction

Master Data Management (MDM), the process of creating and maintaining master data. As per definition Master Data Management (MDM) is a set of coordinated processes, policies, tools and technologies used to create and maintain accurate master data. Master data describes key area of business like customers, products, employee, suppliers, etc. It is stored in multiple, disconnect systems/databases. Unmanaged master data is inaccurate as well as incomplete. Unmanaged master data may lead to poor business decision because of discrepancies.

MDM is required in Relational Model as well as in Dimensional Model (OLAP, BI Analytical data model). In the Relational Model following are example of MDM:

· Customers, suppliers, employees, and sales representatives

· Products, equipment, assets, and stores

· Contracts, licenses, and bills of material

· Company locations and customer geographic divisions

Dimension data (Product, Customer, Employee, Department, Geographical regions, etc.) should also be considered as example in Dimensional model because master data can be used for both models.

Master data is a very important part of data. It is crucial and it must be known and correct. We can’t think of master data without quality. Master data typically changes with a much slower rate than transactional data. Master data follows classical CRUD cycle: Create Read, Update and Destroy.

Advantages

Advantage of MDM is categorized below based on area:

Sales

1. Single shared view of customer may lead to overall customer satisfaction, service and retention.

2. Sales people will be able to effectively up sell and cross-sell to customers because they get access to an accurate customer data.

Products

1. Centralized governance of corporate data will immediately launch new product.

2. MDM will facilitate the new product introduction to market faster. New product launch can be shorter and more agile.

3. Product data will be shared by supplier to customer on timely basis.

4. Increase visibility between suppliers and products.

Cost & Operations

1. MDM in procurement can stop duplication of products.

2. MDM can produce the same effect for the supplier part master, enabling companies to better understand at a macro level from whom they are buying.

3. Expanded margin and lower costs.

Risk Management

1. MDM can enable enterprise wide view of credit risk exposure. It will also reduce business exposure/risk.

2. MDM can increase capitalization on market opportunity with new product.

Reporting for executive

1. Faster, more-effective decision making affecting revenue, costs and agility.

2. Competitive advantage through process agility, and enterprise wide ability to adopt and adapt end-to-end business processes.

3. It will enable to manage resource effectively.

MDM Values

As per Gartner research MDM can bring value to the business falls into below three categories:

· Run the business: Resources consumed and focused on the continuing operation of the business. It includes all nondiscretionary expense as part of the "run the business" cost.

· Grow the business: Resources consumed and focused on developing and enhancing IT systems in support of business growth (typically, organic growth). Discretionary investments are included in the grow the business" cost.

· Transform the business: Resources consumed and focused on implementing technology systems that enable the enterprise to enact new business models. This is very much a "venture" category.

Scenarios

Data that needs versioning, auditing, or any other kind of maintaining of history, is typically master data. Finally, we give more attention to data that we reuse repeatedly. Re-usage increases value of data for us. The value of the data can increase because of other factors as well like quality and integration.MDM is useful when we need any kind of cross-system interaction, like doing analysis over data from multiple systems.

Below are the scenarios where MDM can be used effectively:

1. Customer data integration (CDI) to enable overall customer satisfaction, service and retention as well as increase sale people effectiveness.

2. Product information management to increase revenue various lead and cycle time reductions.

3. Procurement-oriented MDM to expanded margin and lower costs.

4. MDM in risk management can contribute to reduced business exposure and risk.

5. MDM for executives in a form of report can enhance decision making faster. It can contribute towards more-effective decision making affecting revenue, costs and Agility.

6. MDM in Business performance/agility as in service-oriented architecture (SOA)/data service environment can give competitive advantage through process agility, and enterprise wide ability to adopt and adapt end-to-end business processes.

7. Development of a product catalog for supporting selling processes.

8. Integration of multichannel selling, marketing, and service process unifying product, customer and location master data.

9. Support for a marketing campaign using multiple customer files for a year-end sales drive.

10. Unification of identity management (user application logon and security).

11. Business analytics framework to facilitate faster execution and realization of benefits from mergers and acquisitions.

12. Alignment of master data across BI, performance management and operational business applications.

13. Alignment of application development practices with business process management suite BPMS) tools.

14. Maintenance of product, price/discount, packaging and configuration rules for reference by selling and customer support processes.

15. Support for field maintenance and warranty services over the lifetime of a product and customer relationship.

16. Migrating of legacy and multiple ERP systems to a smaller number, or even one, ERP structure over a multiyear effort.

Conclusion

MDM can add value to the business in many different ways, most often indirectly, as an enabler for other business and IT initiatives. MDM enable broader and more complex data integration. It eliminates redundant data management activities and redundant integration activities. It also improves decision making, data quality and simplifies data integration. While creating a clean master list can be a challenge, but it will definitely bring many positive benefits.

Acknowledgement & References:

1. http://www.solidq.com/ce-en/AboutUS/Documents/DQMDM_SQL2008R2.pdf

2. Gartner Research Case Studies: Where MDM Adds Value to the Business by (Andrew White, John Radcliffe).

BI on SQL Azure

2011-07-20T05:13:00.001-07:00

Introduction

A newly emerged concept ‘cloud computing’ is a phenomenon which is quickly embedding into all sectors of IT industry. Data Warehouse (DW) and Business Intelligence (BI) is no exception and are already touching it. It’s right time to identify the potential of existing DW/BI applications post migration to Cloud. Below we will try to cover DW/BI’s compatibility with cloud. ETL, OLAP cube, physical architecture and reporting tools are the major DW/BI processes to be considered and kept in mind before migration. Below we are trying to map each of these processes with cloud respectively.

· ETL (Extract, Transform and Load) is mostly used process in DW. Here we generally receive large files in week-ends or out of business hours to be processed and loaded. It may be better to use the resources according to demand/requirement for the ETL instead of fixed capacity.

· OLAP (Online Analytical Processing) cubes are highly intensive to compute and require stronger processing. High Performance Computing and scalability is the main feature of cloud and it is aligned to OLAP cubes requirements.

· Massively Parallel Processing, back up valuable data, mirrored data and high availability are the architecture needs in DW/BI environment.SQL Azure platform have built in provisions for a architecture, with no physical administrator, high availability and built in fault tolerance.

· There are lots of reporting products/tools used in DW/BI environment. There isn’t a lot of existing support for all tools in cloud but Microsoft is continuously enhancing and improving SQL Azure as a platform as a service to support reporting and all other BI needs.

What can be migrated to cloud?

Now the question comes what can be migrated to cloud? The existing STAR/Snowflake schema design can be migrated as it is to the cloud. Storage allocations could be a challenge. In order to overcome this challenge, existing database’s space usage should be calculated and same amount of space should be requested on the cloud. The amount of data that can be stored in SQL Azure is unlimited but since there is a limit of 50 GB for size of business database, in-order to migrate data which is more than 50 GB, we need to break it in parts and keep it across multiple databases each of 50 GB. Now if you are thinking how this data which is kept across multiple databases can be accessed. The solution lies in skills to generate queries. Parallel queries can be used to access this data.

Once a highly scalable database infrastructure is setup on SQL Azure platform, we can use traditional bcp tool or bcp utility of SQL 2008 R2 to move data from the existing on-premise (headquarters) DW to SQL Azure. SQL Server 2008 R2 Import and Export Data Wizard can also be used. SQL Server Migration Assistant (SSMA for Access v4.2) supports migrating your schema and data from Microsoft Access to SQL Azure.

Once we load DW data on cloud then we need a continuous process to load day to day operational data from various sources on cloud. We could build an ETL framework between office (on premise) and cloud. On premise ETL framework (SSIS packages) should be used to sync data as cloud doesn’t support Business Intelligence Development Studio for now. There could be various choices to sync data and one with minimum cost should always be preferred.

· SQL Azure to SQL Azure synchronization

· Office Head Quarter (on-premise) to cloud

· Cloud to Office Head Quarter (on-premise)

· Bi-directional

Alternatively, Windows Azure Appfabric Integration can be used as an ETL platform. Of course it is not a good choice but alternate option to use Appfabric integration instead of SSIS packages.

At this time there is no direct support for OLAP and CUBE processing on SQL Azure; alternatively with the HPC (High Performance Computing) attributes using multiple Worker roles, manually aggregation of the data can be achieved. It is interesting to see how OLAP cube processing can fit on cloud? Grid computing attribute of cloud can be used in processing of OLAP cubes. Grid computing is a type of parallel and distributed system that enables the sharing, selection, and aggregation of geographically distributed "autonomous" resources dynamically at run time depending on the work load and availability requirements. We could implement Grid Computing in a cloud computing environment and it is a valid to say that "every cloud is a grid". With appropriate support from Cloud Computing Platform and with work load management, a Cloud platform can be effectively used as a Grid to effectively process the OLAP. It is not so worthy as lots of task similar to BI engine needs to be created. It is just an idea that HPC is an important feature of cloud and same can be utilized to process OLAP cube.

SQL Server Reporting Services tool is supported by cloud but it is in early stage. Limited CTP of the new SQL Azure Reporting is kicked off late last year and it is getting a good response. We can build as well as use these reports in cloud with a small limitation that SQL Azure Reporting currently can connect only to SQL Azure database. I believe SQL Server Reporting Services tool should have a great feature on SQL Azure. However, SQL Azure is supporting Excel and Power pivot which makes it very useful and powerful.

Suitability

SQL Azure is suitable for small and medium size (SMB) companies. It can also be suitable for other applications/scenarios which are listed below:

· It can be used for Proof of concepts (POCs) for simple applications.

· New Start up firms can use SQL Azure to avoid initial capital investments on hardware and software licenses.

· Educational and Training institute can use SQL Azure to reduce their infrastructure cost.

· Generally in DW data comes from multiple sources and there might be a need to share some data across customer, partners, vendors and employee. SQL Azure suits best here. The consolidated data should be pushed in SQL Azure and then share across customer, partners, vendors and employee.

· It can also used by the organizations which use their application on quarterly/half yearly/yearly interval. CBSE/ICSE and state boards declare their result annually, IT computation and declaration are done once in a year. Here SQL Azure should be the best choice.

Limitation

A limitation of SQL Azure Database that cannot be missed to mention is that it doesn’t support all of the features and data types that are found in SQL Server. Analysis Services, Replication, and Service Broker, of Business Intelligence Development Studio (BIDS) are the services that are not currently provided on the Windows Azure platform.

Conclusion

· On Premises ETL solution can be used on SQL Azure.

· There is very limited support for OLAP as of now. All BI components of Business Intelligence Development Studio (BIDS) should be supported by cloud including usage based aggregations, portioning etc. These value added features are desired and we expect Microsoft should implement them.

· SQL Azure should support all features which are available in normal database version.

· Elasticity, Scalability and Reliability supports physical architecture required for DW/BI.

· SSRS, Excel and Power Pivot reports are supported on SQL Azure.

· SQL Azure can be used by small and medium level organization.

· It can also be used for high volume of data by overcoming the size limitation of SQL Azure database.

· As per current scenario Microsoft Cloud Platform seems to be positioned as one of the leading platforms for DW/BI because it demonstrates great potential in early stage. We expect a great future of SQL Azure for DW/BI if Microsoft promises to enhance it continuously to overcome its limitation.