8 Replies Latest reply: Feb 26, 2013 2:46 AM by BIDev RSS

    How to create the relationship between ESSBASE 11 and DM  in OBIEE 11G?

    BIDev
      Hi Experts,

      I have one requirement that there is one property table named 'Store Master' in DW,and it contains a lot of attribute, such as Open Date, Close Date, IS 24 Hour etc.
      But another data source is essbase and based on this source, I create all reports.
      In ESSBASE, it has one dimension and hierarchy Location, and it has four level, Country(L1),Region (L2),Province(L3),Store(L4)

      So I want to know how to create the relationship between Location (ESSBASE) and Store Master (DM).

      I try to create one relationship in physical layer between Gen4,Location and Store, then drag the open date and close date into Location Dimension in BMM,then Presentation Layer.
      When I drag column 'Open Date' ,'Gen4,Location ' and 'Sales' into reports, it will generate the following error message:

      State: HY000. Code: 10058. [NQODBC] [SQL_STATE: HY000] [nQSError: 10058] A general error has occurred. [nQSError: 43113] Message returned from OBIS. [nQSError: 43119] Query Failed: [nQSError: 14020] None of the fact tables are compatible with the query request Dim Region.Store Open Date. (HY000)

      However, when I remove the column 'Open Date'. it will be ok

      So what I missing the steps? Please help me. Thanks.
        • 1. Re: How to create the relationship between ESSBASE 11 and DM  in OBIEE 11G?
          sayak
          Hi ,

          U can review the steps from here :

          http://www.oracle.com/webfolder/technetwork/tutorials/obe/fmw/bi/bi11115//federated_data/federated_data.htm
          • 2. Re: How to create the relationship between ESSBASE 11 and DM  in OBIEE 11G?
            BIDev
            Hi sayak ,

            Thanks for your help.
            I want to create one properity table in DW and create the replationship, not the dimension table in ESSBASE and OBIEE.
            For example,
            I should create the relationship between store key in properity table and Gen4 in ESSBASE.
            • 3. Re: How to create the relationship between ESSBASE 11 and DM  in OBIEE 11G?
              Dhar
              Hi,

              Since your Store attributes are in relational DB, but Location hierarhy is in Essbase, you could use LOOKUP method to retrieve the attributes.

              You could bring your relational attribute table as LookUp table, and for the Store logical column of Essbase, you can use Lookup to get the Attributes.

              Refer to Using lookup tables concept with essbase data source for some similar steps.

              Another solution, which I see is by horizontal federation.

              Since, Store is the Lowest level in Location hierarchy and also in Store Attributes in relation DB, you could try creating a single logical table for both of them as below.

              1. Create Essbase Cube as is into the BMM.

              2. Now, pull the 'Store' column from relational DB onto the Gen5, Location column from Essbase. This action now creates, two logical sources for your 'Store' column.

              3. Pull the other attributes even from your relational DB.

              4. Remove all the Memnor and Member keys from the logical table.

              5. So whenever, you create a report with any of these attributes and your essbase measures, BI Server knows that this is the common dimension between the two sources and can map internally.

              Just give it a try, I am sure you will find your way through.Hope this helps,

              Thank you,
              Dhar

              Edited by: Dhar on Feb 25, 2013 1:49 PM
              • 4. Re: How to create the relationship between ESSBASE 11 and DM  in OBIEE 11G?
                BIDev
                Hi Dhar ,

                Thanks for your useful information.
                According to your second method,
                '2. Now, pull the 'Store' column from relational DB onto the Gen5, Location column from Essbase. This action now creates, two logical sources for your 'Store' column.'
                If the length from different data source is not same,such as 1001(DM),L_1001(ESSBASE), can I drag the 'Store' column from relational DB onto the Gen5, Location column from Essbase?
                I think it does not work.Right?
                So I select Store Attributes in relation DB and Location in ESSBASE in physical layer, then create the physical join, such as right("Hour Sales"."H_Sales".""."H_Sales"."Gen6,Location",4) = "Authorization".""."EDW"."T_EDW_MDM_STORE"."US_CODE", then drag the OPEN_DATE and CLOSE_DATE in relation DB to Location in ESSBASE in BMM,finially drag them into presentation layer.
                When I drag the OPEN_DATE ,CLOSE_DATE and Measure to report, it will generate the error message as below, how to solve this issue?(I believe it does not join the fact table)Thanks. Or is there any better method? Whether Lookup functionality can satify my requirement.Thanks.

                [nQSError: 43119] Query Failed: [nQSError: 14020] None of the fact tables are compatible with the query request Dim Location.Store Open Date. (HY000)
                • 5. Re: How to create the relationship between ESSBASE 11 and DM  in OBIEE 11G?
                  Dhar
                  >
                  '2. Now, pull the 'Store' column from relational DB onto the Gen5, Location column from Essbase. This action now creates, two logical sources for your 'Store' column.'
                  If the length from different data source is not same,such as 1001(DM),L_1001(ESSBASE), can I drag the 'Store' column from relational DB onto the Gen5, Location column from Essbase?
                  I think it does not work.Right?
                  Hi,

                  I am not sure if you are talking about the length(as in varchar(128)) of the member value being different in different sources, or the member itself is different in both the sources.

                  I am still assuming, that you are referring to the members not same in both the sources.If it is, the whole concept of federation is based on conforming dimensions. So, it needs that the same dimension information is present in both the sources and then only, you know we can analyze the numbers based on this dimension. So, either the dimension being different in both sources, or the members not present in both the dimensions might lead to incorrect numbers.
                  So I select Store Attributes in relation DB and Location in ESSBASE in physical layer, then create the physical join, such as right("Hour Sales"."H_Sales".""."H_Sales"."Gen6,Location",4) = "Authorization".""."EDW"."T_EDW_MDM_STORE"."US_CODE", then drag the OPEN_DATE and CLOSE_DATE in relation DB to Location in ESSBASE in BMM,finially drag them into presentation layer.
                  We create physical layer relationships, to send over the same relation to the underlying database during querying. So, creating a physical relationship between essbase cube and relation database would not help here.

                  When you set up this federation, BI Server sends individual queries to each source and maps the conforming dimension members internally.

                  Hope I was clear, and this helps.

                  Thank you,
                  Dhar
                  • 6. Re: How to create the relationship between ESSBASE 11 and DM  in OBIEE 11G?
                    BIDev
                    Hi Dhar ,

                    Whether the Lookup functionality can satify my requirement.
                    Since I can not use this functionality before.If it s ok,I should search this functionality
                    • 7. Re: How to create the relationship between ESSBASE 11 and DM  in OBIEE 11G?
                      Dhar
                      Hi,

                      Well, I think you can use LookUp too here. Refer to Using lookup tables concept with essbase data source for some similar steps on this functionality.

                      Hope this helps.

                      Thank you,
                      Dhar
                      • 8. Re: How to create the relationship between ESSBASE 11 and DM  in OBIEE 11G?
                        BIDev
                        Hi Dhar ,

                        Thanks very much.