by Hongde H
The article I choose to read this week is about techniques for quick access from Data Warehouse. In the journal, the author was stating the common problems faced by data warehouse administrators and users. He outlined some query performance techniques which minimizes response time and improves overall efficiency of data warehouse, particularly when data warehouse is access and updated frequently. By and large performance of the system is improved without accessing the original information sources which provide good strategies that make finer data warehouse.
The author started by discussing few techniques that are used in traditional database systems to boost query performance.
Balance components for data warehouse
The author stated that when configuring a Data Warehouse system, It is important to insure that all components are balanced, which means that all active components (CPU, Memory and I/O) are all effectively used and when one component reaches its maximum operating range, all the other components approach their maximum at the same time.
CPU requirements for data warehouse
As we all know CPU plays an important role in making your computer fast and efficient. and there is no exception for database. The database requires periodic reorganization which requires extracts of data, data reloads and data re-indexing. A database requires backing up and restoring in the case of a media component or operational failure. All of these processes require CPU resources. Therefore, the database size will have a large impact on the number and size of the CPUs required.
Use best query techniques for data warehouse
In this section, the author talked about the star query which is one of the most important query types that will be exploited within a Data Warehouse practice.
Optimization of data mart
According to author, Data marts are a critical part of data warehouse design. They are created to store data required for analysis by specific departments. For example, manufacturing department, sales department or inventory control.
The author explains that data is extracted from the warehouse and transformed into facts and dimensions within a data mart, which is then queried using client tools or business intelligence reporting tools. Information builders offer comprehensive solutions for developing, managing, and optimizing a flexible data architecture that efficiently supports any enterprise information initiative
Use Snowflake data model schema
A snowflake Schema in its simplest form is an arrangement of fact tables and dimension tables. The fact table is usually at the center surrounded by the dimension table. Normally in a snow flake schema the dimension tables are further broken down into more dimension table. Snow flake schema is one of the types of designs present in database while the other one is the star schema . The main advantage of the snowflake schema is the improvement in query performance due to minimized disk storage requirements and joining smaller lookup tables.
Use Query cache
The author stated that We can improve query performance by using the query cache. Query cache will store all record of executed query. Query cache will keep record of newly executed queries. The purpose of the query cache is to reduce the response time of query. It will increase the brainpower of data ware house so that system will memorize the latest work it has performed.
Ultimately, I choose this article is because it is closely related to what we had in the class of the week. In spite of the summary, the author explained all techniques in a very detail way. I would suggest everyone to read the whole article in order to have a complete understanding of how all these techniques work. Data has never stop growing, knowing all these techniques will save people a lot of time and resources.
Source: Ritika, & Rakesh, K. S. (2012). Technique for quick access from data warehouse.
Journal of Information and Operations Management, 3(1), 280-283.
Retrieved from http://search.proquest.com/docview/1019052151?accountid=10357