Business Intelligence: Using Mining Structures, Models, Cubes & Dashboards

In a previous article I discussed the design of the data warehouse database. This is the first article in a series of articles that discuss ways to access and use data warehouse data to achieve corporate goals. Topics discussed in this article include mining structures and models, cubes, scorecards and dashboards.

I remember when I was first exposed to SQL Analysis Services. That was when it was launched for SQL Server 2000. Business Intelligence (BI) has come a very long way since then; and, Microsoft has (over the years) continuously updated its suite of BI tools. With SQL Server 2000 Analysis Services accessing data warehouse information took more effort and provided less options than today.

Today BI developers can use the Business Intelligence Development Studio or Visual Studio 2010 (or higher) to build mining structures and models that help sales and marketing teams launch more effective strategies to sell products or services. And, let's face it, nearly every company has a product or service it wants to sell. And, most want marketing to be more efficient and effective in this sluggish economy. What BI can do is answer the question "who" is most likely to buy my product or service. Instead of sending marketing materials to every single prospect; companies can narrow the list down to the people most likely to make the purchase. BI developers can also build cubes; and, ultimately develop scorecards so companies can monitor sales progress; or, determine an organizations progress towards meeting established targets. 

Getting Started

Before a BI developer can build scorecards and dashboards; there are preparation steps that must be followed. The developer must for create a business intelligence project to access and prepare the data in the data warehouse. But cubes are not the only power-tool offered through BI. Mining structures and models give access to predictions that can save time and money as well as boost sales. However, Before a mining structure and model or cube can be created the following three tasks must be completed from within SQL Server Business Development Studio; or, a BI project created with Visual Studio 2010 or higher:

• Create a Data Source: A connection must be made to the database that contains the data to be accessed.
• Create Data Source View(s): A view of the data, to be accessed, must be created. The Data Source View uses the data source to access the database so developers can select the tables/views that contain the data. If a developer is to use some (not all) of the columns in a table the developer can create a Named Query using the Data Source View. Although the project is Analysis Services, Transact-SQL (T-SQL) is used to build the Named Query because the query directly accesses the database objects. The T-SQL select statement can be used to select the desired table and fields, as shown in the following picture.

Likewise, if a New Named Calculation is created, from the Data Source View, T-SQL expressions are also used.

• Create Dimension(s): Data that presents meaningful attributes to support a measurable event, within an organization, are modeled in business intelligence as dimensions. Dimensions provide the data that helps companies learn more about itself. For example, a company's purchases are measurable events as are its sales. Details, or attributes, associated with sales might include products, promotions, customers, region, etc. The details, which are the data stored in dimension tables, provide a way for companies to answer questions about  the internal events. For example, the sale of products might lead the company to ask what products brought in the most money? what products aren't selling? If companies spent money on promotions that company may want to know what ads were tied to peaks in sales? A company may even ask who are our top 10 customers? Or, in what region is our product most popular?  Dimension details is what provides the details that gives companies answers to these questions. And, in a Visual Studio 2010 BI project the dimension(s) are defined before mining structures and models or cubes are created.

Mining Structures and Models

Any company that has historical promotional, orders/sales, demographic and other data has a data goldmine. BI developers can create a mining structure that defines the input data for mining models. The mining models can use demographic (such as gender, age, region, marital status, etc.) and other data to identify the makeup of and drivers for customers who have purchased a product or service. This information can then be used to identify the people most likely to buy. The Microsoft SQL Server 2012 Tutorials - Analysis Services Data Mining tutorials provide BI developers a great foundation to learn how to build a business intelligence project and work with mining structures and models.

Through the tutorials the developers use data in the AdventureWorksDW2012 database to build three mining models: the Decision-Tree, Cluster and Naive Bayes. In addition, the tutorial teaches developers to test the accuracy of each model's predictions to identify the most accurate mining model by creating a Mining Accuracy Chart. Developers begin by selecting the models to be compared.

The Lift Chart's property window (bottom right corner in the following picture) displays the Model Name, Score (which conveys the effectiveness of the model), Population Correct  (a value that defines the percentage of population data that is correct) and Predict Probability (a value that tells how accurate the predictions will be).  There are several useful resources that provide additional information on Lift Charts as follows:  http://technet.microsoft.com/en-us/library/ms175428.aspx and http://msdn.microsoft.com/en-us/library/ab77eca1-bd48-4fef-b27f-ff5b648e0501%28v=sql.90%29 . In the following Lift Chart, the TM_Decision_Tree model is closest to the Ideal Model and will, therefore, provide the most accurate predictions regarding who will buy the product (which in this example is a bike).

Once the company has the profile of the persons most likely to buy the product; marketing materials and promotions can be designed for the targeted audience. The following paragraphs provide additional information about Microsoft models used in the above chart.

Decision Tree Model

The Decision Tree and other mining models, used by Microsoft BI tools, apply advanced statistical methods that have been used for years to analyze data. 

The Decision tree model was developed to make predictions. In the following example, the decision tree results originate from an All node for bike buyers (also taken from Microsoft SQL Server 2012 Tutorials - Analysis Services Data Mining). The light blue represents the group that has a 50.59% probability of buying a bike versus the pink group that has a 49.41% probability of not buying a bike (as shown in the properties window in the bottom right corner of the picture below).

The decision tree presents the attributes as nodes that can be expanded or collapsed. From the All root node the decision tree shows bike buyers in four groups of people based on the number (0, 1, 2, 3 and 4), which defines the number of cars owned.

From there users can see the people grouped by income, age, commuting distance, marital status, etc.

Users can mouse over a node to see the number of cases broken down by the light blue (represented by 0), pink (represented as a 1) and cases with missing values (represented as the word missing).

If the drill-down option is selected when the mining model is created; users can select the drill-down option to view the cases associated with a node.

The Dependency Network tab of the Mining Model Viewer provides access to the All Links slider. This view shows all of the factors relevant in predicting whether someone will buy a bike.

Users can move the slider down towards the Strongest Links option to see the factors that that have the most impact on predicting whether someone is or is not a bike buyer. According to the decision tree model (shown below) age, region and number of cars owned are among the strongest influences. Notice, in addition to the predictions the influencing factors also represent information someone is not likely to conclude without a model.

The following paragraphs present additional information on the two other models that were compared to the decision tree model to determine the most accurate model in this scenario.

Microsoft Clustering

The following diagram shows the data modeled using the Clustering algorithm. Notice the clustering mining model presents the same data in a way that looks very different from the decision tree model. The data is grouped into clusters.

The Cluster Profiles view, show below, provides details on how the clusters are divided into the same attributes (income, age, commute distance, etc.) as the decision tree.

Microsoft Naive Bayes

With the Naive Bayes model the user is first presented the Dependency Network tab. The All Links slider is moved to view the influencing factors used to identify bike buyers.

The Naive Bayes model also has an Attribute Profiles view and an Attributes Characteristics view, as shown below. The following view sow the probability based on attributes.

Querying Multidimensional Data

When creating the inputs for a model the BI Developer uses the Mining Model Prediction tab, which is available from the Mining Structure design window. The mining model is built from the mining structure, which uses Multidimensional Expressions (MDX) as shown in the picture below. The Mining Model Prediction window has an option that allows the developer to manually build the MDX query; or, the developer can select the input table(s)/field(s) and the system will build the MDX query.  Follow are a few resources to learn more about MDX:  Basic MDX Query article; Key MDX concepts article: http://technet.microsoft.com/en-us/library/ms144884.aspx; and MDX Reference: http://technet.microsoft.com/en-us/library/ms145506.aspx .

Cubes

While mining models aide in predictions, cubes provide a way for developers to select views or tables that will be used as measures, which are numeric values pulled from a fact table. The developer can then select one or more dimensions (discussed in the beginning of this article) to be associated with the measure. The following picture shows the Internet Sales table selected as the measure. Geography, Customer, Product, DimPromotion are all dimensions associated with the measure.

Once the measures and dimensions are created users can access the cube's Browse tab and select a dimension. From within the Browse tab users can view the dimension data associated with the measure. In the following example the Customer dimension was selected, therefore; the Browse details show sales data for each customer.

SharePoint 2010 PerformancePoint Dashboards

SharePoint 2010 extends the capability of BI cubes. Developers can make use of SharePoint's business intelligence capabilities by creating a SharePoint site collection from the Business Intelligence Center template. SharePoint creates a Business Intelligence site collection that provides a way to manage external connections, create reports, scorecards and dashboards. (The following article provides useful information on the difference between a scorecard and a dashboard:  http://office.microsoft.com/en-us/sharepoint-server-help/what-is-the-difference-between-a-dashboard-and-a-scorecard-HA101772797.aspx)

Developers can use the Dashboard Designer, accessed from within SharePoint, to define color-coded thresholds that reveal values below, above or at business targets (for performance measurement) and perform other tasks.

Scorecards let managers and executives view data, in real-time, to remain abreast of an organization's progress towards reaching established goals. Excel charts (discussed in an upcoming post) and reports can be added to the business intelligence center to build dashboards that can help the entire organization improve customer service, sales, productivity and more (see the following tutorial on balanced scorecards:  http://technet.microsoft.com/en-us/library/hh750382%28v=office.14%29.aspx).

With SharePoint 2010 organizations not only manage documents; but also achieve piece of mind by knowing where a company stands with every aspect of its key business operations. The following link provides access to a tutorial that includes instructions on how to build a dashboard and scorecards:  http://msdn.microsoft.com/en-us/sql10r2byfbi-trainingcourse_sql10r2byfbi08-hol-01.aspx

Business Intelligence Overview: Companies Remove Guess-Work To Make Sound Decisions

Companies seeking to remove the guess work from important business decisions have turned to business intelligence solutions.With business intelligence companies can use facts (based on historical data) to determine what promotional campaigns were successful and which ones were not. Companies can also view product or service performance to understand what products/services are needed most in a location. And, companies can get to know their customers better to build more compelling brands and marketing strategies. Today, even more complex buying and other patterns can be discovered through data mining, which is discussed later in this post.

In short, business intelligence answers business questions to help companies improve their performance and sales. And, in this sluggish economy, more and more businesses are turning to business intelligence to make sure they make the right choices. This post provides an overview of business intelligences and the Microsoft technologies used to support business intelligence solutions.

What Is Business Intelligence

Business Intelligence (BI) gives organizations the ability to store and use data captured over the years. With a BI system organizations can use their historical data to view product or service performance. They can also make predictions on who is most likely to buy their product or service; as well as identify the aspects that most likely makes a customer return to make repeat sales. Companies can also evaluate the usefulness of its promotional campaigns. For example, sales and marketing managers can view resulting sales from a promotional campaign. And, as companies capture the demographic makeup of their customers they can use that information to know who is buying what. Some companies have even begun to use BI with their eCommerce systems. For example, Market Basket Analysis is used to recommended products to customers based on the items in the customer's shopping cart. 

BI systems not only give organizations the ability to make informed decisions; but it also provides them an opportunity to select key performance indicators (such as sales target, product performance or sales by salesperson) to monitor an organization's performance. Some companies set targets (i.e., indicate the sales the company should reach by a given period) and use scorecards to monitor the company’s performance. These are just a few of the capabilities BI systems bring to the table. The following paragraphs discuss how BI systems are designed and the Microsoft products used to build them.

The Database Processing for Business Intelligence Systems

Traditional business applications use Online Transaction Processing (OLTP). With OLTP systems the data in the database is expected to change frequently. New data is frequently added, existing data is updated; and, tables may even change. In addition, traditional relational database concepts are used to relate the tables. This approach makes it easier for the business to make updates and perform queries against the changing data. Analyzing data in an OLTP system is difficult because the data consistently changes due to the transactions performed against it.

On the other hand, an organization that wants to analyze data (as well as gain a better understand of its business performance, customers, etc) can implement a data warehouse. Unlike the OLTP systems, the data warehouse system supports Online Analytical Processing (OLAP) and is designed solely to support analyzing and retrieving data. Transactions are not performed against a data warehouse. And, updates are only made to the data warehouse to correct errors in the existing data. Other than that the existing data never changes. Instead, new data is cleansed (by removing partial or corrupt data) and then added to the data warehouse so organizations can use historical data for decision support.

One way in which a data warehouse can be updated is based on using the Microsoft SQL Server Integration Service (SSIS). A Database Administrator, or other skilled personnel, can create packages that extract and transform data (i.e., convert data into a relational data source or other format) and then load the data into a table in the data warehouse. This process is commonly referred to as the Extract-Transform-Load (ETL) Process.

BI Database Design & Dimension Modeling

If a company builds its own BI system using Microsoft products the Microsoft SQL Server database is used to store the data. SQL Server BI Developer Studio is used to create the cubes used to retrieve the data from the data warehouse. The client application that displays the data may bebuilt using Microsoft Office SharePoint Server, which has a Business Intelligence site. Or, Microsoft Excel can be used; or, even Visual Studio .NET can be used to build custom applications. (Note that information regarding the client applications will be covered in a future post that focuses on BI client applications, scorecards and dashboards.)

Many companies turn to Microsoft for their business intelligence solutions. This is particularly true of companies whose data is already stored in a SQL Server database. To build a business intelligence system Microsoft SQL Server Analysis Service (SSAS) is installed when SQL Server is installed. Microsoft SQL Server Analysis Service uses a server component implemented as a Microsoft Windows Service.

Microsoft SQL Server Analysis Service supports XML Analysis (XMLA) to share data with client applications.

The database part of the business intelligence system, commonly referred to as a data warehouse, includes dimension tables, fact tables and indexes. Dimension tables capture the descriptive data for each business activity. These tables include the details users will want to study to make important decisions about the business. For example, users may want to view a customer’s age, education, geographic location, marital status, primary language, etc. stored in the Customer dimension table. This information helps marketing managers and others better understand how to market to customers. The primary key of each dimension table is indexed to improve data warehouse performance.

Examples of Dimension tables include Customers, Promotions, Employees, Departments, Products, etc. The following picture shows the Customer dimension table included in the AdventureWorks data warehouse designed by Microsoft for a bogus corporation.

The Fact table, in a data warehouse, contains the numeric values for the data descriptions in the Dimension tables. Hence, each dimension table is associated with a Fact table. The data in Fact tables are key to answering questions about the business. For example, if a company wants to track the effectiveness of a promotion a Sales Fact table is created with fields that capture the amount sold (per sale), quantity sold (per sale), amount the product cost the store (per sale), etc. The fact table is indexed on the primary key developed from the foreign keys used to link the dimension tables to the fact table. The dimension table(s) connected to the Fact table would include the Product table, Date table, the Promotion table, etc.

The modeling concept used to design a data warehouse database is called dimensional modeling. To design a database using this concept tables are grouped into dimensional models. Each dimensional model includes a fact table to manage the facts related to the dimension table(s), as discussed above. The fact tables are then linked to the relevant dimension tables. With dimensional modeling a model is created to represent data that supports each business activity. For example, one model captures sales, another captures inventory, another captures employees, etc.

Data warehouse database designers apply either a star or a snowflake design. The complexity of  the data defines the structure used to design the model. The star is the simplest design. With the star design each dimensional model has one or more dimension table(s) linked to a fact table using a primary key/foreign key relationship, as shown in the following example.

The snowflake structure is more complex than the star.. With the snowflake structure the dimension tables are linked to one another and then to the fact table using a primary key/foreign key relationship. For example, a product table might have a product category table related to it with the Fact table related to the product table. The AdventureWorks data warehouse includes a Product table that has a Product Category table related to the Product table and a Product Subcategory table related to the Product Category table. This means there are three dimension tables related to one another to describe the products. The Product table is linked to the Fact table. And, other dimension tables,as applicable, are linked to the fact table based on the business activity being modeled. Notice, however, this table structure is more complex than the star, which does not use subsets of data to describe data. This is just one of several ways to design a snowflake structure.

Data Mining

Once the data warehouse is in place, a developer can use the SQL Server BI Developer studio to build a cube, which is the component that interfaces with the database to retrieve the data. The cube stores the dimensions (from the selected dimension tables) and measures from the selected Facts tables. The dimension and measures selected are based on a question the business wants to answer; or, information users want to analyze such as the sales performance.

If more complex analysis is desired a data mining structure can be built from the cube using the Data Mining Wizard.

Data mining provides data warehouse users the ability to see patterns or correlations that exist in the data.However, note that these patterns would not be realized without using algorithms to perform additional data processing. The following paragraphs outline a few of the Microsoft algorithms provided by Analysis Services.

Algorithms that Help With Inventory Management, Promotions, etc.

Many grocery stores, online eCommerce sites and other businesses capture and store customer transactions (also referred to as market basket transactions). This data is often analyzed to understand the combination of items people purchase. For example, in cities that experience snow storms, consumers always buy bread, milk and wine the day before the storm. This information is used to understand consumers' purchasing behaviors to support inventory management and build more effective promotions. The algorithm that makes identifying associations possible is the Association Algorithm. In fact, some eCommerce sites use the Association Algorithm to recommend additional items a customer might be interested in based on the items placed in the online shopping cart. The Association Algorithm is also used to support medical diagnosis.

Clustering People into Homogeneous Groups

The Microsoft Clustering algorithm groups cases in a dataset into clusters to reveal conclusions people probably would not reach without the algorithm. The processed data can be used to view those with similar characteristics; or, rules can be applied to omit those with similar characteristics so that the anomalies remain. For example, the algorithm may present a cluster that shows women who buy paper-plates on Friday also buy lipstick.

Making Predictions

The Microsoft Decision Trees Algorithm is a classification and regression Algorithm used for predictive modeling. For example, a company would use the Microsoft Decision Tree Algorithm if the sales team wanted to predict whether a customer is likely to purchase a particular product or service. This would ensure time is only spent marketing to customers who are likely to buy the product or service.  The prediction would be based on information stored in the data warehouse including buying patters and demographic data

Predictions, Factors and Classifications

The Microsoft Logistic Regression Algorithm is a highly flexible Algorithm that supports multiple analytical tasks. For example, it can use demographic data to make predictions about a person or group of persons'  risk for a certain disease. The algorithm may also be used to weight the factors that contribute to a specific outcome. For example, what factors cause a customer to return to the same store.

These are just a few of the models available when using the Data Mining Structure Wizard in Microsoft's SQL Server BI Developer Studio.

Summary

Companies seeking to remove the guess work from important business decisions have turned to business intelligence solutions.With business intelligence companies can use facts (based on historical data) to improve their performance and sales.

The database part of the business intelligence system, commonly referred to as a data warehouse, includes dimension tables, fact tables and indexes. Dimension tables capture the descriptive data for each business activity. The Fact table contains the numeric values for the data descriptions in the Dimension tables. The modeling concept used to design a data warehouse database is called dimensional modeling. Data warehouse database designers apply either a star or a snowflake design. The complexity of  the data defines the structure used to design the model.

Once the data warehouse is in place, a developer can use the SQL Server BI Developer studio to build a cube, which is the component that interfaces with the database to retrieve the data. The cube stores the dimensions (from the selected dimension tables) and measures from the selected Facts tables. If more complex analysis is desired a data mining structure can be built from the cube using the Data Mining Wizard.

Data mining provides data warehouse users the ability to see patterns or correlations that exist in the data; but, these patterns would not be realized without using algorithms to perform additional data processing. SQL Server BI Developer studio includes a number of  algorithms to help organizations realize patterns to increase sales and build a more loyal customer base.

To learn more about Business Intelligence visit Microsoft's Business Intelligence home page. To learn more about Microsoft SQL Server Business Intelligence Development Studio visit Introducing Business Intelligence Development Studio.

5CT9V3W3AEN9