Home » Dynamics GP » Deploying Business Intelligence to the end user with PowerPivot

Deploying Business Intelligence to the end user with PowerPivot

Archives

Twitter Feed

Categories

Retrieve data using queries in Microsoft SQL Server®2005 and 2008, Microsoft SQL Server Reporting Services, Microsoft FRX, SmartList Builder software & Microsoft Excel®2010 with PowerPivot software

Deploy PowerPivot in SharePoint 2010 to give staff access to data

With PowerPivot you can create browser based Business Intelligence pages in SharePoint by uploading Excel workbooks with the PowerPivot add in using SQL queries and views to pull real time data from multiple sources including SQL Databases, Excel Workbooks and Microsoft Dynamics GP, Forecaster, Encore and your Association Management System.

Background Information

§ I love collecting code and re-using it as much as possible to save time and money and leverage SharePoint.

§ I gather views by searching the internet sites like “GPUG’s Share My Code” site, SharePoint Users groups and CodePlex.

§ I belong to several users groups including GPUG, TheSUG, AUG and FEDSPUG where I get valuable information on SharePoint tips and tricks.

§ What is a PivotTable?

§ A PivotTable report is an interactive table that automatically extracts, organizes, and summarizes data.

§ PivotTables allow you to summarize and analyze large amounts of data in excel.

§ PowerPivot allows you to connect to Tables, Views or write a query to pull data into a list from multiple sources

§ What is required to install PowerPivot for Excel?

§ Microsoft Office 2010 and the add on PowerPivot for Excel

§ What is required to deploy Excel PowerPivot reports to SharePoint?

§ SharePoint 2010 SP1, Windows®2008 R2, Microsoft SQL Server 2005 and 2008, Office 2010, PowerPivot and Power View software

§ Download Powerpivot from the Microsoft site

§ http://www.microsoft.com/en-us/bi/powerpivot.aspx

§ It is an Add in that self installs when you open Excel.

§ Open Excel

§ Create a new Blank Workbook

clip_image003
clip_image004

§ Row Fields are fields from the source data that are assigned to a row layout in a PivotTable.

§ Column Fields are fields from the source data that are assigned to a column layout.

§ Report Filters are fields from the source data that act as filters in a PivotTable report

§ Items are the subcategories of a row, column, or report filter.

§ Values Fields are fields from the source data that contain values to be summarized.

§ The Data Area is the range of cells in a PivotTable report that contains summarized data.

clip_image006

clip_image007

clip_image009

§ Components of PowerPivot for SharePoint

Client and server components integrate with Excel and Excel Services in a SharePoint farm.

On a SharePoint farm, Analysis Services runs on an application server where it is paired with related services that handle requests for PowerPivot data.

§ PowerPivot client and server components

clip_image011

PowerPivot Web service runs on a web application server.

It redirects requests from the web application to a PowerPivot System Service instance in the farm.

An Analysis Service server instance in SharePoint integrated mode completes the deployment. It loads, queries, and unloads data.

It also processes data if the workbook is configured for PowerPivot data refresh.

clip_image013

clip_image015

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: