Why Utilise a Data Warehouse?
A data mart is a collection of data in support of management’s decision-making process that is subject-oriented, integrated, time-variant, and non-volatile. The data mart is focused on the concept (for example, sales) rather than the process (for example, issuing invoices). It contains all the relevant information on a concept gathered from single or multiple processing systems. This information is collected and stored at regular intervals and is relatively stable. A Data Warehouse is an enterprise-wide variation of a Data Mart, or a collection of integrated subject-area orientated Marts.
The data warehouse or mart is created to provide a platform for end users to perform their own ad hoc query / reporting functions, traditionally forwarded to the IT department for development. The justification in this approach is based on the idea that users can develop their own ad hoc reports with very short turnaround as opposed to having to submit their requests to IT, and can better refine and elaborate on queries once initially developed.
EnterpriseBI believe that the following are important advantages to a data warehousing approach, as opposed to reporting straight from flat file extracts or application data sources:
Make Information Accessible
Meta data
Due to the nature of the process the Meta Data or “data about the data” is more tuned to the business aspect rather than the IT technical aspect therefore providing more meaning to the end users. It is more business oriented and user friendly. An example of this would be when a user selects the Customer Name rather than cust_id from the data warehouse.
Performance
Due to the design techniques used, the data warehouse is tuned for reporting or querying as opposed to operational systems that are optimised for single database transactions. Indexes are applied and the overall design of the database is tailored for performance.
Data aggregation
Using the data warehousing approach it is possible to aggregate data to more summary levels, instead of holding millions of rows of the data simply to create a summary level report. This is done to reduce the number of rows of data held within a table and therefore reduce the query execution time where detailed queries are not necessary. An example of this may be where reporting is done on a monthly basis and only monthly totals are required as opposed to the atomic level detail. This means that summary tables can be reported against separately from detail reports with the ability to drill from one to the other.
Reduction of load on the operational system
The reports and queries would access the data warehouse as opposed to the live operational system therefore reducing the load and increasing the performance of both the reports and the operational systems.
Make Information Consistent
Consolidation
One version of the truth!! To provide a consolidated picture of the Enterprise data as opposed to having several people create different views of the same data and coming up with different answers to the same questions.
Data sharing
By holding the data centrally all users are be able to share the same data. More than one concurrent user can view the same data too which would prove difficult when using flat reports and spreadsheets.
Data quality
A data mart/warehouse provides integrated, cleansed and consistent data. Any differences in codes between various data sources can be translated and made to be consistent.
Dimensional conformity
The many dimensions of the business such as Time, Location, Customer, etc can be created once and then reused. This means that the same set of rules will apply when creating reports and users will always get consistent naming conventions.
Historical data
Can be merged with current data. This means that time trending can be performed against many different business dimensions.
Transparency
The physical location of the data and its various data sources will be completely transparent to the user community.
Make Information Adaptive and Resilient
Reduction in admin and maintenance time
Data is loaded overnight or at specific times, therefore taking away the need for someone to collate the data and create the reports i.e. at the month end.
Scalability
The design of the data warehouse allows other information to be added in the future and due to the conformity of the dimensional model most of the business dimensions can be reused. This allows the data warehouse to adapt to changes in the business structure easily and to differing business priorities.
Slowly changing dimensions
The basic objective is to capture the movement of dimensional elements over time e.g.. Capturing when a sales person moves from one region or manager to another region or manager. A slowly changing dimension would track these changes over time and allow users to analyse the impact of these changes in business structures. This is only possible using a data warehouse.
Multiple data sources
Data can be extracted using an Extraction, Transformation and Load (ETL) tool from many different source systems such as ERP, finance, HR etc and non-database information such as spreadsheets.
|
