SaaS BI – Part I


BI/OLAP through History

The concepts of OLAP evolved through decades and by nineties it gain immense popularity over the corporate world. Ted Codd , who was the one established 12 rules for an OLAP product. This made some foundation for BI products and Microsoft then acquired a technology from Panorama Software and released their OLAP Services Product. That brought introduced the multidimensional analysis to business users and lead to the standardisation of querying multidimensional data (Early evolution of MDX). Microsoft expands its market share on BI products very fast. In due course Multi dimensional DW analysis became too complex and only large corporate could stick on investing for the maintenance because the demanding requirement of time and money.

The ‘Look and Feel’ trend: A large number of BI solution providers then emerged into the market with an offer of reduced TCO’s. But their strength was advanced visualisation and browsing features. These products proved that they were no longer providing an alternative for OLAP, instead providing some boxed features which are no longer supported well centralised data integration and high level data management capabilities.

Memory -supported BI: Mid market vendors then started depending on the strength in increasing RAM for better BI performance. Even Microsoft also recently accepted the then came up trend of in- memory application by launching their in-memory power pivot solution, which was clubbed with MS excel 2010.

SaaS BI

Overview : SaaS BI or on-demand BI on cloud BI , the different names gives its definition too. Instead of the traditional on-premise BI, SaaS BI will be deployed out of the organisation’s firewall, catered reports and analysis through internet on demand. Vendors usually sells it on a subscription basis or pay-as-you-go basis instead of Licensing and annual maintenance model.

Here are some comparisons between SaaS BI Vs traditional on premise BI

SaaS BI On-premise BI
Deployed outside of the company Deployed in company premise
Installation, Operation and Maintenance are out of scope for the company Installation, Operation and Maintenance are company’s responsibility
Quick initialisation of the BI project Company has to prepare infrastructure, hardware, software and resource for the initialisation of the BI project
Upgrade the subscription plan as usage grows Capital Equipment purchase/replace is needed when a growth of usage comes
Little or no TOC(Total cost of ownership) Hardware , Software investments are huge
Data needs to send beyond firewall, may need to consider security aspects Comparatively easy to handle information security

SQL Azure , How to connect ? and other technical stuffs


# Drivers/Library/Protocol /Technology for connecting SQL Azure from applications/Tools

· NET Framework Data Provider for SQL Server (System.Data.SqlClient) from the .NET Framework 3.5 Service Pack 1 or later.

· Entity Framework from the .NET Framework 3.5 Service Pack 1 or later.

· SQL Server 2008 R2 Native Client ODBC driver. SQL Server 2008 Native Client ODBC driver is also supported, but has less functionality.

· SQL Server 2008 Driver for PHP version 1.1 or later.

· Also supports tabular data stream (TDS) protocol client version 7.3 or later

· OLEDB Connection is not supported

· TCP/IP connections are allowed

· Multiple Active Result Sets (MARS) is supported.

· SQL Server 2008 SQL Server browser is not supported because SQL Azure does not have dynamic ports, only port 1433. But SQL Server R2 management studio is supported

# Visual Studio 2008 or later supported to write application for SQL Azure using Microsoft Visual Basic, Microsoft Visual C#, or Microsoft Visual C++

#ODBC Support

When using the data source name (DSN) wizard to define a data source for SQL Azure, click the With SQL Server Authentication using a login ID and password entered by the user option and select the Connect to SQL Server to obtain default settings for the additional configuration options. Enter your user name and password to connect to your SQL Azure server as Login ID and Password, and then clear Connect to SQL Server to obtain default settings. Click Change the default database to: and enter the name of your SQL Azure database even if it does not show up in the list. Note that the wizard lists several languages in the Change the language of SQL Server system messages to: list.

Select English/ ? h as a language. SQL Azure does not support Mirror Server or Attach Database, so leave those items empty. Finally, the Test Connection button may result in an error that master.dbo.syscharsets is not supported. Ignore this error, save the DSN, and use it.

#Data Migration support

· SQL Server 2008 Integration Services (SSIS)

· The bulk copy utility (BCP.exe)

· System.Data.SqlClient.SqlBulkCopy class

· Scripts that use INSERT statements to load data into the database

# Agent Job Support

SQL Azure Database does not support SQL Server Agent or jobs. You can, however, run SQL Server Agent on your on-premise SQL Server and connect to SQL Azure Database.

#Clustered Index – rule

SQL Azure does not support tables without clustered indexes. A table must have a clustered index. If a table is created without a clustered constraint, a clustered index must be created before an insert operation is allowed on the table.

#User name requirements

Certain user names are not allowed for security reasons. You cannot use the following names:

· admin

· administrator

· guest

· root

· sa

#Connection Issue

In order to provide a good experience to all SQL Azure customers, your connection to the service may be closed due to the following conditions:

· Excessive resource usage

· Long-running queries

· Long-running single transactions

· Idle connections

· Failover because of server failures