Apache Cassandra

Introduction

The Apache Cassandra database is the right choice when you need scalability and high availability without compromising performance. Linear scalability and proven fault-tolerance on commodity hardware or cloud infrastructure make it the perfect platform for mission-critical data. Cassandra's support for replicating across multiple datacenters is best-in-class, providing lower latency for your users and the peace of mind of knowing that you can survive regional outages.

Cassandra's data model offers the convenience of column indexes with the performance of log-structured updates, strong support for denormalization and materialized views, and powerful built-in caching.

Apache Cassandra is a highly scalable and high-performance distributed database
management system that can serve as both an operational datastore (the “system of record”) for
online/transactional applications, and as a read-intensive database for business intelligence
systems. Cassandra is able to manage the distribution of data across multiple data centers and
offers incremental scalability with no single points of failure.
Cassandra is a logical choice for enterprises that need high degrees of uptime, reliability, and
very fast performance.
Cassandra was originally incubated at Facebook and is based upon Google’s BigTable and
Amazon’s Dynamo software. The end result is an extremely scalable and fault-tolerant data
infrastructure that solves small to big data problems, handles write intensive user traffic, delivers
sub-millisecond caching layer reads, and supports demanding workloads involving petabytes of
data.

Cassandra Architecture

Cassandra is a peer-to-peer distributed data management system where every
node is essentially the same with respect to how it functions in the cluster. In Cassandra, there is
no concept of a “master node” or anything similar, with the benefit being derived that no single

point of failure exists for any key process or function.

The scale-out aspect of Cassandra allows node additions to occur with no disruption to
application uptime. Cassandra automatically partitions data across nodes once one or more
nodes have been added to a cluster and “seeds” the new nodes from existing machines in the
cluster.Data redundancy to protect against hardware failure and other data loss scenarios is also built
into and managed transparently by Cassandra.

An administrator, architect, or developer only has to specify a replication and data-partitioning
strategy. From there, Cassandra takes care of everything.
All nodes in the cluster communicate with each other through the gossip protocol. If a node goes
down, the cluster detects the failure and automatically routes user requests away from the failed
machine. Once the failed node is operational again, it rejoins the cluster, and its data is brought
back up to date via the other nodes.

Why Cassandra

• MySQL drives too many random I/Os
• File-based solutions require far too many locks

The new face of data

• Scale out, not up
• Online load balancing, cluster growth
• Flexible schema
• Key-oriented queries
• CAP-aware

CQL Language

CQL provides a very similar syntax to that used in all RDBMSs, making it very easy for
developers and administrators coming from the relational world to begin working with Cassandra.

DDL, DML, and SELECT functionality all can be found in CQL.

cqlsh> CREATE TABLE monkeySpecies (

    species text PRIMARY KEY,
    common_name text,
    population varint,
    average_size int
) WITH comment='Important biological records'
   AND read_repair_chance = 1.0;

CREATE TABLE timeline (
    userid uuid,
    posted_month int,
    posted_time uuid,
    body text,
    posted_by text,
    PRIMARY KEY (userid, posted_month, posted_time)
) WITH compaction = { 'class' : 'LeveledCompactionStrategy' };

cqlsh> INSERT INTO users (KEY, password) VALUES ('jsmith', 'ch@ngem3a');

cqlsh> SELECT * FROM users
... WHERE gender='f' AND
... state='TX' AND
... birth_year='1968';

Batched Operations
Cassandra supports tunable consistency on a per-operation basis, meaning developers can
choose how strong or loose they want data consistency to be for a particular request. If a
developer wants to apply a certain consistency level for a number of different requests, he or she
can encase them in a BEGIN and APPLY BATCH statement.

BEGIN BATCH USING CONSISTENCY QUORUM
INSERT INTO users (KEY, password) VALUES (‘user1’, ‘mypass’)
UPDATE users SET password = ‘newpass’ WHERE KEY = ‘user1’
INSERT INTO users (KEY, password) VALUES (‘user2’, ‘user2pass’)
DELETE name FROM users WHERE key = ‘user5’
APPLY BATCH

Batched operations allow a developer to retry (if necessary) a group of changes in an idempotent
fashion.

Cassandra highlights

• High availability
• Incremental scalability
• Eventually consistent
• Tunable tradeoffs between consistency and latency
• Minimal administration
• No SPF (Single Point of Failure)

Applications suitable to use Cassandra 

Dispersed applications that need to serve numerous geographies with the same fast response times

Web online applications or 

other systems needing around-the-clock transactional input capabilities.

Applications needing extreme degrees of uptime and no single point of failure

Applications that need easy data elasticity, so capacity can be added to service peak workloads for various periods of time and then shrink back 

when user traffic reduction allows – all done in an online fashion

Write-intensive applications that must take in large volumes of data continuously e.g.credit card systems, music download purchases, device/sensor data, web clickstream,data, archiving systems, event logging.

Management of large data volumes (terabytes-petabytes) that must be kept online for query access and business intelligence processing.

Systems that need to store and directly deal with a combination of structured,unstructured, and semi-structured data, with a requirement for a flexible schema/data
storage paradigm that allows for easy and online structure modifications

Essential Guidelines for Choosing a Business Intelligence

Implementing a Business Intelligence (BI) solution leads to dramatic operational improvements and benefits that far outweigh the investments in time, money, and personnel necessary to select, deploy, and maintain such an application.
The correct tool should fit the company like a glove.  It must be, amongst other things, adaptable, scalable, secure, affordable, and have the ability to report on a multitude of business trends.  It should inform of business practices and identify opportunities that allow the end user to make educated decisions.

Consolidation of data is the main drive in the development of business intelligence.  Data de duplication is a growing necessity due to the increase in mobility.  Organizations must now deal with increasing demands, higher degrees of accuracy, firmer controls and tighter timelines.  Companies must find a way to digest large data sets.  Automated reporting and notifications are essential in staying on top of the market trends.  The right business intelligence solution will include the ability to report, plan, monitor and analyze volumes of data.  In turn, this will allow for more strategic and informed business decisions.  The information can be processed and distributed on a company wide basis with even the simplest of templates and visuals.  In this modern age, it is essential for all employees to engage in the analytical process of information to some extent.   Reporting capabilities should be flexible and display actionable data sets.  The ultimate objective is to develop long term goals based on the data produced through business intelligence.

 

The Business Intelligence Technology Stack
To build a Business Intelligence solution, enterprises will need to consider new investments and upgrades to current technology to build out the BI technology stack.

Storage and computing hardware - To implement BI, firms will need to invest or upgrade their data storage infrastructure.

Applications and data sources - To develop an effective BI solution, source data will need to be scrubbed and organized. The challenge is that source data can come from any number of applications, most using proprietary data formats and application-specific data structures.

Data integration- Extraction, transformation and loading (ETL) tools pull data from multiple sources, and load the data into a data warehouse. Again, the trend in data integration and Enterprise Application Integration, in general, is toward standardization through XML and web services.

Relational databases and data warehouses -Firms will need a data warehouse to store and organize tactical or historical information in a relational database. Organizing data in this way allows the user to extract and assemble specific data elements from a complete dataset to perform a variety of analyses.

OLAP applications and analytic engines -  Online analytic processing (OLAP) applications provide a layer of separation between the storage repository and the end user's analytic application of choice. Its role is to perform special analytical functions that require high-performance processing power and more specialized analytical skills.

Analytic applications -Analytic applications are the programs used to run queries against the data to perform either "slide-and-dice" analysis of historical data or more predictive analyses, often referred to as "drill-down" analysis. For example, a customer intelligence application might enable a historical analysis of customer orders and payment history. Alternatively, users could drill down to understand how changing a price might affect future sales in a specific region.

Information presentation and delivery products - The results of a query can be returned to the user in a variety of ways. Many tools provide presentation through the analytic application itself and offer dashboard formats to aggregate multiple queries. Also, enterprises can purchase packaged or custom reporting products, such as Crystal Reports. An important trend in BI presentation is leveraging XML to deliver analyses through a portal or any other Internet-enabled interface, such as a personal digital assistant (PDA).

 

 
1. Is the package a complete solution?
Complete BI solution is how quickly your own data can be leveraged to produce reporting, analytics, visualization, and easy integration with a variety of disparate data sources.  It should go beyond to predefined queries to ad hoc queries and dynamic selection by user.
2.  Is the solution easy to use and administer?
It’s important that ongoing use of your selected system minimizes the amount of IT involvement required. One of the biggest burdens on IT is the mandatory data warehouses necessitated by many BI solutions
3. Investment in hardware and resources
Can we use existing technology, hardware, people resources.
Given the investment in hardware, software, training, and opportunity costs, it’s vital that the selected technology offer as short a path to productivity as possible. To attain a sufficient ROI, users will need to derive value very quickly.
4.Will the solution scale?
Even the most successful BI implementation can run into difficulties when faced with increased data volumes and usage loads. Workloads increase for a number of reasons, including natural data volume growth, selecting additional dimensions for analysis, and incorporating new data sources.
5. Self-Service BI Is About More Than Interactivity- Organizations constantly struggle with their data. Integrating, managing, and verifying data sources are continuous exercises required for businesses looking at ways to increase their competitive advantage and understand what is occurring within their organization’s daily operations. 
Why So Many BI Projects Fail?
1. A confusing product landscape - Confuse between requirement  i.e. reporting Vs  analytics
2. BI Cost model - Complicate the job of estimating a return on investment for the substantial BI outlays.
3.Operational limitations - Some BI software require very lengthy deployments users often complain of bloated feature sets, static reports and queries, and built-in IT when it’s time to modify or expand the inventory of queries and reports. On the other hand, point solutions regularly suffer from considerable amounts of missing functionality. 

 

Database supporting different languages

As we all know, many global industries wants to increase their business worldwide and grow at the same time, they would want to widen their business by providing services to the customers worldwide by supporting different languages like Chinese, Japanese, Korean and Arabic. Many websites these days are supporting international languages to do their business and to attract more and more customers and that makes life easier for both the parties.
To store the customer data into the database the database must support a mechanism to store the international characters, storing these characters is not easy, and many database vendors have to revised their strategies and come up with new mechanisms to support or to store these international characters in the database. Some of the big vendors like Oracle, Microsoft, IBM and other database vendors started providing the international character support so that the data can be stored and retrieved accordingly to avoid any hiccups while doing business with the international customers.
The difference in storing character data between Unicode and non-Unicode depends on whether non-Unicode data is stored by using double-byte character sets. All non-East Asian languages and the Thai language store non-Unicode characters in single bytes. Therefore, storing these languages as Unicode uses two times the space that is used specifying a non-Unicode code page. On the other hand, the non-Unicode code pages of many other Asian languages specify character storage in double-byte character sets (DBCS). Therefore, for these languages, there is almost no difference in storage between non-Unicode and Unicode.

Collation itself specifies the rules for how strings of character data are sorted and compared. The rules for sorting data vary depending on the language and locale.
For example if you was to use a Lithuanian collation, the letter "Y" would appear between "I" and "J" if sorted. And if using the traditional Spanish collation "ch" would be sorted at the end of a list of words beginning with "c".

You can specify collations at following:
1. Creating or altering a database.
2. Creating or altering a table column.
You can specify collations for each character string column using the COLLATE clause of the CREATE TABLE or ALTER TABLE statement. You can also specify a collation when you create a table using SQL Server Management Studio. If you do not specify a collation, the column is assigned the default collation of the database.
3.Casting the collation of an expression.
You can use the COLLATE clause to apply a character expression to a certain
collation. Character literals and variables are assigned the default collation of
the current database. Column references are assigned the definition collation of
the column.
4.When restoring or attaching a database, the default collation of the database and
the collation of any char, varchar, and text columns or parameters

The COLLATE clause can be applied only for the char, varchar, text, nchar,
nvarchar, and ntext data types.

You can execute the system function fn_helpcollations to retrieve a list of all the

valid collation names for Windows collations and SQL Server collations:
SELECT name, description
FROM fn_helpcollations();

Use of nchar, nvarchar, nvarchar(max), and ntext is the same as char, varchar, varchar(max), and text, respectively, except:
Unicode supports a wider range of characters.
More space is needed to store Unicode characters.
The maximum size of nchar and nvarchar columns is 4,000 characters, not 8,000 characters like char and varchar.
Unicode constants are specified with a leading N, for example, N'A Unicode string'

The collation on the database  to accept the Russian characters using the CYRILLIC_GENERAL_CI_AS collation set.  The CI stands for CASE INSENSTIVE and the AS
stands for ACCENT SENSTIVE.

Amazon RDS for Microsoft SQL Server

Amazon RDS frees you up to focus on application development by managing time-consuming database administration tasks including provisioning, backups, software patching, monitoring, and hardware scaling.

You can run Amazon RDS for SQL Server under two different licensing models – “License Included” and “License Mobility through Software Assurance (or Bring Your Own License – BYOL)”.
"License Included" pricing starts at $0.035 per hour and is inclusive of software, underlying hardware resources, and Amazon RDS management capabilities.

Microsoft’s License Mobility program allows customers who already own SQL Server licenses to run SQL Server deployments on Amazon RDS.

Amazon RDS for SQL Server DB Instances can be provisioned with either standard storage or Provisioned IOPS storage. Amazon RDS Provisioned IOPS is a storage option designed to deliver fast, predictable, and consistent I/O performance, and is optimized for I/O-intensive, transactional (OLTP) database workloads.

Amazon Web Services

Amazon Elastic Compute Cloud (Amazon EC2) is a web service that provides resizable compute capacity in the cloud. It is designed to make web-scale computing easier for developers. Amazon Elastic Block Store (EBS) provides persistent storage to Amazon EC2 instances.


Amazon S3 is storage for the Internet. It is designed to make web-scale computing easier for developers.

Amazon RDS is a web service that makes it easier to set up, operate, and scale a relational database in the cloud.

Amazon DynamoDB is a high performance non-relational database service that is easy to set up, operate, and scale. It is designed to address the core problems of database management, performance, scalability, and reliability. It also provides predictable high performance and low latency at scale.

Amazon SimpleDB is a web service providing the core database functions of data indexing and querying in the cloud.

mazon Simple Queue Service (Amazon SQS) offers a reliable, highly scalable, hosted queue in the cloud.

Amazon Simple Email Service (Amazon SES) is a highly scalable and cost-effective bulk and transactional email-sending service for businesses and developers.

Amazon Glacier is an extremely low-cost storage service that provides secure and durable storage for data archiving and backup. It is optimized for data that is infrequently accessed and for which retrieval times of several hours are suitable.

Amazon CloudFront is a web service for content delivery. It delivers your content using a global network of edge locations and works seamlessly with Amazon S3 which durably stores the original, definitive versions of your files.

Amazon ElastiCache is a web service that makes it easy to deploy, operate, and scale an in-memory cache in the cloud. It is protocol-compliant with Memcached, so code, applications, and tools that you use today with your existing Memcached environments work seamlessly with the service.

Amazon CloudWatch is a web service that enables you to monitor your Amazon EC2 instances, Amazon EBS volumes, Elastic Load Balancers, and Amazon RDS database instances in real-time. You can also supply your own custom application metrics. With Amazon CloudWatch you can access up-to-the-minute statistics, view graphs, and set alarms for your metric data.

Amazon Virtual Private Cloud (Amazon VPC) is a secure and seamless bridge between a company's existing IT infrastructure and the AWS cloud.


Amazon Elastic MapReduce is a web service that enables businesses, researchers, data analysts, and developers to easily and cost-effectively process vast amounts of data.

AWS Import/Export transfers large amounts of data directly onto and off of storage devices using Amazon's high-speed internal network and bypassing the Internet.

Storing Log Data using MongoDB

This blog outlines the basic patterns and principles for using MongoDB as a persistent storage engine for log data from servers and other machine data.Servers generate a large number of events (i.e. logging,) that contain useful information about their operation including errors, warnings, and users behavior. By default, most servers, store these data in plain text log files on their local file systems.While plain-text logs are accessible and human-readable, they are difficult to use, reference, and analyze without holistic systems for aggregating and storing these data.

1. Schema Design
The schema for storing log data in MongoDB depends on the format of the event data that you’re storing.The preferred approach is to extract the relevant information from the log data into individual fields in a MongoDB document.When you extract data from the log into fields, pay attention to the data types you use to render the log data into MongoDB. Using proper types for your data also increases query flexibility: if you store date as a timestamp you can make date range queries, whereas it’s very difficult to compare two strings that represent dates. The same issue holds for numeric fields; storing numbers as strings requires more space and is difficult to query.When extracting data from logs and designing a schema, also consider what information you can omit from your log tracking system. In most cases there’s no need to track all data from an event log, and you can omit other fields.

2.System Architecture
Insertion speed is the primary performance concern for an event logging system. At the same time, the system must be able to support flexible queries so that you can return data from the system efficiently.
MongoDB has a configurable write concern. This capability allows you to balance the importance
of guaranteeing that all writes are fully recorded in the database with the speed of the insert.
For example, if you issue writes to MongoDB and do not require that the database issue any response, the writeoperations will return very fast (i.e. asynchronously,) but you cannot be certain that all writes succeeded.
The following command will insert the event object into the events collection.
>>> db.events.insert(event, w=0)
By setting w=0, you do not require that MongoDB acknowledges receipt of the insert. Although very fast, this is risky
because the application cannot detect network and server failures. See write-concern for more information.

Conversely,if you require that MongoDB acknowledge every write operation, the database will not return as quickly but you can be certain that every item will be present in the database.
In this case use pass w=1 argument as follows:
>>> db.events.insert(event, w=1)

Finally, if you have extremely low tolerance for event data loss, you can require that MongoDB replicate the data to multiple secondary replica set members before returning:
>>> db.events.insert(event, w=majority)

Sharding
Eventually your system’s events will exceed the capacity of a single event logging database instance. In these situations you will want to use a sharded cluster, which takes advantage of MongoDB’s sharding functionality.
In a sharded environment the limitations on the maximum insertion rate are:
• the number of shards in the cluster.
• the shard key you chose.
Because MongoDB distributed data in using “ranges” (i.e. chunks) of keys, the choice of shard key can control how MongoDB distributes data and the resulting systems’ capacity for writes and queries.
Shard key choices:

• Shard by Time
• Shard by a Semi-Random Key
• Shard by an Evenly-Distributed Key in the Data Set
• Shard by Combine a Natural and Synthetic Key

Choosing a Mobile BI Solution

Mobile BI Solution are helping remote employees/users  manage supply chains more efficiently or keeping traveling executives informed of the latest financial developments, today’s mobile ad hoc reporting solutions provide the dynamic capabilities organizations need to stay competitive and drive innovation in the field.
While working in the field used to mean relying on static data, today’s mobile BI solutions offer the ability to generate interactive reports with in-depth analytic functionality.

• Solutions which provide unified user experience across all devices are most suitable for Mobile BI solutions. 
• Rather than relying on static data, users should be able to use real-time updates to inform their decisions.
• Mobile BI solution should facilitates sharing reports, both over wireless networks and in person.
• Users may need to access mobile BI solutions from remote locations where internet connectivity is low or absent  or on a plane. While a lack of connectivity prohibits real-time updates, a good mobile BI offering should have some form of reliable offline access to recent and saved reports so that employees can tap into data-driven insights.