{"id":5063,"date":"2013-03-27T15:32:35","date_gmt":"2013-03-27T15:32:35","guid":{"rendered":"http:\/\/craftydba.com\/?p=5063"},"modified":"2013-03-28T16:55:46","modified_gmt":"2013-03-28T16:55:46","slug":"ms-excel-n-azure-sql-database","status":"publish","type":"post","link":"https:\/\/craftydba.com\/?p=5063","title":{"rendered":"MS Excel -N- Azure SQL Database"},"content":{"rendered":"

\"\"<\/a><\/p>\n

I knew Windows Azure SQL Databases<\/a> have been around for a couple of years but never had the time or need to research them. With a possible data center consolidation in the future, I was wondering if my company could leverage a Windows Azure SQL Databases to reduce the total cost of ownership for certain applications?<\/p>\n

Today, I will be working with the sample [AUTOS] cloud database that I created in a previous blog. I want to test connectivity to this cloud database using common programs and tools that my users or developers might choose.<\/p>\n

I will be testing how a power user can use MS Excel<\/a> to report on data contained within a Azure database.<\/p>\n

Let us start with a new MS Excel file called AUTOS-DBMS-IN-THE-CLOUD.XLSX<\/span>. I am assuming created a ODBC connection named AUTOS<\/span> using my article as a reference.<\/p>\n

The first<\/span> step is to go to the DATA<\/span> tab and choose the EXISTING CONNECTIONS<\/span> toolbar button. This dialog box will display any existing connection files. Click the BROWSE FOR MORE<\/span> button to bring up the SELECT DATA SOURCE<\/span> dialog box. Choose the CONNECT TO NEW DATA SOURCE<\/span> item in the select box.<\/p>\n

\"\"<\/a><\/p>\n

The second<\/span> step is to select the type of data source from the DATA CONNECTION WIZARD<\/span>. We want to choose the ODBC DSN<\/span> as our data source.<\/p>\n

\"\"<\/a><\/p>\n

The third<\/span> step is to choose the ODBC data source name from the DATA CONNECTION WIZARD<\/span>. We want to select AUTOS<\/span> as our data source. We need to supply a login name and password to connect to the Azure SQL Database.<\/p>\n

\"\"<\/a><\/p>\n

The fourth<\/span> step is to choose the database name from the DATA CONNECTION WIZARD<\/span>. We want to select AUTOS<\/span> as our database. Make sure we choose the VW_JOIN_MAKES_2_MODELS<\/span> as our source view. Any valid table or view name is acceptable.<\/p>\n

\"\"<\/a><\/p>\n

The fifth<\/span> step is to save the connection information to a file. This allows us to reuse this information for future reporting needs. Choose the default suggested file name from the DATA CONNECTION WIZARD<\/span> dialog box.<\/p>\n

\"\"<\/a><\/p>\n

The sixth<\/span> step is to import the data into excel either as data table, a pivot table, or a pivot chart. I am going to select the data table.<\/p>\n

\"\"<\/a><\/p>\n

The seventh<\/span> step is to start playing with the data that has been imported into our spreadsheet. Again, this is a copy of the original data from the cloud. Any changes to this data table will not reflected in the Azure SQL Database. <\/p>\n

\"\"<\/a><\/p>\n

In a nutshell, importing data from a Azure SQL Database into MS Excel is easy as setting up a file based connection. This task takes 5 steps to accomplish. Afterwards, use this file based connection to import a copy of the data into MS Excel for our reporting needs. <\/p>\n

So far, it has been very easy to access data in the cloud. My next experiment is to execute a performance test between both local and cloud based servers. I am hoping that the cloud beats my low end Dell laptop in this test.<\/p>\n","protected":false},"excerpt":{"rendered":"

I knew Windows Azure SQL Databases have been around for a couple of years but never had the time or need to research them. With a possible data center consolidation in the future, I was wondering if my company could leverage a Windows Azure SQL Databases to reduce the total cost of ownership for certain applications? Today, I will be working with the sample [AUTOS] cloud database that I created in a previous blog. I want to test connectivity to this cloud database using common programs and tools that my…<\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[9],"tags":[786,784,15,800,799,802,801,803,37],"class_list":["post-5063","post","type-post","status-publish","format-standard","hentry","category-db-dev","tag-azure-sql-database","tag-cloud-computing","tag-john-f-miner-iii","tag-ms-excel","tag-odbc-connection","tag-odc-connection-file","tag-read-only","tag-reporting","tag-select"],"_links":{"self":[{"href":"https:\/\/craftydba.com\/index.php?rest_route=\/wp\/v2\/posts\/5063","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/craftydba.com\/index.php?rest_route=\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/craftydba.com\/index.php?rest_route=\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/craftydba.com\/index.php?rest_route=\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/craftydba.com\/index.php?rest_route=%2Fwp%2Fv2%2Fcomments&post=5063"}],"version-history":[{"count":0,"href":"https:\/\/craftydba.com\/index.php?rest_route=\/wp\/v2\/posts\/5063\/revisions"}],"wp:attachment":[{"href":"https:\/\/craftydba.com\/index.php?rest_route=%2Fwp%2Fv2%2Fmedia&parent=5063"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/craftydba.com\/index.php?rest_route=%2Fwp%2Fv2%2Fcategories&post=5063"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/craftydba.com\/index.php?rest_route=%2Fwp%2Fv2%2Ftags&post=5063"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}