business inteligence - can you give me one example

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 is BI all about and what would a BI person produce that is of value to my organization.

Thank you

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

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.

Answer 3

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.