431
SQL Server 2012 Upgrade Technical Guide
You will also need to perform some important data-mining tasks after your in-place
upgrade from SSAS 2005 to SSAS 2012. There are many valuable features in SSAS 2012
data mining that can help you deploy a different predictive model in production,
consolidate mining structures, or refine forecasting models. We will discuss these
important considerations in the “Post-Upgrade Tasks” section later in this chapter.
Side-by-Side Upgrade
For a side-by-side upgrade, you have plenty of options for migrating your mining
models from SSAS 2005 to SSAS 2012:
You can back up the SSAS 2005 database and restore it on SSAS 2012.
With SSMS, you can create an XMLA script for creating the complete database
or any object in the database and then execute the script on SSAS 2012.
You can open the SSAS 2005 project in SSDT 2012 and deploy it on SSAS 2012.
You can reverse-engineer an SSAS 2005 database in SSDT 2012 to create a 2012
project and then deploy the project on SSAS 2012.
You can also quickly import SSAS 2005 data mining models to an SSAS 2012 database
by using the EXPORT and IMPORT DMX commands. However, note that SSAS 2012
supports data mining only if it is installed in Multidimensional and Data Mining mode.
SSAS 2012 in Tabular mode does not support data mining at all, and you cannot
migrate your mining models to an SSAS 2012 Tabular instance.
Chapter 16, “Analysis Services,” covers the options for migrating a complete SSAS
database. So in this section, we focus on the data-mining-specific migration options.
With the EXPORT DMX command, you can export a complete mining structure, one or
more mining models, or a model or structure with dependencies. Exporting with
dependencies means that all objects needed to process the structure, such as the data
source and the data source view, are included in the backup (.abf) file. Here are some
examples of EXPORT commands executed on our sample SSAS 2005 database:
-- Exporting complete structure
EXPORT MINING STRUCTURE [TM2005]
TO 'C:\Upgrade2012WP\TM2005_Structure.abf';
-- Exporting a single model
EXPORT MINING MODEL [TM2005_DT]
TO 'C:\Upgrade2012WP\TM2005_Model.abf';
-- Exporting a model with dependencies
EXPORT MINING MODEL [AR2005]
TO 'C:\Upgrade2012WP\AR2005_Model_Dependencies.abf'
WITH DEPENDENCIES;
432
SQL Server 2012 Upgrade Technical Guide
In SSAS 2012, you can use SSMS to create an empty database. Note that you cannot
create objects you need for processing the mining structures, namely data sources and
data source views, from SSMS. You can use SSDT 2012 to create a SSAS project that
includes only data sources and data source views, deploy it, and then import mining
models and structures.
If you already have the destination SSAS 2012 database and you need to import only a
mining structure, import it from the backup file with the complete structure, as follows:
-- Importing complete structure
IMPORT
FROM 'C:\Upgrade2012WP\TM2005_Structure.abf';
Note that if you import from a file with only the mining model, the associated structure
is created as well. Therefore, you cannot have a structure with the same name in the
destination SSAS database. The following command shows an example of importing a
mining model:
-- Importing a single model
IMPORT
FROM 'C:\Upgrade2012WP\TM2005_Model.abf';
This command imports from the file to which you exported the TM2005_DT model. And
as we just noted, the TM2005 structure cannot exist in the destination database
because it is recreated there during the import. If you executed the second import, the
third one fails. If there is no TM2005 structure in the destination database, then the
third import succeeds. After a successful third import, the TM2005 structure contains
only one model, TM2005_DT.
After the import, you should try to process the complete database to check whether all
dependent objects were imported correctly.
Post-Upgrade Tasks
After your upgrade from SSAS 2005 to SSAS 2012, you should check the accuracy and
the robustness of your predictive models before deciding which one to deploy in
production.
Lift Chart
A Lift Chart is the most popular way to view the accuracy of predictive models. For a Lift
Chart, you need to split your data into training and test sets. You use the training set to
train the models and then try to predict the target variable in the test set. Because you
433
SQL Server 2012 Upgrade Technical Guide
know the real value of the target variable in your test set, you can measure how many
times the predictions were accurate and compare the accuracy of different models. The
Lift Chart provides a standard way to graphically present this comparison. Figure 9
shows a Lift Chart for the predictive models we created in the sample SSAS 2005
database for the value 1 (buyers) of the predicted variable (Bike Buyer). From this chart,
you can easily see the performance of the different models.
Figure 9: Lift Chart for predicting a single value
In the chart, notice that there is a total of six curves and lines. The four curves represent
the predictive models, and the two lines represent the Ideal Model and the Random
Guess. The X axis represents the percentage of the overall population (all cases), and
the Y axis represents the percentage of the target population (bike buyers). From the
Ideal Model line (the topmost line) you can see that approximately 50 percent of
Adventure Works customers buy bikes. If you could predict with 100 percent
probability which customer is going to buy a bike and which is not, you would need to
target 50 percent of the population only to get all bike buyers. The lower line is the
Random Guess line. If you would pick out cases of the population randomly, you would
need 100 percent of the cases for 100 percent of bike buyers. Likewise, you would need
Dostları ilə paylaş: |