Description
data warehouse, data in data warehouse, architecture of datawarehouse, data warehouse design, process flow of data warehouse model, organization and management of data warehouse and OLAP tools.
DATA WAREHOUSE
Architecture to Implementation
INTRODUCTION
1) Introduction 2) Characteristics of Data Warehouse 3) Data in Data Warehouse 4) Metadata 5) Architecture of Data Warehouse a) Conceptual Data Architecture b) Logical Data Architecture 6) Data Warehouse Design 7) Process Flow of Data Warehouse Model 8) Justifying the Data Warehouse
9) Organisation & Management of Data Warehouse
10) Implementation of Data Warehouse 11) Business Intelligence
Introduction
?
Different classes of end users now need organisation-wide information - need of Data Warehouse (DW)
?
The decision makers are required to react quickly to mission critical needs due to rapidly changing, volatile and competitive markets.
They need multidimensional support of information. The decision makers now need information for strategic decisions and not for routine operational decisions, which are automated now. The character of their needs have changed from data to information and now to knowledge. The decision maker is a specialist and needs information urgently from internal & external database which gives larger view of the problem.
?
?
?
?
?
There are three kinds of end users of information,
a) management, b) knowledge workers and c) operation staff.
?
Mgmt needs holistic view of a situation - expected predicting in the future. It helps to know whether a critical changes has taken place in the business, is the change showing any pattern and which factors are affecting the change and its pattern? In order to control the change and use it to business advantage, the management requires analytical information support to make strategic decisions.
?
?
?
Information need of Knowledge Worker cuts across the application systems in different functions.
presenting in a manner which offers additional value of information to the decision maker.
?
?
Data Warehouse defined as,
“ A collection of non-volatile data of different business subjects and objects, which is time variant and integrated down various sources and applications and stored in a manner to make a quick analysis of business situation.” -----Bill Inmon
?
The Concept of DW is as shown below
TPS – transaction
Processing System, AS – Application system, DBS-Database system, DCSData Conversion System)
AS
DBS
TPS
AS
DCS
AS
Ext-DBS
?
DW is a special containing large stock of enterprise data and related meta data processed to a ‘ready to use’ stage for decision maker for operational and analytical business analysis. First step in building DW is to extract data from different sources. After this the data needs to be validated for coding structures, names and formats. It is rationalized to a common unit to measure through transformation or conversion process. Such data is then consolidated to common reference level such as end of month, region, zone, etc. The data so processed is then moved to DW. All these processes are handled by middleware, written to construct the DW. Middleware is a set of programs and routines which pulls data from various sources, checks and validate, moves it from one platform to another and transforms as per design specification and then loads in the DW.
?
?
?
?
?
Operational architecture:
Legacy DB
Operational DB
Middle ware
Data Warehouse System
External DB
Characteristics of Data Warehouse:
?
The scope of Data Warehouse is the whole organization. Historical record of business created from existing application. It enables you to take business view, application view and physical view at a point-intime on any aspects of business situation.
?
?
?
Data warehouse supports cross functional Decision Support System (DSS) to manage the business, as it provides detail, historical, consistent, normalized business data for further manipulation by the decision makers.
DATA IN DATA WAREHOUSE
?
In a computerized environment variety of data is available from various systems in the organization. But all data does not enter into DW. Some enters and some does not. The data in DW is termed as business data, because it has a business value. The business data, representing the state of the business and only its value enters into the Data Warehouse.
?
?
?
?
The business data assumes value because it represents facts and figures about the business data, its metadata is associated to describe the meaning of the business data.
The Data Warehouse designer has to decide before hand which data is a business data and which is not. The business data may fall outside the normal data and information need of knowledge workers. Eg:In airlines ticketing application, passenger data assumes importance to promote airlines business.
?
?
?
?
Business data assumes importance when it is useful to manage the business. The data, which is necessary to manage the business, has a value from strategic point of view. The rest of the data is useful to run the business. All business data are candidates for the Data Warehouse. It is also true that the business data of one organization would turn out to be routine information in case of the other organization. For e.g.: customer data has no business value in manufacturing organization, but in post-sales service organization customer data has a business value. The qualification for business data depends on the business, its current status and business strategy need at that point in time.
?
?
?
?
? ?
Business data could be stored in data warehouse in atomic form or in summary.
The decision depends on its utility and application to manage the business. Other important decision about business data is to decide its currency level in terms of time. The three currency features for the business data are, current data, point-in-time data and periodic data. Current data is a view of the business at the present time. It is unto a second and changes with respect to time. Point-in-time data is a snap shot of business data at a particular moment in time indicating the business status at that point of time.
?
?
?
?
Periodic data is a representation of business data by periods such as last three years, last twelve quarters, etc. Broadly, current and point-in-time data are not candidates for Data Warehouse but the extracted data and analysis of this date are the candidates for the Data Warehouse. Business data entering in Data Warehouse is often a derived data. The derived data is taken from the data set generated at point-in-time or data processed periodically. The derived data may be aggregated at some level through summarization process. The aggregation could be for all levels or for selective levels. The derived data also would be put in data warehouse after enrichment.
?
?
?
?
?
Metadata
?
Various data sources could be options for taking the business data to data warehouse. Further each source may have application specific data definition and its use requiring special derivation and reconciliation process.
It is therefore, necessary to know the data model, data definition, data structure and data usage precisely in each case. This data about the business data is called as Metadata.
?
?
?
In other words metadata is a ‘data about data’.
In any information system, three types of metadata are created, namely ? Time Metadata-System Design ? Control Metadata ? Usage Metadata
?
Time Metadata-System Design:
?
While designing a system, a data which describes the use of input data in the application is known as Time Metadata.
?
This metadata gives details on specification, value, allowed value, the business rules it would use to validate and formula to compute and so on.
Control Metadata:
? ?
The control Metadata is used by the system to produce Data Warehouse.
The data is used to manage and control the process of Data Warehouse creation.
Usage Metadata
? ?
The users of Data Warehouse require this. The metadata is sourced from Data Dictionary
ARCHITECTURE OF DATA WAREHOUSE
Conceptual Data Architecture:
?
Three-layer architecture is recommended for efficient Data Warehouse creation.
It is useful and supportive to meet the needs of IS department to maintain the Data Warehouse and also to meet the data access needs of end users of Data Warehouse. In three-layer architecture, layer-I builds real time data, using Transaction Processing Systems(TPS),Application Processing Systems (APS) and Report Generation Systems (RGS).
?
?
?
This data buildup is done through different systems working in the organizations on different platforms and designed to meet specific functional of information of the end users.
At layer-I, data is created which is of business interest and has a strategic use through Data Warehouse.
?
?
Such data then needs to be put to scrutiny from the angle of content, scope, definition, intended unselect. Since data are coming from different systems operating within and outside organization, it is necessary to reconcile the data in number of ways to make it eligible for Data Warehouse processing. Some data would need reconciliation on time lines. Some data may need some manipulation as definition and scope is inconsistent to the business data intended to be in DW. The reconciling step takes data from multiple, heterogeneous,distributed systems, and combines and enhances it into a single logical image of business data as defined into Enterprise data model.
?
?
?
?
Another important factor, which is handled in reconciliation step, is rationalizing the data from various sources on time scale.
?
After reconciled data is available in layer II, it is then used to derive business data for use of various end users and decision makers. The derived data from the reconciled data is not based on random selection, but is derived based on the most commonly asked queries in the business. The commonly asked queries may be from individuals, departments, group of users. Most of MIS reports required by these users can easily be generated using data in derived layer. Such Data warehouse is very handy and very quick in response in producing the MIS reports. The reason for quick response is most of the processing for searching, accessing, matching, computing and reconciling is already done while creating the reconciled layer.
?
?
?
?
?
Logical Data Architecture:
?
The real time, reconciled and derived data layers are conceptual and each of these layers have physical counterparts.
Data warehouse is the physical realization of the real time data into a derived data. The scope of the Data warehouse includes reconciled data and derived data. The derived data is a business information while reconciled data is a business data. Business information is higher in value compared to reconciled data is ready to use for certain application requirement. Data warehouse is implemented as RDBMS. It can reside on a single server or can be distributed at a different locations.
?
?
?
?
?
?
Data warehouse management will be central through DW master or DBA. Since, Data warehouse contains business data and information which is strategically important, it needs to be secured from unwanted unauthorized exposures. When it comes to use of data from external sources in data warehouse along with data sourced from internal operational systems, the principles applied to internal data to conform its eligibility to be in Data warehouse are also applied to data from external sources. The only difference between the two is, internal data indicates business performance within the organization and external data indicates performance and prospects in the market place. The information support for strategic decision making calls for inclusion of data from both the sources.
?
?
?
DATA WAREHOUSE DESIGN
?
DW design process brings with the construction of Enterprise Data Model. The Enterprise Data Model is build keeping in mind its application to build DW. The objective of this step is to obtain high level unified view of data required for strategic decisions. Enterprise data model may first begin with the help of a generic model of the business. It also takes into consideration a generic data model, if its exists and customize it to enterprise requirement. A detailed process of building the design is explained in the following points:
?
?
?
?
?
Build Enterprise data model which specifies the need of data in terms of form, content, period and its application to manage the business.
Locate operational systems which are more appropriate from where data can be sourced to build Enterprise data model. Identify critical applications, which are necessary to manage the business. Select low-level data entities which when processed, build the critical view of an application or function. Identify related metadata which describes the data used in critical view of the function or business. Map data and metadata to application from where they would be sourced. Get into the process of building reconciled data for subsequent generation of derived data.
?
?
?
?
? ?
?
Determine most sought queries from the organization and process them to produce the results known as derived data which would show instantaneous status of business based on preconceived view of the business. Then clearly define the reconciled data and derived business data for storing in the warehouse i.e. by using RDBMS. The design techniques used DW creations are essentially three, enterprise data modeling, reconciling on time scale, and data replication. Most important after data modeling is to decide on data capture from various sources. The process of putting data in warehouse at appropriate place is executed through a separate application.
?
?
? ?
?
Process Flow of Data Warehouse Model:
OS Select Data entities Reconcile Metadata
Examine Physical Data Design
Map Data to appln Consider all Appln Views Extract Metadata Select Critical views to manage the business
Determine critical queries DW Classify Business data
Derive ready-to-use Data
JUSTIFYING THE DATA WAREHOUSE
?
The traditional justification approach for any investment is to prove that Return on investment is attractive. Essentially, it revolves around savings and benefits, which would be realized from such investment. DW therefore improves the productivity of end users. Apart from increase in productivity of end users, the Data warehouse, more correct and focused business view is possible bringing along knowledge of changed business environment. The DW normally is justified in competitive business environment. In general the key to justify the warehouse lies in specific business advantage arising out of strategic analysis of a business situation & evolving competitive strategies to have edge over the competition through offer of better quality service and level of satisfaction.
?
?
?
?
ORGANIZATION & MANAGEMENT OF DATA WAREHOUSE
?
The purpose and the need of DW is to help manage the business. It is essentially developed for senior managers who continuously seek varied information cutting across the organization this being the case, apart from IS and IT specialist, the business analyst is required to be part of a special team formed for development of DW. The team members and their roles are as given:
?
?
Project Manager
?
PM is responsible for design and development of DW. The person should be from the business management group performing at senior level and should understand the business of today and that of tomorrow. DW architect is a technical person on the team who has good understanding of RDBMS, ORDBMS, Enterprise DM and knowledge of complex application development tools.
DW Architect
?
Business analyst
?
Business analyst plays the role to provide the inputs for business viewing and its interpretation, qualify the business data entering in Data warehouse and throw light on its futuristic use by different end users.
DW developers
?
DW developers are in team to design processes to take up data from various sources and make it ready as business data to be in the warehouse. The team of DW architect, business analyst, DW developers will be led by a steering committee consisting of Business managers responsible for conducting the business in critical areas of business. They would be guiding the architect and business analyst. They will also ensure that required business data in the warehouse is available from operations systems and business applications.
?
?
?
IMPLEMENTATION OF DATA WAREHOUSE
?
Through sufficient tools available for each process involved in the design and development of Warehouse, the subject is complex and requires participation of senior management and IS personnel. In practice DW conceptual model could be for the enterprise as a whole, however it needs to be developed in stages to ensure its success and business benefits. The steps involved in stages implementation are as follows : 1) Establish infrastructure namely DBMS, extraction, replication tools and report writers.
?
?
2) Model that enterprise data from logical structure to physical structure.
3) Prioritize the business data need and segment the enterprise data model matching to this need 4) Model the business data at both logical and physical levels
5) Design, Develop and Implement security aspects.
BUSINESS INTELLIGENCE
?
Business Intelligence is all about converting a large amount of corporate data through processing and analysis into useful information and knowledge thereby triggering some profitable proactive business action or decision. Business intelligence environment is made up of business models, data models, extraction, transformation and loading tools needed to transform and organize the data into useful information and knowledge for storage and further analysis. To set up BI environment, skilled people are required who can understand business intelligence requirement at a point of time and know the right source of data and application from where the data and information will be extracted to build BI.
?
?
Business Model
Data Model
RDBS
User ETL tools
DW
BI Environment
Data Marts
Knowledge Base
Data Cubes
OLAP
BI Database
?
Business Model: It explains business process, flow and connections between processes and data models used by them. Data Model: Data model is all about representing the data and relations between them with specification. Data model explains data structure. Conceptual data model give a view of the overall data scope and model with no details. Logical model gives more details like entities, attributes and their relationship.
?
?
?
?
Physical data model gives representation to logical data model of entities in tables, columns, rows, properties and relationship among them.
Relational data model puts physical model showing entity-relationship in a DB satisfying Codd’s rules of data representation in DB.
?
?
The process leading to BI begins with application of ETL tools, which extracts data, cleanses data, and organizes it in data model after checking for validity and referential integrity for storing in DW. The next step is to build data marts. Data marts is a selection of data warehouse on subjects like sales, production, and other for the functional or subject analysis. Data warehouse and data marts are used further to view data in different dimensions, in the three dimensions (cube) to throw light on subject’s performance and behavior. Once data warehouse, data mart, knowledge base, data cubes are built, queries are built to see the contents of these bases, which is then termed as business intelligence. The different views are obtained through query and OLAP tools.
?
?
?
?
OLAP Tools:
?
OLAP, an acronym for ‘Online Analytical Processing’, is a technique by which the data sources from data warehouses, data marts is visualized and summarized to provide multi dimensional view of the subject. OLAP provides information based intelligence. Business intelligence based on knowledge causes from data mining processes, which are similar to OLAP, but they unearth the knowledge through finding patterns, trends, and behavior of the subject providing an action. Besides OLAP kind of analysis, data mining uses techniques like memory based reasoning, link analysis, neural networks and so on. OLAP records a view or problem; data mining helps to find the solution of the problem.
?
?
?
?
doc_745666011.pptx
data warehouse, data in data warehouse, architecture of datawarehouse, data warehouse design, process flow of data warehouse model, organization and management of data warehouse and OLAP tools.
DATA WAREHOUSE
Architecture to Implementation
INTRODUCTION
1) Introduction 2) Characteristics of Data Warehouse 3) Data in Data Warehouse 4) Metadata 5) Architecture of Data Warehouse a) Conceptual Data Architecture b) Logical Data Architecture 6) Data Warehouse Design 7) Process Flow of Data Warehouse Model 8) Justifying the Data Warehouse
9) Organisation & Management of Data Warehouse
10) Implementation of Data Warehouse 11) Business Intelligence
Introduction
?
Different classes of end users now need organisation-wide information - need of Data Warehouse (DW)
?
The decision makers are required to react quickly to mission critical needs due to rapidly changing, volatile and competitive markets.
They need multidimensional support of information. The decision makers now need information for strategic decisions and not for routine operational decisions, which are automated now. The character of their needs have changed from data to information and now to knowledge. The decision maker is a specialist and needs information urgently from internal & external database which gives larger view of the problem.
?
?
?
?
?
There are three kinds of end users of information,
a) management, b) knowledge workers and c) operation staff.
?
Mgmt needs holistic view of a situation - expected predicting in the future. It helps to know whether a critical changes has taken place in the business, is the change showing any pattern and which factors are affecting the change and its pattern? In order to control the change and use it to business advantage, the management requires analytical information support to make strategic decisions.
?
?
?
Information need of Knowledge Worker cuts across the application systems in different functions.
presenting in a manner which offers additional value of information to the decision maker.
?
?
Data Warehouse defined as,
“ A collection of non-volatile data of different business subjects and objects, which is time variant and integrated down various sources and applications and stored in a manner to make a quick analysis of business situation.” -----Bill Inmon
?
The Concept of DW is as shown below
TPS – transactionProcessing System, AS – Application system, DBS-Database system, DCSData Conversion System)
AS
DBS
TPS
AS
DCS
AS
Ext-DBS
?
DW is a special containing large stock of enterprise data and related meta data processed to a ‘ready to use’ stage for decision maker for operational and analytical business analysis. First step in building DW is to extract data from different sources. After this the data needs to be validated for coding structures, names and formats. It is rationalized to a common unit to measure through transformation or conversion process. Such data is then consolidated to common reference level such as end of month, region, zone, etc. The data so processed is then moved to DW. All these processes are handled by middleware, written to construct the DW. Middleware is a set of programs and routines which pulls data from various sources, checks and validate, moves it from one platform to another and transforms as per design specification and then loads in the DW.
?
?
?
?
?
Operational architecture:
Legacy DB
Operational DB
Middle ware
Data Warehouse System
External DB
Characteristics of Data Warehouse:
?
The scope of Data Warehouse is the whole organization. Historical record of business created from existing application. It enables you to take business view, application view and physical view at a point-intime on any aspects of business situation.
?
?
?
Data warehouse supports cross functional Decision Support System (DSS) to manage the business, as it provides detail, historical, consistent, normalized business data for further manipulation by the decision makers.
DATA IN DATA WAREHOUSE
?
In a computerized environment variety of data is available from various systems in the organization. But all data does not enter into DW. Some enters and some does not. The data in DW is termed as business data, because it has a business value. The business data, representing the state of the business and only its value enters into the Data Warehouse.
?
?
?
?
The business data assumes value because it represents facts and figures about the business data, its metadata is associated to describe the meaning of the business data.
The Data Warehouse designer has to decide before hand which data is a business data and which is not. The business data may fall outside the normal data and information need of knowledge workers. Eg:In airlines ticketing application, passenger data assumes importance to promote airlines business.
?
?
?
?
Business data assumes importance when it is useful to manage the business. The data, which is necessary to manage the business, has a value from strategic point of view. The rest of the data is useful to run the business. All business data are candidates for the Data Warehouse. It is also true that the business data of one organization would turn out to be routine information in case of the other organization. For e.g.: customer data has no business value in manufacturing organization, but in post-sales service organization customer data has a business value. The qualification for business data depends on the business, its current status and business strategy need at that point in time.
?
?
?
?
? ?
Business data could be stored in data warehouse in atomic form or in summary.
The decision depends on its utility and application to manage the business. Other important decision about business data is to decide its currency level in terms of time. The three currency features for the business data are, current data, point-in-time data and periodic data. Current data is a view of the business at the present time. It is unto a second and changes with respect to time. Point-in-time data is a snap shot of business data at a particular moment in time indicating the business status at that point of time.
?
?
?
?
Periodic data is a representation of business data by periods such as last three years, last twelve quarters, etc. Broadly, current and point-in-time data are not candidates for Data Warehouse but the extracted data and analysis of this date are the candidates for the Data Warehouse. Business data entering in Data Warehouse is often a derived data. The derived data is taken from the data set generated at point-in-time or data processed periodically. The derived data may be aggregated at some level through summarization process. The aggregation could be for all levels or for selective levels. The derived data also would be put in data warehouse after enrichment.
?
?
?
?
?
Metadata
?
Various data sources could be options for taking the business data to data warehouse. Further each source may have application specific data definition and its use requiring special derivation and reconciliation process.
It is therefore, necessary to know the data model, data definition, data structure and data usage precisely in each case. This data about the business data is called as Metadata.
?
?
?
In other words metadata is a ‘data about data’.
In any information system, three types of metadata are created, namely ? Time Metadata-System Design ? Control Metadata ? Usage Metadata
?
Time Metadata-System Design:
?
While designing a system, a data which describes the use of input data in the application is known as Time Metadata.
?
This metadata gives details on specification, value, allowed value, the business rules it would use to validate and formula to compute and so on.
Control Metadata:
? ?
The control Metadata is used by the system to produce Data Warehouse.
The data is used to manage and control the process of Data Warehouse creation.
Usage Metadata
? ?
The users of Data Warehouse require this. The metadata is sourced from Data Dictionary
ARCHITECTURE OF DATA WAREHOUSE
Conceptual Data Architecture:
?
Three-layer architecture is recommended for efficient Data Warehouse creation.
It is useful and supportive to meet the needs of IS department to maintain the Data Warehouse and also to meet the data access needs of end users of Data Warehouse. In three-layer architecture, layer-I builds real time data, using Transaction Processing Systems(TPS),Application Processing Systems (APS) and Report Generation Systems (RGS).
?
?
?
This data buildup is done through different systems working in the organizations on different platforms and designed to meet specific functional of information of the end users.
At layer-I, data is created which is of business interest and has a strategic use through Data Warehouse.
?
?
Such data then needs to be put to scrutiny from the angle of content, scope, definition, intended unselect. Since data are coming from different systems operating within and outside organization, it is necessary to reconcile the data in number of ways to make it eligible for Data Warehouse processing. Some data would need reconciliation on time lines. Some data may need some manipulation as definition and scope is inconsistent to the business data intended to be in DW. The reconciling step takes data from multiple, heterogeneous,distributed systems, and combines and enhances it into a single logical image of business data as defined into Enterprise data model.
?
?
?
?
Another important factor, which is handled in reconciliation step, is rationalizing the data from various sources on time scale.
?
After reconciled data is available in layer II, it is then used to derive business data for use of various end users and decision makers. The derived data from the reconciled data is not based on random selection, but is derived based on the most commonly asked queries in the business. The commonly asked queries may be from individuals, departments, group of users. Most of MIS reports required by these users can easily be generated using data in derived layer. Such Data warehouse is very handy and very quick in response in producing the MIS reports. The reason for quick response is most of the processing for searching, accessing, matching, computing and reconciling is already done while creating the reconciled layer.
?
?
?
?
?
Logical Data Architecture:
?
The real time, reconciled and derived data layers are conceptual and each of these layers have physical counterparts.
Data warehouse is the physical realization of the real time data into a derived data. The scope of the Data warehouse includes reconciled data and derived data. The derived data is a business information while reconciled data is a business data. Business information is higher in value compared to reconciled data is ready to use for certain application requirement. Data warehouse is implemented as RDBMS. It can reside on a single server or can be distributed at a different locations.
?
?
?
?
?
?
Data warehouse management will be central through DW master or DBA. Since, Data warehouse contains business data and information which is strategically important, it needs to be secured from unwanted unauthorized exposures. When it comes to use of data from external sources in data warehouse along with data sourced from internal operational systems, the principles applied to internal data to conform its eligibility to be in Data warehouse are also applied to data from external sources. The only difference between the two is, internal data indicates business performance within the organization and external data indicates performance and prospects in the market place. The information support for strategic decision making calls for inclusion of data from both the sources.
?
?
?
DATA WAREHOUSE DESIGN
?
DW design process brings with the construction of Enterprise Data Model. The Enterprise Data Model is build keeping in mind its application to build DW. The objective of this step is to obtain high level unified view of data required for strategic decisions. Enterprise data model may first begin with the help of a generic model of the business. It also takes into consideration a generic data model, if its exists and customize it to enterprise requirement. A detailed process of building the design is explained in the following points:
?
?
?
?
?
Build Enterprise data model which specifies the need of data in terms of form, content, period and its application to manage the business.
Locate operational systems which are more appropriate from where data can be sourced to build Enterprise data model. Identify critical applications, which are necessary to manage the business. Select low-level data entities which when processed, build the critical view of an application or function. Identify related metadata which describes the data used in critical view of the function or business. Map data and metadata to application from where they would be sourced. Get into the process of building reconciled data for subsequent generation of derived data.
?
?
?
?
? ?
?
Determine most sought queries from the organization and process them to produce the results known as derived data which would show instantaneous status of business based on preconceived view of the business. Then clearly define the reconciled data and derived business data for storing in the warehouse i.e. by using RDBMS. The design techniques used DW creations are essentially three, enterprise data modeling, reconciling on time scale, and data replication. Most important after data modeling is to decide on data capture from various sources. The process of putting data in warehouse at appropriate place is executed through a separate application.
?
?
? ?
?
Process Flow of Data Warehouse Model:
OS Select Data entities Reconcile Metadata
Examine Physical Data Design
Map Data to appln Consider all Appln Views Extract Metadata Select Critical views to manage the business
Determine critical queries DW Classify Business data
Derive ready-to-use Data
JUSTIFYING THE DATA WAREHOUSE
?
The traditional justification approach for any investment is to prove that Return on investment is attractive. Essentially, it revolves around savings and benefits, which would be realized from such investment. DW therefore improves the productivity of end users. Apart from increase in productivity of end users, the Data warehouse, more correct and focused business view is possible bringing along knowledge of changed business environment. The DW normally is justified in competitive business environment. In general the key to justify the warehouse lies in specific business advantage arising out of strategic analysis of a business situation & evolving competitive strategies to have edge over the competition through offer of better quality service and level of satisfaction.
?
?
?
?
ORGANIZATION & MANAGEMENT OF DATA WAREHOUSE
?
The purpose and the need of DW is to help manage the business. It is essentially developed for senior managers who continuously seek varied information cutting across the organization this being the case, apart from IS and IT specialist, the business analyst is required to be part of a special team formed for development of DW. The team members and their roles are as given:
?
?
Project Manager
?
PM is responsible for design and development of DW. The person should be from the business management group performing at senior level and should understand the business of today and that of tomorrow. DW architect is a technical person on the team who has good understanding of RDBMS, ORDBMS, Enterprise DM and knowledge of complex application development tools.
DW Architect
?
Business analyst
?
Business analyst plays the role to provide the inputs for business viewing and its interpretation, qualify the business data entering in Data warehouse and throw light on its futuristic use by different end users.
DW developers
?
DW developers are in team to design processes to take up data from various sources and make it ready as business data to be in the warehouse. The team of DW architect, business analyst, DW developers will be led by a steering committee consisting of Business managers responsible for conducting the business in critical areas of business. They would be guiding the architect and business analyst. They will also ensure that required business data in the warehouse is available from operations systems and business applications.
?
?
?
IMPLEMENTATION OF DATA WAREHOUSE
?
Through sufficient tools available for each process involved in the design and development of Warehouse, the subject is complex and requires participation of senior management and IS personnel. In practice DW conceptual model could be for the enterprise as a whole, however it needs to be developed in stages to ensure its success and business benefits. The steps involved in stages implementation are as follows : 1) Establish infrastructure namely DBMS, extraction, replication tools and report writers.
?
?
2) Model that enterprise data from logical structure to physical structure.
3) Prioritize the business data need and segment the enterprise data model matching to this need 4) Model the business data at both logical and physical levels
5) Design, Develop and Implement security aspects.
BUSINESS INTELLIGENCE
?
Business Intelligence is all about converting a large amount of corporate data through processing and analysis into useful information and knowledge thereby triggering some profitable proactive business action or decision. Business intelligence environment is made up of business models, data models, extraction, transformation and loading tools needed to transform and organize the data into useful information and knowledge for storage and further analysis. To set up BI environment, skilled people are required who can understand business intelligence requirement at a point of time and know the right source of data and application from where the data and information will be extracted to build BI.
?
?
Business Model
Data Model
RDBS
User ETL tools
DW
BI Environment
Data Marts
Knowledge Base
Data Cubes
OLAP
BI Database
?
Business Model: It explains business process, flow and connections between processes and data models used by them. Data Model: Data model is all about representing the data and relations between them with specification. Data model explains data structure. Conceptual data model give a view of the overall data scope and model with no details. Logical model gives more details like entities, attributes and their relationship.
?
?
?
?
Physical data model gives representation to logical data model of entities in tables, columns, rows, properties and relationship among them.
Relational data model puts physical model showing entity-relationship in a DB satisfying Codd’s rules of data representation in DB.
?
?
The process leading to BI begins with application of ETL tools, which extracts data, cleanses data, and organizes it in data model after checking for validity and referential integrity for storing in DW. The next step is to build data marts. Data marts is a selection of data warehouse on subjects like sales, production, and other for the functional or subject analysis. Data warehouse and data marts are used further to view data in different dimensions, in the three dimensions (cube) to throw light on subject’s performance and behavior. Once data warehouse, data mart, knowledge base, data cubes are built, queries are built to see the contents of these bases, which is then termed as business intelligence. The different views are obtained through query and OLAP tools.
?
?
?
?
OLAP Tools:
?
OLAP, an acronym for ‘Online Analytical Processing’, is a technique by which the data sources from data warehouses, data marts is visualized and summarized to provide multi dimensional view of the subject. OLAP provides information based intelligence. Business intelligence based on knowledge causes from data mining processes, which are similar to OLAP, but they unearth the knowledge through finding patterns, trends, and behavior of the subject providing an action. Besides OLAP kind of analysis, data mining uses techniques like memory based reasoning, link analysis, neural networks and so on. OLAP records a view or problem; data mining helps to find the solution of the problem.
?
?
?
?
doc_745666011.pptx