Business Decision Making and Dashboards (includes Dynamics CRM) by pitbull99

VIEWS: 141 PAGES: 28

									CUSTOMER IMMERSION EXPERIENCE (CIE) PLATFORM v2.0
Supporting Virtual Machines and Documentation
    CUSTOMER IMMERSION EXPERIENCE (CIE) PLATFORM v3.0
    Supporting Documentation




          Scenario 8
          Business Decision Making and Dashboards (includes
          Dynamics CRM)
          Facilitator Guide – CIE v3.0
                                                    Updated: September 7, 2009




1
                                                                                            CUSTOMER IMMERSION EXPERIENCE (CIE) PLATFORM v3.0




Contents
Objectives................................................................................................................................................................................ 1
Exercise 1 - Monitoring ........................................................................................................................................................... 2
   Business Intelligence Portal ................................................................................................................................................ 2
   Performance Indicators ...................................................................................................................................................... 6
   Export to Office Excel 2007 ............................................................................................................................................... 10
Exercise 1 - Conclusion.......................................................................................................................................................... 11
Exercise 2 - Analysis .............................................................................................................................................................. 11
   Analysis by Product ........................................................................................................................................................... 11
   Analysis by Country ........................................................................................................................................................... 16
   Export to Office PowerPoint 2007 .................................................................................................................................... 21
Conclusion ............................................................................................................................................................................. 23
Appendix: Reporting Options in Microsoft Dynamics CRM ................................................................................................. 24
        Facilitator Guide – CIE v2
                                                                   CUSTOMER IMMERSION EXPERIENCE (CIE) PLATFORM v3.0




Latest update on v3.0: Updated image to Win7 and IE8 and added CRM content

Objectives
Learn how to develop integrated management of the business using SharePoint 2007 and certain functionalities that
used to be part of PerformancePoint Server to conduct monitoring and analysis of business critical data.

The two main areas that will be covered by the workshop are:

        Manage the company’s performance through an automated scoreboard, and
        Gain a better understanding of data activity by being able to easily drill down through the data.

FOR THE FACILITATOR

As an introduction to this scenario, it would be good to mention the technologies that were used to build the Business Intelligence
Portal.

The site itself is just another sub-site within Microsoft Office SharePoint Server. The charts and graphs are created using
PerformancePoint Server. The data for the charts and graphs is read from SQL Analysis Services cubes and the numbers used for the
goals/targets are retrieved from a worksheet that has been uploaded to Excel Services.

Note that the site doesn’t have to be created using MOSS in order to use PerformancePoint. The minimum required for PPS 2007 is
Windows SharePoint Services 3.0. However, Excel Services only comes with MOSS.

FACILITATOR NOTE: The Business Intelligence Portal can be reached either using the link that is on the lower left hand navigation
bar or using the Sites drop down list on the top navigation bar.

To make sure that the response time of the BI Portal site is quick, please access the site at least 10 minutes before starting a CIE
Session.




1    Scenario 8: Business Decision Making and Dashboards                                                       Updated: September 7, 2009
                                                                CUSTOMER IMMERSION EXPERIENCE (CIE) PLATFORM v3.0




Exercise 1 - Monitoring

FACILITATOR NOTE: Before diving into the exercise, it is recommended that you point out the various parts of the Business
Intelligence Portal. It would help to note that various web parts on the page are standard out-of-the-box components that came
with Microsoft Office SharePoint Server.


Business Intelligence Portal

Open Internet Explorer. You can either click on the BI Portal link on the left navigation pane or click on the BI Portal link
in the Sites drop-down list.




2    Scenario 8: Business Decision Making and Dashboards                                                 Updated: September 7, 2009
                                                            CUSTOMER IMMERSION EXPERIENCE (CIE) PLATFORM v3.0




The BI Portal Home Page is displayed.




Browsing through the Sections of this page, you will see that there is a section that contains the latest company financial
reports which can be shared with co-workers:




                                                                                 .




3    Scenario 8: Business Decision Making and Dashboards                                           Updated: September 7, 2009
                                                             CUSTOMER IMMERSION EXPERIENCE (CIE) PLATFORM v3.0




On the section below that you will see links to various sites within the Intranet Portal.




           Note: that the “Sales Opportunities” spreadsheet is generated by Microsoft Dynamics CRM, and contains a
pivot table that automatically refreshes every time it is opened. In this way, it constantly reflects the most current sales
opportunities that the company is pursuing.

When this report is opened, you can see that it is contains a pivot table, with the months the opportunities are due to
close in the columns and the sales people who own the opportunities in the rows. Conditional formatting bar graphs
have been applied to the cells. Under the “Data” tab is an option to refresh this data from Dynamics CRM, which also
happens every time the report is opened. In this way, the report is always showing the most up-to-date list of
opportunities from Dynamics CRM.




4    Scenario 8: Business Decision Making and Dashboards                                             Updated: September 7, 2009
                                                            CUSTOMER IMMERSION EXPERIENCE (CIE) PLATFORM v3.0




In case the customer wants to know how to create this report, it is very simple, and takes less than a minute.

To start, open the list of sales opportunities in Dynamics CRM (under the “Sales” folder). This should display all open
opportunities. Then, click the Excel icon in the top of the list:




Select the option to export a “Dynamic PivotTable”, and then click the “Export” button. This will bring up a blank
PivotTable with the fields available for selection on the right. To create the report above, drag and drop the “Est. Close
Date” field into the “Drop Row Fields here” area of the table, the “Owner” field into the “Drop Column Fields here”, and
Est. Revenue into the “Drop Data Items here” section. To get the Est. Closing date to group my month, right click and
select “Group by” and click OK:




5    Scenario 8: Business Decision Making and Dashboards                                           Updated: September 7, 2009
                                                                CUSTOMER IMMERSION EXPERIENCE (CIE) PLATFORM v3.0




To complete the formatting, select the data and apply conditional formatting to the table. You could also ask people to
experiment with inserting graphs and charts, and formatting the table with different designs.

On the section below that you will see links to various sites within the Intranet Portal.

Performance Indicators

On the right hand side of the main page, you will see the Key Performance Indicators web part, displayed graphically
below. This shows the company’s performance for the year as of the current date.




This is a KPI Web Part that pulls data from a SQL Analysis Cube. It shows the company’s performance based on
indicators that were defined beforehand; which in this case, are Sales Performance (both for Individual and Reseller
sales), Product Costs and Gross Profit Margins.

FACILITATOR NOTE: The KPI web part is a standard component that is included with MOSS. It can be used with static data,
SharePoint lists or SQL Analysis cubes. In this scenario, the KPI web part is using Contoso DataWarehouse cube hosted on SQL 2005
Analysis Services.

Here, the company’s performance is tracked using 3 main categories: Sales, Product Costs and Gross Profit Margin. You
will notice that the overall score for Company Performance is described as "average" (shown as a yellow traffic light),
therefore the results are going to be analyzed in order to find the reasons for this situation. Click on the link "Overall
Company Performance".




6    Scenario 8: Business Decision Making and Dashboards                                                  Updated: September 7, 2009
                                                                CUSTOMER IMMERSION EXPERIENCE (CIE) PLATFORM v3.0




A dashboard page will now be shown with the 3 main categories showing business activity coupled with a view of how
each goal is displayed. Each of the main goals (Sales, Product Costs and Gross Profit Margin) is measured by key
performance indicators (KPIs) that have been defined with relative weights applied to them.

FACILITATOR NOTE: For the purpose of this exercise we will focus on Reseller Sales.




FACILITATOR NOTE: The dashboard is showing a scorecard that is built using PerformancePoint Dashboard Designer. It’s a standard
component of PerformancePoint Server 2007. You have the option of opening the Dashboard Designer on CIE-SRV-04 to show the
audience how the dashboard page was created using PPS 2007.

The traffic lights indicate the status of the indicators and their evolution. It should be noted that 1 performance
indicator has not achieved the expected goal, namely Gross Profit Margin.




7    Scenario 8: Business Decision Making and Dashboards                                               Updated: September 7, 2009
                                                                  CUSTOMER IMMERSION EXPERIENCE (CIE) PLATFORM v3.0




Click on the Gross Profit Margin link in the scorecard. This will take you to a page containing two Analytic charts that
comprise the affect of the Gross Profit Margin.




Two analytic charts are displayed that show what the Gross Profit Margin is dependent on. You can click on the other
indicators to see what they are dependent on.

Note: also at the top of the dashboard page are Filters that can be used to “narrow” or “widen” the scope of the data
that is being used to display the respective charts.

FACILITATOR NOTE: For the purpose of all exercises in this scenario the filters being applied are Date (2009), Product (All) and
Country (All). Explain that the ability to drill-down data is relative to the applied filters.




8    Scenario 8: Business Decision Making and Dashboards                                                     Updated: September 7, 2009
                                                                    CUSTOMER IMMERSION EXPERIENCE (CIE) PLATFORM v3.0




For example, by clicking on Product, a drop-down menu is displayed that will allow you to (in this case) drill-down on the
range of Products that are being shown to see how a particular product line is performing.




FACILITATOR NOTE: Please point out that PerformancePoint doesn’t automatically deselect parent items when a child item is
selected. Also, note that the order of how the filter items are selected, since this will be the order by which PerformancePoint will
apply the filter. For example, in the above diagram, if All is not deselected and Bikes are selected, the filter will become All, Bikes.
And if you selected Accessories after you selected Bikes, the filter will show All, Bikes, Accessories.

    Make sure that you click on Apply once you’ve selected the appropriate filters.




FACILITATOR NOTE: Please point out that the data which drives these scorecards and indicators all reside on SQL Server 2005
Analysis Services cubes. This means that the data is automatically updated whenever the cubes are refreshed; and in the real world,
cubes are refreshed periodically to keep them up-to-date with the company’s metrics.




9      Scenario 8: Business Decision Making and Dashboards                                                      Updated: September 7, 2009
                                                               CUSTOMER IMMERSION EXPERIENCE (CIE) PLATFORM v3.0




Export to Office Excel 2007

PerformancePoint Server is compatible with Microsoft Office 2007. The dashboard can be exported to Office 2007
applications like Excel and PowerPoint. To accomplish this, in the table or chart you want to export, click on the drop-
down menu and choose Export to Excel:




The table has now been exported to Excel. All the data can be accessed at the cell level. Changes can be made to the
data, but remember this is a local copy only.

FACILITATOR NOTE: Please make sure to point out that once the tables and the charts are exported to any of the compatible
Microsoft Office 2007 Products, the data becomes static because it is only a local copy.




10   Scenario 8: Business Decision Making and Dashboards                                               Updated: September 7, 2009
                                                                  CUSTOMER IMMERSION EXPERIENCE (CIE) PLATFORM v3.0




Exercise 1 - Conclusion
The Monitoring component of PerformancePoint uses indicators and automates scorecards to enable a company to
keep track of various performance indicators in their business.

Exercise 2 - Analysis

FACILITATOR NOTE: Now that the scorecard has identified Gross Profit Margin as not meeting a projected target, the next step is to
identify the specific factors that are affecting up to this indicator. Exercise 2 will take the user through the steps that they might
take in using PerformancePoint analytics.

Analysis by Product

Now that we’ve identified that there’s a potential performance issue with the Gross Profit Margin, we will need to do a
drill-down to determine what’s causing it.

Click on the Gross Profit Margin link.




11   Scenario 8: Business Decision Making and Dashboards                                                     Updated: September 7, 2009
                                                            CUSTOMER IMMERSION EXPERIENCE (CIE) PLATFORM v3.0




This will display the Analytic charts for Production Costs and Product Sales




To get a more detailed view, zoom in on a particular chart by clicking on the drop-down menu in the upper right corner
of it.




Click on Open in New Window.




12   Scenario 8: Business Decision Making and Dashboards                                         Updated: September 7, 2009
                                                              CUSTOMER IMMERSION EXPERIENCE (CIE) PLATFORM v3.0




This will isolate the chart in its own browser window; and at the same time, give you more options on how to display the
data.




In the chart tool bar, you can choose to display the data as a grid table. This will give you a different view of the data, if
desired. Note that each of the columns and rows can be expanded or collapsed to allow you to get an aggregate
number or the details of the aggregate amount. Click to expand Q1 2009 as well as the Bikes products.




13   Scenario 8: Business Decision Making and Dashboards                                               Updated: September 7, 2009
                                                             CUSTOMER IMMERSION EXPERIENCE (CIE) PLATFORM v3.0




You can switch to a line graph to see the trends a bit more clearly.

Here you see that there’s a sudden dip in all of the Product Sales in the February 2009 time frame. We can drill-down
further into that data as well.




You notice that there are products that don’t seem to have any sales in the February time frame. This requires further
investigation to determine what the cost of this sales dip is.




14   Scenario 8: Business Decision Making and Dashboards                                          Updated: September 7, 2009
                                                           CUSTOMER IMMERSION EXPERIENCE (CIE) PLATFORM v3.0




Switch back to the dashboard, select the Production Costs graph and isolate it by choosing Open in New Window in the
drop-down menu to give you another view of the relevant data.




15   Scenario 8: Business Decision Making and Dashboards                                      Updated: September 7, 2009
                                                            CUSTOMER IMMERSION EXPERIENCE (CIE) PLATFORM v3.0




Switch the view to Line Chart by clicking on the appropriate icon.




Consistent with the Production Sales, the Production Costs have also dropped during the same time period for the entire
company. Note how the color of each product is defined in the key code to the right side of the screen.

Analysis by Country

 Having seen the dip in sales for February 2009, you would like to see what the sales look like across all of the
 company’s regions.

 Go back to the dashboard and click on Analysis By Country. Notice how the applied filters have been modified from
 before.




16   Scenario 8: Business Decision Making and Dashboards                                            Updated: September 7, 2009
                                                           CUSTOMER IMMERSION EXPERIENCE (CIE) PLATFORM v3.0




 The following graph is displayed




Click on the bar for Q2 2009.




17   Scenario 8: Business Decision Making and Dashboards                                 Updated: September 7, 2009
                                                             CUSTOMER IMMERSION EXPERIENCE (CIE) PLATFORM v3.0




You can switch views or chart formats, by right clicking on the chart and selecting Report Type.




Switch the chart view to Grid. Expand Q4 2008 and Q1 2009 – these are the periods that you want to examine.




By switching to a line chart, you should be able to see the trend clearly.




18   Scenario 8: Business Decision Making and Dashboards                                           Updated: September 7, 2009
                                                              CUSTOMER IMMERSION EXPERIENCE (CIE) PLATFORM v3.0




You can then drill-down further by clicking on the line to get more detail broken down by country.

You get the breakdown by country. Notice that the orange line, the United States, is the country experiencing the most
significant decline in sales.




If you want to look at only one item on the graph, which in this case, is the United States, right-click on the line
representing United States and select Show Only.




19   Scenario 8: Business Decision Making and Dashboards                                              Updated: September 7, 2009
                                                               CUSTOMER IMMERSION EXPERIENCE (CIE) PLATFORM v3.0




The graph updates and will now show sales from the United States only, allowing you to view the most relevant.




To view the data in a prior view (and to switch between datasets, if necessary), use the PerformancePoint drop-down
menu. Select Reset View once the menu is displayed.




This will reset the chart representation to the default format, which in this case is a bar graph, showing data based upon
the applied filters.

FACILITATOR NOTE: This would be a good time to ask the session attendees to discuss what solutions could be offered to address
the issue.




20   Scenario 8: Business Decision Making and Dashboards                                                Updated: September 7, 2009
                                                           CUSTOMER IMMERSION EXPERIENCE (CIE) PLATFORM v3.0




Export to Office PowerPoint 2007

As in Microsoft Office Excel 2007, charts and graphs can be exported to Office PowerPoint 2007. Within our current
graph click on the PerformancePoint drop-down menu. Select Export to PowerPoint




21   Scenario 8: Business Decision Making and Dashboards                                        Updated: September 7, 2009
                                                           CUSTOMER IMMERSION EXPERIENCE (CIE) PLATFORM v3.0




If you want to export the data in another chart representation, change the representation using the PerformancePoint
context menu, by right-clicking on the chart and choosing Report Type. In this example, choose bar chart.




This will display the chart in bar graph format. Use the PerformancePoint drop-down menu to export it out to Microsoft
PowerPoint.




22   Scenario 8: Business Decision Making and Dashboards                                        Updated: September 7, 2009
                                                            CUSTOMER IMMERSION EXPERIENCE (CIE) PLATFORM v3.0




Conclusion
You have now identified the cause of the indicator not meeting a target goal. Armed with this data, the next step would
be to meet with the people responsible for budget forecasting so that forecasts for the upcoming periods can be
corrected.

Having gone through this workshop, you should now have a better understanding of the various features as well as the
main strengths of Microsoft Office PerformancePoint Server 2007.

To recap:

        The Automated Scorecards which are part of the Monitoring component of PerformancePoint give you the
         ability to monitor your company’s performance

        The Analysis component of PerformancePoint gives you the ability to easily retrieve details on relevant areas of
         your company’s performance criteria. This is especially helpful when trying to get to the root cause of a
         problem.




23   Scenario 8: Business Decision Making and Dashboards                                           Updated: September 7, 2009
                                                           CUSTOMER IMMERSION EXPERIENCE (CIE) PLATFORM v3.0




         Appendix: Reporting Options in Microsoft Dynamics CRM
In addition to the creation of an Excel report based on Microsoft Dynamics CRM data covered in Section 1, Microsoft
Dynamics CRM also has its own list of reports. To view these, go into Microsoft Outlook and under Microsoft Dynamics
CRM, open the “Workplace / My Work / Reports folder. In here are many sales, marketing and service reports.




24   Scenario 8: Business Decision Making and Dashboards                                       Updated: September 7, 2009
                                                           CUSTOMER IMMERSION EXPERIENCE (CIE) PLATFORM v3.0




To see an example of a report, we recommend you select either of the “Sales Funnel” reports. These have the best data
in them, and therefore will display meaningful results. The best data is in Q2 of FY09, so make sure you have this date
selected. There is no need to select any other filters – simply “Run Report” at the bottom of the screen.




25   Scenario 8: Business Decision Making and Dashboards                                        Updated: September 7, 2009
                                                           CUSTOMER IMMERSION EXPERIENCE (CIE) PLATFORM v3.0




The report opens up a separate window within Outlook and gives a graphical display of the sales funnel by month. It is
possible to drill into any one of the months by clicking the”+” sign next to it:




26   Scenario 8: Business Decision Making and Dashboards                                         Updated: September 7, 2009

								
To top