simply adding a new alias.
In that way you have a second instance of the table and you can add a different join.
Okay, I will try it,
by they way - is it the same like when I would duplicate table instead of creating alias?
Duplicate a table? You mean having twice the same physical object? Because that's exactly what the alias is.
If you have a physical table TBL1 and you create 2 aliases of it: TBL1_ALIAS1 and TBL1_ALIAS2. If you model these 2 aliases (I'm one of those advising to always create aliases on all your physical tables and model joins only on aliases and not use the physical table itself) the physical query generated will use the same table twice in the query.
I would not duplicate the physical table itself but just create alias.
I was just wondering what would be the difference between duplicating table (right click>duplicate) and creating alias (right click>new object>alias).
1 person found this helpful
An alias as the same columns of its "parent" (physical table), so it's easier from that point of view as you only manage column types in one place.
A duplicated table is a completely independent object, with the problem that the physical name you see isn't the real name of the table in the DB but you will need to look in the properties of the table to see to which table it's linked on the DB, so more complicated to know what data you have in front of you.
Till now I still didn't had the need to duplicate a physical table a single time, aliases always did the job.
I also like to use aliases because I can easily point an alias to a different physical table than the original one without losing keys and joins. I often do that when I want to test a different logic sourcing the table in the DB: I have 2-3 copies of the table with same structure but different data inside, I have all these tables as objects in OBIEE and I just move my alias from one to another (and not hiding it by using a physical table name different than the real name in the DB).
The "alias everything" or "don't alias by default" is a debate with pros and cons and I don't think it's possible to find THE answer to it, I personally prefer alias as it keeps things more separate and transparent: my physical tables have the real name of the object in the DB so I easily know what I have in front of me, and I use aliases to be able to call that table differently based on my need of the moment....
Thanks for your opinion.
But when trying this solution, another issue came up.
I added alias of DateTable - DateTable2. Then I made two joins:
1. From "DateTable"."date" to "UserTable"."ActivationDateOfUser"
2. From "DateTable2"."date" to "UserTable"."DeactivationDateOfUser"
[UserTable is Fact table]
The thing that my TimeHierarchy is based on DateTable1,
so when I make report that contains TimeHierarchy and DeactivationDateofUser it shows wrong data. On the other hand it works with ActivationDateOfUser.
It seems like data displaying does not work with join on alias table or something.
That's a different problem and depend on your model and how it looks like: what are you trying to do exactly? Do you want to see users by date or date by users?
When you say that you want to see activation and deactivation date of user in the time hierarchy what does it mean? You want to have the user as attribute of your time hierarchy?
As you have a time hierarchy you already have dates, so the point is more on what is the FACT between your tables and what dimensions do you expect to use ...
Well, I think I need to reconsider my model.
As it regards another issue, I'll close this topic and possibly open another one in future.
Thanks for help.