Amazon Web Services (AWS) pocket reference for Business Intelligence Architects / Architecture Design

Every large scale IT organization is organized in some form of verticals / Strategic Business Units (SBU), or in some other form. These may be grouped by geography / technology / industry groups etc. Almost inevitably every such organization has a cloud computing capability, and most of cloud based projects / architectures are designed and developed by this capability. This may work till you are working in the capacity of an architect for your own set of projects that just deal with your technology.

I believe that when one intends to grow as an enterprise architect, one needs to collaborate with SMEs from cross environments / technologies / platforms, and for the same one needs to have a good understanding of a variety of each of it.

Why Amazon Web Services (AWS) - AWS is probably the largest cloud player in providing IaaS. Azure and other such platforms have started providing IaaS recently, but their major strength is PaaS where they provide technology to build solutions and the infra is managed by them. If one intends to develop solutions that have a very broad mix variety of technologies, then one would have to opt a very strong IaaS cloud environment, than a PaaS environment.

Below are some of my quick notes on the world of Amazon Web Services, that one might want to keep in consideration while architecting BI solutions on AWS.

1) AWS has two types of clouds : Public / Virtual private cloud (VPC)

In public cloud servers are under AWS control, which can be configured by user. In VPC, servers are hosted within AWS but part of corporate network. IPs are under the control of the corporate network and security between the corporate network and servers hosted on AWS is the obligation of the corporate.

2) Amazon Simple Storage Service (S3) :

• Its an object store, where one can store any type of data in huge amounts, and the same can be accessed using the API provided by amazon for S3. 
• It's a highly available service, as it stores copies of data in multiple locations. It can be used as a staging location for migrating data across availability zones when using Elastic Block Store Disk.
• When data is stored into S3, the datatype is stored in a metadata tag. When a client accesses the data, it can check this tag to ensure that the data is read accordingly.
• S3 can store an object with max 5 GB in size. S3 objects can be accessed via REST/SOAP/HTTP. Third party tools are available to handle storage management inside S3.

3) Amazon Elastic Compute Cloud - EC2

• Provides scalable and flexible compute capacity EC2 instance provides interface to manage Amazon Machine Image (AMI, also known as bundle). Amazon, and other third party providers like RightScale, IBM and others provide ready images for use.
• Any software installation would be lost from EC2 instance, once the instance is "terminated". Persistent images are also available which can persist software changes, once the instance is stopped (but not terminated). These images are based on EBS or S3 instance store.
• If you use a SQL Server 2008 R2 AMI, then the license cost of SQL Server is included in the cost of running the instance. One cannot use their own purchased licenses to offset the cost of SQL Server license in a AWS provided SQL Server AMI.
• One can allocate static IP address to an instance using AWS "Elastic IP", and after that once can RDP to the same using the same IP / DNS every time. Without an Elastic IP, the IP address for the instance would change every time the instance is started and stopped. Elastic IPs are chargeable.

Billing types for EC2 instance

• Reserved Instance - This instance type requires reserving the instance for a fixed term. It includes an up-front cost, along with usage charges. This instance is cheaper than Unreserved instance.

• Unreserved Instance - This instance is billed on pay-per-use basis, but is comparatively expensive than Reserved Instance.

• Spot Instance - These are unique type of EC2 instances, which are basically amazon's way to handle spare capacity. You need to set a price and number of instances you need. When the average spot price falls below the price set by you, the instances would be allocated to your account. But downside is that once the average spot price rise above the price set by you, those instance would stop.

• In AWS, you are not billed for any data transfer between AWS components (for example data transfer between S3 and EC2). But for any data traffic that goes in and out of the instance using Internet, is billable. 
• Various categories of EC2 instances available like Micro, Standard, Cluster Compute, High-Memory Cluster, Cluster GPU, High Memory, High CPU, High Storage, High I/O etc. Also each of them have small, medium, large scaling for each category. A comparison can be seen from here: http://www.ec2instances.info , http://aws.amazon.com/ec2/instance-types/

4) Amazon Elastic Block Storage (EBS)

• Its the storage system / disk where EC2 instance would store and persist data. EBS is created, configured and managed out of EC2 instance and not within it. Even if an EC2 instance has been terminated, data stored on EBS would persist.
• EBS volumes can be 1 GB to 1 TB in size.
• EBS volume availability is restricted to the region and availability zone in which they are created. It's possible to make it available within a different zone by creating a snapshot of EBS and storing it into S3, and again creating a new EBS from the snapshot stored in S3. But EBS cannot be made available across regions by any means.
• One EC2 instance can have many EBS volumes, but one EBS volume cannot be shared by multiple EC2 instances.

5) Amazon Security Groups

• It provides a way to restrict access on EC2 instances, by configuring ports, ip and servers that can connect to an EC2 instance. It acts as a firewall for an EC2 instance.
• All the EC2 instance on which a security group is applied, does not become part of a common group / subnet.

6) Amazon CloudWatch

• Cloudwatch are of two types in AWS - Basic CloudWatch and Detailed CloudWatch.
• Basic CloudWatch is available with EC2 instance. It collects different performance metrics related to the EC2 instance.
• Detailed CloudWatch enables a detailed monitoring of EC2 instances, with alerts and notifications.

7) Amazon Elastic Load Balancing (ELB)

• Elastic Load Balancing can be used for two major purposes - Load balancing and Fault tolerance.
• As a load balancer it can distribute incoming traffic to different servers in a load balanced fashion.
• As a fail over balancer, it can detect a failed / unresponsive / unhealthy EC2 instance and route traffic to other instances as required.

8) Amazon Relational Database Service (RDS)

• Amazon RDS provides full featured database services using MySQL, Oracle as well as SQL Server database engine.
• RDS provides fault-tolerance / high availability by creating Multi-AZ Deployments. With this option, one instance of RDS is created in the availability zone selected by user, and second instance is created in an alternative availability zone. Both instances are kept upto date in parallel. The second instance is not visible / available, until the first instance becomes unavailable, and when it does, the second instance takes over immediately.
• RDS instance can be configured to create Read Replica which are copies of the RDS instance, that can be used for reporting purposes.
• RDS instances are backed up by default in AWS and this backup remains available for a limited time. Backups are totally configurable and can be persisted indefinitely too.

9) Amazon Simple Notification Service (SNS)

• Amazon SNS is a publish and subscribe model using which systems or user can generate and/or receive alerts and/or notifications.
• There are three methods in which alerts / notifications are delivered: Email / Http based web service call / A message via Simple Queue Service (SQS).

10) Amazon CloudFront

Its the Content Delivery Network of AWS that distributes and caches content at the nearest servers based on user request patterns.

11) Amazon Elastic MapReduce (EMR)

• Amazon EMR provides features to process large amounts of data using Hadoop based processing combined with other AWS products.
• EMR also provides option to run HBase (column oriented, distributed, NoSQL database) on Hadoop clusters which enables real-time data access to Hadoop in cloud.

12) Amazon Identity and Access Management (IAM) and Amazon CloudFormation provides means to control permissions to AWS resources as well as manage AWS resources as a system respectively. Amazon Route 53 is a highly available and scalabe Domain Name System (DNS) management service that can be used with AWS IAM to manage domains with faster performance.

SQL Azure Reporting versus SSRS : Challenges in cloud based reporting

SQL Azure Reporting is soon expected to be available on Azure platform, and as the name suggests, it is expected to cater reporting out of SQL Azure. A nice comparison of cloud based SQL Azure reporting and on-premises SQL Server Reporting Services can be read from this page.

Nice part about this cloud based reporting is that one can author report from cloud and host it on the cloud. But when authoring is done on the cloud, the only data source available would be SQL Azure. This makes the scope quite limited for the use of such reports. Earlier too, it was possible to create reports out of SQL Azure databases using SSRS, and one of my popular articles on how to implement the same can be read from here. Instead of being a PaaS / SaaS solution, I see it just as an extension to SQL Azure feature set, something similar to export data functionality. Support for reporting from on-premise data using WCF / RIA services would have made this reporting solution much admirable.

It's also being heard on a few blogs, that one can now wash away their hands from maintaining the reporting infrastructure as reporting is available on cloud, but there are a lot of questions before this statement. In my views, there must be some exceptional reasons to implement a reporting infrastructure for a cloud based database. Instead of it, the best solution is a PaaS / SaaS reporting solution.

Probably it's too early to make any comments right now, and you can get more information from this PDC video. In summary the good news is that it's the first ray of hope to see BI related features and services on cloud. You can sign up for the SQL Azure Reporting CTP from here.

Windows Azure Market Place ( Data Market and App Market ) - Potential jobs for BI Tools

Windows Azure Market Place is a cloud based service offering on the Azure platform from Microsoft . For those who are completely unaware of Azure, I can say that I find it synonymous to Amazon Web Services in the making. Services available or to-be available on this platform can be divided in two parts: Data Market and App Market.

Data Market which was formerly know as Microsoft Codename "Dallas" is now commercially available, and this announcement was made very recently. Also many other significant announcements for the Windows Azure Market Place, which can be read from here. App Market which is another section of services related to application development and hosting would be commercially released, probably early next year.

My area of interest revolves around data market. Data Market being a platform of data / content providers is a potential future candidate to be considered as a vital data source on the cloud. Traditionally we were used to see data sources as only in-premise databases like SQL Server, Oracle, Teradata, DB2 and others. But with the advent of cloud, we also see SQL Azure as a data source today. In my views if this platform evolves considerably, there is a bright scope that this is the probable new data source on the cloud.

Also this opens up jobs for MS BI Tools, especially SSIS and SSRS. I definitely see a requirement to merge data from different data providers that one might have subscribed, and create reports which is facilitated by Excel / Excel Add-In presently, can be replaced by SSRS. Data access from this platform is facilitated through REST APIs or OData, and MS BI Tools lack this support. But by the time DataMarket gains adoption as well as maturity from partners and consumers, we can expect MS BI tools to provide support for the same. Powerpivot is already aligned by Microsoft to serve the data analysis needs. Interestingly Tableau which is one of the leaders in data analysis software with it's huge range of data visualizations, is one of the technology partners and is providing support for data analysis from DataMarket. Check out the announcement of integration between Tableau and DataMarket from here.

To have a first look at the DataMarket section of Windows Azure Market Place, check out this video.

Use of multiple cloud environments with MS BI solution without changing architecture

In my earlier post, I said that one of the critical feature for selecting a cloud vendor for use with your existing BI solution, is to check the feasibility whether this requires an architecture change and if yes, what is the magnitude of the required change. The complexity in this case is much elevated when an enterprise has contracted with more than one cloud vendor. Actually it's a wise policy to have more than one cloud vendor and replicate your data across these cloud environments. In case your primary cloud vendor is down for some reason, you can always use your secondary cloud as a contingency server.

Here we are talking about using cloud as a file server and not as a computing server. From a design point of view, one can think of this architecture where we would be using cloud as a SAN (Storage Access Network). There are many interesting use of this design, whereby without changing the regular architecture, BI applications can take advantage of cloud. For ex., say that your ETL solution powered by SSIS requires to create huge staging at various stages of ETL processing and say this staging is in the form of some files like RAW Files, Excel files or Flat files. on various cloud environments. Or consider a scenario where a central repository of master data feed is created in the form of some files on various cloud environments which would be read by centralized or federated reporting applications. In such scenarios, client and/or server machines needs to be able to access cloud storage without a major architecture shift. For this requirement an intelligent cloud gateway is required which would select the appropriate cloud environment and also enable easy access these cloud storage environments.

One such product of this flavor is Gladinet Cloud Desktop. This tool has very interesting features to act as a shell for various cloud environments. If you want to cloud enable your client machines or servers, this tool can act as a very intelligent gateway for managing your cloud. The USP of this tool is that it adds the required cloud storage environments as a local virtual directory on the machine of which it's installed, and the software takes care of maintaining a cache between the cloud environment and local storage. By using the APIs of this product or by programmatic control, this product can be used to manage your cloud based storage without changing the architecture design of your existing solution. Though there are great complexities involved and I do not claim that this is perfectly suited for the design in consideration, but the capabilities that this product brings to the table for managing cloud based environment is very unique and can be utilized in many scenarios.

In summary, if your BI solution involves dealing with multiple cloud environments, I feel that this product can be very effective in such scenarios. Other services provided by Gladient worth checking out are Gladinet CloudAFS and Gladinet Cloud Backup which provides live backup of SQL Server to the cloud. I do not have enough balance in my pocket to pay the bills for various cloud environments as well as this product to develop a POC. But in case if you do or your enterprise is looking out to develop a POC for any solution that involves multiple cloud environments, then this product is worth giving a shot. I would like to hear back on this from those who were able to digest this idea that I have attempted to present in this post.

How to select the best cloud environment for your business intelligence solution ?

Amazon EC2, Rackspace, Salesforce, GoGrid are some of the famous cloud providers, and Microsoft Azure is probably the newest kid on the block. Cloud is gaining popularity day by day, and businesses as well as solution providers would want to move their existing applications or base their future applications on the cloud. But there are a few factors which should be considered for evaluating different cloud providers that suit your needs. Below is a brief list of such requirements in alignment with BI / MS BI needs.

1) Platform migration without architectural changes : Applications that are already developed when needs to be migrated to cloud, change in architecture of the application due to limitations or constraints of the cloud vendor is out of question. For this requirement the cloud vendor should be providing IaaS and not just PaaS services. The reason I mentioned PaaS and not SaaS is that if one is considering to use SaaS , this would make sense for your future requirements but not for your existing applications. Amazon EC2 provides a major set of services to cater this requirement. Unfortunately, to the best of my knowledge, Microsoft Azure is not up to the mark till date for this requirement.

2) Support for Private Cloud : There might be some very sensitive business logic in WCF (Windows Communication Foundation) services or similar other interfaces that one might not want to expose on the cloud. So the cloud vendor should also be supporting private cloud like Amazon VPC or Windows Azure Appliance Solution.

3) Software Licensing : Many cloud providers do not facilitate use of corporate licenses that enterprises would have procured, and many software vendors do not provide license for the use of product on cloud. Licensing needs to be levelled from software vendor as well as cloud vendor, so that software licenses can be easily used / reused on cloud environment.

4) PaaS Support for operations related to data : In a typical BI project, involving ETL, one can expect more than one database and different forms of staging ( in different file formats ). Cloud vendor should be supporting multiple relational DBs and storage formats required to support data storage. For example, SQL Azure and Project Houston provide a nice platform for data storage, design and operations. Windows storage provides three different kinds of storage formats. But in case if both are required to operate in the same environment, where I need to pull of data from SQL Azure and store in a file format provided by Windows Azure ( say BLOB storage ), both are not on the same platform. If your project has such dependencies, this should be taken care before considering your cloud vendor.

5) Ease of backup and restore operations : Most cloud vendor provides features that flush out any data as soon as you stop paying for the instance i.e using the instance. For permanent data, a separate dedicated storage needs to be purchased. Backing up and restoring this instance can be one big concern. Also enterprises might want portability for such instances, where one might want to create images on the cloud and use it for multiple projects or solutions. Amazon Machine Images is one such example in alignment to this requirement.

Please note that I have considered Amazon for the sake of discussion, but by no means this implies that Amazon EC2 is the best cloud vendor for your requirements. It always depends on the requirements of an enterprise, billing rates and services provided by cloud vendor in accordance with the requirements of the enterprise. The above listed factors should be evaluated at early stages before adopting the cloud environment and considering a cloud vendor.

Products for Data Warehouse management on cloud and in premises : CloudOlap and WhereScape RED

There are two ways to create Data Warehouse, one is using the traditional development life-cycle and other is by using accelerators (i.e. products) that build on the top of data warehouse related technologies provided by vendors such as Microsoft, IBM, Oracle, Teradata and others. In the traditional development methodology, we start with profiling metadata & cataloguing data quality in OLTP, develop relational DW and Cube design, and corresponding ETL tasks in parallel. This process is the suitable way when you have sufficient time, resources and funds to roll out your enterprise wide DW in a phased approach, mostly when your DW is integrated into your proprietary application.

But when you need to ramp up your DW in a short period of time, and you have a limited IT Staff with the right skills, within your organization for developing your DW, one can consider investment into products like WhereScape RED. Some of the noteworthy features of this product are:

1) It provides a GUI for developing DW in a very agile manner

2) It builds ETL processes for fueling the data into DW that you build with this tool

3) It has support for a majority of standard DB / BI vendors

4) It creates automated documentation as a part of the development process

5) License of this product can be quite costly, and the way it can be compensated is by saving on the duration of time for which technical staff needs to be working on the project.

A better idea can be gained from this client story ( Coinstar ) of WhereScape, where they used it for developing their corporate data warehouse.

This product works on the top on DB and BI technologies that work in-premise. Had it been on the cloud, it would be one of the most impressive accelerators for MS BI DW development. WhereScape has been around in this market since quite a few number of years, but seems like cloud on not on their agenda till now.

One another product that provides similar kind of functionality in-premise or on the cloud ( Amazon EC2 ) is CloudOlap. Though I have not evaluated this product much deeply, this video gives a very nice overview of the functionality of this product. CloudOlap provides various power products for Microsoft and SaaS Data Warehousing, in-premises as well as on the EC2 cloud environment. If this product is what it claims to be, I would rate this product much higher compared to WhereScape RED, and the reason it that it is one of the rare products that leverages MS BI to a SaaS platform on the EC2 cloud, and it provides functionality similar to WhereScape in-premises. May be WhereScape should start thinking about it !!

Microsoft Business Intelligence ( SSIS / SSAS / SSRS ) on Amazon Elastic Compute Cloud ( EC2 )

One of the reasons, SSIS is making it's entry into most of the data warehousing eco-systems, is due to it's competitive pricing and the immense support that it has inherently got from SQL Server as a database engine. But when you are in a competitive world, you need to hit the best possible estimates that wins over others. Pricing is a very big factor, especially when your organization is acting as a service provider as well as solution provider. Difference is that as a solution provider, you develop a solution for your client, deliver it and you are done. As a service provider you are expected to host the solution and provide it as a service for the agreed duration of time within the agreed Service Level Agreements (SLA).

Windows Azure and SQL Azure are growing consistently in the cloud computing environment, but still they are not mature enough to provide an integrated environment for SaaS flavor of BI solution on Azure platform. One such cloud computing environment is provided by Amazon Elastic Cloud Compute known as Amazon EC2. IT cannot work in isolation and neither can MS BI, so it's immature to consider just Azure for cloud requirements related to MS BI.

EC2 is a huge platform and has got a lot of services and products. Below is my flavor of understanding and explanation of same, considering the services of interest in alignment to MS BI.

1) Amazon Web Service (AWS) is the medium by which Amazon provides IaaS (Infrastructure as a Service) to it's clientele. Technically speaking, these services are made available in the form of a web service. All the services listed below fall under the classification of AWS. You should read this page to check out for the relational databases supported by Amazon.

2) Amazon EC2 : A web service that provides resizable compute capacity in the cloud. This can be considered a super version of Windows Azure. Consider Amazon EC2 as your processor and Amazon Elastic Block Storage as your hard drive on the cloud. Other services of interest available with EC2 are Amazon CloudWatch, Elastic-Load Balancing, and Elastic IPs.

3) Amazon SimpleDB : A web service for running queries on structured data in real time. It provides the core functionality of a database, real-time lookup and simple querying of structured data without the operational complexity. SQL Azure can be considered similar to this.

4) Amazon Simple Notification Services (SNS) : By using Amazon SNS, developers and businesses can send notifications or messages to applications or people using a “push” mechanism and have these messages delivered over protocol of choice. Probably there is no equivalent of this in Azure services stack.

5) Amazon Simple Storage Service (S3) : A simple web services interface that can be used to store and retrieve large amounts of data, at any time, from anywhere on the web. This is similar to Windows Azure Storage.

6) Amazon Virtual Private Cloud (VPC) : A secure and seamless bridge between a company’s existing IT infrastructure and the AWS cloud. Amazon VPC enables enterprises to connect their existing infrastructure to a set of isolated AWS compute resources via a Virtual Private Network (VPN) connection, and to extend their existing management capabilities such as security services, firewalls, and intrusion detection systems to include their AWS resources. This is an advanced / flexible version of what Windows Azure Appliance Solution is trying to provide.

The main service of our interest, if we intend to start our development or production using MS BI stack on Amazon cloud, is Amazon EC2 running Microsoft Windows Server and SQL Server. Express edition and Standard edition of SQL Server 2005 and SQL Server 2008 are available. This would mean that enterprise level applications can't be executed on this environment.

If I consider a scenario where I need MS BI capabilities for a few months with a handful of developers, instead of buying servers, hardware and software licenses, it would be a cost effective way to start piloting your solution with the kind of platform and services provided by Amazon EC2. If you are thinking about the cost differences, a Standard Edition SQL Server for a Workgroup license of 5 developers would cost at least a couple thousand dollars (at least in 5 digits), and with EC2 it would not cost you even in 4 digits for a couple of months. You don't believe this ? Well, check it out using AWS Simple Monthly Calculator. As I keep telling, cloud is all over the place and if you don't find cloud, cloud would find you !!!

Sharepoint 2010 with limited features on the cloud without BI support = Sharepoint 2010 Online

Microsoft Business Productivity Online Suite (BPOS) is a collection of products that are available on the cloud. These products are Exchange Online, Sharepoint Online, Office Livemeeting and Office Communications Online. These products / services are available in Standard and Dedicated edition. Standard version is a shared services platform and Dedicated edition provides an isolated and dedicated server space in the cloud. Microsoft has made office web apps available for free usage on sky drive, and during the later part of this year, office web apps is also expected to be available on the cloud. This is a complete SaaS offering from Microsoft.

Being a MS BI professional, my interest goes specially in Sharepoint online. Presently it's based on Sharepoint 2007 platform, so it can be easily interpreted that it won't have features like Access Services and Performancepoint Services. During the later half of this year, Sharepoint online is going to be upgraded on Sharepoint 2010 platform. This immediately raises a curiosity in my mind, whether Performancepoint Services and Access Services would be made available in this version. Microsoft has though not officially confirmed anything (to the best of my knowledge), but several Microsoft as well as unofficial sources have confirmed that performancepoint services is definitely not going to be a part of Sharepoint 2010 online. This means an end to the scope of dashboard development and hosting on the cloud.

Access Services may or may not be made available on Sharepoint 2010 online. One interesting feature of Access Services is Web databases. If Access services and effectively creation of web databases on the cloud is made available in sharepoint 2010 online, this can be seen as one of the competition / alternative to SQL Azure. My mind raises a question that when I have capabilities of sharepoint 2010 (though limited) available in the cloud, with web databases facilitated through access services, how many chances are there that I would opt Windows Azure storage and SQL Azure against Sharepoint 2010 online ? In fact, news are that Sharepoint 2010 online might be made available on Azure platform.

In short, Sharepoint 2010 online diminishes the hope and scope of BI by eliminating performancepoint services. Those who are curious to learn more about Sharepoint online can download Microsoft Sharepoint Online Standard Developer Guide. Though it's quite old and there can be changes in the present offerings, but still it would provide a deep insight into Sharepoint Online features.

Data Warehouse as a Service (DaaS) in MS BI Stack

Fast track data warehouse architecture and parallel data warehouse are two entirely different kinds of solutions but targeting a common domain of data warehousing. As SaaS solution is not available in MS BI Stack, similarly DaaS solution is also not available in MS BI Stack. If these kind of solutions are made available on private cloud solutions like Windows Azure Appliance Solution, a DaaS kind of offering can be made available on Microsoft platform. I am not sure if this would be of interest to Microsoft, but in my views, this is definitely of interest to solution provider kind of organizations. I feel that combining Azure and MS BI Stack, Microsoft has all the raw material that is required to develop SaaS or DaaS solutions, but the integration part is missing.

One of the leaders in DaaS solution is Kognitio. Enterprises are looking for on-demand services, and Azure can be seen as the first step in this direction. And whether it's for self-service business intelligence or data warehousing, there is a huge market for each of these solutions and microsoft is eagerly awaited to make entry into these (SaaS and/or DaaS) markets. Integrating MS BI on cloud would not be simple, and again making the same available in the form of service would change the existing MS BI markets. Not only that, solution providers would also have to adapt to this change, and look at a completely Service Oriented Architecture (SOA) solution proposition.

Below is an excerpt from Kognitio DaaS page, to which I fully agree.

• DaaS solves several issues that are common within the data analytics industry. Most notably, the inability to prove business value from data warehousing and analytics projects before spending large capital sums of money.


• DaaS allows you to run your large-scale data analytics projects (marketing campaigns, business reporting etc) and you simply pay on a pay-per-use basis. There is no overhead of implementing a data warehouse onsite, there is no need for your IT department to service, maintain and support the database and its users.

More about Kognitio and the services offered by this company can be read from here.

Difference between SaaS , PaaS , Cloud Computing and Grid Computing in Microsoft BI and Analytics

BI & Analytics space is filled up with a lot of terminologies that sounds very similar and are often used interchangeably by many vendors. But in general when a client comes up with certain requirements, a clear definition of these terminologies helps where a particular product fits in the overall solution design. Also this helps to get an understanding whether a BI & Analytics vendor has all the capabilities for supporting the type of solution that fits the requirement. Below is a list of such terms and their meanings in my understanding and experience.

1) Grid Computing : This term can be put in raw words as "Computing Power". The more computing power you require, the more computing resources you add to the grid. This would sound like cloud computing theory, and indeed it is. In fact, Grid computing can be used to empower Cloud computing or it can be also seen as a service on the cloud.

2) Cloud Computing : It can be seen as a service where you scale-in and scale-out resources as per requirements and you pay as per use. These resources can be computing power as well as storage at the bottom most level, and more services can be built on top of it. Vendor providing cloud environment hosts these services at it's data centers, client subscribe and pay as per needs. Windows Azure and SQL Azure are examples of the same.

3) PaaS : This means Platform As A Service. In this term, platform can be BI & Analytics platform for example. There can be other platforms like Data Cleansing, Data Integration, Data Profiling, Data Mining and others. I see Project Houston and SQL Azure together as a PaaS offering for data storage from Microsoft on Windows Azure Cloud Computing environment.

4) SaaS : This means Software As A Service. In BI & Analytics arena, SaaS BI is an environment that is usually powered by vendor owned data centers or third-party owned cloud computing environment, and SaaS BI Vendor provides PaaS to end clients for BI & Analytics. SaaS BI is a very growing requirement due to its key advantages of almost no hardware or maintenance cost, on demand scaling in or out of resources, pay as per use model of billing, fastest possible BI & Analytics in the most user friendly interface targeted to be operated by Business Users. In simple words, business users would get a web based application using which they can facilitate their BI & Analytics requirements by just making data available to this web based applications.

In my view, I do not see any SaaS BI offerings from Microsoft as of now. Microsoft has cloud offering on Windows Azure platform, and Windows Azure Appliance Solution sounds even better as of now. Project Houston, SQL Azure, Windows Azure, and Powerpivot if available on the cloud (i.e. it should be available on Azure platform and should not need any in-premises installation) can together act as SaaS BI solution, provided all of these work in an integrated fashion. In fact SaaS also provides features for deployment of deliverables created by business users on a collaborative platform that is again on the web. Presently the same is done via Sharepoint in MS BI Stack. An example of the kind of collaborative environment required on the web is Office Web Apps on Skydrive where you can create and share documents on the web.

A well known example of product based on SaaS BI model is "BI OnDemand" solution from SAP BusinessObjects Crystal Solutions.

Self service Business Intelligence on SaaS Platform Alternatives ( Powerpivot Alternatives )

Self-service Business Intelligence is one of the growing markets these days and business users are looking for flexibility and capability to analyze data and design reports or dashboards at will. Products / Services that provide dashboarding solutions, but are IT Developers toys and makes Business Users dependent on IT Staff to request for almost anything facilitated by these products, are miles away from self-service BI.

Any MS BI professional would be aware of Powerpivot which is a "managed" self-service BI tool for business users in the Microsoft BI Stack. But this is not the only star in the constellation of self-service BI tools in the BI galaxy. Below are the alternatives (in my knowledge and in no specific order) that are worthy to be explored, and you would find that one or more products fit your needs depending on at which stage of BI maturity is your enterprise.

Few of these are cost-effective, few are good for enterprise that are starters in the BI space, few are good for better in-memory performance, some are good for better visualizations, and some are good from an overall partner support and product integration perspective. I am reserving a detailed comparison of few or all of these for a future post. If I am asked the top 3 favorites, as a MS BI professional I would be interested in Powerpivot, Qlikview and PivotLink.

1) Powerpivot

2) Qlikview

3) PivotLink

4) Tibco Spotfire

5) IBM Cognos TM1

6) Advizor

7) Altosoft

8) Vizubi

9) SAP BusinessObjects BI OnDemand

Project Houston CTP and Microsoft Quadrant

Today Microsoft declared CTP1 of project code-named "Houston". Almost everyone who keeps their tab on SQL Azure would be aware of this news. By definition, Project Houston is a web-based silverlight interface managed and hosted in Azure data centers, and it facilitates access and management of SQL Azure data and databases. More about the same can be read from this blog post on SQL Azure Team blog. Jamie Thomson has generously shared credentials which can be used to connect to a SQL Azure database using project "Houston", and the same can be read from here.

Regarding the user interface of project Houston, I feel a scaled down reflection of the tool "Quadrant" which is a part of SQL Server Modeling Services. The user interface is pretty basic as of now, but if the user interface can be made similar to Quadrant, I feel that it would be a great way to browse data from SQL Azure than giving it just the SSMS style of user interface. I was very impressed with the UI of Quadrant, and as project Houston is a silverlight based tool, it has the potential to go much ahead than the traditional grid or windows like user interface for browsing / managing data and/or databases.

Those who are not aware of what is Quadrant and how it looks like, they can go through an article which I authored on the same from here.

SQL Azure on Windows Azure Platform Appliance Solution

SQL Azure Team blogged today about the availability of two new offerings. First is the Windows Azure Platform Appliance Solution, and second is availability of SQL Azure on the same. Before I pen down the point that I want to make, I would try to put this in one line.

If you don't find Cloud, Cloud will find you !! In my views, those who are not ready to make themselves compliant to the cloud computing environment as they might be having the impression that Cloud is too far to reach their working environment / technology and put their job at stake, Windows Azure Platform Appliance Solution is the new giant ready to reach your premises / technology / jobs. This solution breaks the barrier that enterprises feel for using and/or migrating to the cloud. And this factor was allowing data out of the premises. With this appliance solution, enterprises can have their private cloud in the enterprise with Windows Azure and SQL Azure.

This is one of the most unique cloud based solution in my knowledge. After Parallel Data warehouse offering, this is the second appliance based offering related to SQL Server from Microsoft. The appliance partners which participated in the Parallel Data Warehouse solution, have participated for this solution too.

What most of us would be interested in, is the question "How does this impact my technology / job ?" Below are a few points from the solution home page:

1) The Windows Azure platform appliance consists of Windows Azure, SQL Azure and a Microsoft-specified configuration of network, storage and server hardware.

2) The appliance is designed for service providers, large enterprises and governments and provides a proven cloud platform that delivers breakthrough datacenter efficiency through innovative power, cooling and automation technologies.

3) The Microsoft Windows Azure platform appliance is different from typical server appliances in that it involves hundreds of servers rather than just one node or a few nodes and It is designed to be extensible, customers can simply add more servers, depending upon the customer’s needs to scale out their platform.

4) The appliance is currently in Limited Production Release to a small set of customers and partners.

Based on above points, it is not hard to understand that this is just a starting point. If this goes well, one can expect enterprises to build their private cloud environment hosted in their datacenters using Windows Azure Platform Appliance solution. I feel like this is the new VMWare in the making for cloud platform. Also as of now, I do not see a competitive product in my knowledge for this kind of solution. And once it creeps into the enterprises, major Microsoft based technologies like .NET and SQL Server would definitely mutate to adapt to this environment.

These winds of change indicate that future versions of technologies like SSIS, SSRS and now with this solution even SSAS can be imagined to be operating on the cloud as SQL Azure is also a part of this solution. And if you are not willing to be cloud-ready and you work with Microsoft based solutions, I do not see time too far when you would start risking you job profile as "Cloud would have found you, coz you never thought of finding the cloud" !! I am looking forward to my readers, to share their thoughts with me on this viewpoint.

Challenges for cloud based SSIS

These days I am busy convincing my management the right people to build Noah's ark. This is to develop readiness against the biggest change that I see coming as of date. This change is Microsoft's entry into cloud based computing. This is a wave very strong and is propagating in all the major Microsoft based products that form key constituency of a solution design.

The next obvious thought is what is this Noah's Ark ? It's the design patterns for adapting all the Microsoft BI Stack of technologies to operate with Cloud based SQL Server i.e. SQL Azure, reducing the need and/or dependency of SQL Server wherever appropriate. In summary, readiness for business intelligence on the cloud. Though SQL Azure is not even near to the capacity of SQL Server, but it's evolving with a very fast pace. Enterprises have already started rolling their eye balls on Azure, and the basic factors of attraction are cost-effectiveness and ease of maintenance. I can foresee a time in the near future when solution providers would be proposing dual flavor of architectures to bid for a project, one with the traditional way of solution design and other would be cloud flavor of the same architecture. If your prospective client is desperately looking for cost-effective architecture design, cloud based architecture can be a silver bullet and a RFP biased with traditional flavor of architecture design is assured to cripple in the views of the client.

Coming back to the subject of this topic, as of this draft, SSIS is not available on SQL Azure or Azure platform. But in my expectation, I can see this need and feature addition coming in the near future. As we know that SSIS is mostly about in-memory transformation of data and secondary memory is the fuel of SSIS. With cloud / Azure platform, to gain flexibility in the scalability of hardware availability and administration, the tradeoff is to give up absolute control over hardware administration. As of now, ETL using SSIS is still tightly coupled to hardware / memory, and it would be very interesting to think of how SSIS on cloud / azure platform would look like. Imagine each and every data flow transform working on the cloud, and I cannot wait to see how similar or different would be the way in which ETL solutions would be developed on cloud platform compared to the way we develop the same on SQL Server.

Theoretically, one thing seems very clear that the tight coupling with hardware will have to be removed for SSIS to be hosted on cloud platform. And this can mean a very major architecture level change, effectively birth of a ETL system on a new world named cloud / Azure platform, and we would might still call this ETL tool / system / service as SSIS !!

Business Intelligence on Cloud

Business Intelligence on high-end hardware like HP Proliant servers is a well known business scenario / setup / practiced pattern. But business intelligence on cloud is not even a common thought. On my previous post, one of my blog readers commented a query "whether I have used SSIS as a data source for SSRS where SSRS applications runs in a cloud ?" I thought I would blog about it so that I can share my thoughts with everyone.

To the best of my knowledge any BI related services like SSIS / SSRS / SSAS does not exist on the cloud as of now. Presently SQL Azure can be used as a light weight storage for some of the BI related requirements. Some of the examples of a probable use for the same are as below:

1) Many a times either a temporary staging or a permanent staging is required in ETL solutions. Also the use of that kind of staging is volatile. SQL Azure can be one of the best options for this requirements.

2) Many enterprise level applications are used and implemented across geographical areas. Often these applications need replication and synchronization of data. Effectively in-premise version of SQL Server i.e. federated database servers need synchronization. SQL Azure can be used as a server for facilitating standard or customized synchronization of database servers.

3) To answer the query of my blog reader, SSIS can still be used as a data source for SSRS applications. SSRS cannot run on the cloud as this service is not available yet. But data can be stored on SQL Azure, accessed from SSIS package which can be used as a data source for SSRS reports. SSRS can also access data from SQL Azure, and one might want to use SSIS where there are multiple relational and/or non-relational data sources involved to create a dataset.

The pace with which the feature set in SQL Azure is getting healthy, it seems very much probable that business intelligence stack avilability on the cloud does not seems too far. My articles on SQL Azure can be read from here.