SQL SERVER – Introduction to Business Intelligence – Important Terms & Definitions

What is Business Intelligence

SQL SERVER - Introduction to Business Intelligence - Important Terms & Definitions bi-icon Business intelligence (BI) is a broad category of application programs and technologies for gathering, storing, analyzing, and providing access to data from various data sources, thus providing enterprise users with reliable and timely information and analysis for improved decision making. To put it simply, BI is an umbrella term that refers to an assortment of software applications for analyzing an organization’s raw data for intelligent decision making for business success. BI as a discipline includes a number of related activities, including decision support, data mining, online analytical processing (OLAP), querying and reporting, statistical analysis and forecasting.

Business Intelligence for Success

Business intelligence equips enterprises to gain business advantage from data. Once an organization is powered with BI it can anticipate an enhanced turnaround time on data collection, come up with fresh ideas for novel business initiatives, foresee accurate picture of customer needs and demands, and perform more targeted marketing campaigns. In addition, it will gain enhanced knowledge that will help it advance its brand into the top slot in terms of market share, cut down its client churn, reduce its overheads and also diminish delays in supply chain, among other advantages. Decisions purely based on the gut feeling cannot assure success; but in BI’s fact-based decision-making framework, confident decisions can be made for assuring business success. Further, BI makes an organization agile thereby giving it a competitive edge in today’s evolving market condition.

Business Intelligence Scenario

Every day, a dairy company collects from 2,000 stores information which is uploaded to the servers at its headquarters at the end of the day. This information is used by the chain’s main office to instantly analyze key operational measures such as the amount of dairy products sold, profits, trends, and so forth.

Next day, by early morning the company’s managers receive performance data. Next, they verify current revenue, time required to perform each job, and other performance measures. With BI, franchise with multiple locations can have consolidated views, as can the company’s regional managers.

This scenario clearly explains how implementation of Business intelligence can be very fruitful for an organization. BI can catalyze a business’s success in terms of

Revenues

  • Distinguish the products and services that drive revenues.
  • Rank customers and locations based on profitability.

Customer relationship management

  • Categorize low-value customers and work toward improving their value.
  • Find customer relationship issues early and take timely action to resolve them.

Sales and marketing

  • Aim at high-value customers to minimize market risk.
  • Rank the success of product promotions based on product and market segment.
  • Find what is in the sales pipeline

Business Intelligence Terms

Ad Hoc Query – Ability to create an immediate ‘request for information’ using an analytical reporting tool to answer a specific business-related query.

Aggregation – Predefined summaries based on comprehensive data in the data warehouse.

Atomic data – Topmost level of detail in the data warehouse.

Attribute – Field represented by a column in reports, tables and charts.

Cube – A powerful analysis tool that is used for viewing and analyzing data in a multi-dimensional format, from any angle, in any combination, through a ‘slice and dice’ and drill down approach.

Data – Factual information for analysis.

Data Mart – A logical subset of related information, typically around one or a few business processes, or a particular subject area.

Data Warehouse – A repository of well-organized corporate data for Business Analysis and Reporting. It is also a collection of data marts.

Database – A collection of data arranged for a convenient and quick search and retrieval.

OLAP – Online Analytical Processing is a category of database software that provides an interface to help users quickly and interactively scrutinize the results in a variety of dimensions of the data.

Balanced Scorecard – A Performance management tool that recapitulates an organization’s performance from several standpoints on a single page.

Dashboard – A reporting tool that presents key indicators on a single screen, which includes measurements, metrics, and scorecards.

Data Cube – A multidimensional structure (typically over three dimensions) that forms the basis for analysis applications, allowing various types of calculations and aggregations.

Data Mapping – Process of spotting a source data element for all data elements in the target data warehouse environment.

Data Quality – It is related to facets such as accessibility, comprehensiveness, exactness, steadiness, significance and timeliness of data.

Data staging – A system where operations like data extraction, transformation and loading operations are executed.

Dimension Table – It contains attributes that describe fact records in the fact table. For instance, pupil enrollment is a central fact table with several dimensions linked to it such as Faculty, Term, and Degree.

Drill Down – A feature that allows users to click on an item to navigate through the hierarchy and move to a level of better detail.

EIS – Enterprise Information System – A category of applications and technologies for presenting and analyzing corporate and external data for various management purposes.

ETL – Extract, Transform and Load -It is a three-step process at the center of data warehousing. In the first step, operational data are extracted from multiple sources. In the next step, it is transformed into the required type. In the final step, data is loaded into the data warehouse.

Forecasting – It is the formulation of trends, predictive models, and scenarios to prepare for the future for improved decision making.

Gap analysis – Study of whether the available business data support business requirements, thus answering questions related to data accessibility, amount of data, missing data, and legacy systems. It examines information and decides on the resources and efforts required to satisfy requirements.

Granularity – Level of detail or summary of data in the data warehouse. The more the detail, the higher the level of granularity.

Institutional performance management – The process of basing an organization’s actions and decisions on actual measured results of performance. It incorporates performance measures, benchmarks, and goals to attain best results.

Measures – A quantifiable, specific piece of information.

Metrics – Measures of performance that observe progress and evaluate trends within an organization.

Normalization – Standard database design technique for relational databases.

Slice and Dice – Multidimensional tools allow users to view data from any angle. The ability to select various angles to view data from is called slice and dice capability.

Snapshot – View of data at a particular moment.

Reference: Pinal Dave (https://blog.sqlauthority.com)

Business Intelligence, Data Warehousing, SQL Server
Previous Post
SQLAuthority News – PASS 2009 Sessions on Query Optimization and Performance Tuning
Next Post
SQL SERVER – Difference Between Candidate Keys and Primary Key In Simple Words

Related Posts

7 Comments. Leave new

  • Hi Pinal,

    I think you should book domain name “BIAuthority.com”. :) glad to read introductory article on BI, hope to have some more on this topic.

    Thanks,

    Ritesh Shah

    Reply
  • Hi Pinal,
    One of the next articles in this series would be to give an intro to the methodoligies of Kimball and Inmon who are considered as the fathers of data warehousing.

    Reply
  • Fernando Labastida
    October 29, 2009 7:16 pm

    Pinal,

    I really like the article – short, succinct, and it gets to the main business point of BI, instead of using industry jargon. That’s what has gotten lost lately in all the talk of new types of BI technologies and different types of dashboards, analytics, SaaS vs. on-premise, etc.

    BI exists for a reason: to help you run your business more efficiently and profitably, and to grow your business without going broke.

    Well done Pinal!

    Fernando A. Labastida
    KPIOnline

    Reply
  • @Pinal, Clear concise semantics are indeed the first
    step in demystifying BI so thank you for providing that! (small note: add spreadmart in your dictionary )

    @Fernando – I agree with your “gotten lost” and jargon remark. I think the reason is the high number of pain points in the industry (driving numerous different approaches and ensuing panic to address/consume them) and the fact that KISS is often lost on folks in this business :) – It’s no different in the software development industry.

    I might also suggest a very clear/no-nonsense book for DW beginners called Data Warehousing for Dummies (yes I’m serious) written by industry veteran Tom Hammergren – I have no business connection to Tom. He’s just a good buddy of mine and a source of BI insight I’ve always tapped when I wanted to get “the real deal”.

    Great blog.
    J.

    Reply
  • Dinesh Agrawal
    November 6, 2009 2:00 pm

    pinal sir
    i am fresher and i am having command on sql server and pl-sql know the some basic concepts of dts (sql server2000)
    .i want to become a buisness intelligence devloper please guide me for this pathway.

    Reply
  • Muhammad Khan
    May 22, 2013 9:56 pm

    Great Article

    Reply
  • This soo good and interesting

    Reply

Leave a Reply