Technology Blog

With proper scheduling these reports can be run extremely quickly using high degrees of parallelism. This allows the full power of the machine to be brought to bear on these reports, and the batch reports can be finished quickly. The canned queries are also predefined queries, but they differ from reports in that there is a requirement to run them online. Canned queries also differ from reports in that they are often parameterized, and hence the data set they visit can vary radically in size. As with the batch reports these queries are a good starting point for testing and development. You can measure the resource requirements of these queries, and the results can be used for capacity planning and for database design.

The canned queries are again good candidates for use on a pilot system, or for early delivery in a phased development. Ad hoc queries, as the name suggests, are the unpredictable element of a data warehouse. They are also generally the main reason for developing the data warehouse in the first place. It is exactly that ability to run any query when desired and expect a reasonable response that makes the data warehouse worthwhile, and makes the design such a significant challenge. The ad hoc query profile will be difficult if not impossible to predict. The best that can be done is to develop an understanding of the queries that are likely to be run. This will come from an understanding of the business and from the requirements capture. This information can then be used in the design of the database to meet those requirements.

It is vital that you get this right, because the decision on what constitutes the fact data for the data warehouse will depend on these answers. This is one of the most important decisions you will make in the whole project. If you get the level of detail wrong for the fact data you may ultimately have to scrap the whole data warehouse and start again. Just to reinforce this statement, and to ensure that it is not taken as a throwaway comment, if you get the level of detail wrong for the fact data you will probably have to scrap the whole data warehouse and start again. The whole design, the sizing, the capacity planning and so on will be based on this decision. If the level of detail is incorrect, the hardware will be the wrong size, the database design and layout will be incorrect, and any partitioning you are using will be wrong. You will need to start over.

If you are aware that, as in the example above, the level of detail will increase later, you can design with that in mind, and ensure that any design you put in place now should not hamper that change later. Having an understanding of these details will allow you to round out the query requirements. It allows the user requirements to be rationalized, and a reasonable estimate of the sorts of queries that are possible in the future to be calculated.

If you are a data warehouse manager, remember to capture all the time, period and business date information by department, as different parts of a business may have different dates and times. This information is crucial to the data warehouse design, because it can for example radically affect your weekend processing if some parts of the business have their week begin on Sunday and others have their week start on Monday.

Part of analyzing and understanding the business is getting to grips with the key business indicators, and the data dimensions that are important. What dimensions or data fields will the users want to summarize and query on? These fields will form the basis of all your aggregations, and this information - in conjunction with the date and period information discussed above-will allow you to define the base set of aggregations that will be required.

As part of the analysis you also need to drive out the meaningful levels of data detail. For example, if the business is a food retailer, does it need tb keep detailed records down to the sale of each individual tin of baked beans, or is it sufficient to. summarize sales of baked beans by tin size by brand by day?