Technology Tips -

Monday, November 28, 2011

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 using 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] "

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 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.