PowerPivot Model: How to TEST or EDIT existing connections?

Standard

Have you ever had someone sent you a PowerPivot model and asked you to do something with it? And if so – may be, you would have to see what data source(s) the model is using and if applicable, you test the existing connections. If you find yourselves in such a situation, this blog post is for you:

1) Open the excel file and switch to PowerPivot Window

2) Now, switch to Design Tab > Click on Existing Connections:

existing connection design tab of powerpivot model

3) Here you’ll find the list of connections under “PowerPivot Data Connections” > Select the connection you wish to TEST or EDIT > Click on EDIT button

4)  Now here you can edit the data source. And if you click on “Test Connection”, you’ll be able to test it too.

edit data source connection power pivot model

5) After you’re done, click on SAVE.

And you’ll now see the “Existing Connections” Box again:

powerpivot select a connection to a data source that contains the data you want to import

Click CLOSE and you’re done, you have successfully edited or tested the existing connection.

 

 

Advertisements

How to import data from Azure Datamarket to Excel

Standard

Short answer: Download Azure Datamarket Excel AddIn

And you want to know why i am writing a blog post for it? spare couple of minutes and you will realize that you were better off just knowing the short answer. yeah seriously. And still if you are adamant on reading it – please drop me an email on contact[at]parasdoshi[dot]com, I want to talk to you! seriously!

Have you ever wondered how to import data from Azure Data market to PowerPivot Excel? And you know what I did? – since I knew we could load data from datamarket into powerpivot, I did that! There is an inbuilt support btw:


Now, I copied this data (CTRL C) and tried pasting it in Excel sheet (CTRL V). And you know what – nothing happened! So tried again! And again nothing happened. Now, i again selected the data from powerpivot window via right clicked -> copy. Went to excel worksheet and right clicked -> paste special. And guess, my laptop froze for a while and in a weird way I was happy because I thought that the copy was successful! But again it did not work. If it had, well I would not have written this blog post.

Any-who, so it was time to read some whitepapers blog posts. some googling and binging. And you know what, while I was binging and googling stuff, I liked the bing wallpaper, so i had to change my wallpaper. So I did that! Look at it, don’t you like it too:

And after a little tweeting facebooking searching, i found EXCEL ADD IN!! yeah! you can download it here: https://datamarket.azure.com/addin

After installation, you will find under the DATA tab. you can sign in to datamarket directly from there. you can create a datamarket account if you do not have one. you allow access if you have not done so before. And then you can browse available data-sets! it’s that easy.

Then you could just select the data-set you want to import and click on “import data”:

And then click on “import data” that you see at the bottom of the below screenshot.

And that’s it – downloading started! optionally you could filter the data if you want.

That’s it. Moral of the story:

Download Excel addin to import data from azure datamarket to excel

 

BTW I am using Excel 2010!