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.
SAP BusinessObjects Data Quality is the industry’s most advanced enterprise data quality solution, offering centralized data quality services through a service-oriented architecture. It offers powerful data parsing, cleansing, standardization, matching, and consolidation capabilities to ensure that users can build a data quality solution that meets any business need.
Biography – Russell Beech, Founder of BI System Builders & Cornerstone Solution®
I architect data solutions and superintend the solutions that I architect. I build teams that are usually a mix of full-time employees and sub-contractors. I ensure mentoring and knowledge transfer. The emergence of big data has opened the door to a rise in interest in predictive analytics aka machine learning. For me the technology changes have provided the opportunity to architect data solutions which combine my enterprise data warehousing experience with data lake concepts and to apply my knowledge of statistics to that data to deliver predictive analytics. To that end I’m putting work into understanding how the evolving technologies hook in to each other. I have a focused interest in learning about big data technologies such as Hadoop, Hive, GCP BigQuery and machine learning and their integration/virtualization with the enterprise data warehouse.
You can find me on LinkedIn here and subscribe to watch BI System Builders videos on our YouTube channel here.