Business Intelligence Tool Selections: General Considerations

When we evaluate which business intelligence tool to use, the first determination is the Buy vs. Build decision. We can use the following table to compare the two approaches.

Category	Buy	Build
Cost		A
Implementation Time	A
Documentation	A
Functionality / Features	A
Tailored for the exact needs		A
Reliance on third-party		A

Clearly, both approaches have its own advantages and disadvantages, and it is often wise to consider each of the components individually. For example, it is clearly not viable to write a relational database from scratch. Therefore, we may have a case where the hardware and the database are bought, but other tools are built from within. In general, deciding which approach to go is dependent on the following criteria:

• User technical skills
• Requirements
• Available budget
• Time

Because each tool in the business intelligence arena has different functionalities, the criteria for the "Buy vs. Build" decision is different for each type. We will get into a more detailed discussion for each tool later.
Should we decide to purchase an existing third-party business intelligence tool, we must then decide which one to buy. Often, there are a number of choices to pick from, some are well-known, and some others not as well-known, but
In addition to tool functionalities, which we will discuss in the following sections, there are several considerations that we should take into account when considering tool vendors in general:

Tool Vendor's Stability: More than anything else, this is probably the most important measure. In my opinion, this is even more important than the current functionalities that the tool itself provide, for the simple reason that if the company is going to be around for a while, it will be able to make enhancements to its business intelligence tool. On the other hand, if the company is likely to be out of business in six months, then it doesn't matter that it has the state-of-the-art features, because sooner or later these features will be out-of-date.
Some of the ways to tell about company's stability are:

• What type of office space is it occupying? Is it wasting money by renting the most expensive office space in the area just so that it can be noticed? Or is it plugging all its money back into R&D so that the product can be improved?
• The background of senior management. The company might be new, but if it has seasoned veterans from major companies like IBM, Oracle, and Microsoft, to name a few, it is more likely to be successful because top management has seen how it's done right.

Support: What type of support is offered? It is industry standard for vendors to charge an annual support fee that is 15-20% of the software product license. Will any software issues be handled promptly?
Professional Services: This includes consulting and education. What type of consulting proposal does the vendor give? Is the personnel requirements and consulting rates reasonable? Is the vendor going to put in someone fresh out of college and charge $200/hr for that person? It might be wise to speak with members of the consulting team before signing on the dotted line. On the education front, what type of training is available? And how much is the consulting team willing to do knowledge transfer? Does the consulting team purposely hold off information so that either 1) you will need to send more people to vendor's education classes, or 2) you will need to hire additional consulting to make any changes to the system.

Business Intelligence Software

As the old Chinese saying goes, "To accomplish a goal, make sure the proper tools are selected." This is especially true when the goal is to achieve business intelligence. Given the complexity of the data warehousing system and the cross-departmental implications of the project, it is easy to see why the proper selection of business intelligence software and personnel is very important. This section will talk about the such selections. They are grouped into the following:

• General Considerations
• Database/Hardware
• ETL Tools
• OLAP Tools
• Reporting Tools
• Metadata Tools
• Open Source Business Intelligence Tools
• Data Warehouse Team Personnel

Business Intellegence Uses

Business intelligence usage can be categorized into the following categories:
1. Business operations reporting
The most common form of business intelligence is business operations reporting. This includes the actuals and how the actuals stack up against the goals. This type of business intelligence often manifests itself in the standard weekly or monthly reports that need to be produced.
2. Forecasting
Many of you have no doubt run into the needs for forecasting, and all of you would agree that forecasting is both a science and an art. It is an art because one can never be sure what the future holds. What if competitors decide to spend a large amount of money in advertising? What if the price of oil shoots up to $80 a barrel? At the same time, it is also a science because one can extrapolate from historical data, so it's not a total guess.
3. Dashboard
The primary purpose of a dashboard is to convey the information at a glance. For this audience, there is little, if any, need for drilling down on the data. At the same time, presentation and ease of use are very important for a dashboard to be useful.
4. Multidimensional analysis
Multidimensional analysis is the "slicing and dicing" of the data. It offers good insight into the numbers at a more granular level. This requires a solid data warehousing / data mart backend, as well as business-savvy analysts to get to the necessary data.
5. Finding correlation among different factors
This is diving very deep into business intelligence. Questions asked are like, "How do different factors correlate to one another?" and "Are there significant time trends that can be leveraged/anticipated?"

Master Data Management

What is Master Data Management

Master Data Management (MDM) refers to the process of creating and managing data that an organization must have as a single master copy, called the master data. Usually, master data can include customers, vendors, employees, and products, but can differ by different industries and even different companies within the same industry. MDM is important because it offers the enterprise a single version of the truth. Without a clearly defined master data, the enterprise runs the risk of having multiple copies of data that are inconsistent with one another.
MDM is typically more important in larger organizations. In fact, the bigger the organization, the more important the discipline of MDM is, because a bigger organization means that there are more disparate systems within the company, and the difficulty on providing a single source of truth, as well as the benefit of having master data, grows with each additional data source. A particularly big challenge to maintaining master data occurs when there is a merger/acquisition. Each of the organizations will have its own master data, and how to merge the two sets of data will be challenging. Let's take a look at the customer files: The two companies will likely have different unique identifiers for each customer. Addresses and phone numbers may not match. One may have a person's maiden name and the other the current last name. One may have a nickname (such as "Bill") and the other may have the full name (such as "William"). All these contribute to the difficulty in creating and maintain in a single set of master data.
At the heart of the master data management program is the definition of the master data. Therefore, it is essential that we identify who is responsible for defining and enforcing the definition. Due to the importance of master data, a dedicated person or team should be appointed. At the minimum, a data steward should be identified. The responsible party can also be a group -- such as a data governance committee or a data governance council.

Master Data Management vs Data Warehousing

Based on the discussions so far, it seems like Master Data Management and Data Warehousing have a lot in common. For example, the effort of data transformation and cleansing is very similar to an ETL process in data warehousing, and in fact they can use the same ETL tools. In the real world, it is not uncommon to see MDM and data warehousing fall into the same project. On the other hand, it is important to call out the main differences between the two:
1) Different Goals
The main purpose of a data warehouse is to analyze data in a multidimensional fashion, while the main purpose of MDM is to create and maintain a single source of truth for a particular dimension within the organization. In addition, MDM requires solving the root cause of the inconsistent metadata, because master data needs to be propagated back to the source system in some way. In data warehousing, solving the root cause is not always needed, as it may be enough just to have a consistent view at the data warehousing level rather than having to ensure consistency at the data source level.
2) Different Types of Data
Master Data Management is only applied to entities and not transactional data, while a data warehouse includes data that are both transactional and non-transactional in nature. The easiest way to think about this is that MDM only affects data that exists in dimensional tables and not in fact tables, while in a data warehousing environment includes both dimensional tables and fact tables.
3) Different Reporting Needs
In data warehousing, it is important to deliver to end users the proper types of reports using the proper type of reporting tool to facilitate analysis. In MDM, the reporting needs are very different -- it is far more important to be able to provide reports on data governance, data quality, and compliance, rather than reports based on analytical needs.
4) Where Data Is Used
In a data warehouse, usually the only usage of this "single source of truth" is for applications that access the data warehouse directly, or applications that access systems that source their data straight from the data warehouse. Most of the time, the original data sources are not affected. In master data management, on the other hand, we often need to have a strategy to get a copy of the master data back to the source system. This poses challenges that do not exist in a data warehousing environment. For example, how do we sync the data back with the original source? Once a day? Once an hour? How do we handle cases where the data was modified as it went through the cleansing process? And how much modification do we need make do to the source system so it can use the master data? These questions represent some of the challenges MDM faces. Unfortunately, there is no easy answer to those questions, as the solution depends on a variety of factors specific to the organization, such as how many source systems there are, how easy / costly it is to modify the source system, and even how internal politics play out.

Data Warehousing - ETL Tool Selection

Buy vs. Build
When it comes to ETL tool selection, it is not always necessary to purchase a third-party tool. This determination largely depends on three things:

• Complexity of the data transformation: The more complex the data transformation is, the more suitable it is to purchase an ETL tool.
• Data cleansing needs: Does the data need to go through a thorough cleansing exercise before it is suitable to be stored in the data warehouse? If so, it is best to purchase a tool with strong data cleansing functionalities. Otherwise, it may be sufficient to simply build the ETL routine from scratch.
• Data volume. Available commercial tools typically have features that can speed up data movement. Therefore, buying a commercial product is a better approach if the volume of data transferred is large.

ETL Tool Functionalities While the selection of a database and a hardware platform is a must, the selection of an ETL tool is highly recommended, but it's not a must. When you evaluate ETL tools, it pays to look for the following characteristics:

Functional capability: This includes both the 'transformation' piece and the 'cleansing' piece. In general, the typical ETL tools are either geared towards having strong transformation capabilities or having strong cleansing capabilities, but they are seldom very strong in both. As a result, if you know your data is going to be dirty coming in, make sure your ETL tool has strong cleansing capabilities. If you know there are going to be a lot of different data transformations, it then makes sense to pick a tool that is strong in transformation.

Ability to read directly from your data source: For each organization, there is a different set of data sources. Make sure the ETL tool you select can connect directly to your source data.

Metadata support: The ETL tool plays a key role in your metadata because it maps the source data to the destination, which is an important piece of the metadata. In fact, some organizations have come to rely on the documentation of their ETL tool as their metadata source. As a result, it is very important to select an ETL tool that works with your overall metadata strategy. Popular Tools

• IBM WebSphere Information Integration (Ascential DataStage)
• Ab Initio
• Informatica
• Talend

Business Intellegence Tools

The most common tools used for business intelligence are as follows. They are listed in the following order: Increasing cost, increasing functionality, increasing business intelligence complexity, and decreasing number of total users.
Excel
Take a guess what's the most common business intelligence tool? You might be surprised to find out it's Microsoft Excel. There are several reasons for this:
1. It's relatively cheap.
2. It's commonly used. You can easily send an Excel sheet to another person without worrying whether the recipient knows how to read the numbers.
3. It has most of the functionalities users need to display data.
In fact, it is still so popular that all third-party reporting / OLAP tools have an "export to Excel" functionality. Even for home-built solutions, the ability to export numbers to Excel usually needs to be built.
Excel is best used for business operations reporting and goals tracking.
Reporting tool
In this discussion, I am including both custom-built reporting tools and the commercial reporting tools together. They provide some flexibility in terms of the ability for each user to create, schedule, and run their own reports. The Reporting Tool Selection selection discusses how one should select an OLAP tool.
Business operations reporting and dashboard are the most common applications for a reporting tool.
OLAP tool
OLAP tools are usually used by advanced users. They make it easy for users to look at the data from multiple dimensions. The OLAP Tool Selection selection discusses how one should select an OLAP tool.
OLAP tools are used for multidimensional analysis.
Data mining tool
Data mining tools are usually only by very specialized users, and in an organization, even large ones, there are usually only a handful of users using data mining tools.
Data mining tools are used for finding correlation among different factors.

Business Intelligence

Business intelligence is a term commonly associated with data warehousing. In fact, many of the tool vendors position their products as business intelligence software rather than data warehousing software. There are other occasions where the two terms are used interchangeably. So, exactly what is business inteligence?
Business intelligence usually refers to the information that is available for the enterprise to make decisions on. A data warehousing (or data mart) system is the backend, or the infrastructural, component for achieving business intellignce. Business intelligence also includes the insight gained from doing data mining analysis, as well as unstrctured data (thus the need fo content management systems). For our purposes here, we will discuss business intelligence in the context of using a data warehouse infrastructure.
This section includes the following:
Business intelligence tools: Tools commonly used for business intelligence.
Business intelligence uses: Different forms of business intelligence.
Business intelligence news: News in the business intelligence area.