Jun 21
Business Intelligence and SharePoint 2010

The Microsoft Business Intelligence toolset includes many components such as data source integration (SQL Server Analysis Services; SAS and SharePoint lists), Integration tools (Business Connectivity Services; BCS, and SQL Server Integration Services; SSIS), and End-Users UI tools (Excel, Visio, and Access and their associated SharePoint services).

EXCEL 2010

Microsoft Office Excel has been the tool of choice and the most recognizable instrument used when exporting and compiling ad-hoc data into spreadsheet form to create tables, graphs, and charts.

Let's examine Excel 210 and how we use it today without any advance add-ins or services. Generally we export a set of data from an external source and manipulate the data to represent the set of important data we are interested in. Then we graphically build charts, graphs (trend lines), and tables to display this data. In this example (Figure 1) used from the SharePoint 2010 BI samples workbook you can see that we have Slicers (Item Name and Ship Province) categories that are clickable. When you click these categories the sheet graphics are instantly updated with the appropriate data that represents the category you selected. This is a very useful way of using Excel 2010 to consume datasets to present graphical sense of the data.

However how do you share this sheet with others?

In most circumstances you either send your colleague a file server UNC (Drive mapping) or send it to them in e-mail. However what happens when the data that created this spreadsheet is updated and quickly becomes outdate? The Associate VP and Deans from each school decide to review this data to decide next year's tuition and how to best address research grants fiscal disbursements. However not knowing that the dataset was updated last night during a monthly data refresh, now their decisions are based on old data you that you built and presented to them yesterday.


SHAREPOINT EXCEL SERVICES


With the combination of SharePoint and Excel you gain considerable power and control of data publishing. Using the SharePoint 2007 or 2010 Excel services (Figure 2) you are now empowered to present these spreadsheets through SharePoint web parts. Instead of sending these spreadsheets to the AVP or Deans using UNC or e-mail, you now simply send them a website URL. Now they all access the same data from the same location from anywhere they have secure internet access. If you then realize that just before the AVP and Deans meeting the data has changed you simply update the master spreadsheet and publish the new data to the SharePoint Excel Services web part and now those decisions being made are being made with the best and most accurate data available to them. With Excel Services 2010 user are no able to click in the Excel web part and update the information instantly redrawing the graphs, line charts, and any other visualizations that have been created.

POWERPIVOT CLIENT ADD-IN FOR EXCEL 2010
Note* SharePoint Servers must be running the Excel Services and PowerPivot for SharePoint. Also enable the Site Collection feature named Analysis Services Integration.

The Office PowerPivot Client Add-in feature for Excel has significantly improved and enhanced the robustness and capability to crunch large datasets.

More to come…

POWERPIVOT FOR SHAREPOINT (ANALYSIS SERVICES WITH SHAREPOINT)
Once the SharePoint 2010 farm Pivot technologies are enabled you are able to load the PowerPivot enabled Excel workbooks to SharePoint. The SharePoint PowerPivot System Services queries and manages PowerPivot data in Excel workbooks.

More to come…

SQL SERVER REPORTING SERVICES

With the Reporting Services Integration feature capabilities of SharePoint 2010 you can integrate existing Report Server services into SharePoint 2010.

http://technet.microsoft.com/en-us/magazine/ff686706.aspx

PERFORMANCEPOINT SERVICES

More to come…

Comments

There are no comments for this post.

 ‭(Hidden)‬ Blog Tools