8 Replies Latest reply on Jul 30, 2015 3:28 PM by 2973433

    Impossible multiple join in physical layer

    2973433

      Hey,

       

      My problem regards joins in physical layer.

      But let's start from the beggining. I want to have report that will display me "ActivationDateOfUser", "DeactivationDateOfUser" (both belong to "UserTable") in the TimeHierarchy( its dimension based on DateTable).

      In order to display correct data I wanted to make two joins:

      1. From "DateTable"."date" to "UserTable"."ActivationDateOfUser"

      2. From "DateTable"."date" to "UserTable"."DeactivationDateOfUser"

       

      Unfortunately it turned out that in BI tool I can only create one join between two tables, so only one column will display correct data when connected to TimeHierarchy.

       

      How can I solve this situation?

        • 1. Re: Impossible multiple join in physical layer
          Gianni Ceresa

          Hi,

          simply adding a new alias.

          In that way you have a second instance of the table and you can add a different join.

          • 2. Re: Impossible multiple join in physical layer
            2973433

            Okay, I will try it,

            by they way - is it the same like when I would duplicate table instead of creating alias?

            • 3. Re: Impossible multiple join in physical layer
              Gianni Ceresa

              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.

              • 4. Re: Impossible multiple join in physical layer
                2973433

                I see,

                I was just wondering what would be the difference between duplicating table (right click>duplicate) and creating alias (right click>new object>alias).

                • 5. Re: Impossible multiple join in physical layer
                  Gianni Ceresa

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

                  1 person found this helpful
                  • 6. Re: Impossible multiple join in physical layer
                    2973433

                    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.

                    • 7. Re: Impossible multiple join in physical layer
                      Gianni Ceresa

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

                      • 8. Re: Impossible multiple join in physical layer
                        2973433

                        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.