Category Archives: Business Intelligence

Know-Your-Tech: SQL Server & Power BI Event – Dubai, UAE

SQL Server and power BI session on 27th April at Microsoft Office, Dubai Internet City

If you are an IT Professional and looking for a breakthrough performance to accelerate your organization’s business and enable new, transformational scenarios; or if you want to see how to create dashboards, reports and also explore new Power BI Designs. This is the session for you. REGISTER NOW!

Untit_powerbi_led

reg

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).

6

 

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

7

 

8

 

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.

5

Here are couple of interesting articles that are worth reading:

1) A PivotTable Trick That Brings Data Validation to Excel Serviceshttp://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

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.

1

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

2

“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 :

3

4

 

This allowed me to refresh data using Windows Authentication…

 

 

 

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:

12

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 !

13

 

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

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

esterday, 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.

10