Analysis Services Data Mining - Mining Models

Home > SQL Server > Analysis Services > Data Mining > Mining Models

Mining Models in Analysis Services

Mining models are the core components of data mining in SQL Server Analysis Services (SSAS). They represent the patterns and relationships discovered from your data by data mining algorithms. Each mining model is built based on a mining structure, which defines the data sources, input columns, and predictable columns used for training.

Types of Mining Models

Analysis Services supports various types of mining models, each designed to solve different analytical problems:

Association Rules: Discover relationships between items in a dataset, often used for market basket analysis.
Clustering: Group similar data points together based on their characteristics.
Decision Trees: Create a tree-like structure that branches based on attribute values to predict a target outcome.
Linear Regression: Predict a continuous numerical value based on other variables.
Logistic Regression: Predict the probability of a binary outcome.
Neural Networks: Model complex non-linear relationships in data.
Sequence Clustering: Identify common sequences in ordered data.
Time Series: Forecast future values based on historical data.
Classification: Predict a categorical outcome.
Key Influencers: Identify factors that most influence a predictable outcome.

Creating and Managing Mining Models

Mining models are created and managed using SQL Server Data Tools (SSDT) or SQL Server Management Studio (SSMS). The process typically involves:

Defining a Mining Structure: Select data sources, input columns, and predictable columns.
Selecting Algorithms: Choose the appropriate data mining algorithm for your analysis goals.
Training the Model: The algorithm processes the data to discover patterns.
Exploring and Validating: Use viewers and queries to understand and evaluate the model's performance.

Interacting with Mining Models

Once created, mining models can be queried using the Data Mining Extensions (DMX) language or the XML for Analysis (XMLA) protocol. You can retrieve predictions, discover rules, or analyze model content.

For example, to retrieve the top associations from an association rules model, you might use a query like this:

SELECT
    [MODEL_NAME],
    [ASSOCIATION_RULES].*
FROM
    [MyAssociationModel]
NATURAL PREDICTION JOIN
    (SELECT NULL AS [Support], NULL AS [Confidence], NULL AS [Order]
    ) AS T
WHERE [Support] > 0.01

Note: The specific syntax and options available for querying mining models depend on the algorithm used and the version of Analysis Services.

Best Practices

Understand your business problem thoroughly before selecting an algorithm.
Ensure your data is clean and properly prepared.
Experiment with different algorithms and parameters to find the best fit.
Validate your models using independent test data to avoid overfitting.

Warning: Poorly designed or overfitted models can lead to misleading insights and incorrect business decisions.

This section provides an overview of mining models in SQL Server Analysis Services. For detailed information on specific algorithms, querying techniques, and advanced scenarios, please refer to the related documentation links in the sidebar.