THE SQL Server Blog Spot on the Web

Welcome to SQLblog.com - The SQL Server blog spot on the web Sign in | |
in Search

SQLBI - Marco Russo

SQLBI is a blog dedicated to building Business Intelligence solutions with SQL Server.
You can follow me on Twitter: @marcorus

Importing data from Analysis Services 2000 to PowerPivot

Companies that have cubes on Analysis Services 2000 and still have not completed a migration to SSAS 2005/2008 might be interested in connecting to AS2000 from PowerPivot. Unfortunately, there are several limitations about using AS2000 as a data source for PowerPivot.

Connecting issues

The client PC where PowerPivot for Excel runs requires the PivotTable Service installed to be able to connect to AS2000. This might seem a non-issue because it is the same requirement for any existing AS2000 client (in particular, it requires the PivotTable service). However, chances are that existing AS2000 clients are on older virtual machines or servers accessed through Terminal Server, while Excel 2010 might be installed on another server (used by remote connections) or on the client PC. At the end, it is often the case that the PivotTable service is not present on the same PC where PowerPivot for Excel runs.

Furthermore, because the PivotTable Service is only available in 32-bit version, it cannot be used by the 64-bit version of Excel.

Publishing issues

A workbook containing a connection to AS2000 requires the 32-bit PivotTable Service to refresh data. Thus, if such a workbook is published on SharePoint 2010 (which is only available in 64-bit version) its data cannot be refreshed. This could be a serious blocking issue for using PowerPivot to create dashboards using also AS2000 data (ideally, a dashboard for the company would get data from all the cubes available on different servers).

Possible workarounds

A possible workaround could be the one of using a 32-bit instance of SQL Server to create some views on a SQL database which queries the AS2000 cube through a Linked Server connection. On the server where this instance of SQL Server runs it would be necessary to install PivotTable Service too.

With this workaround MDX queries would be defined on the SQL Server side and the end user wouldn’t be able to create its own MDX query through MDX designer. At the end, the connection would be simple for the user creating a PowerPivot connection (just select SQL Server, choose a database and the right view) but they would depend on the IT department to get new queries for their reports.

Another workaround is using a 32-bit instance of SQL Server Reporting Services 2008 R2 to expose AS2000 cube data through a Data Feed. In this case, the MDX query would be in the report definition and the end user would be able only to use existing report or designing his own reports with Report Builder (using a 32-bit client with the PivotTable service installed on the client). Probably this is the workaround that don’t require assistance from IT department. However, users will need to understand how to create a report that exposes a Data Feed in a way that is good for further analysis using PowerPivot.

Published Tuesday, August 17, 2010 9:14 AM by Marco Russo (SQLBI)

Comment Notification

If you would like to receive an email when updates are made to this post, please register here

Subscribe to this post's comments using RSS

Comments

 

Swapna said:

Hi,

I have a pivot table report in Excel ,source for this table is a tabular model.

I want to add this pivot table to Power Pivot data model, is that possible?

I tried but couldn't do that.Cant we add pivot tables to the data model?

Thanks

November 26, 2014 3:45 AM
 

Marco Russo (SQLBI) said:

You can add a table to a data model, but not a Pivot Table.

November 26, 2014 4:45 AM
 

Marco Russo (SQLBI) said:

However, you can convert the pivot table to formula, then create a table using these cells and then you should be able to do the import in the data model.

November 26, 2014 4:46 AM

Leave a Comment

(required) 
(required) 
Submit

About Marco Russo (SQLBI)

Marco Russo is a consultant, writer and trainer specialized in Business Intelligence with Microsoft technologies. He runs the SQLBI.COM website, which is dedicated to distribute resources useful for BI developers, like Integration Services components, Analysis Services models, tools, technical information and so on. Marco is certified as MCT, MCDBA, MCSD.NET, MCSA, MCSE+I.

This Blog

Syndication

Archives

Powered by Community Server (Commercial Edition), by Telligent Systems
  Privacy Statement