Power of Excel, BI.

What is BI?

Business intelligence (BI) is the process of aggregating, storing, analyzing, and then reporting that data in order to make decisions and future forecasting, current business state, Risk involve, predict,manage business performance and take business decisions.

Tools Selection for BI.

In current market there are lots of BI tools available for analysis. While selecting BI tools it is advised that to select the tool which can interact with all the versions of Microsoft Excel effectively and also should be able to interact with other MS Office products. 

Why excel support is required in BI Tools?

Lots of BI vendors say that "Our Bi Product offer analysis". But if you check in the market there
are lots of BI vendors who have changed their tool to support Excel or use the power of Excel in analysis and reporting, like Cognos, which unveiled Go! Office; Business Objects, which introduced Live Office; and Information Builders, which recently introduced Quick Data for WebFocus. Excel is used widely because Excel has lots of in built spreadsheet functions, analytical features, and universal presence on desktop.

The current king of Business Intelligent (BI) tools is Microsoft Excel. Microsoft states that there
are over 150 million excel users, with many of them using Excel for BI-reporting and analysis of corporate data.

The spreadsheet program that some experts have called “KING OF BI”, “Number One BI
Tool” and I Say “Heart of BI” because without excel its very difficult to get BI reports and analysis. Excel is more widely used for planning purposes. To prepare plans, one should have to starts from actuals, and that actuals comes from BI Report. In the absence of a planning system, there is not better place to enter plan data than in excel.It's perfect for entering data, doing what-if-analysis or producing forecasts based on formulas, thanks to its analytical features and its universal presence on desktop.

Also in the perspective of the software development or IT professionals rarely prefer the Excel as BI Tool but BI reports are very dynamic and the tool which is developed for the BI should be very flexible which can adopt the change immediately and produce report.

Frequent changes in Business Logic and tracking Metrics require the power of the change
at end-user. Most of the time the changes require is very simple but critical from business perspective. In case of BI tools the end user/ management have to take help of the IT/ BI specialist developer for customization, this involves to go through whole SDLC, Find sponsor for project, request for requirement change then management will assign project to one development team, and then after some period of time the reports added to existing tool or new tool is developed on the basis of the requirement. The whole process for additional changes will
become very long for Business. However if Excel is used as BI, Excel is the Smart tool which allows users to change reports in a few minutes or hours and which is the most important feature for the BI Tool that enable users to rapidly develop and maintain dashboards, including dynamic, interactive data capabilities such as drill-down, filter, and sort. Second thing is that customization can be done by the User him self, it doesn’t required any programmer. If user is aware of some
coding (VBA) skills then he can do automation of the report and reduce manual input work.
With Excel BI User can analyze and report data from any number of sources on one page.

No other BI system provides such analytical power.
Excel 2007 is the new version of Excel with more analytical and reporting features. It provides
more spontaneous access to OLAP data, conditional formatting based on the KPI. Excel 2007 is more powerful because of the feature of integration with Microsoft SQL Server 2005 Analysis Services to provide a rich end-user analysis, newly designed PivotTable reports, a new formula-based model for accessing Analysis Services data, improved sorting and filtering capabilities, and a completely redesigned visualization engine. Bi user can share Excel workbook to browser based environment using SharePoint Excel services. It can fully interactive, data-bound workbooks that include charts, tables, and PivotTable reports as parts of a portal, dashboard, or business scorecard, without requiring any help from IT or development team, and that present a "single version of the truth."

Alex Payne, group product manager, Microsoft office Business Applications, says “We have different approach when we say BI features are just part of Excel.” It is not separate add-on that you pay for. We are providing BI natively inside Excel.” 

Excel Services for BI 

It is great to use excel to create BI Reports and analysis, but if you give this copy of excel which
contains report to many people and in future if process or functionality needs to be changed,then its very difficult to redistribute the new version of Excel report to the whole user group. “One version of truth”…. Where excel based BI system fails.There is also a chance of exposing the business proprietary formulas to people or user group who are not meant to see it. The problem we all face is “HOW DO YOU GET THIS DATA PUBLISHED SO OTHERS CAN USE AND INTERACT WITH THE DATA WHILE KEEPING PROPRIETARY INFORMATION HIDDEN.” Answer is Excel services.

Excel Services is a new server technology that was designed from the ground up to be scalable and robust with web based interface which uses Excel Web Access which is created by using client application Excel 2007. Workbook authors no longer need to send them in e-mail messages. Sending workbooks in e-mail messages often results in multiple copies of a workbook, essentially creating conflicting the "single version of truth" and exposing proprietary formulas and logic.

Another advantage is in a multi user scenario, each user has own web session. The server loads the workbook in memory according to the user permission of access read-only, (or targeted
user group defined by author).If a user applies a filter to a workbook, other users are not affected by those changes, and their interactions do not modify the original file.

To create a PivotTable report, user can interact with Microsoft SQL server analysis services data, select the cubes, fields.The new cube functions added to the Excel 2007 and Excel Services which add on to the business intelligence. Using that You can convert PivotTables directly into cube functions which enables general users to rapidly model data when needed, to support business decisions.

With the Excel 2007 business user can ascertain how to respond change needs without the involvement of the IT or Programmer. Using cubes Users can pull in data from SQL server analysis services and various other data sources, which greatly increases the flexibility to combine external data points and create reports and analyse the data effectively.

In Excel 2007, a user has full control over the content, whereas in Excel Services, the workbook author can control the level of functionality that is available to the workbook consumer, in addition to controlling the visibility of formulas and the ability to edit data values.

Following snap of Excel 2007 shows the Pivot table with different types of the KPI presented in various ways from cube data.  

Bringing Data Together in Dashboards

You can use the new features in Excel 2007 and Excel Services to access, manipulate, and display data in new and different ways. When data is ready for publication, information workers can combine their data with other data in dashboards. Dashboards enable visualization and reporting on large amounts of data in an easily consumable format. Excel Services and SharePoint Server 2007 have the ability to create business dashboards is a key business intelligence capability. Below figure shows a dashboard that was created by using Excel Services, KPIs, and filtered Web Parts from Office SharePoint Server 2007. It focuses on identifying trends, anticipating challenges and opportunities, and supporting business decisions.

Dashboard-using Excel Services

No code is required to create dashboards. SharePoint Server 2007 includes a set of Web Parts that enable users to develop and maintain dashboards, including dynamic, interactive data capabilities such as drill-down, filter, and sort with familiarities with Excel by using PivotTables reports, Excel charts, and formulas. Dashboards can also take advantage of SharePoint Server 2007 KPI web parts which are external to Excel, which are highly visual representations of important business factors.

One big limitation with excel services is the inability to utilize the excel object model and a spreadsheet with the VBA code behind cannot be published to Excel Server.

SQL server reporting-services Integrated with Windows SharePoint Services v3 to enable publishing, viewing and management of reports.

In SharePoint, the data for KPIs can come from any SharePoint list, an excel workbook, SQL server analysis services or it can be manually entered.

The data is exposed using a KPI list and KPI appropriate data. The KPI web parts then expose that data in to SharePoint site.

Conclusion
Excel Services enables end users to create robust solutions without involving developers, and it  gives workbook authors, database administrators, and developer’s greater control over the environment. The combination of the Excel Web Access and the Excel Web Services API provides rich and versatile access to workbooks. At the same time, the security model is the same whether you access the workbook through Excel Web Access or programmatically through the Excel Web Services interface.

Workbook authors use Excel 2007 to create workbooks and accompanying business logic, including connecting directly to back-end data sources for live data. Those authors, including non-technical users, can create workbooks, reports, and dashboards with dynamic data, more securely sharing that information across the enterprise and making it available to other users and applications. Developers can take advantage of business logic created by those business users without needing to re-create it in application code. The physical infrastructure that underlies Excel Services is highly scalable and configurable. This enables you to support changing business needs as they arise and provide guaranteed availability for mission-critical calculations.

The combination of Excel Services and Excel 2007 enables both technical and non-technical users to securely share, manage, and reuse data and business logic, creating a true enterprise solution for workbooks.

WSP Builder: ERROR: Could not load file or assembly 'CabLib, Version=6.9.26.0, Culture=neutral, PublicKeyToken=85376ef9a48d191a' or one of its dependencies. An attempt was made to load a program with an incorrect format.

PROBLEM:

I'm running the latest release of WSPBuilder and extnsions for Visual Studio 2008. I installed the MSI. I am running on a Windows Server 2003 system with SharePoint on it.

When I was trying to build the WSP then I was getting below error:

"Could not load file or assembly 'CabLib, Version=6.9.26.0, Culture=neutral, PublicKeyToken=85376ef9a48d191a' or one of its dependencies. An attempt was made to load a program with an incorrect format."

I searched for cablib.dll in the WSPBuilder folder, found two versions of cablib.dll one is marked as x64 and x86.

Solution:

I copied the cablib.dll from the "C:\Program Files\WSPTools\WSPBuilderExtensions\Resources\x86"

directory and paste it in the root "C:\Program Files\WSPTools\WSPBuilderExtensions\"

Then everything works fine.

Use the ‘Comments’ form to share your thoughts.

SharePoint Capacity Planning

SharePoint Capacity Planning There is no hard limit enforced by the system, the most useful parementes for the capacity design are listed in the table below.  The capacity planning for MOSS 2007 from Microsoft can be found at http://technet2.microsoft.com/Office/en-us/library/9994b57f-fef8-44e7-9bf9-ca620ce207341033.mspx?mfr=true and is summarized here Object Scope Guideline for optimum performance Comment Site collections Database 50,000 Total throughput degrades as the number of site collections increases. Web sites Web site 2,000 The interface for enumerating subsites of a given Web site does not perform well much beyond 2,000 subsites. Web sites Site collection 250,000 You can create a very large total number of Web sites by nesting the subsites. For example, 100 sites each with 1000 subsites is 100,100 Web sites. Documents Folder 2,000 The interfaces for enumerating documents in a folder do not perform well beyond a thousand entries. Documents Library 2 million You can create very large document libraries by nesting folders. Security principals Web site 2,000 The size of the access control list is limited to a few thousand security principals, in other words users and groups in the Web site. Users Web site 2 million You can add millions of people to your Web site by using Microsoft Windows security groups to manage security instead of using individual users. Items List 2,000 The interface for enumerating list items does not perform well beyond a few thousand items. Web Parts Page 100 Pages with more than 100 Web Parts are slow to render. Web Part personalization Page 10,000 Pages with more than a few thousand user personalizations are slow to render. Lists Web site 2,000 The interface for enumerating lists and libraries in a Web site does not perform well beyond a few thousand entries. Document size File 50 MB The file save performance degrades as the file size grows. The default maximum is 50 MB. This maximum is enforced by the system, but you can change it to any value up to 2 GB (2047 MB) if you have applied Windows SharePoint Services Service Pack 1. Object Guidelines for acceptable performance Notes Scope of impact Site collections per Web application 50,000 Total farm throughput degrades as the number of site collections increases. Farm Web sites per site collection 250,000 You can create a very large total number of Web sites by nesting the subsites. For example, 100 sites each with 1000 subsites is 100,000 Web sites. Site collection Subsites per Web site 2,000 The interface for enumerating subsites of a given Web site does not perform well as the number of subsites surpasses 2,000. Site view Documents per library 5 million You can create very large document libraries by nesting folders, using standard views and site hierarchy. This value may vary depending on how documents and folders are organized, and by the type and size of documents stored. Library Items per container 2,000 Testing indicates a reduction in performance beyond two thousand items. Using indexing on a flat folder view can improve performance. List view Document file size 50MB (2GB max) File save performance is proportional to the size of the file. The default maximum is 50 MB. This maximum is enforced by the system, but you can change it to any value up to 2 GB (2047 MB) if you have applied Windows SharePoint Services Service Pack 1. For more information, see Configuring large file support in Installing and Using Service Packs for Windows SharePoint Services. Library, file save performance Lists per web site 2,000 Testing indicates a reduction in list view performance beyond two thousand entries. List view Field types per list 256 This is not a hard limit, but you may experience list view performance degradation as the number of field types in a list increases. List view Web parts per page 100 This figure is an estimate based on simple web parts. The complexity of the web parts dictates how many web parts can be used on a page before performance is affected. Page Web Part personalization per page 10,000 Testing indicates a reduction in performance beyond a few thousand user personalizations. Page Features per Web application 250 As the number of activated features per Web application increases, so does the memory usage of the w3wp process. This value is an estimate; your environment may support more or fewer activated features for a given Web application. Farm People objects Object Guidelines for acceptable performance Notes Users in groups per web site 2 million You can add millions of people to your Web site by using Microsoft Windows security groups to manage security instead of using individual users. Security principals per web site 2,000 The size of the access control list is limited to a few thousand security principals, in other words users and groups in the Web site. User profiles per farm 5,000,000     Personalization and Search objects Object Guidelines for acceptable performance Notes Search indexes 1 per SSP*   Content sources 250   Search scopes 250   Best bets 25,000   Audiences 10,000   Audience memberships 5,000,000   Indexed documents per content index 5,000,000   Indexed documents 20,000,000   Thesaurus entries 10,000   Alerts 1,000,000    Logical architecture objects Object Guidelines for acceptable performance Notes Shared service providers 3 (20 max)   IIS app pools 8 Maximum number is determined by hardware capabilities. Web applications 4 Maximum number is determined by hardware capabilities. Web applications per SSP 99   Site collections per Web application 50,000   Zones per farm 5* The number zones defined for a farm is hard coded to 5. Content databases per Web application 100   Site collections per database 50,000   Physical objects Object Guidelines for acceptable performance Notes Index server 1 per SSP*   Excel calculation server No limit   Search server No limit   Query server 1 per database*   WFE servers per database server 8*   WFEs per Domain Controller 3 Depending on how much authentication traffic is generated, your environment may support a greater number of WFEs per domain controller. Level Performance limits Site collection 2,000 subsites of any site is the recommended limit. The same content database is used for an entire site collection. This may affect performance in operations such as backup and restore. Site 2,000 libraries and lists is the recommended limit. Library 10,000,000 documents is the recommended limit. 2,000 items per view is the recommended limit. Folder 2,000 items per folder is the recommended limit