Some time ago, one of the Accounting Division’s staff asked me to reveal if the last decision of the Bank about introducing new participation papers were successful or not; also, he asked me to specify what percent of its sales were because of changing other deposits.
It was a hard job, because we had not any clear idea on how to deal with this matter! It had done, but I kept thinking about solving this problem via Business Intelligence Solutions rather than executing complex queries on databases.
When I was reading “The Data Warehouse Toolkit Second Edition”, I realized that it is very similar to “Market Basket Analysis” concept, and after doing some search it began to sink in.
Indeed, I found it out that it could be done via association algorithm of the Data Mining. This subject is about the market basket analysis, and finding the association of products based on their sales, in order to offer new discount for increasing the sales.
For instance, you can imagine that when you find it out that the toothpaste and toothbrush were sold together in most situations, it could be a good starting point for designing new promotion!
Sunday, April 13, 2008
Saturday, March 29, 2008
Conformed Dimension
The word of Enterprise Data Warehouse is very nice at the first glance, yet when businesses want to estimate its cost, they usually prefer to have some important data marts that drive their most important business goals; in order to break its enormous cost. Beside, having these kinds of data marts could be a huge problem especially when those could not integrated with each other during the time that those are created.
Having bus architecture is undoubtedly one of the vital parts of building an enterprise data warehouse during the time, for it will provide the join possibility between the data marts by specifying the conformed dimensions.
Conformed dimensions are what which are common between data marts and make the drill across possible. Off course, it could not be eliminated from enterprise main features.
Finally, the most common problem that you may encounter during building the conformed dimensions is different level of grains that different data marts are needed, so it is recommended that you design dimension’s levels correctly which will even be helpful for drilling through actions.
Having bus architecture is undoubtedly one of the vital parts of building an enterprise data warehouse during the time, for it will provide the join possibility between the data marts by specifying the conformed dimensions.
Conformed dimensions are what which are common between data marts and make the drill across possible. Off course, it could not be eliminated from enterprise main features.
Finally, the most common problem that you may encounter during building the conformed dimensions is different level of grains that different data marts are needed, so it is recommended that you design dimension’s levels correctly which will even be helpful for drilling through actions.
Saturday, March 1, 2008
Factless Fact Tables
I have recently got familiar with the concept of “Factless Fact Tables”, and due to the fact that it was very interesting for me, I like to share it with you, if you are not already familiar with it.
The “Factless Fact Table” is a table which is similar to Fact Table except for having any measure; I mean that this table just has the links to the dimensions. But what is the benefit?
These tables enable you to track events; indeed they are for recording events.
Another kind of this Table is known as “Factless Coverage Table” which is very usefull. Imagine that you have a retail stores and each store has its own promotion policy. It would be very complicated if you wanted to answer this sort of question: “Which products were on promotion that didn't sell?”
The best way for covering these kinds of questions is the coverage tables. These tables hold the coverage data for answering these questions.
I also recommend reading “Factless Fact Tables” that was written by Ralph Kimball.
The “Factless Fact Table” is a table which is similar to Fact Table except for having any measure; I mean that this table just has the links to the dimensions. But what is the benefit?
These tables enable you to track events; indeed they are for recording events.
Another kind of this Table is known as “Factless Coverage Table” which is very usefull. Imagine that you have a retail stores and each store has its own promotion policy. It would be very complicated if you wanted to answer this sort of question: “Which products were on promotion that didn't sell?”
The best way for covering these kinds of questions is the coverage tables. These tables hold the coverage data for answering these questions.
I also recommend reading “Factless Fact Tables” that was written by Ralph Kimball.
Wednesday, January 23, 2008
“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:
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:
Friday, January 4, 2008
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.
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.
Sunday, December 30, 2007
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.
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.
Tuesday, December 25, 2007
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.
Subscribe to:
Posts (Atom)