Dimensional Modeling Techniques
What is Dimensional Modeling?
Strengths of Dimensional Modeling
STAR Schema
- A database design that stores a central fact table surrounded by multiple dimension tables
- Star schema represents a compromise between the fully normalized model and the denormalize
- OLAP Characteristics
- d model
Fact table
- Contains Keys to dimensions, and measures
- Measures are typically described as the performance measures of the business
- Usually numerical, additive and represent counts, currency amounts, percentages or ratios
- Examples of measures are transaction amounts, balance, count of approved and declined transactions
Dimension table
- An entity by which the business views the measures (facts)
- Dimensions are groupings of similar data into a larger category
- Dimensions may be hierarchical in nature, like Time – days into months, months into quarters
What is OLAP?
- Involves drilling down to lower levels
- Involves roll-ups to higher levels of summarization
ETL Tools
TOOLS for Front End Analysis (BI tools)
What is Business Intelligence?
– storing,
– providing access and
– analyzing data
to help enterprise users make better, faster business decisions.
Why Business Intelligence?
– By definition, the moment any given business is operating, it begins generating data. Some obvious examples are banking, sales, production data, etc.
– In addition there also exists large volumes of data which are important to the business but not directly generated by business operations. Examples are market data, competitive data, tenders and proposal etc.
– As such, none of the above described information can be used in its raw form by corporate management to make decisions although the information is critical in helping make those business decisions.
– Therein lies the necessity for Business Intelligence.
– BI technologies help bring decision-makers the Data in a form they can quickly digest and apply to their decision making.
– BI turns Data into Information for people making decisions in a company.
Business Intelligence applications include the activities of:
– Decision support systems,
– Query and reporting,
– Online analytical processing (OLAP),
– Statistical analysis, Forecasting, and
– Data Visualization
BI Benefits
– Make better decisions by turning enterprise data into real information
– Gain a competitive advantage by getting timely, flexible, sophisticated analysis of corporate data
DATA WAREHOUSING ARCHITECTURE
An analysis and reporting system that covers all the areas an organization requires to support its business decisions at an enterprise level.
Reporting Pressures
Relieve reporting pressure on transactional databases.
Restructure Data
Restructure data to speed up data analysis and reporting capabilities.
Reduce Complexity
Reduce the user complexity associated with generating new reports
Clean Data
Create a repository of “clean data” that does not require wholesale changes to the transactional systems or business processes.
Multiple Source Analysis
Allow easier reporting across multiple transactional systems and external data sources.
Historic Analysis
To provide a data source supporting a longer span of time than can be reasonable supported on the transactional systems.
What is Dimensional Modeling?
- Dimensional Modeling is a logical design technique that seeks to present the data in a standard framework that is intuitive and allows for high performance access.
- Predictable, standard framework (facts, dimensions)
- Gracefully extendable
- Standard Approaches to Standard Problems
- Easy management of aggregates
STAR Schema
- A database design that stores a central fact table surrounded by multiple dimension tables
- Star schema represents a compromise between the fully normalized model and the denormalize
- OLAP Characteristics
- d model
Fact table
- Contains Keys to dimensions, and measures
- Measures are typically described as the performance measures of the business
- Usually numerical, additive and represent counts, currency amounts, percentages or ratios
- Examples of measures are transaction amounts, balance, count of approved and declined transactions
Dimension table
- An entity by which the business views the measures (facts)
- Dimensions are groupings of similar data into a larger category
- Dimensions may be hierarchical in nature, like Time – days into months, months into quarters
What is OLAP?
- OLAP is an On-line Analytical processing technology which creates new business information from existing data , through a rich set of business transformations and numerical calculations.
- Involves drilling down to lower levels
- Involves roll-ups to higher levels of summarization
ETL Tools
- Informatica
- Data Integrator
- Data Stage
- MS SQL-Server DTS
- Ab Initio
- Data Junction
TOOLS for Front End Analysis (BI tools)
- SAP BusinessObjects
- Cognos
- Web Focus
- Brio
- Hyperion
- Micro Strategy
What is Business Intelligence?
- Business Intelligence is a broad category of application and technologies for:
– storing,
– providing access and
– analyzing data
Why Business Intelligence?
– By definition, the moment any given business is operating, it begins generating data. Some obvious examples are banking, sales, production data, etc.
– In addition there also exists large volumes of data which are important to the business but not directly generated by business operations. Examples are market data, competitive data, tenders and proposal etc.
– As such, none of the above described information can be used in its raw form by corporate management to make decisions although the information is critical in helping make those business decisions.
– Therein lies the necessity for Business Intelligence.
– BI technologies help bring decision-makers the Data in a form they can quickly digest and apply to their decision making.
– BI turns Data into Information for people making decisions in a company.
Business Intelligence applications include the activities of:
– Decision support systems,
– Query and reporting,
– Online analytical processing (OLAP),
– Statistical analysis, Forecasting, and
– Data Visualization
BI Benefits
– Make better decisions by turning enterprise data into real information
– Gain a competitive advantage by getting timely, flexible, sophisticated analysis of corporate data
DATA WAREHOUSING ARCHITECTURE
An analysis and reporting system that covers all the areas an organization requires to support its business decisions at an enterprise level.
Reporting Pressures
Relieve reporting pressure on transactional databases.
Restructure Data
Restructure data to speed up data analysis and reporting capabilities.
Reduce Complexity
Reduce the user complexity associated with generating new reports
Clean Data
Create a repository of “clean data” that does not require wholesale changes to the transactional systems or business processes.
Multiple Source Analysis
Allow easier reporting across multiple transactional systems and external data sources.
Historic Analysis
To provide a data source supporting a longer span of time than can be reasonable supported on the transactional systems.
1 comments:
it's actually a knowledge full post. thanks to shear . this post has removed my a number of wrong thing . i thing if you to-do your acctivetice you will achive much popularety.. at last..thanks.
Information visualization Low
Post a Comment