PowerPivot: Quick Tip regarding Copy-Pasting Tables in PowerPivot 2010

Standard

Power Pivot Paras Doshi Microsoft Business Intelligence Excel 2010I was working on couple of PowerPivot models recently and both models needed a Date Table. So here’s what I did:

1. Imported the filtered version of the Date Table (DateStream) from Azure Data Market. Added Calculated Columns to the table. Let me call this Table1

2. I copied entire Table1 and pasted it to an another PowerPivot model – let’s call that Table2

 

After this:

And I went to happily create data models! Later, I while testing the model (that had Table2), I realized that it had lost the “formulas” – in other words when I copy-pasted from Table1 to Table2 in PowerPivot – it did not copy-paste the formulas but it just pasted the values.

So, 1. Copy Pasting a Table in a PowerPivot does not copy-paste’s the Formula’s in the columns.

And also it does not keep the “Data connection”. In this case, Table1 was connected to Azure Data source and I could “refresh” data if I chose to. But Table2 was “Static” – In other words, It lost the data connection with the data sources. So I could not “refresh/update” data from the data source if need be.

S0, 2. If you copy Paste a Table in a PowerPivot, the newly copied table does not keep the “Data connection” to data source.

 

I have also posted this here: http://beyondrelational.com/modules/1/justlearned/tips/17976/power-pivot-copy-paste-a-table-in-a-powerpivot-does-not-copy-pastes-the-formulas.aspx

“An unexpected system error occured…” – While trying to establish a Data Connection in Performance Point 2010 Dashboard Designer

Standard

So I got an error while trying to set up a Data Connection via Performance Point 2010 Dashboard Designer: “An unexpected system error has occurred . additional details have been logged for your administrator” – and so I did quick searches and read this & this & this & this & this and with the help of the links I solved the error. Now, there are lots of moving parts but for machine I was able to solve the error by:

1. SharePoint 2010 Central Administration

2. Manage Services on Server

3. Stopping “PerformancePoint Service”

4. And then Starting it Again.

5. If you are still facing issues then consider re-booting the server. In my case, it was a demo machine and so I quickly rebooted it.

Simple? Yeah but I spent 30 odd mins trying to figure out how to solve this error and so I thought I would document this.

 

Works? Yes? Great! No? Try:

-> Configure secure service store and unattended service account.

-> you can check out the links that I added earlier.

 

Before I got the error – I had

1. Successfully Added “PerformancePoint Service Application” via “Manage Service Applications”

2. I had made sure that the PerformancePoint Service and the Secure Store Service were Started.

3. Created a Business Intelligence Center Site and checked the Site Collection Features were properly configured.

But I got an error while trying to create a Data Connection. And I solved it! And Now can spend time creating few reports! That’s about it for this post.

performance point 2010 sharepoint business intelligence microsoft

Microsoft Business Intelligence: Power View can be Exported to PowerPoint (PPT)!

Standard

A short post to point out that Power View reports can be exported to PowerPoint (PPT) – and PPT slides would also have a “click to interact” button and if the security plus network access is configured correctly then the Interactive data exploration without leaving the Slides! very cool. I had pointed this fact out earlier here. And the official resource can be found here: Export a Power View Report to PowerPoint

I am referring to SQL Server 2012 BI and SharePoint 2010 here. And here is a  step by step guide:

1) Create report > Save it > And then Go to File > Export to Power Point

Export Power View reports to PowerPoint 2010 SQL Server 2012

A note about security: “Export to PowerPoint” requires windows authentication method.

2) Select the location of the PPT file that will have the “Power View” reports.

Export to PowerPoint requires windows authentication method File Location

Now you can store the PowerPoint file at any place but it would be important to consider whether the machine from which it would be accessed has the network access to the SharePoint Power View reports. If not, the Power View reports would just show up as “static images” and the click to interact would not work.  Important security point to consider for your scenario.

3) Open the File > start slideshow > Navigate to the slide (if applicable) > can you see the “click to Interact” button? Yes? Great! you can do interactive data exploration from the PowerPoint environment itself! I find this very Impressive because Power View is meant for Business Users to do Data Exploration and create rich visualizations and once they are done with it – they can export it to PowerPoint and show their Power View chops to their boss and peers and more importantly, make better business decisions. Any-how, here’s the image:

CLICK to Interact button Export to PowerPoint Powe View SQL Server 2012 SharePoint 2010

That’s about it!

New Journal Article Published – Title: “Building an Ideal Tabular Model for Power View reports”

Standard

Part 2/2 of the series on building Ideal Models for Power View reports is live!

Summary: “In this article, we will first compare the PowerPivot and Tabular models, which will help you choose between these two models for your scenarios, and then we will study the reporting properties in a Tabular Model that you can configure to build an Ideal model for Power View reports.”

Read here: SolidQ Journal: Building an Ideal Tabular Model for Power View reports by Paras Doshi

Blog-Post about Part 1 is here: SolidQ Journal: Building Ideal PowerPivot Model for Power View reports

 

If you have any feedback or comments, please drop a comment or contact me.

SharePoint PowerPivot Site: How to extend document library to connect to Tabular Model for some Power View magic!

Standard

So you are SharePoint site admin and your task is to extend Document Library to connect to SSAS Tabular Model so that your Power View reports can connect to Tabular models. we’ll see that in this blog-post.

Note: Creating a SharePoint Business Intelligence Dev Environment is a different thing, please refer: Bird’s Eye view of SharePoint BI Dev Environment Setup process and official reference http://msdn.microsoft.com/en-us/library/hh231687%28v=sql.110%29.aspx

In this Blog Post, I am assuming you are through step 1- 8 and we’ll see a Tutorial on Step 9: “Extend the Document Libraries to include BISM connections.” so that we can create Power View reports on Tabular Models.

Here are the steps:

1) Open SharePoint PowerPivot site

2) PowerPivot site > Documents > Do you see New Document disabled? Well, we are going to fix that!

Power Pivot Site New Document Disabled

3) Switch to PowerPivot Gallery > Library > Click on Library settings:

Power Pivot Site Gallery Library Settings

4) Click on advanced settings

Power Pivot Document library advanced settings

5) Here change the setting of “Allow Management of Content Types?” to YES

And Click on OK

6) Back on Document Library Settings Page, Under Content Types, click on “Add from existing site content types”

Power Pivot document library setting BISM

7) On “Add Content Types” Page > from Available Site Content Types – Add “BI Semantic Model Connection” to content types to add. and click on OK.

BI semantic model connection content type

8) Now,

Go to PowerPivot Gallery > Documents > New Document.

Can you see BI Semantic Model connection? Yes? Awesome!

create a new BI semantic model connection

9) Now let’s test it.

Note: I am on my Dev Machine so I am going to test it by just connecting a Tabular Model that’s running on same machine.

BISM connecting to a Tabular Model for Power View

Click on OK.

10) can you see the connection? Yes? Click on Power View icon.

can you see the BISM connection

11)Can you see the view? yes? Go ahead! Create Awesome Power View reports!

sample power view report

That’s about it for this post.

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.

 

 

How to Solve Error: After Reboot, SharePoint sites says “Service Unavailable”

Standard

I had to reboot my Dev Machine on which I have my SQL Server 2012 Business Intelligence Setup. After reboot, It turns out that when I wanted to open the SharePoint Site (for Power View related work) – it gives me error:

“Service Unavailable

HTTP Error 503. The Service is unavailable”

Now, I did quick searches found this and this

Turns out, for my scenario – the default application pool had stopped:

default application pool stopped

So this is what I did to solve the error:

1) Verified that the account and password for the App Pool is correct

2) Started it.

This is how you do it:

Start > IIS Manager > Application pools > Locate Default Application Pool that stopped > Verify that Account and Password are correct. > Start it.

After doing that, the SharePoint site started working again!

Related articles: