Information, tips, news and announcements about SQL Server Analysis Services and PowerPivot directly from the product team.
One of the keys to self-service BI is that people need the highest quality, up-to-date data for their analysis. Many people don’t have direct access to query the databases underlying the applications that they use on a day to day basis, both because of the complexity of the data and also the need for adding business logic and security on top of the raw data.
Gemini includes support for data feeds, which lets you get your data from all kinds of interesting places such as applications and data services. One of the most common places where people get their data today is Reporting Services reports. So let’s start with some great news for Gemini users.
In SQL Server 2008 R2, every single Reporting Services report can be used as a data feed for your Gemini models! This means that users can get refreshable data feeds sourced from all the places where SSRS can get data, without any further work required to make data available for Gemini.
Once you have found a useful report, you can add it to your Gemini model by clicking the new orange “Export to Data Feed” button on the toolbar. This will launch Excel and take you straight into the import wizard. If you already have Excel workbooks open, you will be prompted to either pick an open model to add the feed to, or create a brand new model.
The import wizard for data feeds is very similar to the wizard for other types of data. In particular, you can preview data and pick which columns from the feed to add to your model. If the data feed contains multiple tables, such as a Reporting Services report with different data regions, then you can pick which tables to add and specify table names.
You can also start from the Gemini client, browse your report server for a useful report and add it to your Gemini model. Whether you start from the report, or start from the Gemini client, you can add as many feeds as you need to build your model, including combining it with data from other sources (see Edward’s blog on Importing data into Gemini).
Reporting Services is just one of the many places where you can get data feeds. You can also use data feeds from the Internet or other sources, as long as they follow the REST and Atom model (see next section for technical details on developing a custom data feed). One really cool place for getting data is the Open Government Data Initiative (OGDI) at http://ogdisdk.cloudapp.net/ which is built on the Azure Services Platform.
If you know the URL of the data feed, you can use the From Data Feeds button in the Gemini client to import it. You can also set up advanced properties to have more control over how the data shows up, including choosing options like whether to include all the standard Atom columns like title and author name which are not shown by default.
If you are a developer, you are probably already wondering how you can enable Gemini users to get data feeds from your applications. The good news is that there are lots of easy ways to support this. The basic technology is REST and Atom, so if you already have web services support you are in good shape to get started. One of the simplest ways to build a data feed is to use the great support for syndication in WCF. You can add your custom columns to the feed using the extensibility mechanisms that they supply, see one of the OGDI feeds mentioned above for an example, such as http://ogdi.cloudapp.net/v1/gsa/ConusPerDiemRates2009/ (you may need to turn off feed reader view in your browser to be able to see the raw XML).
If you are developing on the .NET platform and want to build a flexible and powerful data service, then ADO.NET Data Services (“Astoria”) is your best bet. If you haven’t used this technology yet, you will be surprised at how few lines of code it takes to get a huge amount of functionality, including everything you need for data feeds in Gemini.
Other than the data feeds themselves, if you want to add the orange data feed button to allow users to launch Gemini from your application or save a data feed file for later use, then you will also have to build a service document (.atomsvc file) for your data feeds. ADO.NET Data Services includes a service document already, but if you have built your own data feed then you can use WCF’s ServiceDocument class, or otherwise the format is simple enough that you can build one yourself.
If you developed have a desktop application rather than a server application that could include REST web services, but you still want to get data into Gemini, then you have a similar option. Gemini can import data from regular text files and the clipboard so in many cases users will already be able to use your data. But, if you want to let users launch Gemini right from your application to import data, you can create local Atom XML files and a corresponding data service document and then use the Gemini launcher to provide the same kind of seamless experience you saw in Reporting Services earlier.
I have tried to get data from this atom feed:
And PowerPivot wizard connect but dont get any data, this is because this service don't use REST?
PowerPivot works great with OData feeds, check out www.odata.org for more information.
Hi, I'm trying to access a data feed that requires basic authentication. I fill in the required details on the advanced screen, but when I look at the request in fiddler no header information seems to be passed in. Am I missing something?
I'm encountering a very annoyring problem using data feeds in PowerPivot for Excel. Importing the data feeds works great, and I easily create a nifty PowerPivot table off the feeds. The problem is that a day or so later, refreshing the PowerPivot data generates the following error for every data feed (except one):
Errors in the high-level relational engine. The following exception occurred while the managed IDbCommand interface was being used: The remote server returned an error: (500) Internal Server Error..
An error occurred while processing the 'LegacyLink' table.
The operation has been cancelled.
Out of line object 'DataSourceView', referring to ID(s) 'Temp_DSV', has been specified but has not been used.
These are data feeds from our Reporting Services Sharepoint site. The reports are updated overnight, so something tells me that the updating process is causing the error (the one report that does not cause data feed problems is a litle-used one that does not appear to be updating).
Needless to say data feeds are useless if they fail when the underlying data is updated. There must be a solution, but I have no ideas.
Same issue when we try and refresh the data from "SQL Out of line object 'DataSourceView', referring to ID(s) 'Temp_DSV', has been specified but has not been used."
probably I could help you guys.
I've created some sort of personal BI tool based on Power Pivots from Atom Feeds as you.
And I experienced with same issues/bugs as you:
- Out of line object 'DataSourceView', referring to ID(s) 'Temp_DSV'
By my opinion this is definitely a bug in PP. But here is several cases how to reproduce it and fix.
- create PP from a Feed, create references, rename some PP object (via spreadsheet tab renaming)
- create PP from a Feed, create references, now play with references creation/deletion, add/delete columns especially those which are PK or FK - I do not have certain test case but such steps could lead to a bug
I found only one way:
1) delete references
2) if you happen renaming objects after creating references - delete and create again those objects
3) if you happen deleting PK/FK columns - delete and create again affected objects
And finally, my PP scheme counts about 30 entities from different feeds and now it works without any errors. After several months of troubles. So I can say - there is a way :)
In my case, the problem turned out to be that the feed included a reference to a report column for the report version; although I can't explain why, the fact that the report version completely changed every time the report ran, was causing the feed to break. I edited the feeds to remove the refernece to this column and they worked fine from there.