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!
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 ?
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.
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 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
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…
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
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.
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
Today’s business decision-makers have a distinct advantage over their predecessors when it comes to business intelligence (BI) tools that they can use for analysis, trending, and presentation of data. A good BI strategy, supported by a solid base of BI technologies, can streamline business processes while also boosting productivity and profit. The overall BI strategy of Microsoft comprises a suite of server and client-side data integration tools. Powerful analytical and reporting tools in SQL Server 2005 provide the backbone data management infrastructure, while Microsoft Office applications, specifically Microsoft Office Excel, provide the flexibility for information workers to remotely interact with centralized and secure data sources. This article, which is adapted from the Microsoft Press book Microsoft Office SharePoint Server 2007 Best Practices, focuses on how you can use Excel and Excel Services to analyze business intelligence data within SharePoint sites. http://technet.microsoft.com/en-us/magazine/2009.02.bookbi.aspx
You can find the steps to configure the your MOSS web site so you can deploy on it PPS dashboard in this linkthat I found on the PerformancePoint Official Blog
Ayman El-Hattab's Technology Blog 