Demo Script: PowerPivot Demo

clutteredreverandΔιαχείριση Δεδομένων

31 Οκτ 2013 (πριν από 3 χρόνια και 5 μήνες)

171 εμφανίσεις

Demo Script:
PowerPivot Demo





Created
4/15/2010

Page
1


Demo


Introduction

Click Instructions

Presenter Script


1.


In this demo, you'll learn how you can
use the familiar environment of
Microsoft Excel 2010 with the
PowerPivot
add
-
in to create compelling
self
-
service Business Intelligence
solutions.


2.


Users can share and collaborate using the
secure, managed environment of
Microsoft SharePoint Server 2010 and
Microsoft SQL Server 2008 R2
-
based
management tools.

Click Here

Click Here

Demo Script:
PowerPivot Demo





Created
4/15/2010

Page
2


My Profile

Click Instructions

Presenter Script


1.

On the taskbar, click on
Sales
Trends.xlsx
.

Richard is a typical analyst in his
organization and

was just tasked by his
VP to gather data. Richard’s VP has a
hunch on 瑲enTV 瑨a琠are happening by
proTuc琠claVV anT regionH anT wan瑳 瑯W
co浰are 瑨eVe 瑲enTV verVuV 瑨irT par瑹
industry data. To do this, Richard’s VP
wan瑳 hi洠瑯WacceVV infor浡瑩on 瑨a琠
iV in
瑨e corpora瑥 Ta瑡 warehouVe aV well aV
ex瑥rnal Ta瑡 ou瑳iTe 瑨e organiYa瑩on.


How would Richard even start? Let’s see
how he can pull all of 瑨iV 瑯来瑨er in a
Vingle analy瑩cal 浯Tel 瑯Wperfor洠Vo浥
瑲uly re浡rkable analyViV.



Click Here

Demo Script:
PowerPivot Demo





Created
4/15/2010

Page
3


Sales Trends

Cli
ck Instructions

Presenter Script


1.

Switch to the
PowerPivot

tab.

Let’s start in Excel. You can see this is the
fa浩liar uVer in瑥rface wiWh 瑨e Fluen琠
Ribbon 瑨a琠waV in瑲oTuceT wi瑨 Office
2007, but it’s got a new tab called
PowerPivoW.




Click on the
PowerPivot window
button.

PowerPivot is an optional add
-
in for Excel
2010 that allows end
-
users like Richard
to connect to data from multiple sources.
This enables interactive modeling and
analysis of massive amounts of data
at
lightning
-
fast processing speeds.

Click Here

Click Here

Demo Script:
PowerPivot Demo





Created
4/15/2010

Page
4



3.

Click the
From Database
button.

The Table Import Wizard lets you add
business data from your corporate
network, your local computer, or ad hoc
data sources on the web. You can
connect to external data
sources directly,
or use predefined connection objects
such as ODBC to get the data. You can
also enter or copy in data from other
worksheets, documents, websites, or text
files.


4.

Select
From Other Sources
.


Click Here

Click Here

Demo Script:
PowerPivot Demo





Created
4/15/2010

Page
5



5.

Scroll down.

PowerPivot can connect to any of your
existing IT systems or data sources, such
as Oracle, IBM DB2, or Terradata.


6.

Scroll down.


Click Here

Click Here

Demo Script:
PowerPivot Demo





Created
4/15/2010

Page
6



7.

Click
Cancel
.



8.

Click on the header for the
UnitCost
column
.

Notice that
there are almost 4 million
records loaded in this FactSales table.
Despite this large size, Richard is able to
easily perform operations on the data,
such as sorting a column.

Click Here

Click Here

Demo Script:
PowerPivot Demo





Created
4/15/2010

Page
7



9.

Select
Sort Largest to Smallest
.

PowerPivot is extremely fast
, even when
sorting millions of rows of data.



Click Here

Demo Script:
PowerPivot Demo





Created
4/15/2010

Page
8


Industry Data

Click Instructions

Presenter Script


1.

On the taskbar, click on
IndustrySalesData.xlsx
.



2.

Click on the upper
-
left corner of the
spreadsheet.

Remember that Richard needs to
compare his company data with industry
trends. With data from his corporate
data warehouse already loaded, Richard
combines this analysis with external data
such as industry sales trends.

Click Here

Click Here

Demo Script:
PowerPivot Demo





Created
4/15/2010

Page
9



3.

Select
Copy
.



4.

On the taskbar, click on the
PowerPivot window for
Sales
Trends.xlsx
.


Click Here

Click Here

Demo Script:
PowerPivot Demo





Created
4/15/2010

Page
10



5.

Click the
To New Table
button.

PowerPivot lets you simply copy and
paste data as a new table in the model.


6.

Click
OK
.

It will even
analyze the field names and
row data to automatically create
relationships between the data outside
of the warehouse and existing structured
data.



Click Here

Click Here

Demo Script:
PowerPivot Demo





Created
4/15/2010

Page
11


Insert PivotTable

Click Instructions

Presenter Script


1.

Click on the
PivotTable

button.

Now, all of this data
-

from the SQL
Server data warehouse, and from
external data sources
-

is combined in a
single model that can be used to perform
analyses that would not previously have
been possible from a single analytical
tool. This is something u
niquely available
in Microsoft Excel and PowerPivot.


2.

Select
Chart and Table (Vertical)
.

Now let’s look at the ways Richard can
uVe all 瑨iV co浢ineT Ta瑡 瑯WviVualiYe
co浰ariVonV 瑨a琠浡y no琠have been
apparen琠or eaVily Trawn before.


R
icharT iV going 瑯WuVe Nxcel 瑯WorganiYe
瑨e Ta瑡 in a char琠anT 瑡ble 瑨a琠will
浡ke i琠eaVier 瑯WviVualiYe rela瑩onVhipV
be瑷een all 瑨eVe Tifferen琠VourceV.


Since we’re working in Excel, the
func瑩onV we uVe anT 瑨e ac瑩onV we 瑡ke
are all fa浩liar. Pe

can crea瑥 a
瑲aTi瑩onal Pivo瑔able by aTTing valueVH
Click Here

Click Here

Demo Script:
PowerPivot Demo





Created
4/15/2010

Page
12


rows, and columns.


3.

Select
Existing Worksheet
.



4.

Click
OK
.


Click Here

Click Here

Demo Script:
PowerPivot Demo





Created
4/15/2010

Page
13



5.

In the Gemini Task Pane, expand the
FactSales

node.

The difference is that the fields
we’re
Trawing fro洠are co浩ng fro洠瑨e 浯Tel
we juV琠crea瑥T.




Select
SalesAmount
.

We’ll start building this PivotTable by
aTTing SaleV A浯un琮

Click Here

Click Here

Demo Script:
PowerPivot Demo





Created
4/15/2010

Page
14



7.

Collapse the
FactSales

node.



8.

Expand the
DimDate

node.

We’ll add some Year columns and then
aTT ProTuc琠䍡瑥gory anT Sub
-
ca瑥gory
rowV.

Click Here

Click Here

Demo Script:
PowerPivot Demo





Created
4/15/2010

Page
15



9.

Click on
CalendarYear
.



10.

Click on
Column Labels
.


Click Here

Click Here

Demo Script:
PowerPivot Demo





Created
4/15/2010

Page
16



11.

Collapse
DimDate
.



12.

Expand
DimProductCategory
.


Click Here

Click Here

Demo Script:
PowerPivot Demo





Created
4/15/2010

Page
17



13.

Select
ProductCategoryName
.



14.

Expand
DimProductSubcategory
.


Click Here

Click Here

Demo Script:
PowerPivot Demo





Created
4/15/2010

Page
18



15.

Select
ProductSubcategoryName
.




Click Here

Demo Script:
PowerPivot Demo





Created
4/15/2010

Page
19


Cond. Format

Click Instructions

Presenter Script


1.

Select the three year columns.

And just like

that we have a basic
PivotChart, pulling data from our model
and calculating fields near
-
instantaneously from millions of rows of
data.


2.

Switch to the
Home
tab.


Click Here

Click Here

Demo Script:
PowerPivot Demo





Created
4/15/2010

Page
20



3.

Click on the
Conditional Formatting
button.

Let’s add a fe
w viVualiYa瑩onV 瑯W瑨iV
repor琠瑯W浡ke i琠eaVy for enT
-
uVerV 瑯W
unTerV瑡nT 瑨e nu浢erV.


We’ll start with Conditional Formatting, a
na瑩ve fea瑵re of Nxcel. Then aTT Da瑡
barV 瑯W瑨e annual nu浢erV for each
ca瑥gory.




Select
Data Bars
.


Click Here

Click Here

Demo Script:
PowerPivot Demo





Created
4/15/2010

Page
21



5.

Select the
Green Gradient Fill
.




Click Here

Demo Script:
PowerPivot Demo





Created
4/15/2010

Page
22


Sparklines

Click Instructions

Presenter Script


1.

Switch to the
Insert
tab.

Excel 2010 includes new formatting and
visualization features that further
enhance the way complex data can be
visualized to improve understanding and
help business users like Richard’s boss
浡ke be瑴er TeciVionV.




Click on the
Line

sparkline type.

The new Sparklines feature works like a
mini
-
line chart to give you a quick, cell
-
based historical or

series visualization.
This makes it easier to draw comparisons
across a number of rows.


Click Here

Click Here

Demo Script:
PowerPivot Demo





Created
4/15/2010

Page
23



3.

Select the
Grand Total
column.



4.

Click
OK
.




Click Here

Click Here

Demo Script:
PowerPivot Demo





Created
4/15/2010

Page
24


PivotChart

Click Instructions

Presenter Script


1.

Click in the
PivotChart.

Richard can take advantage of the new
multiple data sources we just added by
building a PivotChart that compares
internal and external trend data.


2.

Expand
IndustryTrends
.

Let’s begin to add some of these fields to
瑨e 䍨ar琬 V瑡r瑩ng wi瑨 InTuV瑲y SaleV.

Click Here

Click Here

Demo Script:
PowerPivot Demo





Created
4/15/2010

Page
25



3.

Select
Sum_of_IndustrySales
.



4.

Expand
ProductClass
.


Click Here

Click Here

Demo Script:
PowerPivot Demo





Created
4/15/2010

Page
26



5.

Click on
ClassName
.

Now when we add this Product Class
Name
field, we get an alert at the top of
the Task Pane.


6.

Click on
Slicers Vertical
.


Click Here

Click Here

Demo Script:
PowerPivot Demo





Created
4/15/2010

Page
27



7.

Click the
Create

button.

Underlying the model are the
relationships between the tables and
fields for all the data that’s been
i浰or瑥T. The PowerPivo琠aTT
-
in 瑯WNxcel
iV V浡r琠enough 瑯Waler琠you when
rela瑩onVhipV are neeTeTH anT even
Te瑥c琠rela瑩onVhipV be瑷een Vo浥 Ta瑡
au瑯W
a瑩cally.




Click
Close
.


Click Here

Click Here

Demo Script:
PowerPivot Demo





Created
4/15/2010

Page
28



9.

Expand
FactSales
.

We see the same thing when we add the
Contoso Sales numbers. A new
relationship is created.


10.

Select
SalesAmount
.


Click Here

Click Here

Demo Script:
PowerPivot Demo





Created
4/15/2010

Page
29



11.

Click the
Create

button.



12.

Click
Close
.


Click Here

Click Here

Demo Script:
PowerPivot Demo





Created
4/15/2010

Page
30



13.

Collapse
FactSales
.



14.

Expand
DimDate
.

Let’s add the Year value to the chart to
Vee a 3
-
year view.

Click Here

Click Here

Demo Script:
PowerPivot Demo





Created
4/15/2010

Page
31



15.

Click on
CalendarYear
.



16.

Click on
Axis Fields
.


Click Here

Click Here

Demo Script:
PowerPivot Demo





Created
4/15/2010

Page
32



17.

Collapse
DimDate
.




Click Here

Demo Script:
PowerPivot Demo





Created
4/15/2010

Page
33


Publish Workbook

Click Instructions

Presenter Script


1.

Switch to the
File

tab.

This worksheet is now a fully interactive
Business Intelligence analysis tool that
can be shared with other people. These
users can easily sort and filter the data
with just a web browser using
SharePoint. Richard, a typical analyst,
was able to use his familiar environment
within Excel to pull together this unique
view of the data.


2.

Click on
Share
.

To share

the analysis with other
employees, Richard can post this over
the web into Microsoft SharePoint
Server. It’s as simple as doing a “Save
As” function.

Click Here

Click Here

Demo Script:
PowerPivot Demo





Created
4/15/2010

Page
34



3.

Click on
Save to SharePoint
.



4.

In the Recent Locations, click on
PowerPivot Gallery
.


Click Here

Click Here

Demo Script:
PowerPivot Demo





Created
4/15/2010

Page
35



5.

Click in the
File Name
field.



6.

Click
Save
.




Click Here

Click Here

Demo Script:
PowerPivot Demo





Created
4/15/2010

Page
36


PowerPivot Galley

Click Instructions

Presenter Script


1.

Click the right arrow.

Once published to SharePoint, any end
user can see the
analysis over the web.
They can browse for the file through the
new carousel view which allows users to
interactively visualize thumbnails of the
analysis they want to see.


2.

Click on the workbook preview
image.

After finding the newly
-
pub
lished
Industry Trends workbook, we can now
open this from within our web browser

Click Here

Click Here

Demo Script:
PowerPivot Demo





Created
4/15/2010

Page
37



3.

In the ClassName slicer, click on
Economy
.

After opening this file, users can interact
with it, and select slicers to filter the
data.


4.

Open the
Favorites

menu.

With the analysis secured in the
SharePoint environment, IT can now
manage, administer and apply the
appropriate permissions to the Excel file.

Click Here

Click Here

Demo Script:
PowerPivot Demo





Created
4/15/2010

Page
38



5.

Select
PowerPivot Management
Dashboard
.

You may have noticed that the

PowerPivot workbook that Richard
created earlier was just one of several
such workbooks that have been created
by users at Contoso. The job of managing
these self
-
service BI solutions falls to
Contoso’s IT administrators.



Click Here

Demo Script:
PowerPivot Demo





Created
4/15/2010

Page
39


Mgmt. Dashboard

Click Instruct
ions

Presenter Script


1.

Open the
View
menu.

In the management console for
SharePoint Server 2010, IT can monitor
the performance of PowerPivot
applications. In this case, you can see a
spike here in the middle of Query
Response Times
indicating something
may need to be done with the allocation
of resources


2.

Select
Average Instance CPU
.

You also can manage and monitor other
things like Average Instance CPU.

Click Here

Click Here

Demo Script:
PowerPivot Demo





Created
4/15/2010

Page
40



3.

Click the
Play

button.

In the management console, you can also
see a time
-
series visualization that shows
workbook activity over time. We actually
can play the timeline to see changes in
how certain workbooks are being used
and how many users are accessing them.


4.



Click Here

Click Here

Demo Script:
PowerPivot Demo





Created
4/15/2010

Page
41



5.




6.



Click Here

Click Here

Demo Script:
PowerPivot Demo





Created
4/15/2010

Page
42



7.


This visualization is showing us that a
particular workbook may need extra
attention in terms of resourcing.


8.


Within SharePoint, this PowerPivot
Management Dashboard allows
IT to
manage Excel workbooks in a central
place. The Dashboard tracks what
workbooks are being stored in the
system, what resources they require, and
how many users are using them. Armed
with this information, IT can make
informed decisions about how to
allocate
resources to support the needs of the
business.



Click Here

Click Here

Demo Script:
PowerPivot Demo





Created
4/15/2010

Page
43


Conclusion

Click Instructions

Presenter Script


1.


As you’ve seen in this demonstration,
MicroVof琠e浰owerV uVerV 瑯Wwork wi瑨
large a浯un瑳 of infor浡瑩on wi瑨in 瑨e
fa浩liar Nxcel

environ浥n琮 UVerV can
eaVily organiYe Ta瑡 fro洠浵l瑩ple
VourceV anT perfor洠Teeper kinTV of
analyVeV 瑨a琠can be VhareT 瑨roughou琠
瑨e organiYa瑩on in a 浡nageT IT
environ浥n琮


For 浯re infor浡瑩on on MicroVof琠
BuVineVV In瑥lligence Volu瑩onVH con瑡cW

your MicroVof琠RepreVen瑡瑩ve.


Click Here