There are various types of hierarchy that range from simplistic to complex hierarchies with multiple level starting points. These complex hierarchies are often referred to as ragged hierarchies. Ragged hierarchies can occur because some of your source data systems may capture data at a very granular level whilst another system may capture or output data at summary levels. You may also be using third party data and wish to combine it with your in house data. When the data is consolidated it may not be possible to populate all levels of the hierarchy with the same coverage if at all.
In these situations it can be useful to undertake a mapping exercise with an outcome similar to that shown in the diagram. This should occur before modelling the hierarchies in dimension tables and the ETL build.
Biography – Russell Beech, Founder of BISB & Cornerstone Solution®
Russell is a Business Intelligence Leader who has specialised in providing Analytics, Data Warehousing and Business Intelligence Solutions for almost two decades. He is the founder of BISB (BI System Builders Ltd) and the innovator of BISB’s Strategic Business Intelligence Framework known as Cornerstone Solution®. Russell developed Cornerstone Solution® as a strategic framework to successfully deliver Business Intelligence services within End to End BI solutions.
Russell holds a BSc in philosophy and Science, a MSc in Tourism Management, and a Postgraduate Diploma from the Chartered Institute of Marketing. After studying, Russell started work in Strategic Marketing becoming a fully elected member of the Chartered Institute of Marketing in 1997. His involvement in analysis and planning activities extended into databases. This encompassed Ralph Kimball’s Redbrick Platform, Data Warehousing, Business Intelligence, and several analysis tools including BusinessObjects. Post the Millennium, he joined the Product Group at BusinessObjects UK. After several years at BusinessObjects Russell worked for two prominent BusinessObjects partners. Firstly, Northgate Information Solutions as their SAP BusinessObjects Solution Architect, followed by HCL Axon as their SAP BusinessObjects Subject Matter Expert. He founded BISB in 2009. He has extensive technical, functional and business knowledge around Data Warehousing and Business Intelligence Solutions.
The Role of the BI Architect
The BI Architect must understand the BI vision and strategy and cause both to come to reality through the end to end BI system. The BI Architect achieves this through fastidious planning and designing pre-implementation, troubleshooting and mentoring during implementation, and governing and evangelising post implementation.
The most effective BI Architects know how to formulate Solution Architecture. They are able to hook together several different architectural disciplines to develop the solution. They will understand Business Architecture: Unless the organisation’s business processes and business requirements are understood how can a BI solution framework be proposed? They must of course be experienced with Data Architecture: Data is at the core of Business Intelligence and the best BI Architects will be experienced data modelers. Hand in hand with experience in data modeling BI Architects must also understand Information Architecture: The sources that the data will come from, it’s journey through the organisation, and how it will be consumed and by who. Experience with Application Architecture is critical for the full understanding of the selection of the BI platform, ETL software, BI tools etc. The most effective BI Architects will know how to size the BI/DW system, understand licensing and pricing and work closely with Infrastructure Architects to understand any required integration and procure the relevant hardware, web application servers, load balancers etc. Finally SLA’s should be considered within the Service Architecture: When must data be available by and how quickly must software execute etc.?
To enable all these things to happen successfully the BI Architect must have a clear understanding of the business process areas as well as a high technical level of expertise in the full BI platform. The BI Architect should have knowledge of the potential BI tools, knowing what they can do, how they integrate with other components and their current development road map. Understanding the development road map enables the BI System to be as future-proof as possible and the BI Architect to advise on the future as well as the now. Poorly informed decisions made today can prove very costly tomorrow. This thorough understanding of the BI tools and components, along with BI processes enables the BI Architect to deliver a BI system that constitutes best practice and exhibits high performance.
When powerful Business Intelligence tools such as SAP BusinessObjects are implemented there must exist the BI function of BI governance. Once the BI System has been developed the best place for governance to be rolled out is through a core BICC and the BI Architect should be central to this activity. For example, consider Self-service BI. When a user interacts with the BI System the user is not simply executing a piece of code within a single application such as Microsoft Excel; several different applications will be interacting together across functional areas. These applications are interdependent and so called ‘stress points’ will occur. In Self-service BI hundreds and even thousands of users can hit the BI System simultaneously. If governance is not applied to these ‘stress points’ they may become ‘breakpoints’. The BI Architect should be familiar with these ‘stress points’ and in practising End to End BI apply governance to ensure that they do become breakpoints.
Not least required is the ability to evangelise the end user community through clear articulation of the BI processes and demonstrations of the benefits of the selected BI tools. In order to gain end user adoption of the BI System it is paramount to give the very best impression of the BI tool set to the end users. End users need to perceive that they are getting more benefits with the new tools than they previously had. New tools may lead to different ways of working and improved process, this in itself requires some change management. The knowledge and skills of the BI Architect make for an effective evangelist. An internal marketing campaign may be designed to ensure that positive communications about the benefits of the BI system are being transmitted and not lost or watered down in the change adoption process.