Dynamics GP, Microsoft Office, Sharepoint, Uncategorized

Deploying Business Intelligence to the end user with PowerPivot

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


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




§ 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


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.



Dynamics GP, Sharepoint

Microsoft Dynamics GP Business Analyzer–New reporting tool

The Microsoft Convergence conference has come to an end so it is time to go back to work and implement the new products that were demonstrated  in the sessions. The new Microsoft Dynamics GP Business Analyzer is the first product that I am going to install from the GP 2010 installation disk.  This will allow my end user to run their reports from the desktop without having to log into GP.  This will save time for the users who just want to run a quick report.

It requires SQL 2008, Dynamics GP 2010 R2 and Reporting services 2008 or later. You can pull from a GP database that is on SQL 2005 but the reporting services must be running on SQL 2008 or later. Lync turns on extra features but is not required for the installation.  If you want the map functionality then you need SQL 2008 R2.

To install Business Analyzer you get the installation Disk for GP2010R2 and look under additional products.  Select GP Business Analyzer.  You need to enter where your reporting services server is so if you don’t know you need to go to the Reporting services configuration manager and look under the web service URL – this is your reporting services server location.

After Business Analyzer is installed you go to Start – Programs – Business Analyzer and the configuration window opens. You select the role and it will add default reports.

The top window is the primary report area where your report is displayed. You can use any reports written in SQL reporting services.  you can resize the report areas, use charts, pull from multiple companies, upload photos or images.

The actions are on the left hand side where they are buttons for information, view and drill back.

This looks like it could provide a simple platform to deploy reports.  I am going to try to install on a terminal server and see if staff can log in and run reports remotely.

Should be fun to play with since you can run any reports in it – It can connect to any datasource so it is not just tied to GP.

Dynamics GP

Performance Point server, FRX and Forecaster

My Rant on FRX and Dynamics GP –
Microsoft today anounced that they will retire PerformancePoint as a product. I first saw this in the Blog by Peter Koller http://peterkol.spaces.live.com/blog/cns!68755AEAC31F9A6C!992.entry – I wasn’t really surprised because I have not been able to get any clear answers from Microsoft on what the upgrade path is for Forecaster, FRX and webport. 
I was in the beta testing on Performance Point and it is impressive but it is complicated to use for staff and the licensing was too expensive.
We are currently using FRX Forecaster for budgets, Webport to post the financials for staff viewing and FRX for financial Reporting.  The software is all old and too complicated for an average staff person to use.  I have helped several associations with modifying or creating reports in FRX because they don’t have the inhouse resources.  FRX is very old and not user friendly many people that I talk to don’t like the product but use it because it comes with Dynamics GP. 
Forecaster has never been fully adopted by my staff because it isn’t Excel and they only want to use Excel for their budgeting.  I set up the budgets in Forecaster and then copy the worksheets into Excel for the staff to enter.  Then we manually enter the numbers back into forecaster to roll up and import into Dynamics GP – I use Crystal to pull the data into a spreadsheet and then use the budget integration tool to pull the budgets back into Dynamics GP.  Great Job security since I am the only one who knows how to do this but this should all be seamless. 
The staff should be able to enter everything – including details and notes into Excel and have it automatically tie into Dynamics GP in a temp budget file that with workflow would be able to be approved and moved to the final budget. They should be able to view the data and run different scenarios easily.  They should also be able to compare to prior years budgets and actuals with just drag and drop functionality.
The financial statements should also be easy to modify and change views. You should be able to publish the financial statements to Sharepoint site with alerts and you should be able to print to adobe and email directly to staff from FRX. 
I have talked to other associations that are using other products but we have invested in Microsoft and are looking for them to add this functionality to their product.
Forecaster 7.0 has some functionality closer to Excel but it is still complicated to set up which leaves me as the only one who can maintain the product.
I have heard that Webport is no longer being supported but Microsoft has not given me an alternative yet for it. They did say that it moved to FRX reports but I am not licensed for that part of FRX so I don’t know if they will give me license for this other product.  They should have made the drill down viewer free so I could send the financials to staff and they could use the drill down viewer (Microsoft had talked about having a viewer like Adobe for FRX reports when they purchased FRX)
I have been waiting for Microsoft to come out with an amazing budgeting, forecasting and reporting product to go with Dynamics GP and had High hopes that Performance point would be that product.  Looks like they have given up on trying to use it and everyone was right that told me Microsoft was just buying proclarity to get rid of it as a competitor.
I am looking forward to Convergence in New Orleans this year because I hope they are going to have some direction for the products that I have invested so much in.
We have invested in MOSS 2007 so I am fine with them moving everything over to SharePoint. I just want to know that they have a plan…