Microsoft SQL Server Analysis Services makes it easy to create data mining solutions using wizards and integrated visualizations. Particularly if you are new to machine learning, the tools in Analysis Services are an easy way to design, train, and explore data mining models. The data in your models can be stored in a cube, relational database, or any other source support by Analysis Services. After creating a model, you can put it into production by accessing the model to create predictions using prediction multiple clients, including Integration Services and ASP.NET.
Basic Data Mining Tutorial (SQL Server 2014) – This tutorial walks you through a targeted mailing scenario. It demonstrates how to use the data mining algorithms, mining model viewers, and data mining tools that are included in Analysis Services. You will build three data mining models to answer practical business questions while learning data mining concepts and tools.
Intermediate Data Mining Tutorial (SQL Server 2014) – This tutorial contains a collection of lessons that introduce more advanced data mining concepts and techniques such as, forecasting, market basket analysis, neural networks and logistic regression, and sequence clustering.
DMX Tutorials (SQL Server 2014) – The Data Mining Extensions (DMX) query language has syntax like that of SQL but can be used to create, query, and manage predictive models stored in Analysis Services. These tutorials demonstrate how to create a new mining structure and mining models by using the DMX language, and how to create DMX prediction queries for use in applications.
Data Mining Solutions
Microsoft SQL Server Data Mining resources
Creating and Querying Data Mining Models with DMX: Tutorials (Analysis Services – Data Mining)
This tutorial describes how to use SQL Server Data Tools to develop and deploy an Analysis Services project, using the fictitious company Adventure Works Cycles for all examples.
This tutorial includes the following lessons.
This tutorial provides lessons on how to create and deploy a tabular model at the 1400 compatibility level. If you’re new to Analysis Services and tabular modeling, completing this tutorial is the quickest way to learn how to create and deploy a basic tabular model by using Visual Studio. Once you have the prerequisites in-place, it should take two to three hours to complete.
This tutorial includes the following lessons:
These lessons are not required to complete the tutorial, but can be helpful in better understanding advanced tabular model authoring features.
What you learn
- How to create a new tabular model project at the 1400 compatibility level in Visual Studio with SSDT.
- How to import data from a relational database into a tabular model project workspace database.
- How to create and manage relationships between tables in the model.
- How to create calculated columns, measures, and Key Performance Indicators that help users analyze critical business metrics.
- How to create and manage perspectives and hierarchies that help users more easily browse model data by providing business and application-specific viewpoints.
- How to create partitions that divide table data into smaller logical parts that can be processed independent from other partitions.
- How to secure model objects and data by creating roles with user members.
- How to deploy a tabular model to an Azure Analysis Services server or SQL Server 2017 Analysis Services server by using SSDT.
To complete this tutorial, you need:
This tutorial is based on Adventure Works Cycles, a fictitious company. Adventure Works is a large, multinational manufacturing company that produces and distributes bicycles, parts, and accessories for commercial markets in North America, Europe, and Asia. The company employs 500 workers. Additionally, Adventure Works employs several regional sales teams throughout its market base. Your project is to create a tabular model for sales and marketing users to analyze Internet sales data in the AdventureWorksDW database.
To complete the tutorial, you must complete various lessons. In each lesson, there are tasks. Completing each task in order is necessary for completing the lesson. While in a particular lesson there may be several tasks that accomplish a similar outcome, but how you complete each task is slightly different. This method shows there is often more than one way to complete a task, and to challenge you by using skills you’ve learned in previous lessons and tasks.
The purpose of the lessons is to guide you through authoring a basic tabular model by using many of the features included in SSDT. Because each lesson builds upon the previous lesson, you should complete the lessons in order.
- SQL Server Management Studio (SSMS)
-Manage database schema
– Write & execute queries
– Stored Procedures
- SQL Server Integration Services (SSIS)
– Extract, Transform and Load (ETL) data
– Updating data warehouses
– Cleaning and mining data
– Creating “packages”
– A series of tasks of manipulating the input data
– Can take a variety of sources
– XML, flat files, relational data sources
- SQL Server Analysis Services (SSAS)
– Design, create and manage multidimensional structures with
data that is aggregated from multiple sources
– Data cubes
-Analysis by data mining
- SQL Server Reporting Services (SSRS)
– Server-based reporting platform
– Publish reports to a report server, or Microsoft Windows application or to a Sharepoint site.
– Can schedule reports to run
– Users can subscribe to reports
– Reports (.rdl files) can be exported to a variety of formats
– Excel, PDF, CSV, XML, Word
What is Business Intelligence?
Business intelligence is used for transforming raw data into meaningful information for analysis through techniques and tools such as MSS, Data Warehousing, OLAP, Data Mining, either individually or in the combination.
Diagram: BI through DW, OLAP & DM
How does it work?
- Once raw data is gathered, data engineers/analysts or BI developers use ETL (Extract Transform Load) tool to manipulate, transform, and classify the data in a structured database
- The structured databases are so-called data warehouses or data marts, where business owners and decision makers can access business data and use them to solve their business problems.
- BI developers/analysts can use the data visualization tools (eg. Power BI) to summarize the results on visual dashboards, making such information accessible to various stakeholders. It helps users analyze past performances and adapt future strategy in light of a specific goal. Such as: what does the data say about my latest sales performances, and how can I improve them? Is the data revealing increased ROI from my advertising investments? etc.
Microsoft BI-related Products:
• SQL Server Management Studio (SSMS)
• SQL Server Integration Services (SSIS)
• SQL Server Analysis Services (SSAS)
• SQL Server Reporting Services (SSRS)
– Report Builder
• Power BI
•SQL Server Visual Studio
- Dr Ami Peiris, Infosys330 lecture notes, The University of Auckland
- Hugo Le Squeren, retrived from: https://blog.dataiku.com/2015/05/27/what-is-the-difference-between-business-intelligence-and-data-science