Data Mining Build Model with OLAP Data Source

By: Siddharth Mehta

Overview

We saw how to we use a relational data source to develop a SSAS data mining structure and model. Relational transactional data is too voluminous in nature where in contrast, OLAP data is aggregated and more analytical in nature. From a performance perspective, a data mining model would perform better on analytical data sources compared to relational data sources. In this chapter we will look at how to use OLAP data sources to create a data mining structure and develop a data mining model as well. Our intention is to look at existing sales of customers and use the Clustering Algorithm to predict future sales.

Explanation

Open the Adventure Works OLAP database that was restored on the SSAS instance.

1) Once you open the Adventure Works database in SSDT, it should look as shown below.

2) In Solution Explorer, right-click the Mining Structures folder, and select New Mining Structure to launch the Data Mining Wizard. Select the Definition Method page of the Data Mining Wizard, select the option From Existing Cube and click Next.

3) Select the Microsoft Clustering algorithm and click Next.

4) We want to use the Customers as the input data source for our model, so select the Customers dimension as the base table and click Next.

5) Select the Customer attribute as the primary key, and select Next.

6) Select the following attributes Commute Distance, Education, Occupation, Gender, House Owner, Marital Status, Number Cars Owned, Number Children At Home, Total Children, and Yearly Income from the Customer Dimension. Also, select the Internet Sales Amount and Internet Tax Amount from Internet Sales fact and click Next.

7) On the Specify Mining Model Column Usage page, select the Sales Amount as predictable, and click Next.

8) Accept the defaults on the Specify Columns Content and Data Type page, Slice Source Cube page, Create Testing Set page and click Next.

9) On the final page, provide an appropriate name for the OLAP mining model as well as the OLAP mining structure. This wizard also creates a cube that includes the results of the mining model. You can also create a dimension from the results of the mining model and add this to the existing cube. Click the check boxes for Create Mining Model Dimension, Create Cube Using Mining Model Dimension, and Allow Drill Through. Click Finish to create these objects.

10) You should be able to see the OLAP mining structure now in the editor. Once you deploy, you should be able to visualize the mining model clusters in the mining model viewer. You can use this mining model in the same way as explained in previous chapters to derive predictions.

Additional Information

• Explore the different database objects like the new cube, new dimension, and the DSV created from this wizard and analyze to understand how the OLAP data mining model fits in a SSAS project.