2

I'm having difficulty connecting a dimension table (recursive/hierarchical) to a fact table as there are concerns/issues to deal with:

  • The dimension table belongs to a parent-child relationship structure
  • From the original table, it keeps growing
id item_name parent_id
1 classification null
2 category null
3 group null
4 modern 1
5 modified 1
6 tools 2
7 meters 2
8 metal 3
9 plastic 3
10 lead 8
11 alloy 8

Denormalizing this kind of table is not suitable as a new entity type comes in, it would affect the dimension structure.

What is the best approach to this type? Kindly provide an example and what would be the query statement after connecting the fact and dimension.

James Z
  • 12,209
  • 10
  • 24
  • 44
zysirhc
  • 37
  • 7
  • You need to choose whether you want a simple dimension table that has all the advantages of simplicity, but without the ability to handle an infinite hierarchy. The first question is: what does the end user want to see and what does the reporting tool support? The typical solution to this is to acknowledge that there really will be a finite number of levels, i.e. 30, and create 30 columns to store that – Nick.Mc Apr 24 '21 at 03:43
  • Hi @Nick.McDermaid Checking with the users, they need to include these item for filtering and groupings.The reporting tool to be used either PowerBI or Kibana. – zysirhc Apr 24 '21 at 04:06
  • I suggest you first use the query here to find the actual depth of this table. https://stackoverflow.com/questions/2199942/getting-hierarchy-data-from-self-referencing-tables – Nick.Mc Apr 24 '21 at 04:10
  • This looks a lot like an EAV model, which is great for the application but annoying for reporting. Another question for analysis would be: how many different records are there with no parents. and how often do they change? – Nick.Mc Apr 24 '21 at 04:12
  • Right now, it has 60 records with no parents. Changes or adding of item group takes 2-3 months approx. – zysirhc Apr 24 '21 at 06:52
  • 60 records with no parents would mean that there is only a depth of one.... your sample data has a depth of three. Are you able to post the actual data in the table? – Nick.Mc Apr 24 '21 at 07:11
  • I can't post the actual records of the users. If regarding permutation/levels max (child) is 3, common are level 2. – zysirhc Apr 24 '21 at 07:37
  • May be this can help - http://www.datamartist.com/data-modelling-how-to-make-a-dimension Initially you can account for 3 levels and if 4th level gets added, you can add it as column in dimension. – CleanBold Apr 24 '21 at 16:43
  • If your hierarchy is relatively simple (e.g. max 3-4 levels with no skipping) then you can normally build it into a standard dimension (oarent, grandparent, great grandparent columns). If the hierarchy is more complex (unknown number of levels, ragged hierarchies, etc) then the design is normally driven by the requirements of the BI tool you are using and what it needs to navigate the hierarchy; this often means the creation of "helper" tables – NickW Apr 25 '21 at 09:12

0 Answers0