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.
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.
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.
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.
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.
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.
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.
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
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.
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
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
