Alias shortcut vs copying table

[robblob]

I have built a few framework models where I have used an alias shortcut to make multiple joins to a table, and I have also copied tables instead to accomplish the same thing.  My question is, is there a reason to do one vs the other from a performance and/or operational standpoint?  From what I have seen, there really is no difference in the results that I have seen based on the two approaches.  I just want to make sure I'm using the most efficient method in order to get the most accurate and timely results. 

[MFGF]

I have built a few framework models where I have used an alias shortcut to make multiple joins to a table, and I have also copied tables instead to accomplish the same thing.  My question is, is there a reason to do one vs the other from a performance and/or operational standpoint?  From what I have seen, there really is no difference in the results that I have seen based on the two approaches.  I just want to make sure I'm using the most efficient method in order to get the most accurate and timely results.

Hi,

If you use an alias shortcut, there is only one query subject for you to have to update if the underlying table changes. If you make copies of the query subject, you'd need to update all of them if the underlying table structure changes, and it's not always obvious or easy to figure out which ones you need to update in the potentially huge array of other query subjects. So you can see that alias shortcuts can save you a lot of work in maintenance later on. I'd take this option every time :)

MF.

[robblob]

Hi,

If you use an alias shortcut, there is only one query subject for you to have to update if the underlying table changes. If you make copies of the query subject, you'd need to update all of them if the underlying table structure changes, and it's not always obvious or easy to figure out which ones you need to update in the potentially huge array of other query subjects. So you can see that alias shortcuts can save you a lot of work in maintenance later on. I'd take this option every time :)

MF.

Thank you very much!  That was the missing piece of the puzzle for me to really understand the different.  Looks like I have some (a lot) updates to make.

[robblob]

Hi,

If you use an alias shortcut, there is only one query subject for you to have to update if the underlying table changes. If you make copies of the query subject, you'd need to update all of them if the underlying table structure changes, and it's not always obvious or easy to figure out which ones you need to update in the potentially huge array of other query subjects. So you can see that alias shortcuts can save you a lot of work in maintenance later on. I'd take this option every time :)

MF.

I think I see the problem now with why an alias shortcut won't work.  Each join has different filtering criteria, but it appears that you can't set a filter for an alias shortcut.  It's also possible that I just haven't stumbled upon how to do it yet, but I may have hit a snag.

[MFGF]

I think I see the problem now with why an alias shortcut won't work.  Each join has different filtering criteria, but it appears that you can't set a filter for an alias shortcut.  It's also possible that I just haven't stumbled upon how to do it yet, but I may have hit a snag.

Hi,

Wouldn't you just set up the join filtering criteria as part of the join? There's an ellipsis (...) button on the right when creating/editing a join that allows you to define the join as an expression. Your join filter criteria can be defined here.

Cheers!

MF.

[robblob]

Wouldn't you just set up the join filtering criteria as part of the join? There's an ellipsis (...) button on the right when creating/editing a join that allows you to define the join as an expression. Your join filter criteria can be defined here.

Interesting.  I will have to experiment with this.  I've never modified the join expression before, so hopefully it's straight forward.  Will report back with my findings.

[robblob]

Wouldn't you just set up the join filtering criteria as part of the join? There's an ellipsis (...) button on the right when creating/editing a join that allows you to define the join as an expression. Your join filter criteria can be defined here.

Alright, so I think I have it figured out, but I'm still not sure it makes perfect logical sense.  Here is an example to hopefully put it into perspective.

Say you have an order and three corresponding reference numbers assigned to it.  You have a table that stores order details and a table that stores reference numbers where the order number is your key, and you have reference types of 'A', 'B', and 'C' that have to be set as a filter on the join.  I could join the reference table to the order table three times while copying the table which leads to issues with maintenance as you have already pointed out.  The other solution is to join to the order table four times, once to the original table with no filter but a join on the order number.  Then, three more alias shortcuts with "AND TYPE = 'A'" so on and so forth to get the different reference types.  You have to have an additional join because if I set a filter on the first table, the alias will inherit the filter.

Does this seem like the right method for doing this?

To further complicate the matter, what if my join is dependent upon the reference type?  So I have three different reference fields in my order table, but I have to join them to one reference field in the reference table and set my filter.  I still have to create that first join to establish the relationship prior to creating the alias shortcut.  I can override the join with the shortcut, but do I experience any kind of negative feedback by doing so since all aliases are based on the original join?  Hopefully that made sense.

[MFGF]

Alright, so I think I have it figured out, but I'm still not sure it makes perfect logical sense.  Here is an example to hopefully put it into perspective.

Say you have an order and three corresponding reference numbers assigned to it.  You have a table that stores order details and a table that stores reference numbers where the order number is your key, and you have reference types of 'A', 'B', and 'C' that have to be set as a filter on the join.  I could join the reference table to the order table three times while copying the table which leads to issues with maintenance as you have already pointed out.  The other solution is to join to the order table four times, once to the original table with no filter but a join on the order number.  Then, three more alias shortcuts with "AND TYPE = 'A'" so on and so forth to get the different reference types.  You have to have an additional join because if I set a filter on the first table, the alias will inherit the filter.

Does this seem like the right method for doing this?

To further complicate the matter, what if my join is dependent upon the reference type?  So I have three different reference fields in my order table, but I have to join them to one reference field in the reference table and set my filter.  I still have to create that first join to establish the relationship prior to creating the alias shortcut.  I can override the join with the shortcut, but do I experience any kind of negative feedback by doing so since all aliases are based on the original join?  Hopefully that made sense.

Hi,

No - this still sounds too complicated. Your first (and original) Reference query subject should have no embedded filter in the query subject. You join it to your Order Details query subject, and in the join you convert to an expression and add the and [Reference Type] = 'A' syntax. Create two alias shortcuts of the Reference query subject, and join these using the same approach, but with B and C respectively as the reference types. That way you end up with one original query subject and two shortcuts.

MF.

[Kiran P]

It never occurred to me that we can add filter condition in a join definition. Interesting. Thanks for providing this tip.

Regards
Kiran

Sent from my ZUK Z2132 using Tapatalk

[MFGF]

It never occurred to me that we can add filter condition in a join definition. Interesting. Thanks for providing this tip.

Regards
Kiran

Sent from my ZUK Z2132 using Tapatalk

It doesn't cover all eventualities (eg where you need to report off just that one query subject and see filtered results) but where you have a situation such as the above where you're joining from another table to see different variations of the reference number, it seems like the simplest approach :)

MF.

[robblob]

You join it to your Order Details query subject, and in the join you convert to an expression and add the and [Reference Type] = 'A' syntax. Create two alias shortcuts of the Reference query subject, and join these using the same approach, but with B and C respectively as the reference types. That way you end up with one original query subject and two shortcuts.

MF.

Sorry to bring this back up so long after the original post, but this approach doesn't seem to be working for some reason within my model.  I have pulled in the original unfiltered table, created alias shortcuts for each join, established the relationship to each alias and modified the join expression with "AND REFTYPE = 'X'".  It accepts the expression, but when testing the data items from these query subjects, it completely ignores the additional syntax.  Even when viewing the SQL it makes no mention of the modified expression.  I know it's hard for you to provide meaningful insight without seeing the expression within the model, but figured I would bring it up anyway.  It seems like it should work since it accepts the syntax and it's very straightforward and basic.  Anyway, just wanted to see if you had additional feedback or could point me towards a possible alternative solution. 

[rockytopmark]

FWIW, I have a PMR opened with IBM regarding Alias Shortcuts and their joins being ignored... I think a bug may have been introduced somewhere between 10.2.2 and 11.0.7, as our 10.2.2 model with Alias Shortcuts produces the expected results (both data and MDX/SQL) while the exact same model, upgraded to 11.0.7 does not... full outer joins showing up where an inner join is defined in the model.