Posts Tagged ‘MOSS’

On Friday, January 23, Microsoft announced significant changes to its business intelligence strategy.  You can find the detail behind this at http://www.microsoft.com/presspass/features/2009/jan09/01-27KurtDelbeneQA.mspx as well as a number of great blog entries.

To help our customers and partners, I want to summarize those changes then talk about how they will impact you.

Here are the announced changes:

1. Performance Point Monitoring and Analytics will be downgraded into MOSS Enterprise CALS:  This is the part of PPS that handled all dashboarding and KPI’s.  It is the very visual, very sexy part of the product that really helped it sell and also the part that worked the best.  If you own MOSS Enterprise, you now get M&A for free.
2. Performance Point SP3 Release in July/Planning Discontinued:  Planning is the portion of PPS that is the hardest to implement and was intended to replace Forecaster and Enterprise Reporting.  MS will release a performance update service pack (SP3) and then will stop development of new features.  As of July, when SP3 releases, PPS will cease to exist as a stand alone product.
3. Management Reporter To Be Managed by Dynamics:  Management reporter, the FRx replacement reporting tool in PPS, will be given to the Dynamics team to own and enhance.
4. Enterprise Reporting is Back on Support: All previous announcement of Enterprise Reporting’s support discontinuation (planned for Jan 2010) are revoked and its back on the price list.
5. Proclarity Desktop:  Gone.  Not much more comment other than that.

Why did they decide to do this?  Answering solely for myself:

1. The planning module was definitely a v1 release and required significant technical and programming expertise to implement.  However, the Dynamics channel already had good forecasting tools (Forecaster, ER) and wasn’t willing/able to adopt PPS quickly solely for that.  The channel that was adopting it (infrastructure and MOSS partners) weren’t very adept at financial planning and forecasting, so an automatic channel disconnect was setting in.
2. PPS was expensive.  In this environment, it wasn’t going to get good play.  In addition, customers with Enterprise Agreements were feeling "nickled and dimed" over the additional cost in the EA for PPS.
3. MS truly believes in "Democratizing BI", especially in the role Excel and SQL play in this space.  By pushing core BI functionality back into MOSS, Excel and SQL, they allow the widest possible audience to buy and use these features.
4. Lastly, I truly believe someone finally said "Hey, don’t you think the Dynamics team should be working on the finance stuff?".

So, what does this mean to the MS customer?

1. If you own PPS Planning, stick with it, but start looking for a transition in 2012 or thereabouts.
2. If you own PPS  and only use it for M&A, renegotiate your EA when it comes up to lower the cost of it by the PPS piece that got added.  Other than that, you are in the catbird seat.  Good job!
3. If you were looking at PPS for planning, kill the search and talk to your reseller about alternatives.
4. If you were looking at PPS for M&A, continue.  Having it in MOSS Enterprise is great news!
5. If you were looking at Forecaster, FRx, Enterprise Reporting, stop being fearful of the decision.  The products are great and will be supported into the foreseeable future.

As to Management Reporter, we don’t know what’s going to happen to that.  It was billed as the next-gen FRx, but its feature set is, IMHO, pretty crappy still.  I think it will be enhanced significantly by the Dynamics team and become a truly awesome product, but don’t look for short term results.

MS promises it will deliver more roadmap information at 2009 Convergence in New Orleans.  Will have our usual large team there, so we’ll update you after the conference.

As promised at iSight, our customer conference from last week, here is the document comparing Microsoft Office Sharepoint Server with Performance Point for Business Intelligence needs. There is some overlap between the products depending on what you want to do and this document highlights where they overlap and where they differ.

Phew…3 days of hardcore BI geekdom come to a close.  The action for today was very representative of the first two days:

Keynotes

Kurt DelBene did a nice job, along with Kristina Kerr, Senor Product Mgr.  Kurt gave a pretty good, but very stock analyst\marketing\can’t-tell-you-anything preso on the future of the BI stack and integration of the office solutions to same.  If you think self-service via Excel and greater integration with MOSS, you think right.  However, Kurt also commented that we are only "halfway through Wave 14", so you won’t see anything for at least another year.

Kristina did a very catchy demo of Microsoft Surface as a BI UI by showing water usage data for the downtown Seattle core, mapped against Visual Earth, against temperature and time.  Very cool, very 3-D.  Not a single customer I work with will ever buy it.  K:  Come up with a self service app that lets me order and pay in a restaurant without talking to a waiter, and I think you may have a winner.

From an overall perspective, the keynotes (with the exception of Ben Stein) were the definite weak point of the conference.  Little solid info, lots of marketing stuff. 

Sessions

The only one I did today was Avoiding Common Mistakes in Analysis Services:  Very nicely done by Craig Utley.  Between his discussion around Attribute Hierarchies and Attribute Relationships, he solved 4 separate problems I have currently with customer deployments.  Also, he has a great book called Business Intelligence with Microsoft Office Performance Point Server 2007. 

Vendor Pavilion

Today was the first day I spent time in the Vendor Pavilion and spent the entire time with Robert Sterling, VP of Partner Alliances, at Strategy Companion.  SC is a great vendor of BI plug in solutions for Dynamics CRM and is one of the few that produce a truly powerful and highly integrated solution. 

Hands on Lab

The balance of the day was spent in the HOL working on Performance Point Dashboards, Excel Services\MOSS, and Dynamics AX integration with SSAS.  I think I set a conference record of almost 4 hours in front of the training machines, but was very impressed by the content.

Overall Review

In general, this is a good conference and worth the money, provided you look closely at the conference agenda BEFORE you sign up.  If you’re a client of IBIS and customer of Microsoft, send your CIO or high level tech team combined with at least one SQL Server DBA.  If you are a partner, send an architect and at least on business application (ERP or CRM) consultant.  This year, only I went.  Next year, we’ll probably want to take at least one more person.

Avoid the keynotes.  Split the sessions up to cover maximum ground.  Spend lots of time in the HOL. 

Thanks for reading, folks.

Okay, once you get past the fact that the opening keynote by Stephen Elop was preceded by 80’s cover tunes played (loudly and early and before I had enough caffeine) by "The Dudley Manlove Quartet" (who had 5 members), the keynotes were definitely worth the wait.

The key (no pun intended) points:

1. MS wants to "democratize" BI. 

In essence, put easy to use tools, built in Excel, into the hands of the end user.  Then allow them to use those tools to build stuff that the rest of the organization can share through MOSS.  This "self service" BI is nicknamed Project Gemini and will start appearing in staged feature pack releases (I think that’s what they said). 

The most astonishing thing I saw was one of the program managers, working from a $1,000 workstation using Excel 2007 with the Gemini plug-in, pull in a data set of 20,000,000 rows (that’s right, 20 million rows), and dynamically build a pivot table whose dimensional relationships were created on the fly, upload the table to MOSS and, while doing so, dynamically create an Analysis Cube that everyone else could share.  And he did in in 20 seconds.  Almost any Excel knowledgeable accountant I know could do what he did if they had this loaded.

2.  SQL 2008 is totally, totally cool. 

You will now hear it here first:  I am leaving my wife for SQL 2008.  MS just set a data loading record of 30 minutes for 1TB of data. This is a world record for a wintel platform and beats Oracle by 15 minutes.  Combine that with its ability to scale massively up (they were doing demos with 150TB of data where reports were kicking out in 4 or 5 seconds) and still meet the needs of the SMB space, and SQL2008 is definitely a hotty.

The rest of the day was a plethora of preso’s on KPI’s and Dashboards in MOSS and Performance Point, combined with great session on using Excel and Excel Services to deploy good, solid BI solutions inexpensively (both on initial investment and TCO).

It appears IBIS is leading the pack in BI for the SMB space with our fixed fee offerings around Analysis Services and Excel, and especially in our work with deploying dashboards deployed in MOSS via Excel Services.

One last note:  MS has to learn not to let the marketing department try to make humorous Powerpoint preso’s for technical staff.  MS is a lot of things, but as a humorist, they really, really suck. 

More tomorrow.  If you want intra-day updates, check out my twitter feed at http://twitter.com/DwightSpecht.

Dwight

Creating a dashboard using SharePoint, Excel Pivot Charts and Microsoft CRM data is nothing new. Perform a Google search and you will receive thousands of results. However, finding information on how to create a live dashboard using the latest components of MOSS 2007, Excel Web Services, Excel 2007 and Microsoft CRM data is a bit more difficult.

1) Using the old method available in SharePoint 2003 of inserting the Office Pivot Chart web part and connecting to your CRM data source to retrieve the data does not work in MOSS 2007. The Office Pivot Chart web part is not available in this version using MOSS 2007 and Excel 2007.

2) Setting up Excel Web Services authentication methods (Windows Authentication, Single Sign On, or none) to allow the automatic refresh of the data in the spreadsheets on your dashboard page can be a bit confusing. I will discuss this in more detail later in this post.

3) Certain items used in Excel spreadsheets are not supported in Excel Web Services.

4) There is not one document that explains it all from start to finish, with all of the pitfalls highlighted. That is what I am hoping to accomplish with this post. My initial hurdles in getting this to work were all centered on getting the data to refresh with live data every time the page is opened. I kept receiving a data retrieval error saying the connection was unavailable. This was mainly the combination of the following:

a. Data Authentication

b. Creating the Excel Report based on a Query table, instead of a Pivot Table and Chart

c. Publishing the Excel Chart as a Report instead of a dashboard

This post is written based on the assumption that you have knowledge of MOSS 2007, creating a dashboard page in SharePoint and that the environment has been configured to use Excel Web Services. If not, below are some links that go into detail about how to do this and some other helpful links:

1) Plan external data connections for Excel Services – This article contain full instructions on how to configure MOSS to use Excel Web Services. Note: All of the steps in this article should be completed before moving on to building your reports and your dashboard.

http://technet.microsoft.com/en-us/library/cc262899.aspx#section7

2) Using Analysis Services data in Excel Services – This article goes into depth on configuring your servers to use Kerberos Authentication. This is required if you are using Windows authentication as your method of authenticating your spreadsheets to the data source.

http://www.tonstegeman.com/Blog/Lists/Posts/Post.aspx?List=70640fe5%2D28d9%2D464f%2Db1c9%2D91e07c8f7e47&ID=43

3) Excel Services part 12: Unsupported features

http://blogs.msdn.com/excel/archive/2005/12/01/499206.aspx

Before moving on with the rest of this post, a word on authentication; when creating your Excel spreadsheet and connecting to your CRM database to retrieve data, you have to select a method of authentication. There are three options:

1) Windows Authentication

2) Single Sign On

3) None

All three have their caveats, but the third option of “None” is the easiest to configure and is the one I have chosen to use in this example. The other two options require a much deeper understanding of Kerberos authentication and this will not be addressed in this post. Read the section entitled “Authentication to external data” in the “Plan external data connections for Excel Services” article listed above for a complete explanation on the configuration of each.

As I noted above, you need to configure your MOSS environment prior to actually creating your spreadsheet and building your page. A synopsis of the steps is listed below and is explained in detail in the “Plan external data connections for Excel Services” article:

1) Enable MOSS to use Excel Web Services

2) Add a trusted file location

3) Enable external data access for a trusted file location

4) Configure the unattended account settings – This is required if your authentication method is “None.” In this step, you will want the unattended account to be a domain user that is also a user in CRM. In my case, I just used the administrator login used when we installed CRM.

5) Create a data connection library

6) Add a data connection library to trusted list

7) Set a registry key to surface a data connection library in the client – This step is not required, but if you would like the data connection library to show up as a location to select data connections from when building your spreadsheet and creating a connection, you will have to do this. Otherwise, you can still access the location by typing in the path to the URL when browsing for your data connections.

Once you have completed these steps, you are ready to create your worksheet and integrate it into your website.

1) Create a new connection within a new Excel spreadsheet.

· Open Microsoft Excel 2007.

· Go to the Data tab.

· Select “From Other Sources.”

· Select “From Data Connection Wizard.”

· Select Microsoft SQL Server. Click Next.

· Enter your server name. It is ok to use Windows Authentication here. Click Next.

· Select your CRM database and the view in which to use. In this example, I am going to create a simple graph using the Opportunity View. Click Next.

· Select a file name for your data source that will be saved. We will need to modify the location so that the data connection is stored to the Data Connection Library on the SharePoint site. To do this, click Browse. If you do not have a link to your SharePoint Data Connection Library, you can find this by browsing to your SharePoint site using Internet Explorer and selecting the Data Connection Library link. Copy the location from the Address Bar in IE (All the way through the DataConnections only. Do not include the .aspx ending. Ex. – http://intranet/Department%20Sites/Sales%20%20Marketing/sales_dash/Data%20Connections/) and paste this before the file name you have selected.

· Select the “Always attempt to use this file to refresh data check box.”

· Click on the Authentication Settings button.

· Select your authentication method. In my case, I select “None.” This will trigger the Excel Web Services to use the Unattended Account we set up earlier.

· Select OK on this dialog and then Finish on the previous dialog.

· When you are prompted with the Web File Properties dialog, select SharePointLibrary as your Connection Type and ReadWrite as your UDC Purpose.

· On the Import Data dialog, select PivotChart and PivotTable Report. Note: Selecting Table will not work. It is called a Query Table and is not an option that is supported in Excel Web Services.

· Build your Pivot Chart. Here I am performing a simple summation of the Estimated Value of my opportunities in a particular category.

· Click on any area within the Pivot Table, click Data from the top menu bar, then Connection Properties.

· Under the usage tab, select all three of the Data Refresh options.

· Under the definition tab, select the “Always use the connection file” checkbox.

· Since we have changed the connection information, we will have to re-save it to the Data Connection Library on the SharePoint site. To do this, click on the Export Connection File button. If your location did not default to the Data Connection Library, browse to it as described in step (h). Click Save to update the Data Connection file.

· Click OK to close the Connection Properties dialog.

· We are now ready to publish the spreadsheet to the Reports Library on the SharePoint site. Click on the Office Button in the top left corner, select Publish, then select Excel Services.

· Before saving the file, make sure you are publishing it to the Reports Library on the SharePoint. As in step (h), if you do not have a link to the Reports Library, you can find it by navigating to the Reports Library on your SharePoint site using Internet Explorer. Copy the address and paste it before the file name. (Ex. http://intranet/Department%20Sites/Sales%20%20Marketing/sales_dash/ReportsLibrary/)

· The next dialog will prompt you with choices of the items you would like to publish. In my case, I only want to display the chart, so from the Show tab, I select Items in the Workbook and Chart 1. You can choose to show any item in the spreadsheet that is support by Excel Web Services. Select OK.

· The next dialog is important as well. Be sure to select Dashboard Page. If you select Report, whatever you intend to publish is only published as a snapshot and the data will not refresh when revisiting the web page containing your data and charts.

· Your published report will be rendered in Internet Explorer. The next step will be to add the report from your Reports Library to your SharePoint Dashboard page.

Assuming that your base dashboard page has already been built in SharePoint, I am jumping ahead a few steps to actually adding the new Excel Chart to the page.

· From your dashboard page, select Add a Web Part.

· From the dialog box, select the Excel Web Access part.

· After the part is added click on the “Click here to open the tool pane” link.

· In the “Workbook Display” section of the Properties, select the ellipsis to browse your SharePoint Report Library. Select the Excel file that you uploaded earlier.

· Go through all of the Properties areas for the web part, adjusting what is displayed and what is not. Once you are done with the Properties, select OK to add your web part to the dashboard page.

There you have it! To build more reports and data connections, simply follow the steps listed above and then add them to your dashboard page. In this example I used the CRM database as my data source, but in reality this will work for any external data source.