Working of Alias

[Former Member]

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.

[Former Member]

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

[Former Member]

Hi Abhijith,

Thanks for your reply..........

again i have a confusion like which join i have to make SHORT CUT.

i will give u clear picture of my universe.

Table name fields

D1--

---

Financial_Period--

---

ID_Finance

D2----

---

Local_Currency -

---

Local Currency name

D3--

---

Currency_Conversion-----From Currency, to currency

F----

---

Fact_Financials

Loop--

---

D3-D1-FD2--D3

If i make D1----F is shortcut it is returning -

---

1 record

If i makeD2 - F is short cut it is returning--

---

4 records

With out resolving the loop -

---

1 record

Suggests me which one i need to make as short cut.

Thanks

[Former Member]

Hi Abhijith,

If possible please give reply to this.

Any one who have a solution for this please suggests me.

Thanks.

[Former Member]

Hi shahin,

This is not an easy question to answer since I'm not really sure what is the purpose behind your joins. For example, I can understand the reason for the join between Currency Conversion and Local Currency but no so much the link between Financial Period and Currency Conversion

I'm also not convinced that your dimensional model is the best. Since a dimension-dimension join without any actual snowflaking seems a bit weird.

Anyway my suggestions are:

1. If you want to use Short Cut Joins: Make both Dimension to Dimension joins as short cut joins. Ideally you should combine this with also making different contexts for you F-D1 and F-D2 joins. See then if you are getting your queries the way you intend them.

2. Don't use shortcut joins. And dump the idea of using an alias as well. Instead make D3-D1-F as one join context - calling it Financial Period and D3-D2-F as another join context- calling it Local Currency. Now see again if your queries are getting fired correctly. (You should get the same results as above if I've understood your question correctly)

3. Remodel your dimensional model. I would ideally flatten that whole structure. I don't see the need for that currency conversion table if you're just going to be mapping it to one standard currency. But this is a very complicated thing to do, and would require a larger understanding of your data model and business requirements. Probably hard to do on a forum thread.

Anyway, try the above. One of them should work. And maybe you can give a brief description about your data model; why you need these particular tables joined this way etc.

For now my recommendation is Method #2. When designing I normally tend to avoid Aliases as much as possible, unless I get a roleplaying dimension. (Flight Landing Time, Flight Takeoff TIme both linked to DIM_TIME)

Good luck

Abhijith

[Former Member]

Hi Abhijjith,

Thanks for your reply........

One small question, with out resolving the loop if i run the report 4 joins (which r in loop) will include in SQL right?

Is it possible to include 4 joins ( which r in loop) with resolving the loop?

Because i need those 4 joins in SQL query, when i pick the fields from tables ( which r included in loop).

Thanks.

Edited by: shahin13 on Nov 18, 2011 9:43 AM

[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.