13 Replies Latest reply on Sep 8, 2017 3:39 PM by Christian Berg

    OBIEE - Multi level hierarchy into single column

    3459307

      Hi,

       

      I have a multi level tree account table with say parent level at level1 and leaf level in any of the child levels say level20. My fact table has data only for leaf level. My requirement is to aggregate all the levels into single column to show in report. For example, below is my table structure is

       

      Acct Dim Table:

       

      ACCT_SKEYACCT_LEVEL1ACCT_LEVEL2…….ACCT_LEVEL20

       

      Fact Table:

      ACCT_SKEYFact1

       

      Thanks in advance. Nag

        • 1. Re: OBIEE - Multi level hierarchy into single column
          Christian Berg

          3459307 wrote:

           

          My requirement is to aggregate all the levels into single column to show in report.

           

          What do you mean? A hierarchical column where you can drill into the data?

           

          Please be precise about your requirements so proper help can be provided.

          • 2. Re: OBIEE - Multi level hierarchy into single column
            Robert Angel

            I concur with Christian, more detail please.

             

             

            OBIEE does not provide rollup aggregation, if your data source is a database table / view then you would need to add the additional levels as explicit column(s) in that table, then default sum aggregation behaviour on those columns will do the rest for you, and if you can put them together in a data hierarchy then you will also get a drill behaviour similar in principal to MOLAP.

             

            Alternatively but less performant you can also join to your fact table's base level members to a hierarchy table, base to base, and make the table self join in tree walking fashion, not as fast as the above option which flattens your hierarchy, but if you don't have the luxury of changing your ETL and can live with the performance then it is an option.

            • 3. Re: OBIEE - Multi level hierarchy into single column
              3459307

              Thanks Christian, Robert for your quick reply.

               

              My Account table structure:

               

              My Fact table has data at leaf level. I am trying to create a report looks as below

               

               

              I hope I gave the requirement , let me know if it is not clear.

              Thanks,

              Nag

              • 4. Re: OBIEE - Multi level hierarchy into single column
                Christian Berg

                So you just have a hierarchy that's ragged and skip-level. Still it's just a normal hierarchy object in the RPD:

                 

                https://www.rittmanmead.com/blog/2010/07/obiee-11gr1-support-for-ragged-skip-level-value-based-hierarchies/

                • 5. Re: OBIEE - Multi level hierarchy into single column
                  3459307

                  Thanks again Christian. I looked into this, my requirement is little different, i.e., I don't want hierarchal view and then click + symbol to expand to see further. When I place acct column with measure, it should aggregate to all the levels and also we are going to create filters using this column.

                   

                  Thanks Again for you time and inputs.

                  Regards,

                  Naga

                  • 6. Re: OBIEE - Multi level hierarchy into single column
                    3459307

                    Hello gurus any suggestions?

                    • 7. Re: OBIEE - Multi level hierarchy into single column
                      Christian Berg

                      What you "want" is flattening the hierarchy...

                       

                      Then again I put "want" into quotes sicne that already smells like you're already fixed on HOW you want to do things (compared to WHAT you want to do - i.e. fulfilling the requirement / business need).

                       

                      Also your "example" with the "measures" doesn't help at all since your made up numbers look like they are non-additive and just show the value for a member in the level.

                       

                      tl;dr - if you can't describe your requirement precisely and correctly we will have a hard time helping you with any pertinent solution. Be precise.

                      • 8. Re: OBIEE - Multi level hierarchy into single column
                        asim cholas

                        only 1 measure value for all levels? as it joins like that

                        • 9. Re: OBIEE - Multi level hierarchy into single column
                          3459307

                          Hi Christian,

                          Apologies for the confusion and delayed response.  I don't have the solution yet.  Attached are my table structures and the expectation is to show all the Accts (child's and then respective parents). Hope it gives better understanding of requirement.

                           

                          Acct requirement.png

                          Thanks again,

                          Nag

                          • 10. Re: OBIEE - Multi level hierarchy into single column
                            3459307

                            Unfortunately that is the requirement.

                            • 11. Re: OBIEE - Multi level hierarchy into single column
                              Christian Berg

                              I still don't see how that is NOT just a hierarchical column and a measure next to it...

                              • 12. Re: OBIEE - Multi level hierarchy into single column
                                3459307

                                I agree, it is hierarchical column but user doesn't want to open each level. When report runs default all level should populate as shown above.

                                • 13. Re: OBIEE - Multi level hierarchy into single column
                                  Christian Berg

                                  How does that have any bearing on anything? If you open all the levels of a hierarchical column when BUILDING the analysis in Answers, then that STATE will be saved in the analysis definition and always rendered like that!

                                   

                                  The tool is around since some time so it actually know a thing or two and isn't the dumb "list reporting" tool most people use it as...

                                   

                                  It's all in the XML definition. Insisting on "different" approaches rather than using things as they are designed just wastes time and resources and pointlessly creates error sources and things you need to maintain and administer without any added value.

                                   

                                  <saw:report xmlns:saw="com.siebel.analytics.web/report/v1.1" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:xsd="http://www.w3.org/2001/XMLSchema" xmlVersion="201201160" xmlns:sawx="com.siebel.analytics.web/expression/v1.1">

                                     <saw:criteria xsi:type="saw:simpleCriteria" subjectArea="&quot;PC2&quot;">

                                        <saw:columns>

                                           <saw:column xsi:type="saw:hierarchicalColumn" hierarchyID="Segments" tableName="&quot;Customers&quot;" dimensionID="Customers" columnID="c7ccf64c77fb2dbe7">

                                              <saw:hierarchyLevels>

                                                 <saw:hierarchyLevel levelID="Total"/>

                                                 <saw:hierarchyLevel levelID="Cust Segment"/>

                                                 <saw:hierarchyLevel levelID="Detail"/></saw:hierarchyLevels></saw:column></saw:columns></saw:criteria>

                                     <saw:views currentView="0">

                                        <saw:view xsi:type="saw:compoundView" name="compoundView!1">

                                           <saw:cvTable>

                                              <saw:cvRow>

                                                 <saw:cvCell viewName="titleView!1"/></saw:cvRow>

                                              <saw:cvRow>

                                                 <saw:cvCell viewName="pivotTableView!1"/></saw:cvRow></saw:cvTable></saw:view>

                                        <saw:view xsi:type="saw:titleView" name="titleView!1"/>

                                        <saw:view xsi:type="saw:pivotTableView" name="pivotTableView!1" scrollingEnabled="false" rowsPerPage="50000">

                                           <saw:edges>

                                              <saw:edge axis="page" showColumnHeader="true"/>

                                              <saw:edge axis="section"/>

                                              <saw:edge axis="row" showColumnHeader="true">

                                                 <saw:edgeLayers>

                                                    <saw:edgeLayer type="column" columnID="c7ccf64c77fb2dbe7">

                                                       <saw:drillState>

                                                          <saw:drillStep drillType="down">

                                                             <saw:QDR>

                                                                <saw:staticMemberGroup>

                                                                   <saw:groupType>

                                                                      <sawx:columnRefExpr columnID="c7ccf64c77fb2dbe7"/></saw:groupType>

                                                                   <saw:hierarchyMembers>

                                                                      <saw:hierarchyLevelMembers>

                                                                         <saw:hierarchyLevel levelID="Total"/>

                                                                         <saw:members xsi:type="saw:integerMembers">

                                                                            <saw:value>1</saw:value></saw:members></saw:hierarchyLevelMembers></saw:hierarchyMembers></saw:staticMemberGroup>

                                                                <saw:target>

                                                                   <sawx:columnRefExpr columnID="c7ccf64c77fb2dbe7"/></saw:target></saw:QDR>

                                                             <saw:selectionGroups>

                                                                <saw:selectionGroup columnID="c7ccf64c77fb2dbe7" groupID="0"/></saw:selectionGroups></saw:drillStep></saw:drillState></saw:edgeLayer></saw:edgeLayers></saw:edge>

                                              <saw:edge axis="column" showColumnHeader="rollover"/></saw:edges>

                                           <saw:displayFormat>

                                              <saw:formatSpec/></saw:displayFormat></saw:view></saw:views></saw:report>

                                   

                                   

                                  Edit: not sure how that picture ended up inside the code