Jul 02 2010

Universe Models For Recursive Data Part III: Alias Versus Flattened

This is the third of several posts that will review my presentation “Universe Models For Recursive Data” which was originally presented at the 2009 GBN conference, then at the North Texas / Oklahoma ASUG chapter meeting, and finally at the Mastering BusinessObjects conference in Melbourne. As with my other presentations there is a PDF file that can be downloaded from my conference presentations page. The first post introduced the concepts of recursive (as opposed to hierarchical) data and provided a couple of examples. The second post reviewed some of the different design challenges that I have seen in working with recursive data models. In this post I will introduce four different possible solutions and present a scorecard for each, showing how well it solves the issues presented in the prior post in this series. Links to both prior posts are presented at the end of this entry. I have also included Oracle SQL scripts that can be used to create and populate the tables used in this post.

This post will cover slides 22 through 30 from the presentation and will describe the first two solutions (one with two variations) outlined in the presentation.

Solution Options

The four different solutions that I included in my presentation were: Universe aliases, Flattened structures (column or snowflake), Ancestor Model, and Depth First Tree Traversal. All of them work fine on a clean recursive hierarchy. Each of them partially works for at least some of the other challenges. Some of them present unique challenges (extra disk space requirements or lack of native drilling functionality) that will also be addressed. I am presenting the solutions in increasing order of complexity. This post will cover aliases and flattened structures (both versions). In the next post I plan to cover the ancestor model, and finally I will cover the depth first tree traversal in its own post.

Universe Aliases

This solution is the only one that can be completely self-contained within the universe. No DBA or ETL work is required. There are any number of ways to create an alias. I can:

  • Right-click on a table and select Insert Alias
  • Select an existing table in my structure, then select Insert + Alias from the menu
  • Open my table browser and insert an existing table. An alias will automatically be created for me.
  • Select an existing table in my structure and click the “Insert Alias” toolbar button

… and there are other ways to get aliases in my universe, especially if I have loops to resolve. The bottom line is that the process is quite simple.

Here’s what an alias looks like after it has been created and joined to an existing table in my structure.

screen shot of alias implementation in a BusinessObjects universe

The join can be a bit tricky. In this case, the employee row MGR_ID is joined to the manager row EMP_ID in order to make the relationship work. It might help to look at the raw data again from an earlier post.

raw data used to demonstrate recursion in a BusinessObjects universe

See how the recursive relationship is going to work after establishing this join? Field works for Ferrerez, and Ferrerez works for Noakes. Who does Noakes work for? His MGR_ID column is empty (NULL) implying that he does not have a manager. He owns the company. 🙂

Pros of Alias Solution

The primary advantage of this solution is that it is completely self-contained in the universe. No DBA or ETL work is required. That’s about it.

Cons of Alias Solution

There are several cons to this solution. It does not represent lateral relationships at all. I have to use outer joins in order to preserve those rows with missing keys (Noakes in this example). Both of these are important, but the most substantial drawback to this solution is that the depth is determined by the number of aliases that the universe designer creates. In the image shown above there is only one link: from manager to direct employee. How can I — in one step — determine my indirect reports? With only one level of alias, I can only report one level of my hierarchy. How many can I report with this structure?

screen shot of multi-level alias implementation in a BusinessObjects universe

With that structure I now have two outer joins, but I can report on three levels instead of just two.

How many alias levels do I create? Generally when I have seen this solution used (or used it myself) we resort to asking how many levels are required and then creating some number above that. If I need five, I will create seven. If I need seven, I will create ten.

That means, of course, if I have created ten levels and all of a sudden we have twelve I have to update my universe. That’s not a problem (as long as I keep up with things) but it’s certainly not desirable.

Alias Scorecard

Here’s the scorecard for the alias solution for each of the four scenarios I outlined earlier.

alias scorecard for handling recursive data

Aliases are the easiest solution to implement but they don’t score well. Let’s move on to the next solution.

Flattened Structure – Single Table Columns

The next solution involves running either a SQL script or some form of ETL. I need to take the recursive table relationship and flatten it out much like I did with aliases, but this time in the database itself. The net result is that I will take data going down in rows:

data in tables is presented as rows

and pivot it into columns in a table.

data for a hierarchy can be pivoted into columns

The net result is all of my recursion is done during the script process and I end up with one table that contains everything (or every person in my case) stored at their specific level in the hierarchy. It easily allows me to drill because it creates a very natural hierarchy.

Pros of Flattened Table Solution

It handles unbalanced hierarchies much better than aliases because missing lower nodes are simply NULL in the table. That’s fine. This solution can also handle ragged hierarchies with a proper “plug node” strategy. If I have a lower level value (Divisional Director) that reports directly to the president (top level) then level 2 (Vice President) will be empty. I need to fill something in so I can drill properly. More important, that plug node has to tell me what the path is or else I cannot drill up properly. Suppose I had a director named Smith who reported directly to Noakes. The first column in my table would include Noakes. The third column would include Smith. The second column (the missing value due to the raggedness of my data) would contain Smith VP Not Assigned or something like that.

Flattened tables cannot handle lateral hierarchies at all because I can’t store two values in a single column.

Cons of Flattened Table Solution

As already mentioned, this solution cannot handle lateral hierarchies at all. It also requires DBA or ETL work if the number of hierarchy levels changes. My column names should reflect the position (node type) in the hierarchy. That’s not a problem unless my hierarchy levels change, then I might want to update my structures.

But by far the most critical issue with this solution is the fact that it requires DBA or ETL work if my levels ever change. Much like aliases when I have seen this solution implemented I generally see extra columns at the end of my table just to allow for future expansion.

Flattened Table Scorecard

Here is my scorecard for the Flattened Table solution.

flattened columns scorecard for handling recursive data

Flattened Structure – Snowflake Tables

One thing that I noticed about the data for the flattened structure is that I repeat a lot of values. For example, Noakes is the “level 1 mgr” for every person in the company. It might seem to be more efficient to use a structure like this:

screen shot of snowflake structure in a BusinessObjects universe

This would reduce my overall storage requirements because I would end up with a single row for the highest level table.

However, it also reintroduces the need for outer joins, which the initial flattened structure avoided.

Pros of Flattened Snowflake Solution

Because the tables get smaller as I get further up the tree (ultimately to a single-row table in my simple example) my overall storage requirement should be smaller as well. If I only need the top one or two levels, my queries should be very efficient. Finally, I think it would be easier to maintain as well. If a new level appears, I add a new table to my chain with the proper restrictions on the ETL for proper table population.

Cons of Flattened Snowflake Solution

Each of the solutions defined so far suffers from some form of this issue: I have to define a table (or column) for every possible level of my hierarchy. If I do not know what the total number of levels will be, I can try to anticipate and create extra tables to support future expansion. But that is not the best solution. Because these tables are maintained in the database, I have to talk to my DBA or ETL team when changes are required. Because the tables are joined I have to consider whether to use outer join to preserve depth on unbalanced hierarchies. And finally, the “plug node” strategy I outlined earlier becomes a “plug row” strategy in this case, and that’s substantially more complicated.

Flattened Snowflake Scorecard

Here is the scorecard for the flattened snowflake solution. In my opinion, it’s a slightly worse solution than the flattened table solution simply because of the join issue and the plug row concern.

snowflake scorecard for handling recursive data

Next Time

The solutions covered in this post are the least complex and therefore offer the least flexibility. They are easy to set up; in the case of aliases the entire solution can be built within the universe designer application. All of the other solutions require some sort of database scripting. In the next post I will talk about the ancestor model and how we used it at a manufacturing client. It has some definite advantages, and it handles just about all of the different challenges I have outlined. I don’t have to worry about plug nodes, and it handles both ragged and unbalanced hierarchies quite well. However it has an impact on disk usage and it can’t be drilled using the native functionality provided by BusinessObjects. Do the pros outweigh the cons? Come back soon and see for yourself. 😎

Related Links

Supplemental Material
Scripts to create and populate the basic HR table used for this presentation.

  • Create table
    create table employee
    (emp_id number(5) not null
    ,emp_lastname varchar(20)
    ,emp_firstname varchar(15)
    ,emp_dob date
    ,emp_address varchar(40)
    ,emp_area_code varchar(7)
    ,emp_town varchar(15)
    ,emp_phone varchar(18)
    ,showroom_id number(4)
    ,emp_start date
    ,emp_mgr_id number(5)
    ,emp_sex varchar(1)
    ,job_id number(4));
    
    alter table employee add constraint emp_pk primary key (emp_id);
    create index emp_dept on employee(dept_id);
    create index emp_showroom on employee(showroom_id);
    create index emp_mgr on employee(emp_mgr_id);
    
  • Populate table
    insert into employee (emp_id, emp_lastname, emp_firstname, emp_dob, emp_address, emp_area_code, emp_town, emp_phone, emp_start, emp_mgr_id, emp_sex)
    values (101, 'Noakes', 'Nicholas', '12-MAR-48', '2356, Melrose Street', '30190', 'San Jose', '12-00-00-01', '01-JAN-91', NULL, 'M');
    
    insert into employee (emp_id, emp_lastname, emp_firstname, emp_dob, emp_address, emp_area_code, emp_town, emp_phone, emp_start, emp_mgr_id, emp_sex)
    values (102, 'Ferrerez', 'Ferdinand', '10-FEB-64', '25 Arcadia Avenue', '75897', 'Los Angeles', '22-55-56-32', '30-MAR-96', 101, 'M');
    
    insert into employee (emp_id, emp_lastname, emp_firstname, emp_dob, emp_address, emp_area_code, emp_town, emp_phone, emp_start, emp_mgr_id, emp_sex)
    values (103, 'Field', 'Felicity', '15-DEC-60', '12 Brasilia Street', '12014', 'Santa Barabara', '14-46-54-22', '26-MAR-95', 102, 'F');
    
    insert into employee (emp_id, emp_lastname, emp_firstname, emp_dob, emp_address, emp_area_code, emp_town, emp_phone, emp_start, emp_mgr_id, emp_sex)
    values (104, 'Fraser', 'Frank', '13-MAR-67', '45 Seaside Avenue', '75016', 'Los Angeles', '22-55-18-33', '13-DEC-91', 101, 'M');
    
    insert into employee (emp_id, emp_lastname, emp_firstname, emp_dob, emp_address, emp_area_code, emp_town, emp_phone, emp_start, emp_mgr_id, emp_sex)
    values (105, 'Snow', 'Sara', '03-OCT-65', 'Square Woodstock', '18000', 'San Jose', '14-34-34-30', '01-MAY-93', 101, 'F');
    
    insert into employee (emp_id, emp_lastname, emp_firstname, emp_dob, emp_address, emp_area_code, emp_town, emp_phone, emp_start, emp_mgr_id, emp_sex)
    values (106, 'Speed', 'Sonya', '03-DEC-70', '5, The Vale', '22000', 'San Jose', '14-32-39-43', '04-JUL-96', 105, 'F');
    
    insert into employee (emp_id, emp_lastname, emp_firstname, emp_dob, emp_address, emp_area_code, emp_town, emp_phone, emp_start, emp_mgr_id, emp_sex)
    values (107, 'Spencer', 'Steve', '01-NOV-64', 'Square Osaka', '33010', 'Los Angeles', '22-24-25-89', '16-APR-91', 105, 'M');
    
    insert into employee (emp_id, emp_lastname, emp_firstname, emp_dob, emp_address, emp_area_code, emp_town, emp_phone, emp_start, emp_mgr_id, emp_sex)
    values (108, 'Helen', 'Harrison', '01-AUG-66', 'Via Firenze', '38200', 'Los Angeles', '22-34-31-11', '13-MAY-94', 101, 'F');
    
    insert into employee (emp_id, emp_lastname, emp_firstname, emp_dob, emp_address, emp_area_code, emp_town, emp_phone, emp_start, emp_mgr_id, emp_sex)
    values (109, 'Thomas', 'Tom', '01-DEC-68', '11 Over Way', '24000', 'San Jose', '22-45-67-45', '20-DEC-95', 101, 'M');
    
    insert into employee (emp_id, emp_lastname, emp_firstname, emp_dob, emp_address, emp_area_code, emp_town, emp_phone, emp_start, emp_mgr_id, emp_sex)
    values (110, 'Thatcher', 'Terry', '03-OCT-50', 'Stars Parkway', '21000', 'San Jose', '12-11-11-09', '06-DEC-92', 109, 'M');
    
    insert into employee (emp_id, emp_lastname, emp_firstname, emp_dob, emp_address, emp_area_code, emp_town, emp_phone, emp_start, emp_mgr_id, emp_sex)
    values (111, 'Davis', 'Diana', '12-AUG-64', 'Rue Opera Sauvage', '92100', 'Los Angeles', '14-54-11-10', '22-SEP-92', 101, 'F');
    
    insert into employee (emp_id, emp_lastname, emp_firstname, emp_dob, emp_address, emp_area_code, emp_town, emp_phone, emp_start, emp_mgr_id, emp_sex)
    values (201, 'Pickworth', 'Paul', '12-FEB-51', '23 Las palmas road', '00316', 'New York', '12-24-26-44', '12-JAN-93', 101, 'M');
    
    insert into employee (emp_id, emp_lastname, emp_firstname, emp_dob, emp_address, emp_area_code, emp_town, emp_phone, emp_start, emp_mgr_id, emp_sex)
    values (202, 'Forest', 'Florence', '10-OCT-32', 'Rue des Lombards', '75100', 'New York', '22-54-11-10', '23-DEC-94', 201, 'F');
    
    insert into employee (emp_id, emp_lastname, emp_firstname, emp_dob, emp_address, emp_area_code, emp_town, emp_phone, emp_start, emp_mgr_id, emp_sex)
    values (203, 'Brown', 'Bella', '12-APR-59', 'Hollywood Blv', '36020', 'New York', '22-36-25-50', '03-FEB-92', 202, 'F');
    
    insert into employee (emp_id, emp_lastname, emp_firstname, emp_dob, emp_address, emp_area_code, emp_town, emp_phone, emp_start, emp_mgr_id, emp_sex)
    values (204, 'Porter', 'Pete', '15-NOV-57', 'Avd Torre De Embarra', '34100', 'New York', '14-44-11-66', '13-APR-92', 201, 'M');
    
    insert into employee (emp_id, emp_lastname, emp_firstname, emp_dob, emp_address, emp_area_code, emp_town, emp_phone, emp_start, emp_mgr_id, emp_sex)
    values (205, 'Irving', 'Ira', '12-FEB-64', '44 Beach avenue', '13000', 'New York', '12-56-55-20', '18-JUN-95', 204, 'M');
    
    insert into employee (emp_id, emp_lastname, emp_firstname, emp_dob, emp_address, emp_area_code, emp_town, emp_phone, emp_start, emp_mgr_id, emp_sex)
    values (206, 'Bailey', 'Ben', '12-JUN-57', '4 Palisades Drive', '75090', 'Long Island', '12-33-51-29', '01-DEC-90', 204, 'M');
    
    insert into employee (emp_id, emp_lastname, emp_firstname, emp_dob, emp_address, emp_area_code, emp_town, emp_phone, emp_start, emp_mgr_id, emp_sex)
    values (207, 'Duckworth', 'Dave', '09-SEP-66', 'Rue du grand temps', '75018', 'New York', '12-85-01-61', '04-NOV-93', 201, 'M');
    
    insert into employee (emp_id, emp_lastname, emp_firstname, emp_dob, emp_address, emp_area_code, emp_town, emp_phone, emp_start, emp_mgr_id, emp_sex)
    values (208, 'Ince', 'Ian', '10-AUG-53', 'Sunset Blvd', '31061', 'New York', '22-52-22-00', '04-DEC-95', 207, 'M');
    
    insert into employee (emp_id, emp_lastname, emp_firstname, emp_dob, emp_address, emp_area_code, emp_town, emp_phone, emp_start, emp_mgr_id, emp_sex)
    values (209, 'Hilary', 'Hibbs', '01-FEB-60', 'Sand Hill Road', '92800', 'New York', '12-54-11-10', '08-JUN-95', 202, 'F');
    
    insert into employee (emp_id, emp_lastname, emp_firstname, emp_dob, emp_address, emp_area_code, emp_town, emp_phone, emp_start, emp_mgr_id, emp_sex)
    values (301, 'Dagmar', 'Davinda', '12-APR-58', '12, The Crescent', 'SL1 1HG', 'Slough', '01628-764234', '24-JUN-95', 101, 'F');
    
    insert into employee (emp_id, emp_lastname, emp_firstname, emp_dob, emp_address, emp_area_code, emp_town, emp_phone, emp_start, emp_mgr_id, emp_sex)
    values (302, 'Presley', 'Percy', '30-OCT-62', '1 Jubilee Close', 'SL5 23F', 'Maidenhead', '01628-834582', '15-JUL-95', 301, 'M');
    
    insert into employee (emp_id, emp_lastname, emp_firstname, emp_dob, emp_address, emp_area_code, emp_town, emp_phone, emp_start, emp_mgr_id, emp_sex)
    values (303, 'Perry', 'Philippa', '24-FEB-71', '23 Rice Hill', 'SL3 12S', 'Maidenhead', '01628-567231', '28-SEP-96', 302, 'F');
    
    insert into employee (emp_id, emp_lastname, emp_firstname, emp_dob, emp_address, emp_area_code, emp_town, emp_phone, emp_start, emp_mgr_id, emp_sex)
    values (304, 'Hubert', 'Henri', '13-DEC-69', '5 Grand Lane', 'SL3 12S', 'Maidenhead', '01628-243535', '17-APR-96', 302, 'M');
    
    insert into employee (emp_id, emp_lastname, emp_firstname, emp_dob, emp_address, emp_area_code, emp_town, emp_phone, emp_start, emp_mgr_id, emp_sex)
    values (305, 'Adamson', 'Anita', '12-OCT-69', '24 Loose Lane', 'SL4 23D', 'Cookham', '01628-782364', '15-FEB-96', 301, 'F');
    
    insert into employee (emp_id, emp_lastname, emp_firstname, emp_dob, emp_address, emp_area_code, emp_town, emp_phone, emp_start, emp_mgr_id, emp_sex)
    values (306, 'Beaver', 'Bertie', '12-MAR-72', '223 Grange Hill', 'SL2 67E', 'Windsor', '01628-187632', '13-JAN-96', 305, 'M');
    
    insert into employee (emp_id, emp_lastname, emp_firstname, emp_dob, emp_address, emp_area_code, emp_town, emp_phone, emp_start, emp_mgr_id, emp_sex)
    values (307, 'Motson', 'Mervin', '22-DEC-74', '67 Blows Down', 'SL5 45G', 'Cookham', '01628-198371', '17-JUN-96', 305, 'M');
    

Scripts to create and populate the flattened version of the HR table, Oracle syntax

  • Create flattened table
    create table emp_flat
    (emp_lvl_1 varchar2(20)
    ,emp_lvl_2 varchar2(20)
    ,emp_lvl_3 varchar2(20)
    ,emp_lvl_4 varchar2(20)
    );
  • Populate flattened table.
    Only four levels are supported.
    Starting point (Noakes) is hard-coded.

    insert into emp_flag (emp_lvl_1, emp_lvl_2, emp_lvl_3, emp_lvl_4)
    select a.emp_lastname
    ,      b.emp_lastname
    ,      c.emp_lastname
    ,      d.emp_lastname
    from employee a
    ,    employee b
    ,    employee c
    ,    employee d
    where a.emp_id = b.emp_mgr_id(+)
    and b.emp_id = c.emp_mgr_id(+)
    and c.emp_id = d.emp_mgr_id(+)
    and a.emp_id = 101;
  • Create Snowflake Tables
    create table emp_level_01
    (emp_id number(5)
    ,emp_lvl_1 varchar2(20));
    
    create table emp_level_02
    (emp_id number(5)
    ,emp_mgr_id number(5)
    ,emp_lvl_2 varchar2(20));
    
    create table emp_level_03
    (emp_id number(5)
    ,emp_mgr_id number(5)
    ,emp_lvl_3 varchar2(20));
    
    create table emp_level_04
    (emp_id number(5)
    ,emp_mgr_id number(5)
    ,emp_lvl_4 varchar2(20));
  • Populate snowflake tables
    Only four levels are built, each starting from the prior table.
    Starting point (Noakes) is hard-coded.

    insert into emp_level_01 (emp_id, emp_lvl_1)
    select emp_id, emp_lastname
    from employee
    where emp_id = 101;
    
    insert into emp_level_02 (emp_id, emp_mgr_id, emp_lvl_2)
    select e.emp_id, e.emp_mgr_id, e.emp_lastname
    from employee e, emp_level_01 e1
    where e.emp_mgr_id = e1.emp_id;
    
    insert into emp_level_03 (emp_id, emp_mgr_id, emp_lvl_3)
    select e.emp_id, e.emp_mgr_id, e.emp_lastname
    from employee e, emp_level_02 e2
    where e.emp_mgr_id = e2.emp_id;
    
    insert into emp_level_04 (emp_id, emp_mgr_id, emp_lvl_4)
    select e.emp_id, e.emp_mgr_id, e.emp_lastname
    from employee e, emp_level_03 e3
    where e.emp_mgr_id = e3.emp_id;
    

16 Responses to “Universe Models For Recursive Data Part III: Alias Versus Flattened”

  1. Comment by Andreas (Xeradox)

    I am wondering how BOE XI 4 (Aurora) will handle hierarchies (RDBMS and OLAP sources). I would expect some new features, as hierarchies are a base functionality in any SAP BW system for example.

    Otherwise kudos for your work, Dave!

  2. Comment by Dave Rathbun

    From what I have been lead to believe, Webi XI 4.0 will handle SAP hierarchies natively. That will be a huge plus.

  3. Comment by Sriraman

    Dave,

    What happen if hierache level changes dynamically

    e.g
    At jan mon (A->B->C->d) A is the lead
    At Feb month B left the job and E is added for B (A->E->C->d)

    now if i want to calculate sales done by D then how drill function works for jan and Feb. Hope you can understand my question.

    Thanks,
    Sriraman

  4. Comment by Dave Rathbun

    You would need to add some sort of effective-dating strategy to your hierarchy, which is not covered in this series of posts at all.

  5. Comment by Sriraman

    Thanks dave for your quick response and it is great post for us which we waited for long days.Hierarchies is the huge problem for me and your post which helps me to explore more.

    It is great site for all BO developers.

    Thanks,
    Sriraman

  6. Comment by David

    I read the original presentation and have a question is about Ancestor/Descendant. In the CON’s you said “Does not support standard drilling technique”. Can you give some hints as to how we can do drilling? I was looking an Universe only solution to handle the drilling.

    Thanks,
    David

  7. Comment by Dave Rathbun

    Hi, David, thanks for your question. I’ll talk more about this when I get the details posted, but basically the issue is that for standard drilling you have to get your objects into different columns. I can drill from Year to Quarter to Month because the data is in three different columns, and therefore three different objects. My hierarchy has been flattened. With the ancestor model, no flatting has taken place. I have columns for ancestor, parent, and child, but I don’t drill from ancestor to parent… it doesn’t work that way.

    I have since been told (and shown) that Crystal can use this model to drill. But Web Intelligence and Desktop Intelligence cannot.

  8. Comment by Salim Jaffer

    Hi David – I too looked at your original presentation and had a question about the Ancestor/Descendant option. I need to make a prompt out the hierarchies, this way the user can pick what node they want to start from. I would like to make this prompt appear the way a hierarchy prompt displays (tree look). Is there a way to be able to do this?

  9. Comment by Dave Rathbun

    Hi, Salim, thanks for your question. We were able to do this (deliver a prompt in a tree structure) using the Tree Traversal design, which I have not published yet. You can review it in the presentation if you have not already.

    I’m not sure the ancestor model would lend itself well to that structure.

  10. Comment by Joshua

    Hi,
    I have question regarding the ‘unbalanced hierarchies’. After creating the snow flake tables how do I join them to a fact table that has emp short name. Do i join them at each level of the each of the snowflake tables to the fact table or do i create short cut joins? Thanks.

  11. Comment by Dave Rathbun

    With a snowflake structure you do not join anything but the lowest level to the fact. If you do, you are losing the hierarchy. Every employee is present in the first level, and they would join to the fact. The second level of the fact includes the next level up of the hierarchy from the first level (managers). The third level includes the top level managers. It’s very much like aliases in that each level has to join to the level below it to ultimately get to the fact.

  12. Comment by Joshua

    Thanks Dave,
    In the case of flattened structure for ‘unbalanced hierarchies’ how do I join them to a fact table. Do i join them at each level to the fact table or do i create short cut joins? I would like to have drill capability too. Thanks Joshua.

  13. Comment by David Foster

    Hi Dave,

    Good post, looking forward to the subsequent posts. Any idea when we should expect them?

  14. Comment by Amol m

    Hey Dave,
    I was able to look at your slides and see how to setup a ancestor model. The question I have is how to I setup my dimension objects? At the end of the day I want to setup cascading prompts so the end user can select the correct level. We have a ragged hierarchy that goes for 7 levels. That tells me I need 7 objects to setup the cascading in bobj. Do I need to create 7 alias of the ancestor model table? if so how should they be joined? And finally how does it join back to the fact?

    Thank you again I have learned so much from you.

  15. Comment by Chinmay

    Hello Dave,

    Again superb post from your side. Highly appreciate your efforts.

    I have gone through all the three presentations. However in your presentations, you haven’t mentioned anything about bridge table as a possible solution to resolve such issues.

    Could you please share your views about using Bridge table in recursive data mart model building ?

    Regards,
    Chinmay Athavale

  16. Comment by Chandra

    Hi Dave,

    Thank you very much for this wonderful information on handling the recursive data for universe design, I have a requirement for designing a universe for Bill Of Materials (BOM) where it has almost all complexities you have mentioned. I noticed the last line in this blog “In the next post I will talk about the ancestor model and how we used it at a manufacturing client”, but I don’t see a continued post on the same. It would be really helpful if you could post more information on this as well as any improvements you have noticed with universe designer to handle the recursive data (as almost 7 years passed since this post).

    Regards,
    CK