Click here to close now.




















Welcome!

Related Topics: @CloudExpo, Microservices Expo

@CloudExpo: Article

A Cloud Computing Business Intelligence Organization

Moving data warehouses to the cloud

Data Warehousing As A Cloud Candidate
Over the past  year, we have started seeing greater support for Cloud from major vendors and Cloud is here to stay. The   bigger impact is that,  the path is clearly drawn for the enterprises to adopt Cloud.  With this in mind,  it is time to identify the potential for existing  data center applications  to be migrated to Cloud.

Most of the major IT majors predict a  HYBRID Delivery will be future,  where by the future enterprises needs to look  for a delivery model that comprises of certain work loads  on  Clouds  and some of them continue to be on data centers and then look for a model that will integrate  them together.

Before  we go further into  a  blue print of  How Data warehouses  fit  within a HYBRID Cloud environment, we  will see the salient features of  Data warehouses  and how the Cloud tenants  make them a  very viable work load to be  moved to Cloud.

A data warehouse  is a subject oriented, integrated, time variant and non volatile collection  of data in support of management's decision making process.

Data Warehousing  Usage

Cloud Tenant Value Proposition

ETL   (Extract, Cleaning, Transform, Load) process is  subject to variable patterns. Normally we may get large files over the week end or in night time to be processed and loaded.

It is better to  use the COMPUTE  resources on demand for the ETL as they require , rather than having a fixed capacity

OLAP (Online Analytical Processing) and related  processing needs for  MOLAP (Multi dimensional OLAP) and / or ROLAP (Relational OLAP) are highly compute intensive and requires stronger processing needs

High Performance Computing and ability to scale up on demand,   tenants of Cloud will be highly aligned to this need

Physical architecture needs are complex in a data warehousing environment.

  • MPP Servers (Massively Parallel Processing)
  • Shared Nothing Data Architecture
  • Mirrored Copies of Disk Space
  • High Availability Clustering

Most of the IaaS , PaaS offerings  like Azure platform, Amazon EC2 have built in  provisions for a highly available architecture, with most of the day to day administration  is  abstracted from the  enterprises.

The below are some of the advantages of SQL Azure Platform

  • No physical administration required - software installation and patching is included, as this is a platform as a service (PAAS)
  • High availability and fault tolerance are built in

Multiple Software and platform  needs,

  • Database Design Tools (STAR Schema Modeling)
  • ETL Tools
  • Data Cleansing Tools
  • OLAP Tools
  • Spatial Tools
  • Data Mining Tools
  • BI Reporting Tools

The product stack of  data warehousing  environment  is really huge and most organizations will normally find  it difficult to get into  a ideal list of software and platforms and tools  for their BI  platform. platform. SaaS for  applications like  data cleansing or address validation and PaaS for reporting like Microsoft SQL Azure reporting will be ideal to solve the tools and platform maze.

 

The following are the ideal steps for migrating  a  in-premise  data warehouse  system to a cloud platform, for the sake of case study , Microsoft  Windows Azure platform is chosen  as the target platform.

1. Create Initial Database / Allocate Storage / Migrate Data
The existing STAR Schema design  of the existing  data warehousing system can be migrated to  Cloud platform as it is.  And  migrating  to a  Relational database  platform like  SQL Azure should be straightforward. To migrate the data,   the initial  storage allocations of the existing  database on  the data center needs to be calculated and the same amount  Storage resources will be allocated on the Cloud.

You can store any amount of data, from kilobytes to terabytes, in SQL Azure. However, individual databases are limited to 10 GB in size. To create solutions that store more than 10 GB of data, you must partition large data sets across multiple databases and use parallel queries to access the data.

Once a high scalable database infrastructure is setup on SQL Azure platform , the following are some of the methods in which the data from the existing on-premise  data warehouses can be  moved to SQL Azure.

Traditional BCP Tool : BCP  is a command line utility that ships with Microsoft SQL Server. It bulk copies data between SQL Azure (or SQL Server) and a data file in a user-specified format. The bcp utility that ships with SQL Server 2008 R2 is fully supported by SQL Azure. You can use BCP to backup and restore your data on SQL Azure You can import large numbers of new rows into SQL Azure tables or export data out of tables into data files by using the bcp utility.

The following tools  are also useful, if you existing  Data warehouse is in  Sql Server within the data center.

You can transfer data to SQL Azure by using SQL Server 2008 Integration Services (SSIS). SQL Server 2008 R2 or later supports the Import and Export Data Wizard and bulk copy for the transfer of data between an instance of Microsoft SQL Server and SQL Azure.

SQL Server Migration Assistant (SSMA for Access v4.2) supports migrating your schema and data from Microsoft Access to SQL Azure.

2. Set Up ETL & Integration With Existing  On Premise Data Sources
After the initial load of the  data warehouse on Cloud,  it required to be continuously refreshed   with the operational data.  This process  needs to extract  data from different data sources (such as flat files, legacy databases, RDBMS, ERP, CRM and SCM application packages).

This process will also carry out necessary transformations such as joining of tables, sorting, applying  various filters.

The following are typical  options available in Sql Azure platform  to  build a  ETL platform between the On Premise and data warehouse hosted on cloud.  The tools mentioned above on the initial load of the data also holds good  for ETL tool, however they are not repeated  to avoid duplication.

SQL Azure Data Sync :

  • Cloud to cloud synchronization
  • Enterprise (on-premise) to cloud
  • Cloud to on-premise.
  • Bi-directional or sync-to-hub or sync-from-hub synchronization

The following diagram courtesy  of  Vendor will give a over view of how the SQL Azure Data Sync can be used for ETL purposes.

Integration provides common  Biztalk  Server integration capabilities (e.g. pipeline, transforms, adapters) on Windows Azure, using out-of-box integration patterns to accelerate and simplify development. It also delivers higher level business user enablement capabilities such as Business Activity Monitoring and Rules, as well as self-service trading partner community portal and provisioning of business-to-business pipelines.  The following diagram courtesy of the vendor shows how the  Windows Azure Appfabric Integration can be used as a ETL platform.

3. Create CUBES & Other Analytics  Structures
The multi dimensional nature of  OLAP requires a analytical engine to process the underlying data and create a multi dimensional view and  the success of OLAP has resulted in a large  number of vendors  offering OLAP servers using different architectures.

MLOAP :  A Proprietary multidimensional database with a aim on performance.

ROLAP :   Relational OLAP is a technology that provides sophisticated multidimensional analysis that is performed on open relational databases.  ROLAP can scale to  large data sets in the terabyte range.

HOLAP : Hybrid OLAP is an attempt to combine some of the features of MOLAP and ROLAP technology.

SQL Azure Database does not support all of the features and data types found in SQL Server. Analysis Services, Replication, and Service Broker are not currently provided as services on the Windows Azure platform.

At this time  there is no direct support for OLAP and CUBE processing on SQL Azure,  however with the HPC (High Performance Computing ) attributes  using multiple Worker roles,  manually  aggregation of the data can be achieved.

4. Generate Reports
Reporting consists of  analyzing the data  stored in the data warehouse in multiple dimensions and  generate standard reports for business intelligence and also generate ad-hoc reports.  These reports present data in graphical/tabular form and also provide statistical analysis features.  These reports should be rendered as Excel, PDF and other formats.

It is better to utilize the SaaS based or PaaS based reporting infrastructure rather than custom coding all the reports.

SQL Azure Reporting enables developers to enhance their applications by embedding cloud based reports on information stored in a SQL Azure database.  Developers can author reports using familiar SQL Server Reporting Services tools and then use these reports in their applications which may be on-premises or in the cloud.

SQL Azure Reporting  also currently can connect only to SQL Azure databases.

Summary
The above steps will provide a path to migrate   on premise  Data warehousing  applications to Cloud. As we needed lot of support from the  vendor in terms of IaaS, PaaS  and SaaS,   Microsoft Azure Platform is chosen as a platform to support the case study.  With several features  integrated as part of  this, Microsoft  Cloud Platform  positioned to be  one of the leading platform for BI on Cloud.

The following diagram  indicates a blue print of a  typical Cloud BI Organization on a Microsoft Azure Platform.

More Stories By Srinivasan Sundara Rajan

Srinivasan is passionate about ownership and driving things on his own, with his breadth and depth on Enterprise Technology he could run any aspect of IT Industry and make it a success.

He is a seasoned Enterprise IT Expert, mainly in the areas of Solution, Integration and Architecture, across Structured, Unstructured data sources, especially in manufacturing domain.

He currently works as Technology Head For GAVS Technologies.

Latest Stories
Mobile, social, Big Data, and cloud have fundamentally changed the way we live. “Anytime, anywhere” access to data and information is no longer a luxury; it’s a requirement, in both our personal and professional lives. For IT organizations, this means pressure has never been greater to deliver meaningful services to the business and customers.
Puppet Labs has announced the next major update to its flagship product: Puppet Enterprise 2015.2. This release includes new features providing DevOps teams with clarity, simplicity and additional management capabilities, including an all-new user interface, an interactive graph for visualizing infrastructure code, a new unified agent and broader infrastructure support.
For IoT to grow as quickly as analyst firms’ project, a lot is going to fall on developers to quickly bring applications to market. But the lack of a standard development platform threatens to slow growth and make application development more time consuming and costly, much like we’ve seen in the mobile space. In his session at @ThingsExpo, Mike Weiner, Product Manager of the Omega DevCloud with KORE Telematics Inc., discussed the evolving requirements for developers as IoT matures and conducte...
Container technology is sending shock waves through the world of cloud computing. Heralded as the 'next big thing,' containers provide software owners a consistent way to package their software and dependencies while infrastructure operators benefit from a standard way to deploy and run them. Containers present new challenges for tracking usage due to their dynamic nature. They can also be deployed to bare metal, virtual machines and various cloud platforms. How do software owners track the usag...
Explosive growth in connected devices. Enormous amounts of data for collection and analysis. Critical use of data for split-second decision making and actionable information. All three are factors in making the Internet of Things a reality. Yet, any one factor would have an IT organization pondering its infrastructure strategy. How should your organization enhance its IT framework to enable an Internet of Things implementation? In his session at @ThingsExpo, James Kirkland, Red Hat's Chief Arch...
Providing the needed data for application development and testing is a huge headache for most organizations. The problems are often the same across companies - speed, quality, cost, and control. Provisioning data can take days or weeks, every time a refresh is required. Using dummy data leads to quality problems. Creating physical copies of large data sets and sending them to distributed teams of developers eats up expensive storage and bandwidth resources. And, all of these copies proliferating...
Malicious agents are moving faster than the speed of business. Even more worrisome, most companies are relying on legacy approaches to security that are no longer capable of meeting current threats. In the modern cloud, threat diversity is rapidly expanding, necessitating more sophisticated security protocols than those used in the past or in desktop environments. Yet companies are falling for cloud security myths that were truths at one time but have evolved out of existence.
Digital Transformation is the ultimate goal of cloud computing and related initiatives. The phrase is certainly not a precise one, and as subject to hand-waving and distortion as any high-falutin' terminology in the world of information technology. Yet it is an excellent choice of words to describe what enterprise IT—and by extension, organizations in general—should be working to achieve. Digital Transformation means: handling all the data types being found and created in the organizat...
Public Cloud IaaS started its life in the developer and startup communities and has grown rapidly to a $20B+ industry, but it still pales in comparison to how much is spent worldwide on IT: $3.6 trillion. In fact, there are 8.6 million data centers worldwide, the reality is many small and medium sized business have server closets and colocation footprints filled with servers and storage gear. While on-premise environment virtualization may have peaked at 75%, the Public Cloud has lagged in adop...
SYS-CON Events announced today that HPM Networks will exhibit at the 17th International Cloud Expo®, which will take place on November 3–5, 2015, at the Santa Clara Convention Center in Santa Clara, CA. For 20 years, HPM Networks has been integrating technology solutions that solve complex business challenges. HPM Networks has designed solutions for both SMB and enterprise customers throughout the San Francisco Bay Area.
The Software Defined Data Center (SDDC), which enables organizations to seamlessly run in a hybrid cloud model (public + private cloud), is here to stay. IDC estimates that the software-defined networking market will be valued at $3.7 billion by 2016. Security is a key component and benefit of the SDDC, and offers an opportunity to build security 'from the ground up' and weave it into the environment from day one. In his session at 16th Cloud Expo, Reuven Harrison, CTO and Co-Founder of Tufin,...
The time is ripe for high speed resilient software defined storage solutions with unlimited scalability. ISS has been working with the leading open source projects and developed a commercial high performance solution that is able to grow forever without performance limitations. In his session at Cloud Expo, Alex Gorbachev, President of Intelligent Systems Services Inc., shared foundation principles of Ceph architecture, as well as the design to deliver this storage to traditional SAN storage co...
MuleSoft has announced the findings of its 2015 Connectivity Benchmark Report on the adoption and business impact of APIs. The findings suggest traditional businesses are quickly evolving into "composable enterprises" built out of hundreds of connected software services, applications and devices. Most are embracing the Internet of Things (IoT) and microservices technologies like Docker. A majority are integrating wearables, like smart watches, and more than half plan to generate revenue with ...
The Cloud industry has moved from being more than just being able to provide infrastructure and management services on the Cloud. Enter a new era of Cloud computing where monetization’s services through the Cloud are an essential piece of strategy to feed your organizations bottom-line, your revenue and Profitability. In their session at 16th Cloud Expo, Ermanno Bonifazi, CEO & Founder of Solgenia, and Ian Khan, Global Strategic Positioning & Brand Manager at Solgenia, discussed how to easily o...
The Internet of Everything (IoE) brings together people, process, data and things to make networked connections more relevant and valuable than ever before – transforming information into knowledge and knowledge into wisdom. IoE creates new capabilities, richer experiences, and unprecedented opportunities to improve business and government operations, decision making and mission support capabilities.