Skip to Content
author's profile photo Former Member
Former Member

Working of Alias

Hi All,

i am working on BOXI R2, i have already existed universe with one fact table and many dimensions. there is no loops its working properly. i got new requirement on same universe, added the new table then i got loop. i resolved it by alias.

Existed

Assume tables like Fact F, Dimensions D1 and D2

joins F-- -D1 and F --


D2

New Requirement

new table D3

joins D1--


D3 and D2--


D3

Loop is D3---- D1


F-- -D2 -- -D3, i made D3 as alias. i resolved the loop like D3Alias
D1

F

D2
-----D3

My question is, if i select the fields from D3 what r the joins are picked.

If I am not clear, i will explain with better example.

Thanks.

Add a comment
10|10000 characters needed characters exceeded

Related questions

1 Answer

  • author's profile photo Former Member
    Former Member
    Posted on Nov 10, 2011 at 03:53 PM

    Hi Shahin,

    If I get your question, you want to know exactly what joins your query will pick if you use objects from D3 table which is also the table which you've aliased.

    I think first of all you, we need to understand that for practical purposes your D3_Alias table will work almost like a new table has been inserted in your Universe. So you will need to create objects from D3_Alias table seperately.

    This means, if you have created objects like say Name as D3.Name , Address as D3.Address, then if you were to pull these objects into your query the joins chosen would be the joins containing D3 and D2 (because you have joined D1 with the D3Alias table which is practically a different table). So if you wanted your query to choose the D1-D3 join path you would need to have a different set of objects modeled on D3Alias table. Eg. Alias Name as D3Alias.Name. Now if you were to pull in AliasName object the D1-D3Alias join path would be used.

    Let me give you a better example. If you have the TIME dimension table. And your fact table is FLIGHT_FACTS. Now in the FLIGHT_FACTS table I could have two columns - FlightTakeOff_Time and FlightLanding_Time. Both of these are TIME keys.

    So instead I alias my TIME dimension, so that I now have FLIGHT_TAKEOFF_TIME and FLIGHT_LAND_TIME and I would make seperate joins from my fact columns to these aliased tables. This would resolve the loop as well as enable me to create new objects.

    So my Flight Take Off object would be selected from FLIGHT_TAKEOFF_TIME alias table while Flight Landing Time object would be selected from FLIGHT_LAND_TIME alias table.

    Does this clear it up a little? It would be so much easier to just draw it on a piece of paper. 😊

    Btw..I'm assuming, you have a strong need to join D3 with D2 and D1...are you sure you just can't use something like a shortcut join instead?

    Edited by: Abhijith Mohan on Nov 10, 2011 5:06 PM

    Add a comment
    10|10000 characters needed characters exceeded

    • Former Member Former Member

      Hi Shahin,

      Sorry about the late response. But I think you're phrasing your question incorrectly.

      If my understanding is correct you do not need those four joins in your query. Instead you require the exact data set from the 4 tables that gives you the information you need.

      This is exactly why you would implement the aliases/contexts as specified in my earlier post. I'm more or less confident that the answer you get will be the correct one.

      Simply putting all four join conditions into a SQL will results in a SQL related bug, with your data getting chopped off.

      If you delve a little more into the functional aspects of the tables and the data they hold, I think you will find that you get what you require with the solutions provided.

Before answering

You should only submit an answer when you are proposing a solution to the poster's problem. If you want the poster to clarify the question or provide more information, please leave a comment instead, requesting additional details. When answering, please include specifics, such as step-by-step instructions, context for the solution, and links to useful resources. Also, please make sure that you answer complies with our Rules of Engagement.
You must be Logged in to submit an answer.

Up to 10 attachments (including images) can be used with a maximum of 1.0 MB each and 10.5 MB total.