Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Students Click Here

How to handle self-joining tables in source?

How to handle self-joining tables in source?

How to handle self-joining tables in source?

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
5555             6666
6666             7777

RE: How to handle self-joining tables in source?

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

RE: How to handle self-joining tables in source?

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

RE: How to handle self-joining tables in source?

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.

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members! Already a Member? Login

Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close