Home - Ayman El-Hattab's Blog [MVP]

8/26/2010

Indexing files larger than 16 MB in SharePoint 2010

Tonight, I came across an interesting blog post by Fellow MVP, Todd Klidnt in which he explains how to work around the 16 MB limitation for file indexing in SharePoint 2010 using PowerShell.

“As with previous versions of SharePoint, SharePoint 2010 will not index the contents of files larger than 16 MB. There are a couple of reasons for this such as network usage pulling large files across and the time it takes to break them apart”. Click here to continue to the post.

Posted at 4:36 AM by Ayman El-Hattab | Category: SharePoint 2010 | Permalink | Email this Post | Comments (0)

8/22/2010

SPQuery to External List ignores the RowLimit attribute

Using CAML Queries is the only supported way to query external lists. It comes in very handy If you want to retrieve external data within a sandboxed application, without using a full-trust proxy. However using CAML Queries with external lists comes with some gotchas. Consider the following snippet :

SPQuery query = new SPQuery();
query.Query = "<Method Name='ReadList' />" +
"<OrderBy><FieldRef Name=\"CustomerID\"/></OrderBy>" +
"<RowLimit Paged=\"TRUE\">10</RowLimit>" +
"<Aggregations Value=\"Off\"/>";

SPListItemCollection items = lstCustomers.GetItems(query);

Unfortunately, this query just ignores the RowLimit and returns all the data from the external data source and not only the first 10 items as specified in the query. This is a known issue!

For more information :

http://social.technet.microsoft.com/Forums/en-US/sharepoint2010programming/thread/7a86ba74-ea19-42f3-bde9-690542c84552

Posted at 2:44 PM by Ayman El-Hattab | Category: SharePoint 2010; SharePoint Development | Permalink | Email this Post | Comments (0)

8/9/2010

Browsing PowerPivot Generated Cubes in SQL Server Management Studio

When publishing Excel Workbooks that contains PowerPivot objects, a real Analysis Services cube is generated on the fly, you don’t believe me?

1. Install the PowerPivot Add-in for Excel 2010 and use it to create a workbook.

2. Publish the workbook to a document library which is marked as a trusted data source (Unlike MOSS 2007, All SharePoint sources are trusted by default in SharePoint 2010 as shown below).

3. Open SQL Server Management Studio, click Connect > Analysis Services and type the URL of the workbook you just published and click Connect.

Interesting, eh ?

Posted at 12:34 PM by Ayman El-Hattab | Category: Business Intelligence; PowerPivot; SharePoint 2010 | Permalink | Email this Post | Comments (0)

8/5/2010

Presenting tomorrow : “Delivering Business Intelligence Using SharePoint 2010 Excel Services”

Tomorrow, I’ll be presenting @EgyGeeks online UG. I’ll speak about SharePoint 2010 Excel Services from both the technical and the business perspective. If you have any questions or if you need me to cover anything related to Excel Services, don’t hesitate to leave me a comment here.

For more info, follow the hashtag #EgyGeeks on twitter.

Posted at 3:25 PM by Ayman El-Hattab | Category: Business Intelligence; Conferences and Events; Speaking | Permalink | Email this Post | Comments (0)

8/4/2010

Excel Services limitations and workarounds

Yesterday, while presenting about Business Intelligence using SharePoint 2010 Excel Services, we came across the data validation limitation. NOTE: Excel Access Web Part refuses to load a published Excel workbook that contains Data Validation.

Here are couple of interesting articles that are worth reading:

1) A PivotTable Trick That Brings Data Validation to Excel Services http://blogs.msdn.com/b/excel/archive/2008/02/13/a-pivottable-trick-that-brings-data-validation-to-excel-services.aspx

2) Unsupported Features in Excel Services
http://msdn.microsoft.com/en-us/library/ms496823.aspx

Posted at 11:05 PM by Ayman El-Hattab | Category: Business Intelligence; SharePoint 2010 | Permalink | Email this Post | Comments (0)

7/31/2010

Cannot Refresh or Filter Data in Published Workbooks [Excel Services – SharePoint 2010]

As shown in the screenshot below, I was trying to consume data from SQL Server Analysis Services cube using Excel 2010 Pivot Table.

Refreshing and filtering data worked as expected on the client but when I published my workbook to Excel Services, filtering and slicing produced the following error :

“The data connection uses Windows Authentication and Excel Services is unable to delegate user credentials.”

Of course, the workbook existed in a trusted location and the external data connection file resided in a trusted connection library.

To solve this issue, I navigated to the Central Administration and started “Claims to Windows Token services” as shown below :

This allowed me to refresh data using Windows Authentication…

Posted at 11:09 PM by Ayman El-Hattab | Category: Business Intelligence; SharePoint 2010 | Permalink | Email this Post | Comments (0)

Using external data in Excel Services [MOSS 2007]

Excel Services supports displaying data from external locations if those locations were configured as “Trusted”. Try creating a simple excel document with a Table that displays data from a SQL server table . Now try to view it in Excel Web Access. You will get the following error:

After some investigations, I found out that Excel services supports displaying data from external data sources, but not in a table format, only in a pivot table!

To work around this limitation, you need to convert your tables to pivot ones? How ? Download this tool and you are good to go !

For more info : http://blogs.msdn.com/b/cumgranosalis/archive/2006/11/03/query-tables-work-around-for-excel-services.aspx

http://msdn.microsoft.com/en-us/library/cc514223(office.12).aspx#MOSS2007TenTips_ConfiguringExternalDataConnections

Posted at 2:39 PM by Ayman El-Hattab | Category: SharePoint Development; Business Intelligence | Permalink | Email this Post | Comments (0)

7/29/2010

Error when publishing to MOSS 2007 "This file cannot be saved to this location because there is no connection...."

Yesterday, I was playing with Excel Services on an old MOSS 2007 VM that has no network. When publishing an Excel Spreadsheet to Excel Services, I received the following error:

"Microsoft Office Excel This file cannot be saved to this location because there is no connection to the server. Check your network connection and try again. "

After some quick research, I found out that this problem is related to the System Event Notification Service. To work around this issue, I ran net stop sens from the command line.

And I was able to publish the Excel spreadsheet :)

Posted at 12:03 PM by Ayman El-Hattab | Category: Business Intelligence; SharePoint Development | Permalink | Email this Post | Comments (0)

7/27/2010

Cool Stuff : All up Business Intelligence Demo – Release 9.2

Microsoft has developed a comprehensive Business Intelligence solution demo (version 9.2) using SQL Server 2008 R2, Microsoft Office 2010 and SharePoint 2010 technology. This solution enables Business Intelligence for everyone at Contoso (fictional company created by Microsoft) through familiar tools, self–service capabilities, and access to critical business information.

There are two ways to access this demo solution, online remote access to server configured by Microsoft or via configuring virtual machine locally.

The Demo Release 9.2 has been made available on the Microsoft Partner site for download. There are 42 files in the download (around 29 GB in all, once extracted though over 80). Need some serious RAM and of course Hyper-V for it to work.

Key Features:
• SharePoint 2010 Insights, PerformancePoint Services, Excel Services, Visio Services, Reporting Services, Search, Social Relevance, Communities, Team Sites, People Profiles, Blogs, Organizational Browser, Workflows
• Silverlight, integrated with Bing Maps for the Enterprise and SQL Server Analysis Services
• Office 2010 Web Applications, Excel Co-editing
• PowerPivot for SharePoint, Excel
• PowerPivot Management Dashboard
• Report Builder 3.0
• SQL Server 2008 R2 DB Engine

Get it from here: http://www.mssalesdemos.com

For more info : http://bisqlserver.rdacorp.com/2010/03/all-up-bi-demo-release-92.html

Posted at 10:25 PM by Ayman El-Hattab | Category: SharePoint 2010; Business Intelligence | Permalink | Email this Post | Comments (0)

Interview with Microsoft MVPs

Just thought of sharing this interesting interviews with you … I really enjoy reading them and knowing the different learning techniques and strategies of other fellow MVPs.

Click here to read the interviews and click here to read mine :)

Posted at 9:18 PM by Ayman El-Hattab | Category: MVP | Permalink | Email this Post | Comments (0)

RSS Feed

1 - 10

Ayman El-Hattab's SharePoint Corner

Ayman El-Hattab's Blog [MVP]

‭(Hidden)‬ Google Analytics