Microsoft SQL Azure and BI

There are number of Business intelligence software available in the market from 0 to thousands dollar cost but what if you just have to analyze your data and use business intelligence feature for free – we will outline some details of Microsoft offering something in business intelligence domain. And we will import data from MS SQL Azure which is handy too, if you are familiar with cloud computing then it will more easy for everyone to import data from Microsoft SQL Azure and work with the business intelligence technology for free. 

We are assuming this- your organization is dealing with millions of rows of data –such data may be stored in legacy system, in flat file, or in other system- and you need to analyze it anyhow- so you just need to do the following.

Download Power Pivot for free from Microsoft and install office 2010 and using excel 2010 you can import data from MS SQL Server or you have any system you can import data inside excel using power pivot for detail analysis.

In this scenario we need:

• Microsoft SQL Azure database on cloud

• Power Pivot free

• Excel 2010

Once you have installed office then install Power Pivot component which is free and create a connection to MS SQL Azure- just point your location to Data (Excel 2010)- Add connection to azure and select ODBC option to connect to MS SQL Azure environment.

If you already have taken space for azure database otherwise subscribe for MS SQL Azure database and you will be given further instruction in email from Microsoft.

You can create, copy and work with T-SQL using Microsoft SQL Azure environment, there are few SQL Azure limitation in statements they are not supported in SQL Azure environment.

You can connect to SQL Azure environment using SQL Server Management Studio (SSMS) having full support of SQL Azure environment, if you want to access SQL Azure database from visual studio.net using ado.net so the some part of the code will be replaced – you can change provider username with SQL Azure login id it all depends on what type of format you are using- for example it can be [email protected] and so on. Your ODBC connection setting or azure connection string providername could be something like
  "Server=tcp:[sqlazureserver].database.windows.net "

As SQL Azure compliant with 3rd party application –and allow them to connect with MS SQL Azure environment. You also can use ODBC and ASP.NET and ADO.net to connect with SQL Azure- so this time we are assuming you have taken ODBC connection approach to connect with Excel 2010 Power Pivot and then you can import/export data to azure. As of now you are able to understand the entire azure connection functionality then with the help of proper credential you can connect with MS SQL Azure via ODBC connect option in Excel 2010 data menu. If you have configured it then your data from legacy system available on excel sheet – can be exported to SQL Azure or can be imported to excel using power pivot if you want to import your data from SQL Azure to Excel 2010 via power pivot.

Conclusion About Microsoft SQL Azure:

We have taken ODBC approach to access SQL Azure database from Excel 2010 with a plug-in Power Pivot to import data from Azure – with the same approach you can export data. Our objective was to transfer data from old database to Microsoft SQL Azure or vice versa.

Leave comments for any issue or idea.

Private cloud

Private cloud attribute enables on-demand self service mechanism which means peripherals including memory, network, computer and resources should be available from a portal /website so finally whenever anyone wants to access private cloud service they can access it. So anyone is going to access such services does not require any intervention from cloud service provider.

Wide network:
Another aspect of private cloud is- it should be available throughout multiple devices like smartphone, PC, laptops, tablet/tab and any other device available in the market.

Dedicated area for resources:
Dedicated area for Resource - is another aspect of private clouding, if someone is going to provide you private cloud services then it should provide you dedicated area for resources where computer, network, memory. Disk /tap should be allocated to consumers from shared area.

Sudden change in resource utilization:
Another aspect of private cloud id it should have tendency to change its position from utilization if someone is using resources from private cloud then consumer should be able to reduce its resources as per requirement. Tendency to release or use resources on demand should be there for all private cloud consumers.

Understand Private Cloud

Rented resources:
One more aspect of private cloud is- it should provide your cloud services as it has been used – means whatever you have used you pay for it, if consumer has used some resources then they should be bill for such resources. These recourses utilization is monitored by cloud services provider via policy and its billing should be transparent to consumer and providers.

Private cloud provides changeable, dedicated area of resources that consumer of cloud services can access through self -services mechanism in such environment where all that is dedicated to your organization.

Understand What is Windows Azure

You might be facing such kind of scenario where you are having issues in managing your network when you are scaling out and may be facing issues for memory when application needs more space to run, and load balancing, and managing environment and patching type of issues.
You can think of one scenario where you do not have to think about your application which is design on your pc or you do not need to take care of your application in desktop environment,. The only way to interact your business will be Internet – and you do not need to think of your hardware/software etc- that scenario is called Windows Azure cloud is a platform from Microsoft for design , build and host web application and this application will be accessed via cloud OS that is Windows azure.
One major advantage we could see here is reduced hardware cost – traditionally you need to manage a server and clients in order to your entire client should access that application, configuring client and its support might be another issue.
Windows azure platform includes cloud services for operating system and developer services that provide a functionality to develop application that span from small to enterprise entities.
Windows azure and sql azure are the major components of windows azure platform. Whereas windows azure provides storage, networking and content delivery feature and scaling web application feature on Internet.
Microsoft sharepoint on-line, exchange on-line, office communication on-line and net meeting already released before windows azure where as these are sharing common operational element like datacenter, provisioning and identity infrastructure.
Windows azure platform also provide test staging and different testing environment in software developer life cycle where they can move their application from test to production environment this environment is based on subscription. We have made it very simple  to understand what is windows azure if you still have any issue to understand it please let us know.

SQL Azure Reporting Services

You can develop and deploy report online with sql azure reporting services –it can give you business intelligence - Just imagine a scenario where you are working with your application and getting reports but everything is on cloud, idea is very simple and been implemented and used by Azure reporting services, SQL Azure reporting is basically cloud based reporting mechanism build on sql server reporting services and Microsoft azure technologies. This cloud based reporting or you can call it cloud reporting services basically  easy for provisioning, cutting cost, highly available and reduced administration and management for reporting server – whereas traditionally everybody is managing report server for all reports to be available and working.
If you have confusion with SQL Server reporting services on which sql azure reporting is based, sql server reporting services- is basically a tool-set that allows anyone once installed on physical machine to create and manage and maintain reports after installation whereas sql azure reporting service is cloud based hosted serves for anyone to use it anywhere online in a cloud environment where Microsoft data-center is hosted where you do not need to focus on anything but on your main objective.

understand sql azure reporting by example

You need to sign up/subscribe for sql azure reporting services.
You will be having business intelligence design studio for designing report online
Or you will be having report builder where you will be authoring report to azure reporting online.
You will store all report data in sql azure online
You can manage report online sql azure report portal where you render these report into different formats xls, pdf etc.
Your authentication model will not be windows based but it will be online username/password but the thing is story does not end here please read further.

SQL Azure Reporting deep dive:

Windows azure environment contains a container for your number of services provisioned and vm according to your need if you have at least free azure account for a month.

Sql reporting is cloud service corresponding to sql server reporting service on azure cloud,

You will get much more for business intelligence as stack in azure container, you also can host SSAS on windows azure as part of you app.

And you have choice to author your customize report on windows azure container or azure environment for your enterprise to access it.

Now you have free azure account for at least one month where you have azure sql server reporting instance already provisioned, and windows azure will give you load balance environment on azure to give you report access any time you can also scale up and scale out this azure environment as per your need and according to your pocket too.

From windows azure dashboard you will select sql reporting link from your left navigation area and can create new sql reporting service to work with sql reporting and then you will have reporting service url to setup the actual report.

Once you have created sql reporting instance in your azure container then you have another dashboard from where you can configure users who can access your report on azure- you also can create item there which is called data source means which DB you want to access in that report.

It will prompt you for sql server credential and done, now you can use it to author report SSRS on azure container I hope this is helping for you to go further to design report I still believe you are not scared of so far.

You can browse your report builder as you do it traditionally but this time in your virtual environment, and write query to access sales numbers from xyz table(assuming a table name here) and define dataset as you do it in report designer. Now your dataset can talk with sql server reporting services and SQL DB behind the scene.

Once you save this report in your azure environment or reporting server in azure environment as .rdl file but you would see this report as item in your dashboard area in your azure environment.

Anyone can browse this report online via thin browser once you have granted permission who can access this report- hope this entire work can be done in azure container things become handy when you sign up for one month free azure access. ( see snap below as to what azure container will look like).

Though after one month free trial will expire-  it might cost you more but I haven’t tried something like publishing or authoring .rdl file somewhere else and accessing it from azure container might be nightmare for anyone because  of credential etc might be difficult part.

this illustration is for learning how you can get microsoft azure services where reporting services will be configured

If you are scared till now to understand this let me tell you this is basic stuff configuring sql azure reporting capabilities on azure and accessing it from azure container anyways stay connected to learn more stuff about azure and sql azure reporting on this site.

Microsoft SQL Azure

Microsoft has started working on cloud and as of now Microsoft has released few products based on cloud computing – if you have heard a buzz called -sql azure – so probably be thinking what is Microsoft SQL Azure.
 SQL Azure is cloud based technology for people who want to work with RDBMS model in cloud space.
You just think of one scenario -you are working in organization where you have to manage databases that include software to manage and a dedicated server - where your database and software will be installed online sql like a cloud space for you is called dedicated server, this kind of dedicated server is different from dedicated hosting  and after few months you probably be asking for some patches to fix some database related issues. This is not the end- you have to manage high availability, disaster recovery that may increase overall cost if your planning is to scale up or scale out.

Other Cloud Database may be used for such functionality.
But with a cloud technology especially you can manage hosted database – this will probably decrease your cost overall. But with cloud technology you will have to face scaling issues.

Why SQL Azure in Cloud?

SQL Azure is an environment for managing RDBMS databases on cloud they are already distributed on different node.
SQL Azure is not like an environment where you have to deploy your databases on different machines for different purposes SQL Azure is already deployed on different sql instances.
You do not have to worry about its patches or you have to manage it physically, just do all stuff on cloud with all RDMBS feature plus you can pay some cost and scale up your RDMBS environment. This means you are saving physical architecture cots of RDMBS environment when you need it to scale out.
SQL Azure provides you scalability, availability and reliability anyway. Azure is an environment of clustered SQL Servers based in cloud in Microsoft data centers.

Understand Microsoft Cloud by image example

Some facts about Microsoft SQL Azure:
•    Microsoft has released backup functionality in SQL Azure –just copy object
•    Microsoft may Support full text indexing in SQL Azure.
•    Microsoft may support rich Reporting Services.
•    Distributed transaction not supported in Microsoft SQL Azure Cloud
•    Select Into statement is not supported in SQL Azure.

Now, ready to take off – install SSMS 2008 R2 to create your own database in cloud and user database can be 1gb to 5gb on web edition. SQL Azure database support standard API like ODBC, ADO.NET these two technologies are very famous to manipulate data.
Few select statements you can use supported in  SQL Azure environment:
if you want online sql server management studio then you can register for sql azure online where you can same environment almost and you also can run sql queries there, Microsoft is offering free trial for azure you can browse Microsoft site and register for free sqll azure.

Hope you have great user experience on this site, please stay connected and leave your comments for any inputs or any other interface Azure supporting?