When spoken of database warehousing broadly, it is a database which is used for reporting and analysis. Different operational systems upload the data into a data warehouse. The main function of data warehousing is storage of data. The data that is uploaded in a data warehouse is free from bugs and is highly indexed. Thus data warehousing can be considered as the database which includes business intelligence tools which are basically tools to extract and transform and load the data into a repository. It also has the tools to manage and retrieve the metadata of the database.
Generally a data warehouse will maintain a copy of the data that is provided by different operational systems. Thus this complex structure enables the data warehouse to maintain the data history. A data warehouse will keep the history even if the source is not keeping it. It integrates the data from different source systems, thus enables a central view for the enterprise, which is very important and crucial for a big organization. The data warehouse is able to improve the data quality. It does that by making codes consistent. It flags the bad data or can even fix it. With all these features it is able to provide a consistent view of data to the organization. It provides a single view of the data of interest and does not let the source operational systems, to interfere. It is also capable to change the structure of the data without making any changes to the contents.
In a dimensional approach the transactional data is partitioned into numeric transaction data or into reference information. Thus in this approach the data can be broken into either facts or dimensions. The main advantage of this approach is that it is easier to understand and to use for the user. To retrieve data from this data warehouse structure is very fast. It is easy to understand because it is broken into measurements and their contexts. The main disadvantage is that loading the data from various operational systems is a complicated process, thus it becomes very difficult to maintain the data integrity. With dimensional approach it is next to impossible to change the structure of the data warehouse.
In a normalized way of uploading the data into the warehouse is done after reaching certain level of normalization. The normalized structure of the data divides it into different entities, which is converted into different physical tables at the time of implementation. This approach allows different operational systems to upload the data into the database. The disadvantage is that to reach a certain level of normalization it becomes difficult to join different tables from various sources, and then to access the information from these normalized tables without having prior knowledge of the source is quite a complex activity.
High quality of dba expertise needs to maintain data warehouse databases. Dbametrix is the team of expert database dba to provide high level of Oracle database support with remote database monitoring and remote dba. For more details, contact our sales department using contact us form.