Google BigQuery- An externalized version of Dremel

So What is Google Big Query?? Its powerful Big Data analytics platform used by all types of organizations to run SQL-like queries against multiple terabytes of data in a matter of seconds. With this cloud based interactive query service we can handle web-sized amounts of data at blazing fast speed. 


Big Query (released in 2010)is actually the external or public implementation of one of the Google’s core technologies so-called Dremel .Big Query provides the features available in Dermel to third party conserving its unparalleled query performance. Both in fact share the same underlying architecture and performance characteristics. 

Big Query release made it possible to utilize the power of Dremel and to take advantage of Google’s massive computational infrastructure.

Let’s take a deeper look into power of Dremel… It is a query service that allows you to run SQL-like queries against very, very large data sets and get accurate results in mere seconds. You just need a basic knowledge of SQL to query extremely large datasets in an ad hoc manner.

Dermel runs through tens of thousands of servers simultaneously and makes it easy to analyse large amount of data such as a collection of web documents or a library of digital books or even the data describing millions of spam messages.

“According to Google’s paper, this has been used inside Google since 2006, with “thousands” of Googlers using it to analyse everything from the software crash reports for various Google services to the behavior of disks inside the company’s data centers”


The two core technologies that makes Dremel and BigQuery so fast is the Tree Architecture of Dremel And that the Data is stored in a Columnar Storage fashion in so doing, it gives very high compression ratio and scan throughput. 

So how to use data in Big Query or how to import data into Big Query:

• Upload your data to Google Cloud Storage
• Import the files to Big Query. Executed using command-line tool, Web UI or API, which can typically import roughly 100 GB within a half hour.

Other Important Features of Google Big Query:

• BigQuery is designed to handle structured data using SQL. Apart from SQL queries we can easily read and write data in Big Query via Cloud Dataflow, Spark, and Hadoop

• BigQuery provides extremely high cost effectiveness and full-scan performance for ad hoc queries and cost effectiveness compared to traditional data warehouse solutions and appliances.

• BigQuery is the best choice for ad hoc OLAP/BI queries that require results as fast as possible.

• BigQuery requires no capacity planning, provisioning, 24x7 monitoring or operations, nor does it require manual security patch updates. You simply upload datasets to Google Cloud Storage of your account, import them into Big Query, and let Google’s experts manage the rest.

If you like this post, please share it on google by clicking on the Google +1 button.

Please go through our latest post TOP 6 BIG DATA TRENDS IN THE NEAR FUTURE

Data Warehouse and Business Intelligence Books

Please refer the below link for downloading:

Author:The Kimball Group have written authoritative books on dimensional data warehousing and business intelligence. Hope this helps

Datawarehouse and business Intelligence e-books

Difference between ETL tool and OLAP tool

The main difference is the purpose for which ETL tool and OLAP tools are used.

ETL tools is meant for extracting the data from one or more than one source and loading into the Target tables.Before loading into the target it involves a lot of transformations to implement the business logics and standards.Also we have the staging area to clean the data and then load.Informatica and Datastage are few of the popular ETL tools

You can check about the popular ETL tools

OLAP tools are mainly meant for reporting purpose. It loads the data extracted and loaded by ETL from the target tables to the OLAP repository and then makes the necessary changes for creating a report out of it.These reports are viewed by the End user and they can check their Datawarehouse.Cognos and OBIEE are few of the powerful reporting tools

You can check about the Popular reporting Tools

List of Popular Reporting tools in Market

Reporting Tools

OBIEE

SAP Crystal Reports

Jaspersoft

Pentaho

Business objects

Pentaho reporting

BIRTS

Cognos

Microstrategy

Actuate

Qlikview

Proclarity

Excel

Crystal reports

Data Integrator 8.12 Pervasive

Transformation Server 5.4 IBM DataMirror

Transformation Manager  5.2.2 ETL Solutions

Differences between Datawarehouse schema and RDBMS schema?

RDBMS Schema	DWH Schema
Used in OLTP system like banking	Used for OLAP systems
Its Normalized using either of the normal forms	Its De normalized
Its difficult to understand the tables and understand in User perspective	Easy to understand and mainly used for data analysis and extracting the data.

What is a CUBE?Types of Cubes in DWH

Cubes
Cubes are data processing units composed of fact tables and dimensions from the data warehouse. They provide multidimensional views of data, querying and analytical capabilities to clients. A cube can be stored on a single analysis server and then defined as a linked cube on other Analysis servers. End users connected to any of these analysis servers can then access the cube. This arrangement avoids the more costly alternative of storing and maintaining copies of a cube on multiple analysis servers. linked cubes can be connected using TCP/IP or HTTP. To end users a linked cube looks like a regular cube.Linked cube are cubes in which a sub-set of the data can be analysed into great detail. The linking ensures that the data in the cubes remain consistent.

OLAP cubes
These are OLAP cubes created by clients, end users or third-party applications accessing a data warehouse, relational database or OLAP cube through the Microsoft® PivotTable® Service. E.g. Microsoft® Excel™ is very popular as a client for creating offline local OLAP cubes from relational databases for multidimensional analysis. These cubes have to be maintained and managed by the end users who have to manually refresh their data. 
PivotTable® Service
This is the primary component that connects clients to the Microsoft® SQL Server™ 2000 Analysis Server. It also provides the capability for clients to create local offline cubes using it as an OLAP server. PivotTable® Service does not have a user interface, the clients using its services has to provide its user interface.

Virtual cubes
These are combinations of one or more real cubes and require no disk space to store them. They store only the definitions and not the data of the referenced source cubes. They are similar to views in relational databases.

MOLAP cubes
MOLAP Cubes:   stands for Multidimensional OLAP. In MOLAP cubes the data aggregations and a copy of the fact data are stored in a multidimensional structure on the Analysis Server computer. It is best when extra storage space is available on the Analysis Server computer and the best query performance is desired. MOLAP local cubes contain all the necessary data for calculating aggregates and can be used offline. MOLAP cubes provide the fastest query response time and performance but require additional storage space for the extra copy of data from the fact table.ROLAP cubes
ROLAP Cubes:   stands for Relational OLAP. In ROLAP cubes a copy of data from the fact table is not made and the data aggregates are stored in tables in the source relational database. A ROLAP cube is best when there is limited space on the Analysis Server and query performance is not very important. ROLAP local cubes contain the dimensions and cube definitions but aggregates are calculated when they are needed. ROLAP cubes require less storage space than MOLAP and HOLAP cubes. 
HOLAP cubes
HOLAP Cubes:   stands for Hybrid OLAP. A ROLAP cube has a combination of the ROLAP and MOLAP cube characteristics. It does not create a copy of the source data however, data aggregations are stored in a multidimensional structure on the Analysis Server computer. HOLAP cubes are best when storage space is limited but faster query responses are needed.