You can use your favorite social network to register or link an existing account:
Or use your email address to register without a social network:
Sign in with these social networks:
Or enter your username and password
Forgot your password?
Yes, please link my existing account with for quick, secure access.
No, I would like to create a new account with my profile information.
Today's author, Pej Javaheri, a Product Manager on the SharePoint team, talks about configuring Excel Services to work with the just-released SQL Server 2008.
With the recent announcement of SharePoint Server 2007 supporting SQL Server 2008, like you, I was excited to setup my Excel / Excel Services environment to take advantage of the great new capabilities available, and there are many. I encourage you to take a look at how the new SQL environment will benefit your SharePoint deployment, and your Business Intelligence reporting and analysis capabilities.
SharePoint Server 2007 can use SQL Server 2008 as a repository without any extra configuration requirements. What about connecting Excel client to the new Analysis Services environment?
Excel accesses Analysis Services 2008 the same way it does 2005. From within Excel, select the Analysis Services drop down from the Data tab -> From Other Sources drop down, and then walk through the data connection wizard to identify location, cube, and credentials. Ensure that the necessary SQL Server 2008 client components are installed prior to making the connection as the provider for Analysis Services 2008 is MSOLAP.4 (more about this later).
You should now have a connection to a cube within Excel and a pivot table ready.
So far so good right? With a few simple clicks you are accessing the cube on Analysis Services 2008. You've created a great looking report, and now want to distribute it on SharePoint Excel Services. Here's how you go about setting up the connection so that Excel Services can access Analysis Services 2008.
If you take a look at the connection string in Excel, you'll notice that connecting to Analysis Services 2008 uses the following provider, MSOLAP.4. This is not in the list of providers that ships with Excel Services so you will need to add this to the list. You will also need to install the client access components of SQL Server 2008 on each of your SharePoint servers that will require access to SQL Server 2008. For example, you can install the client access components on the Shared Service where Excel Services runs.
To add the provider to the list of approved providers in Excel Services, go to the Shared Services administration page (Central Administration -> Shared Services Administration) for Excel Services. Select Trusted Data Providers from the Excel Services Settings of the Shared Services Administration page. You will see that by default MSOLAP, MSOLAP.1, MSOLAP.2 and MSOLAP.3 are installed by default. You will need to add MSOLAP.4 to the trusted list in order for the connection to work in Excel Services.
Click Add Trusted Data Provider at the top of the list, and enter
Provider ID = MSOLAP.4Data Provider Type = OLE DBDescription = Microsoft OLE DB Provider for OLAP Services 10.0.
You are now set. Publish your workbook to Excel Services and you will be able to view, interact and refresh data from Analysis Services 2008 in Excel Services.
Comments: (11) Collapse
Pingback
Link to this post was added to our website in the [SSAS Articles]/[Excel] Section:
www.ssas-info.com/.../1096-using-excel-a-excel-services-with-sql-server-analysis-services-2008
Just a comment. Lately, your posts have been uber-technical, explaining scenarios with products most Excel users will never encounter.
I remember when someone on this blogged expressed that the posts will cover all skill levels. How about a few posts on real-world skills that users can do in Excel natively.
I guess it's cool that you can "Read Excel Files from Linux", but who's asking?
Please don't let this blog become the MSDN Magazine of Office blogging.
Mike - I appreciate the feedback and honesty.
A quick bit of background: Originally this blog was created to talk about the new features in Excel 2007. And when we go public with details about the next version of Office, this will be the place to hear about new Excel features again. In the meantime, my approach has been to let others across the team post articles about what interests them, and I haven't been imposing restrictions on what can be blogged. We have some fairly technical people on the team, and as you've pointed out sometimes the articles can be technical as well. In addition, this blog is also the home for content related to Excel Services, our server-based offering of Excel. Many of the topics related to Excel Services are necessarily technical in nature because it requires knowledge of SharePoint and related technologies and are directed at Admins and Solution Developers.
So, in short, I hear you. It's a totally valid point. Not sure yet the best way to do this, but I'll try to bring it down a notch or two.
Thanks for your continued support.
-Joe
On my job I use only SQL server but is good to know new ideas on how to improve our methods and routines.
Thanks and now I am a faithful reader of this blog.
Luiz Martins
www.g1brazil.com
I like the mix in this blog from absolute beginner to expert. There are subjects that interest me and those that dont. I know how to skip the ones that dont. Please keep the content as close to this as possible. The mix is perfect.
Gents and of course ladies - another off topic comment (apologies), but it's best to hear from the horse's mouth, and frankly, I don’t know where else to turn to.
What is the best way to report a bug. It is a bug. It is a bug with Office 2007. It is most likely a bug with Excel 2007 (it is OLE behavior, but reason suggests it is on the Excel side). It persists through SP1 and all updates, but I don't think it's introduced in the SP.
I have tried support (they don't listen, and seemingly don't understand), MSDN (I was told in no uncertain terms that MSDN is limited to how-to discussion and I have a support issue. I don't, see the previous sentence), MSFT tech communities (this was one of those issues that solicited 0 responses - which means it goes over people's heads).
A little bit about the bug - and much more I could offer but won't for brevity's sake - it is nasty: non-trivial (not easily reproducible), it is probably more common than thought (pretty common once certain conditions are met, and it covers pretty much all documents used in my line of business originally created with Office 2003 and which work perfectly in Office 2003), most users probably don't realize it's there in the normal operating environment (since you have to look at field codes to notice, and they're turned off by default and most users don't venture there), and it breaks expected behavior in a subtle but very important and impactful manner.
Please tell me what I need to do / whom to talk to.
Thanks in advance.
Ilya Barskiy
Ilya- If you've tried support.microsoft.com/select and hit a dead end, click the "Email" link at the top right of the blog page to contact me directly. Explain in as much detail as you can what the problem is. I'll try to help as best I can.
Is Excel Services only available with Sharepoint Server? That seems like a lot of expense for this one piece. Is there anyway to get Excel Services as a stand alone app? I would like to continue to offer access to OLAP Cubes over the web, but with the demise of the OWC and it's limited functionality in browsers other than IE, that is not an option.
Joseph,
I sent you a detailed e-mail. I think I could've sent it twice - sorry. Please let me know if you don't get it (and still care).
Thanks for your help.
Ilya
Ryan: Yes, Excel Services is only available in Microsoft Office SharePoint Server when you have an Enterprise CAL (client access license). It does not ship as a standalone app.
Thanks,
Joe
Comments: (loading) Collapse