My first Publication Agile-Data-Warehouse-Design-eBook

188 Chapter 6 The self-join pattern can be used to create Year-End dimensions for “as at” reporting Who and What As well as creating CV swappable dimensions the self-join view technique can be used to create Year-End dimensions for “as at” reporting; for example, a Year-End dimension for 4 April 2011 can be created by replacing the CV.Current = ‘Y’ constraint in the view definition with: ‘4/4/2011’ between CV.Effective_Date and CV.End_Date Consequences The CV swappable dimension is a “gold star” agile design pattern. As long as you have tracked history for a dimension from day one, a CV view can be added at any time, when CV reporting requirements emerge, without increasing ETL program- ming effort (if implemented as a materialized view) or rewriting any existing queries, because the view is hot swappable. Don’t trust CV only requirements. Build HV dimensions and deliver CV views Having said that, it is often the case that CV reporting is the first choice. Stakeholders will define attributes as CV/HV rather than HV/CV (with the CV default first) because they initially want BI solutions to mimic the CV only per- spective of existing operational reporting systems. Or perhaps, stakeholders just define CV attributes because they simply cannot see a need for history—yet. Either way, unless you are dealing with a very large dimension (customer), you should default to HV ETL processing, hide the HV dimension and make a CV view available. That way, when stakeholders finally demand HV reports, you can simply swap views rather than reload the entire warehouse. Previous Value Attribute Pattern Problem/Requirement CV/PV attribute requirements Occasionally, BI users will simply need the previous value of a dimensional attrib- ute rather than its full history. This can be sufficient when there is a “one off” macro-level change such as the renaming/relocating of branch offices. Previous values can also be necessary when BI users want to look at “alternative realities”. By running “as previously” reports they can see what things would be like if a change had not occurred. Stakeholders can define previous value requirements by docu- menting an attribute as CV/PV. Solution Implement separate CV and PV attribute columns CV/PV attributes are implemented by defining additional PV columns (also known as Type 3 SCDs). Figure 6-18 shows PREVIOUS TERRITORY PV1 added to the EMPLOYEE dimension. This is marked PV1 to link it to the current value TERRITORY attribute marked CV1. During ETL processing, whenever TERRITORY is updated its existing value is saved into PREVIOUS TERRITORY prior to storing the new value. PV attributes work well for small numbers of attributes that must be tracked but do not change frequently, because they only allow users to go back one version. Multiple PV attributes would allow for more versions; for example TERRITORY LAST YEAR PV1, TERRITORY 2YR AGO PV1, all linked to the current TERRITORY CV1 but can soon become unwieldy.

My first Publication Agile-Data-Warehouse-Design-eBook | Page 208