Business intelligence multi dimensional databases
Download
1 / 54

Business Intelligence & Multi-Dimensional Databases - PowerPoint PPT Presentation


  • 124 Views
  • Uploaded on

Business Intelligence & Multi-Dimensional Databases. Nirmal Jonnalagedda. Outline. BI: History BI: Overview Common Functions of BI BI: What can you do with it? Multidimensional Databases Contrast MDD and Relational Databases When is MDD (In)appropriate? MDD Features Pros/Cons of MDD.

loader
I am the owner, or an agent authorized to act on behalf of the owner, of the copyrighted work described.
capcha
Download Presentation

PowerPoint Slideshow about ' Business Intelligence & Multi-Dimensional Databases ' - loren


An Image/Link below is provided (as is) to download presentation

Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author.While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server.


- - - - - - - - - - - - - - - - - - - - - - - - - - E N D - - - - - - - - - - - - - - - - - - - - - - - - - -
Presentation Transcript
Business intelligence multi dimensional databases

Business Intelligence & Multi-Dimensional Databases

Nirmal Jonnalagedda


Outline
Outline

  • BI: History

  • BI: Overview

  • Common Functions of BI

  • BI: What can you do with it?

  • Multidimensional Databases

  • Contrast MDD and Relational Databases

  • When is MDD (In)appropriate?

  • MDD Features

  • Pros/Cons of MDD


Bi history
BI: History

  • 1958 - Term first used by IBM researcher Hans Peter Luhn

    • He defined intelligence as: “the ability to apprehend the interrelationships of presented facts in such a way as to guide action towards a desired goal”

  • BI is understood to have evolved decision support systems (DSS) in the 1960’s

  • In the 80’s DSS concepts evolved and split

    • data warehouses, Executive Information Systems, OLAP


Bi an overview
BI : an overview

  • There are many different opinions

    • Depends on where you work

    • Generally BA is a subset of BI

  • BI - Ability for an organization to take its capabilities and convert these things into knowledge

  • Often includes the implementation of

    • Key Performance Indicators (KPIs), Trending Analysis, Predictive Modeling


  • What does BI provide

    • historical, current and predictive views of business operations

  • Where does BI get this information

    • From within your business

    • Not necessarily focused on the actions of others

      • Called competitive analysis

  • End Goal : Support better decision making

    • BI is sometimes called a decision support system (DSS)


  • BI applications can often vary in scope

    • Can be enterprise wide, focusing on critical business applications

      • Monitoring the popularity of a product in a nationwide grocery chain

      • Tracking responses to mail offers and only mailing those who respond

    • Can be department or project specific, focused on individual decisions and how those affect an organization

      • Monitoring employee productivity and department spending


Common functions of bi
Common Functions of BI

  • Reporting

  • Online Analytical Processing

  • Analytics

  • Data, Process, Text Mining

  • Complex Event Processing

  • Business Performance Management

  • Benchmarking

  • Predictive, Prescriptive Analytics


Bi what can you do with it
BI : What can you do with it?

  • Identify cost cutting ideas and practices

  • Uncover new business opportunities

  • React and even predict retail demand

  • Avoid repeating costly mistakes

    • Especially useful in large enterprises with many departments

  • Easily correlate and group business information and metrics into an understandable format

  • Understand customer behavior


Database evolution
Database Evolution

  • Flat files

  • Hierarchical and Network

  • Relational

  • Distributed Relational

  • Multidimensional


Mddb why
MDDB: Why?

  • No single "best" data structure for all applications within an enterprise

  • Organizations have abandoned the search for the holy grail of globally accepted database

  • Instead selecting the most appropriate data structure on a case-by-case basis from a palette of standard database structures

  • Multidimensional Databases for OLAP?


  • From econometric research conducted at MIT in the 1960s, the multidimensional database has matured into the database engine of choice for data analysis applications

  • Inherent ability to integrate and analyze large volumes of enterprise data

  • Offers a good conceptual fit with the way end-users visualize business data

  • Most business people already think about their businesses in multidimensional terms

  • Managers tend to ask questions about product sales in different markets over specific time periods


  • Spreadsheets – A 2D database? multidimensional database has matured into the database engine of choice for data analysis applications

  • Functionalities

  • What about a stack of similar spreadsheets for different times?

  • Limitations?

  • We can not relate data in different sheets easily


What is a multi dimensional database
What is a Multi-Dimensional Database? multidimensional database has matured into the database engine of choice for data analysis applications

A multidimensional database (MDDB) is a computer software system designed to allow for the efficient and convenient storage and retrieval of large volumes of data that are

  • intimately related and

  • stored, viewed and analyzed from different perspectives.

    These perspectives are called dimensions.


A motivating example
A Motivating Example multidimensional database has matured into the database engine of choice for data analysis applications

An automobile manufacturer wants to increase sale volumes by examining sales data collected throughout the organization. The evaluation would require viewing historical sales volume figures from multiple dimensions such as

  • Sales volume by model

  • Sales volume by color

  • Sales volume by dealer

  • Sales volume over time


Contrasting relational and multi dimensional models
Contrasting Relational and Multi-Dimensional Models multidimensional database has matured into the database engine of choice for data analysis applications

The Relational Structure


Multidimensional Structure multidimensional database has matured into the database engine of choice for data analysis applications

Measurement

Dimension

Dimension

Positions


Differences between mddb and relational databases
Differences between MDDB and Relational Databases multidimensional database has matured into the database engine of choice for data analysis applications


Contrasting relational model and mdd example 2
Contrasting Relational Model multidimensional database has matured into the database engine of choice for data analysis applicationsand MDD-Example 2


Mutlidimensional representation
Mutlidimensional Representation multidimensional database has matured into the database engine of choice for data analysis applications


Viewing data an example
Viewing Data - An Example multidimensional database has matured into the database engine of choice for data analysis applications

  • Assume that each dimension has 10 positions, as shown in the cube above

  • How many records would be there in a relational table?

  • Implications for viewing data from an end-user standpoint?


Performance advantages
Performance Advantages multidimensional database has matured into the database engine of choice for data analysis applications

Volume figure when car type = SEDAN, color=BLUE, & dealer=GLEASON?

  • RDBMS – all 1000 records might need to be searched to find the right record

  • MDB has more ‘knowledge’ about where the data lies

  • Maximum of 30 position searches

  • Average case

  • 15 vs. 500


Total Sales across all colors and dealers when model = SEDAN?

  • RDBMS – all 1000 records must be searched to get the answer

  • MDB – Sum the contents of one 10x10 ‘slice’


  • Data manipulation that requires a minute in RDBMS may require only a few seconds in MDB

  • MDBs are an order of magnitude faster than RDBMSs

  • Performance benefits are more for queries that generate cross-tab views of data

  • The performance advantages offered by multidimensional technology facilitates the development of interactive decision support applications like OLAP that can be impractical in a relational environment.


Real world benefits
Real World Benefits require only a few seconds in MDB

  • Ease of data presentation and navigation

  • Ease of maintenance

  • Performance


Ease of data presentation and navigation
Ease of Data Presentation and Navigation require only a few seconds in MDB

  • Intuitive spreadsheet like data views are natural output of MDDBs

  • Obtaining the same views in a relational environment, requires either a complex SQL or a SQL generator against a RDB to convert the table outputs into a more intuitive format

  • Even for end users well skilled in SQL, some forms of output, such as ranking reports (i.e. top ten, bottom 20%), simply cannot be performed with SQL at all!


Ease of maintenance
Ease of Maintenance require only a few seconds in MDB

  • Ease of maintenance because data is stored as it is viewed

  • No additional overhead is required to translate user queries into requests for data

  • To provide same intuitiveness, RDBs use indexes and sophisticated joins which require significant maintenance and storage


Performance
Performance require only a few seconds in MDB

  • Multidimensional databases achieve performance levels that are difficult to match in a relational environment.

  • These high performance levels enable and encourage OLAP applications

  • Performance of MDBs can be matched by RDBs through database tuning

  • Not possible to tune the database for all possible adhoc queries

  • Tuning requires resources of an expensive DB specialist


Adding dimensions an example
Adding Dimensions- An Example require only a few seconds in MDB


When is mdd in appropriate
When is MDD (In)appropriate? require only a few seconds in MDB

First, consider situation 1


When is mdd in appropriate1
When is MDD (In)appropriate? require only a few seconds in MDB

Now consider situation 2

1. Set up a MDD structure for situation 1, with LAST NAME

and Employee# as dimensions, and AGE as the measurement.

2. Set up a MDD structure for situation 2, with MODEL and

COLOR as dimensions, and SALES VOLUME as the measurement.


When is mdd in appropriate2
When is MDD (In)appropriate? require only a few seconds in MDB

MDD Structures for the Situations

Note the sparseness in the second MDD representation


When is mdd in appropriate3
When is MDD (In)appropriate? require only a few seconds in MDB

  • Our sales volume dataset has a great number of meaningful interrelationships

  • Interrelationships more meaningful than individual data elements themselves.

  • The greater the number of inherent interrelationships between the elements of a dataset, the more likely it is that a study of those interrelationships will yield business information of value to the company.

  • Highly interrelated dataset types be placed in a multidimensional data structure for greatest ease of access and analysis


When is mdd in appropriate4
When is MDD (In)appropriate? require only a few seconds in MDB

  • No last name is matching with more than one emp # and no emp # is matching with more than one last name

  • In contrast, there is a sales figure associated with every combination of model and color resulting in a completed filled up 3x3 matrix

  • Performance suffers (RDB 9 vs. MDB 18)


When is mdd in appropriate5
When is MDD (In)appropriate? require only a few seconds in MDB

  • The relative performance advantages of storing multidimensional data in a multidimensional array increase as the size of the dataset increases

  • The relative performance disadvantages of storing non-multidimensional data in a multidimensional array increase as the size of the dataset increases.

  • NO inherent value of storing Non-multidimensional data (employee data) in multidimensional arrays


When is mdd in appropriate6
When is MDD (In)appropriate? require only a few seconds in MDB

  • The relative performance advantages of storing multidimensional data in a multidimensional array increase as the size of the dataset increases

  • The relative performance disadvantages of storing non-multidimensional data in a multidimensional array increase as the size of the dataset increases.

  • NO inherent value of storing Non-multidimensional data (employee data) in multidimensional arrays


When is mdd appropriate
When is MDD Appropriate? require only a few seconds in MDB

The greater the number of inherent interrelationships between the elements of a dataset, the more likely it is that a study of those interrelationships will yield business information of value to the company.

  • Most companies have limited time and resources to devote to analyzing data

  • It therefore becomes critical that these highly interrelated dataset types be placed in a multidimensional data structure for greatest ease of access and analysis.


When is mdd appropriate1
When is MDD Appropriate? require only a few seconds in MDB

Examples of applications that are suited for multidimensional technology:

  • Financial Analysis and Reporting

  • Budgeting

  • Promotion Tracking

  • Quality Assurance and Quality Control

  • Product Profitability

  • Survey Analysis


Mdd features rotation
MDD Features - require only a few seconds in MDBRotation

  • Also referred to as “data slicing.”

  • Each rotation yields a different slice or two dimensional table

  • of data – a different face of the cube.


Mdd features rotation1
MDD Features - require only a few seconds in MDBRotation


Mdd features rotation2
MDD Features - require only a few seconds in MDBRotation

  • All the six views can be obtained by simple rotation

  • In MDBs rotations are simple as no rearrangement of data is required

  • Rotation is also referred to as “data slicing”


Mdd features ranging
MDD Features - require only a few seconds in MDBRanging

  • How sales volume of models painted with new metallic blue compared with the sales of normal blue color models?

  • The user knows that only Sports Coupe and Mini Van models have received the new paint treatment

  • Also the user knows that only 2 dealers viz, Carr and Clyde have unconstrained supply of these models


Mdd features ranging1
MDD Features - require only a few seconds in MDBRanging

  • The end user selects the desired positions along each dimension.

  • Also referred to as "data dicing."

  • The data is scoped down to a subset grouping


Mdd features ranging2
MDD Features - require only a few seconds in MDBRanging

  • The reduced array can now be rotated and used in computations in the same was as the parent array

  • Referred to as “Data Dicing” as data is scoped down to a subset grouping

  • Complex SQL query is required in RDB

  • Performance is better in MDB as less resource consuming searches are required


Mdd features roll ups drill downs
MDD Features - require only a few seconds in MDBRoll-Ups & Drill Downs

  • Users want different views of the same data

  • For eg., Sales Volume by model vs sales volume by dealership

  • Many times views are similar

  • Sales volume by dealership vs. volume by district

  • Natural relationship between Sales Volumes at the DEALERSHIP level and Sales Volumes at the DISTRICT level

  • Sales Volumes for all the dealerships in a district sum to the Sales Volumes for that district


Mdd features roll ups drill downs1
MDD Features - require only a few seconds in MDBRoll-Ups & Drill Downs

  • Multidimensional database technology is specially designed to facilitate the handling of natural relationships

  • Define two related aggregates on the same dimension

  • One aggregation is dealership and the other district

  • District is at a higher level of aggregation than dealership


Mdd features roll ups drill downs2
MDD Features - require only a few seconds in MDBRoll-Ups & Drill Downs

  • The figure presents a definition of a hierarchy within the

    • organization dimension.

  • Aggregations perceived as being part of the same dimension.

  • Moving up and moving down levels in a hierarchy is referred to

  • as “roll-up” and “drill-down.”


Mdd features roll ups drill downs3
MDD Features - require only a few seconds in MDBRoll-Ups & Drill Downs


Queries
Queries require only a few seconds in MDB

  • High degree of structure in MDB makes the query language very simple and efficient

  • Query language is intuitive

  • Output is immediately useful to end user


Queries example
Queries: Example require only a few seconds in MDB

  • Display sales volume by model for each dealership

    PRINT TOTAL.(SALES_VOLUME KEEP MODEL DEALERSHIP)


Queries example1
Queries: Example require only a few seconds in MDB

  • Corresponding SQL

    SELECT MODEL, DEALERSHIP, SUM(SALES_VOLUME)

    FROM SALES_VOLUME

    GROUP BY MODEL, DEALERSHIP

    ORDER BY MODEL, DEALERSHIP


Queries example2
Queries: Example require only a few seconds in MDB


Pros cons of mdd
Pros/Cons of MDD require only a few seconds in MDB

  • Cognitive Advantages for the User

  • Ease of Data Presentation and Navigation, Time dimension

  • Performance

  • Less flexible

  • Requires greater initial effort


? require only a few seconds in MDB


ad