Can you give me one example of Business Intelligence?

Question

I don't really understand what Business Intelligence is all about. If I start from having a corporate DB, what is it that a BI person would do? I found plenty of material on the web, but it usually is a bit too complex. I want a simple example that would make me understand what BI is all about and what would a BI person produce that is of value to my organization.

Answer 1

Business Intelligence is often a completely separate sect from Database Administration and Database Development. Business Intelligence, at the highest level, includes three main facets:

1. Reporting
2. Integration
3. Analysis

Reporting

Reporting is the creation, deployment, and management of reports as well as the added ability for users to customize reporting dynamically.

Integration

Data integration and transformation solutions. On the very simplest level, it is the means of extracting, transforming, and loading data into a data source, from a data source (which could be anything as simple as a flat file). Integration is a mile deep, but that's the most basic functionality of it.

Analysis

Online Analytical Processing (OLAP) used to design, create and manage structures that contain data aggregrated from source data stores. A catch phrase for this is data mining.

These are extremely simplified descriptions of what Business Intelligence encorporates. There is a science behind BI, as well as each of these facets individually. Database Professionals dedicate their time and careers to mastering these.

Answer 2

The value depends very much on the individual organisation and its requirements. Depending on the level of sophistication required, a B.I. role might fall into a few different categories:

• Spreadsheet jock - working from data sets extracted directly from operational systems, this role will produce reports and analysis using desktop tools such as Excel or Access. Often, this role isn't an I.T. specialist, or may be undertaken part-time by somebody. Depending on their level of technical skill and access to the underlying databases they may be dependent on other staff such as database administrators to produce the data extracts.
  
  This role brings value when bundled reports do not meet requirements and additional work is necessary to get management information from the databases but a dedicated B.I. development team would be too expensive. Normally this role will be necessary in all but trivial cases, although it may not have a full-time requirement on small sites.

• B.I. Developer - If the data extraction is complex or requires integration from multiple sources it may be necessary to build a data warehouse or other reporting system to integrate present the data in a format that's usable for reporting. People in this role will typically have technical development skills to a greater or lesser extent.
  
  Often this type of team will be separated into ETL and reporting functions, but this is not always the case. Report developers, 'Spreadsheet Jock' types and other power users may use data from the reporting system through various tools.
  
  The organisational value from this role is realised when the data is too complex to manage with an ad-hoc approach and a dedicated reporting system is necessary. In this case, a smaller B.I. team with technical skills and appropriate tooling can automate a lot of work that would otherwise be carried out manually with desktop tools and ad-hoc extracts. A data warehouse system can also populate self-service reporting facilities such as OLAP cubes that allow end-users within the business to produce and maintain their own reports.

• Data Architect - A mature data warehouse system will prompt data requirements from the business that cannot be fulfilled with data available from source systems. It may be necessary to coordinate changes to operational systems driven from these requirements in order to capture additional data or clean up data inconsistently or incorrectly recorded at source.
  
  A data architect may take a role that sits across multiple operational and reporting systems to coordinate fulfilment of data requirements that need changes to be made across multiple systems.
  
  The need for this role is often not recognized, but it becomes important on larger sites. Often, reporting requirements are not met well by operational systems, and the authority of data warehouse teams does not extend to making changes to operational systems. In this case a data architect acts as a coordinator or director depending on the level of authority in the role. The principal value is to issue changes to operational systems where they do not meet data requirements.

• Data Governance - Regulatory or business requirements may dictate data correctness or governance standards. If the operational systems are prone to data errors (which is usually the case) then a data governance function may be put in place to manage validation and corrections to the data.
  
  Data quality may be important for a variety of reasons, often to do with accounting or regulatory requirements. A data governance or data quality officer is typically a business-led role with responsibility for arranging fixes to data already recorded in systems.

• Analyst - A variant of the spreadsheet jock role where the user actually works in some capacity where they do analytical work on the data (for example an Insurance Actuary).
  
  An analyst may be important to the business for a variety of reasons, depending on the role. In the case of an Actuary, their role is to estimate reserves to be held against future claims, maintiain pricing models for insurance products or to provide valuations on various financial transactions.

Most B.I. staff tend to fall into one or more of these categories. The value to an organisation varies with individual circumstances. One common phenomenon that I observe is that people responsible for operational systems greatly underestimate the amount of work that actually takes place in these roles. I've seen one insurance company that had 170 staff just in the accounts department of their European operations. Most of their time was spend wrangling data extracts in spreadsheets and operating manual reconciliation and control processes.

Management Information is very often a poor cousin in the priorities during development and operation of line-of-business applications. A poorly coordinated or non-existent data architecture strategy can cost a large amount of time and money. The default behaviour is to treat systems as silos with nobody having direct authority to fix cross-system data issues. Leave this for long enough and the net effect is back office operations employing hundreds of clerical staff (often qualified finance personnel) spending most of their time doing the work of a few stored procedures.

Answer 3

what would a BI person produce that is of value to my organization.

I'm going to take a stab at this part of the question as I think others have done a good job of explaining what BI is. I work for a company with many clients and I know a great deal of information about the functions we provide for those clients.

Our applications are very data-centric; our industry is regulated by the government so compliance with federal and state laws is critical. What do our BI specialists bring to the company that makes them valuable?

• First we import millions of records from the client so that they have the information they need to do their job. Making data from their databases fit into our databases is a critical job and not very simple; you have missing information for required fields, data type mismatches, data integrity issues (I can't put 02/30/2012 into a date field for instance). We also do customization, so I have to design a place to put in data which we would not store for other clients and then create the import to get the data in. Without the client's data, the application doesn't work. The data is too extensive to be entered manually.

• Next, the client's managers need to see the data in ways that help them manage their business. So they request reports, lots and lots of reports, budget reports, expense reports, comliance reports etc. These reports are so complex that the queries behind them may be more than a thousand lines long. It can take an expert in SQL to write this kind of reporting code.

• Further, the business intelligence people are often deeper into the details of the business than many application developers, so they are the first line on evaluating requirements too. We are the ones who point out the necessary information that is missing and the conflicting business rules because we are so deeply familar with the data and how it is stored and what is is going to be used for.

• Once reporting gets to a certain point, we need to separate it from the transactional database and build a data warehouse so that the people doing complex analysis of the data are not causing the people who are entering data to get blocked. The way to structure data for analysis is not usually the best way to structure data for transactions and thus again we are in the business of transforming the data form one data structure to another that is very different. The abilty to deep dive into the data through analysis of several years worth of data is something that is a huge selling point to our clients. So again, we add value by producing a product our clients need to manage their business.

If your data needs are all internal, you still may have internal clients who need this level of analysis. In this case, you are probably more concerned with the reporting aspect of data warehousing than importing data into a transactional system. But still the abilty to use the data you have been collecting to make management decisions is invaluable to most organizations.

Whether you need a BI specialist tends to revolve around how extensive your data needs are and how complex the system is. A smaller business may not have enough work for a person of this nature and may hire consultants to create the reports they need. BI specialists tend to work only at medium to large businesses.

If you are business that creates COTS software, you probably need BI specialists to be the consultants who know your product inside and out and create customized rpeoting from it for your clients.

Answer 4

While they are not great examples of best practice, the SQL Server sample databases would be good place to start. They include an OLTP, data warehouse and analysis services databases for a fictional organisation. Studying the differences between them should help you make sense of how OLTP (transaction) and OLAP (analytical/BI) databases differ and why.

http://msftdbprodsamples.codeplex.com/

• The AdventureWorks OLTP database supports standard online transaction processing scenarios for a ficticious bicycle manufacturer (Adventure Works Cycles). Scenarios include Manufacturing, Sales, Purchasing, Product Management, Contact Management, and Human Resources.

• The Adventure Works DW database demonstrates how to build a data warehouse.

• The Adventure Works AS project can be used to build an AS database for business intelligence scenarios.