Data Mining Add-in for Excel

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

20 Νοε 2013 (πριν από 3 χρόνια και 10 μήνες)

378 εμφανίσεις






Dept. of Business Information Systems,
HCOB

Data Mining
Add
-
in for
Excel




Table of Contents

Installation

................................
................................
................................
................................
................................
..............

2

Connect to an Analysis Services Server
................................
................................
................................
................................
...

5

Table Analysis Tools

................................
................................
................................
................................
................................

8

Analyze
Key Influencers

................................
................................
................................
................................
......................

8

Format Range of Cells as a Table

................................
................................
................................
................................
......

14

Detect Categories

................................
................................
................................
................................
..............................

17

Forecast

................................
................................
................................
................................
................................
.............

22





I
nstallation


1.

Download

SQL Server 2005 Data Mining Add
-
ins for Microsoft Office 2007
http://www.microsoft.com/downloads/details.aspx?FamilyId=7c76e8df
-
8674
-
4c3b
-
a99b
-
55b17f3c4c51&displaylang=en


2.

Double click SQLServer2005_DMAddin.exe
and you will be presented with the following

“Welcome
Dialog”.

C
lick

the


Next

>


button.



3.

Select “I accept the terms in the license agreement” and
click


Next

>

.


4.

Enter your name and the name of your organization in the fields and then click

Next

>”
.



5.

Select “Data Mining Client for Excel” to make sure
this feature will be installed and available to use,
and then click

Next

>”
.








6.

Click “Install”
.




Connect to an Analysis Services Server


This tutorial will help you
get started with

using the

Da
ta
M
ining
Client in Microsoft
Excel, by sho
wing
you how to connect to an Analysis S
ervi
ces S
erver, and access internal and external data.

1.

To access the data mining client, click on the data mining tab to
display the

data mining client ribbon.


2.

SQL Analysis S
ervices provide
s

data mining functionali
ty that is exposed through the Data Mining
C
lient
. To setup the connection to an Analysis Servi
ces S
erver, click “
<No
Connection
>” button on the
ribbon.


3.

Click
“New” to create
a

new connection.


4.

Follow the instructions below the image to fill in the fields needed to connect to an Analysis Services
Server.




S
erver name:

http://141.218.104.15/
OlapWebService/msmdpump.dll
”.



L
og on credential
s
:

“Use a specific user name and password”

o

U
ser

n
ame
:
Provided by Instructor

o

Password
:
Provided by Instructor

o

Check
“All saving password”
.



C
atalog name
: DMAddinsDB



Friendly name: Use the default OR type in
any name you like


5.

C
lick

Test Connection


to check the connection is successful
then

click

OK
” to return to close this
dialog.


6.

Click

“OK”

in the “Connect to Analysis Services” dialog and you will see your newly created
connection in the “Analysis
Services Connections”. C
lick

Close


to get back to Excel.




Table Analysis Tools


The following steps will help you to get started using table analysis tools for Excel. The table
analysis tools provide automatic analysis of the data that exits inside of

your Excel workbook and all the
data needs to be inside the Excel table.


Analyze Key Influencers


The following steps will

guide you through using

the Analyze Key Influencers tool, which is
designed to help users detect the key characteristics of a data set that influence a certain outcome.


1.

Open
the “Sample Excel Data”



2.

Choose

“Table Analysis Tools Sample”
.



3.

Select

one of the cells under “Marital Status” that contains

Single” and click
the “
Analyze


tab

on
the ribbon.



4.

C
lick

Analyze Key Influences


button
.


5.

Select
“Purchased Bike”

from the Column Selection drop down then
click

the


Run


button

to start
analyzing
.


6.

After
the
analysis is complete,
the

Discrimination
Reporting
” dialog

shows up. Change the options
if needed to make it look like the image below. Click on “Add Report”



7.

Click “Close” to close this dialog



8.

The analysis is
complete.

You will have something similar to the tables below.





F
ormat

Range of Cells as a Table


Some of the Analysis tools require that the data be
formatted as a table, so the following steps will
guide you through formatting a group of cells into a table


1.

Select the “Forecasting” worksheet in the
sample excel data we used above then highlight the data
like you see in the image



2.

Click “Format as
Table”
.



3.

Choose a

style that is appealing to you.



4.


Click “OK” in the “Format as Table” dialog that appears after you select a style




5.

Notice when the table is created, the ribbon automatically changes to show the tables analysis tools.
These tools
are only available when the selection is within the table object. Selecting outside the
table will hide the tools and reve
rt

to the default ribbon.





Detect Categories


The following steps will help you how to use the Detect Categories tool, which will
help users
automatically create more manageable data sets across large data volumes by detecting
data
categories
based on common properties.


1.

Select the “
Table Analysis Tools Sample
” worksheet in the sample excel data
.

Select

one of the
cells under “Marital Status” that contains
“Single” and click
the “
Analyze


tab on the ribbon.



2.

Click

on “
Detect Categories



3.

In
the Detect Categories dialog

you can
select the columns to be used

to analyze then

click Run
button
.

Select EVERYTHING EXCEPT FOR ID.



4.

After analyzing is complete, the categories are created.

If you

switch
back to the

“Table Analysis
Tools Sample”

sheet you will notice that

each row has been identified
by a category under the
category column.



5.

You
can change the category by clicking category filter:


6.

Last one is the category
report;

this report shows the distribution of
the column values across all the
categories. By default, the report will be ordered alphabetically
.


7.

You can change the category of the report by clicking on the chart to bring up the chart controls,
and
then

click on the filter next to the column field

to change the category you want to analyze.




Forecast


This section will demonstrate
h
ow to use
the
F
orecast function of the table analysis tools for Excel.
The forecast tool allows you to extend the series of data using the predictive power of SQL server
analysis services
.


1.

Select the “Forecasting” worksheet in the sample excel data then click on the “A
nalysis” tab in the
ribbon
. Select any cell INSIDE the
formatted
table

that you created in one of the “Format Range of
Cells as a Table” section


2.

Click

Forecast

to bring up the Forecast dialog.


3.

Select the columns you want to forecast, and then select

the number of time units you want to
forecast
.
Make sure your box looks like the image above. C
lick
“R
un


to start the data forecast.


4.

After that a chart will come out and this chart contains both original data and the forecasted data
.
The forecasted dat
a will be presented as the dotted line


5.

The

original data

in the “Forecasting” worksheet
will have the forecasted data appended at the end.