“Other” member in Data Visualization

One important and fabulous thing that I learned from "Data Analysis Using SQL and Excel" book is how I can represent correct data using correct SQL query.
Imagine that we have an orders table in which we have customer’s orders. Now we need to present the following query:
What is the distribution of the number of orders in the 5 states that have the largest number of orders? (By representing the Other‘s as a other category)

SQL:

---

Chart:

---

Analyzing vs. Designing

I want to describe the difference between "Data Mining with SQL Server 2005" and "Data Analysis Using SQL and excel" in general.
This difference is derived from the distinction between analyzing and designing data mining systems.
For analyzing a Data Mining system, you must use some tools that have the potential of rapid development, in order to interact with the stakeholders and users quickly. In this situation, I recommend using Excel and SQL to clarify the results for users, due to the fact that, in this situation there is not any implemented mining model on OLAP server.
But if you are designing a Data Mining system, you must have a good knowledge of the OLAP server and the development tools that it has.
As a result, I think the “Data Mining with SQL Server 2005" suits for Designing, while the "Data Analysis Using SQL and excel" is appropriate for Analyzing.

I prefer to read both of these books, in order to handle the analyst and designer roles in a BI project, Maybe it is because of the fact that, I could not find anyone who can do one of these roles.
But I prefer to read the “Data Analysis Using SQL and excel” at first, then involve in “Data Mining with SQL Server 2005”.
I think it is worth to buy “Data Analysis Using SQL and excel”, but I have not any idea about the other book, but it is not so important, because there are not any other books which describe Data mining with SQL Server 2005, practically.
Finally, I do not think these books can be used instead of each other. In fact, they must be used as complementary.

Data Analysis Using SQL and Excel

I work as a system analyst and Designer, and also Database Designer. I have a good command of database and SQL. Moreover, I performed some data realization using Excel. But I never imagined how tightly they can fit together.

I am familiar with normal distribution and some other simple techniques in statistics theoretically, yet I have a big problem with statistic concepts like Regression and other ones.

When I started to read some books about data mining, this was the main problem that I dropped data mining. When I saw this book, I felt like the time that I seen MDX Solution book. It raised my hopes of achieving the difficulty of data mining. I take a quick look at it, and it seems organized very well.

I decided to write an overview about this book in order to help my friend Pedro and some other people who want to make a decision to buy this book or not, but keep it in your mind that I write this post based on taking a quick overview on that, not by reading it completely.

Chapter 1: A Data Miner Looks at SQL
The author introduces Dataflow concept in this chapter.

Chapter 2: What’s In a Table? Getting Started with Data Exploration
This chapter explains how you can explore SQL results with excel charts. I discover another point of view by skimming this chapter.

Chapter 3: How Different Is Different?
The basic concepts of statistic and the combination of statistics, SQL, and Excel are explained in this chapter.

Chapter 4: Where Is It All Happening? Location, Location, Location
The geography and the processes which could be done using SQL and Excel play a primary role in discovering the knowledge. You can observe this great job by reading this chapter.

Chapter 5: It’s a Matter of Time
This chapter does just as the previous one but for time.

Chapter 6: How Long Will Customers Last? Survival Analysis to Understand Customers and Their Value
Nothing will be better than the sentence that author mentioned at the first: “Survival analysis estimates how long it takes for a particular event to happen. A customer starts; when will that customer stop? By assuming that the future will be similar to the past (the homogeneity assumption), the wealth of data about historical customer behavior can help us understand what will happen and when.”

Chapter 7: Factors Affecting Survival: The What and Why of Customer Tenure
“This chapter builds on this foundation, by introducing three extensions of basic survival analysis. These extensions solve some common problems faced when applying survival analysis in the real world. They also make it possible to understand the effects of other factors besides tenure on survival.”

Chapter 8: Customer Purchases and Other Repeated Events
This chapter discusses everything about customer behavior: when, where, and how. With one notable exception: what customers purchase.

Chapter 9: What’s in a Shopping Cart? Market Basket Analysis and Association Rules
“This chapter dives into the detail, looking at the specific products being purchased, to learn both about the customers and the products they are buying. Market basket analysis is the general name for understanding product purchase patterns at the customer level.”

Chapter 10: Data Mining Models in SQL
“This chapter takes an alternative approach that introduces data mining concepts using databases. This perspective presents the important concepts, sidestepping the rigor of theoretical statistics to focus instead on the most important practical aspect: data.”

Chapter 11: The Best-Fit Line: Linear Regression Models

Chapter 12: Building Customer Signatures for Further Analysis
This chapter focuses on data preparation.

Data Mining

I love to know more about data mining, but I have never found any simple book which discusses data mining and statistics practically.

Yesterday, I realized that the library bought "Data Analysis Using SQL and Excel" that describes about Data Mining with SQL and Excel in practical way.

I decided to read it, in order to know the fundamental concepts and becoming eligible to read other books.

I hope it will be useful as the author mentioned in the preface.

process analysis services objects through SSIS

There are two methods for populating data into SSAS, which are used mostly for non-standard data sources:

1. Using Dimension Processing Data Flow Destination
2. Using Partition Processing Data Flow Destination

You can map your source data into the dimension or Partition, and set the update method which can be Add, Full, or Update.

Analysis Services Processing Task

This task force the SSAS to process Database, Cube, Partition, Measure Group, Dimension, Mining Structure, and Mining Model through SSIS.
At first you must specify the connection which specifies the objects that must be processed in connection manager combo box.



Then you must add the objects that you want to get processed.



Next you must set their process options based on the action that you want to happen. There are some actions that are available for some objects that I listed them as follow:



When you set the process option to Process Incremental you will have to configure its settings, The settings are included of Measure Group, Partition, and Source Data that can be table or query.



You can change the processing order of this batch via Change Settings button. The Change Settings form has two tabs that are called Processing options tab and Dimension key errors tab.
You can set some general settings such as transaction type (Sequentional, or Parallel), write back table, and process related objects in the first tab.



Finally you can set the action that you want to occure when an error raised in the second tab.

Processing Analysis Services Objects through SSIS

There are two ways to process analysis services objects:

1. Force SSAS to handle the process via SSIS
   you must use the Control Flow Tasks for this kind of operations:
   
• Analysis Services Processing Task
  This task provides you the SSAS objects' options visually.
  
• Analysis Services Execute DDL Task
  you can specify a DDL script to this task to perform your Process via XML/A.


2. Process those objects through SSIS
   the best situation in which you can use these destinations is where you have non-standard sources and you need near real-time processing.
   you must use the Data Flow Destinations for this kind of operations:
• Dimension Processing Data Flow Destination
• Partition Processing Data Flow Destination

There are two other ways for handling these processes:

1. You can run your XML/A script via ASCMD.exe
   you can do this by using the Execute Process Task.
2. Handling the process with AMO.
   You must write program in .net language through Script Task.

I will describe each of these tasks separately, later.