Joomla ServiceBest Web HostingWeb Hosting
Home Services Data Warehouse Development

Dimensional Modelling

Dimensional Modelling is the process of designing optimised data structures that will support the reporting requirements of the business.

Database design for transactional systems focusses on the processes required to store and update data - for Dimensional Modelling, the focus is the end user reporting requirements. The structures derived are based on sets of values (the Facts) that are to be reported on and the various ways in which the information is to be 'sliced and diced' (i.e. the Dimensions). Put simply, if you think of a typical report, then the Dimensions will form the row and column headers and the individual cells in the report will be comprised of Facts.

The end result of a Dimension Modelling exercise will be a set of Fact tables that hold the measures that the business is interested in (e.g. sales quantity, cost, sell price etc.) and the Dimensions that relate to each Fact table (e.g. product, date of sale, store etc.). Dimensions that may be used by multiple Fact tables need to be designed so they can be properly shared - these are called 'Conformed Dimensions'. Conformed Dimensions enable the reporting environment to easily relate one set of facts to another.

There are many issues that need to be addressed during the Dimensional Modelling phase, including how you handle changes to your Dimension data over time; the granularity of the fact data; and the hierarchies of data within Dimensions (e.g. Product Category-> Product Group -> Product).

Without conducting a formal Dimensional Modelling exercise, there is no guarantee that a multi-dimensional data source built in Analysis Services will actually meet your reporting requirements.