Building business intelligence skills
What is business intelligence (BI)?
Business Intelligence (BI) systems enable organizations to improve their business performance by leveraging information about their customers, suppliers and internal business operations. The end goal of a BI system is to enable users to consume a large amount of pertinent data, analyze it and produce either revenue generating opportunities or cost savings to the business. Generally, BI systems need to:
- Gather data from several sources - for example transactional databases around the company and various related applications such as customer relationship management and supply chain management.
- Consolidate, organize and standardize information in repositories such as data warehouses and data marts.
- Provide analytical tools that enable specialists to run queries against the data and uncover patterns and potentially diagnose problems.
Extract, transform and load (ETL)
Gathering transactional data from multiple source applications (internal and external) is often referred to as ETL for Extract, Transform and Load. Data is extracted from a myriad of applications or repositories. Then, it is generally transformed to conform to a schema design that is ideal for analysis in the data warehouse. In addition, there are other transformations that take place, such as EBCDIC to ASCII, packed decimal to integer and changes to date formats (e.g. mmddyy to yyymmdd). And, finally the data is loaded into the data warehouse.
Consolidate, organize and standardize
The data warehouse is a relational database designed for high performance of complex queries. Once the data is loaded into a data warehouse or data mart, it becomes the one stop shop for any forecasting of inventory, expenses, revenues; typically anything where past performance can be used to predict trends. An important note of differentiation here is the data mart vs. the data warehouse. Most data marts are application specific. They target solving one particular set of business problems, while a data warehouse is intended to consolidate information from all business areas.
Analyze and report
Technical and business analysts use a variety of tools to access the data in the data warehouse, analyze the information and view the results. The most common are:
- Query and Reporting Tools
These tools enable users to perform historical, slice and dice type analysis against information stored in the data warehouse. Typical queries look like, What was the total revenue for all stores in the southern region during the third quarter? Generally, there are pre-built queries and reports provided by the tools for such reports.
Query results and reports can be delivered through specific desktop applications, intranets or external portals for viewing by a specific audience. For example, many executives make decisions based on trends predicted by a key performance indicator report that is a part of their executive portal.
- Online Analytical Processing (OLAP) and data mining
OLAP analytical engines and data mining tools allow users to perform predictive multidimensional analysis, or drill down analysis. These tools can be used for forecasting, customer profiling, trend analysis and even fraud detection. In the area of customer profiling, customers can be segmented based on commonality and then predictions can be made on who will abandon the enterprise and who will buy more. Other OLAP analysis typically answers the `what if' and `why' types of questions. For example, What would be the effect in New Hampshire of a 5% increase in the price of canned tuna?
Business Intelligence Today
According to market analysts, business intelligence has become the most crucial and sought after domain of information technology for the enterprise. Why is this the case? Well, in today's economic climate, businesses of all size and in all markets need extra leverage to survive. This leverage typically comes from key decision makers having fast access to the business information they need to evaluate a situation.
It is no wonder that International Data Corp. (IDC), a premier global market intelligence and advisory firm in the IT industry predicts the market size for business intelligence systems will double by 2006 to more than $14 billion world wide. Other analysts, such as Meta Group, also believe that the emphasis in the database industry is shifting from transaction processing to business analytics and data warehousing capabilities.
Making sure you're ready for your BI projects
Data warehousing and business intelligence projects require very sound technical skills. It is definitely not a trivial project to collect data from many different sources, validate it, combine it, structure it properly, store it reliably, distribute it and analyze it. You must have a sound knowledge of data warehousing and business intelligence technology. In addition to those technical skills one also needs to know how business intelligence technology can aid in the organization's decision making process; which requires knowledge of the business as well.
Getting started with BI skills
Relational database skills are the foundation of Business Intelligence. The first step in building BI skills is learning and using DB2® Universal DatabaseTM (UDB). There are many resources available to you - classroom courses, computer based courses, online tutorials, textbooks and certifications. Choose the most convenient for you.
The table below outlines some of the key resources for building DB2 UDB skills.
DB2 UDB Skills Resources
|Resource||For More Information|
|Computer Based Courses||http://www.ibm.com/software/data/education/selfstudy.html|
Getting BI certified
IBM offers an IBM Certified Solution Designer - DB2 Business Intelligence V8 Certification for BI. This designation requires completion of two tests:
Figure 1. BI Certification Roadmap
Since relational database skills are a foundation for BI, Test 700: DB2 UDB V8.1 Family Fundamentals is a pre-requisite to becoming an IBM Certified Solution Designer - DB2 Business Intelligence V8. This particular test contains 54 multiple choice questions and requires a 61% or better to pass the exam. The exam covers knowledge of DB2 UDB products, features, security, objects, transactions and SQL.
Test 705: Business Intelligence (BI) Solutions v8.1 is an ideal way to confirm your knowledge of fundamental BI concepts and the design, development and support of BI systems. This certification will ensure that your skills are current and relevant and will improve your credibility with respect to BI projects.
Test 705 contains 56 multiple choice questions, taken in a 75 minute period. You are required to obtain a minimum 58% to pass the exam. i.e. 33 questions out of 56 must be correct. The 56 exam questions are based on four aspects of BI:
BI Certification Topics
|1||Business Intelligence Terminology, Functions and Differentiators||Defining BI terms |
Differentiating between a data mart and a data warehouse
Articulating the benefits of BI
|2||Gathering BI Customer Requirements||Determining the number of users, types of queries, number of queries and user work tables.|
|3||Business Intelligence Data Sourcing||Identifying various sources of data (internal, external, operational and non-operational). |
Identifying methods of extracting and transforming data.
|4||Business Intelligence Solution Architecture||Building logical data models|
There are several ways to prepare for taking the BI Solutions test, including taking classroom courses. My personal recommendation is to obtain the book Business Intelligence for the Enterprise by Mike Biere (ISBN 0131413031). This book is written to prepare you for the BI Solutions test and will cover all that one needs to know to prepare for the test.
I would also recommend taking the sample test offered by IBM. This is a practice run with the types of questions that will be on the actual exam. You will have to register, if you have not done so before. You then select the BI Solutions test from a list of available DB2 tests. The sample test will be presented question by question and must be completed in a prescribed amount of time. This is truly an excellent way to prepare oneself for the actual test environment.
Other courses and resources
IBM offers a comprehensive portfolio of technical training to build your BI skills. The table below summarizes the available courses pertinent to BI.
BI classroom courses
|Course Code||Course Title||Duration (days)||Description|
|DW030||Introduction to Business Intelligence and Data Warehousing||2||Gain an understanding of architectural components of a DW, their functions, and implementation processes. Learn to relate the concepts of BI and representative usage scenarios. Identify selected products and their key features for use with implementing BI. |
This course is intended for data architects, data modelers, technically oriented project leaders, database designers and administrators.
|XDW01||Data Warehouse Mastery Program||12.5||This course is a customizable combination of DW 110, DW120, DW130 (see below), additional self study material and mentoring conference calls. It is a very comprehensive program that helps build your data warehouse skills portfolio. The program details the major dimensions of successful enterprise class data warehouses and data marts through relevant content, challenging case studies, mentoring and exercises. |
This course is intended for data warehouse modelers, data administrators, information analysts, project managers and technical professionals who design and implement effective data warehouse solutions.
|DW110||Building the Data Warehouse||4.5||Learn practical methods and techniques for designing and constructing a Data Warehouse (DW) and to provide a comprehensive Business Intelligence (BI) Solution Architecture. Apply the techniques in the context of an incremental process, suitable for constructing a DW and BI solution from a departmental or functional workgroup perspective. Also, learn how the process can further be extended for the construction of consistent, corporate-wide data warehouses.|
|DW120||Advanced Data Warehouse Workshop: Multi-Dimensional Modeling||4||This course concentrates on Relational Online Analytical Processing (ROLAP) and Multidimensional Online Analytical Processing (MOLAP) design techniques.|
|DW130||Advanced Data Warehouse Workshop: Extract, Transform and Load||4||Review and evaluate solution models for Very Large Databases (VLDB) and standard data warehouse populating systems. Map these solution models into techniques and commercially available software tools.|
|DW180||Advanced Data Warehouse Workshop: VLDB Issues and Solutions||3||Learn to identify the key issues that often inhibit successful implementation of Data Warehouse (DW) and Business Intelligence (BI) solutions. Discover lessons learned by IBM's consulting professionals and the acclaimed IBM Teraplex Center in the form of tips and tricks.|
I find that the IBM Course Roadmaps are most useful when determining which course to take and in what order. The BI course roadmaps are located at http://www-306.ibm.com/services/learning/us/catalog/bus/roadmaps/. As an example, here is what the Business Intelligence and Data Warehousing Overview roadmap looks like:
Keeping yourself up to date on a regular basis
First and foremost, book mark http://www-306.ibm.com/software/data/db2bi/. This is the IBM DB2 Business Intelligence site. Any information on new releases, white papers, redbooks, retail publishing books or tutorials you will find at this site. For example, currently they are featuring and online tutorial, entitled BI Tutorial: Extended Lessons in Data Warehousing . This tutorial is presented as a case study in which you are the database administrator for a company. The financial department of the company wants to track, analyze and forecast the sales revenue across geographies on a periodic basis for all products sold. In the tutorial, you learn how to maintain an existing data warehouse and how to work with the warehouse metadata. The tutorial takes about one hour to complete.
Another great start for keeping up to date on BI skills is at the DB2 developerWorks site (http://www-106.ibm.com/developerworks/db2/). This site offers a myriad of technical resources for DB2 information management software. Regular visits help you keep up to date through new articles, tutorials and news and events. If however, you're looking for a specific piece of information, you can access the Library located in the left navigation bar. The library houses a good variety of resources. Things like books on DB2 information management software - newly published books, IBM Redbooks and manuals can be found there. White papers are also available - these are IBM developed documents with technical information specific to a particular subject.
I also recommend subscribing to DB2 Magazine (www.db2mag.com). This quarterly publication covers a range of topics, is devoted to DB2 and is targeted at database administrators, analysts, programmers, designers and consultants. The articles are timely and are written by experts in the field. In addition to the quarterly publication, you can stay informed on all the latest news by subscribing to the monthly DB2 Magazine e-mail. Generally the magazine will cover several topics related to BI. From their website, you can choose to look at the BI articles by selecting Magazine from the left navigation bar and then `Browse Articles By Topic'. From there, select Business Intelligence to view all recent articles relating to BI.
IDUG - the International DB2 Users Group - is another good resource for keeping your BI skills current. This is an independent, user-run organization whose mission is to support and strengthen the information technology community by providing education and services designed to promote the effective use of the DB2 family of products. IDUG offers a wide range of useful information, including regional users groups, discussion forums and conferences:
- regional users groups meet on a regular basis and have guest speakers such as DB2 experts and vendor partners speak on specific topics and provide product demos
- discussion forums are a great way to network with colleagues and learn the tricks and tips of the trade
- conferences are held around the world and provide an excellent venue to hear the latest trends, tips and techniques on data management technology and business intelligence
On the topic of conferences, another excellent opportunity is the IBM Data Management Technical conference. It takes place once a year, usually in the September - October time frame. This conference hosts a broad range of experts who offer insights and advice in keynote sessions, presentations and round table discussions. The conference is intended for database administrators, application programmers, system programmers, and of course data warehouse and business intelligence architects.
As business intelligence and data warehousing become a large part of running a business, having the proper skills on this technology becomes more and more important. Implementing a business intelligence system encompasses the collection of data from many different sources, validating and structuring the data properly, then storing it in a data warehouse, and making it available to users for analysis. To do this, you must have the proper technical skills and understanding of how the BI system will help the organization produce revenue generating or cost savings opportunities. Many resources are at your fingertips for building the proper BI skills. IBM offers a BI certification which is a very useful way to confirm your knowledge of BI concepts. There are also retail books and classroom courses offered. After taking the certification and classroom courses, it's still necessary to keep up to date the latest advances in BI. For that purpose, I recommend being an avid visitor to DB2 Developer works, an eager reader of DB2 Magazine and an enthusiastic member of IDUG.