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.

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 :

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 ?


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.

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

2) Unsupported Features in Excel Services