How to handle self-joining tables in source?

[Guest_imported]

We had a case where the source system is set up with a self-joining table. Basically it works like this, the table is Chart of Accounts, there is a field for the Ledger Account, and a field for the Parent Ledger Account. The parent ledger account has a foreign key back to the same table linking to the ledger account field. This allows for any number of levels in the chart of accounts. How can this type of scenerio be built into MicroStrategy?

Sample Data:
Chart of Accounts
Ledger Account Parent Account
1111 2222
2222 3333
3333
4444
5555 6666
6666 7777
etc

 

[nlim]

You'll need to use version 7i with the table aliasing function. CHeck out their docs.

 

[fukudahs]

You can find out a guideline to do this in TN4000-7X0-0450 -How to simulate a self-join using views in MicroStrategy Desktop 7.x

 

[jfinerfrock]

MSTR's solution to this problem prior to 7i(they refer to this as a recursive table) is to use views of the recursive tables. One big advantage to using this method was that we could filter the views so that a parent only included those records that had a child. The advantage here was that if we ran a report that just listed the parent and you didn't want to see the children (no metrics) you didn't need to bring a filter into the report. We utilized this method for our implementation and it worked well.

Using the aliasing function in MSTR 7i is a viable solution however there are drawbacks. The first is that unlike views, the column names are the same so you may end up with some inadvertent joins unless you go into the attributes and set up the manual relationships over the automatic. The second is that you get a mirror image of the recursive table with no filtering. So if you put the parent attribute on a report and the child attribute on another report without metrics then the lists will be exactly the same.

One last thing to keep in mind with recursive tables, (an Employee Table is a good example.) Say that you have a sales team of (sales numbers included) Jim ($3.00), Pat ($5.00), and Tom ($4.00). Jim and Pat work for Sue and Tom works for Jon. When you want to see the total sales for the managers, Jon’s numbers are $4.00 and Sue’s numbers are $8.00. That works well. Now understand that as well as being a manager Jon is also a salesman. His numbers are $9.00. Unfortunately, even though Jon’s numbers and Tom’s numbers should be added together to get Jon’s total numbers they will not. Jon will be reported under the grandparent manager and that manager will show up in the same attribute as the parent attribute. I hope that I did not confuse anyone more but we have been wrestling with this for some time now.