How to predict and analyze data using SSAS.

Problem: How to predict and analyze data using SSAS.

Solution: Data mining

After collecting all the data in a SSAS Cube, based on this data we can create a data mining model based on an algorithm to predict future data. SQL Server Data Mining includes multiple standard algorithms, including EM and K-means clustering models, neural networks, logistic regression and linear regression, decision trees, and naive bayes classifiers. All models have integrated visualizations to help you develop, refine, and evaluate your models. Integrating data mining into business intelligence solution helps you make intelligent decisions about complex problems.

This is also called machine learning or predictive analytics.

To drive the predictive analysis, you should have data mining structures created on top of the cube dsv.

On the backend, datamining is done by queries based on the patterns and these queries are called DMX.

Key Data Mining Features:

·         Multiple data sources: You can use any tabular data source for data mining, including spreadsheets and text files. You can also easily mine OLAP cubes created in Analysis Services. However, you cannot use data from an in-memory database.

·         Integrated data cleansing, data management, and reporting: Integration Services provides tools for profiling and cleansing data. You can build ETL processes for cleaning data in preparation for modeling, and ssISnoversion also makes it easy to retrain and update models.

·         Multiple customizable algorithms: In addition to providing algorithms such as clustering, neural networks, and decisions trees, SQL Server Data Mining supports development of your own custom plug-in algorithms.

·         Model testing infrastructure: Test your models and data sets using important statistical tools as cross-validation, classification matrices, lift charts, and scatter plots. Easily create and manage testing and training sets.

·         Querying and drillthrough: SQL Server Data Mining provides the DMX language for integrating prediction queries into applications. You can also retrieve detailed statistics and patterns from the models, and drill through to case data.

·         Client tools: In addition to the development and design studios provided by SQL Server, you can use the Data Mining Add-ins for Excel to create, query, and browse models. Or, create custom clients, including Web services.

·         Scripting language support and managed API: All data mining objects are fully programmable. Scripting is possible through MDX, XMLA, or the PowerShell extensions for Analysis Services. Use the Data Mining Extensions (DMX) language for fast querying and scripting.

·         Security and deployment: Provides role-based security through Analysis Services, including separate permissions for drillthrough to model and structure data. Easy deployment of models to other servers, so that users can access the patterns or perform predictions.