Categories
- All Categories
- 15 Oracle Analytics Sharing Center
- 14 Oracle Analytics Lounge
- 213 Oracle Analytics News
- 42 Oracle Analytics Videos
- 15.7K Oracle Analytics Forums
- 6.1K Oracle Analytics Idea Labs
- Oracle Analytics User Groups
- 78 Oracle Analytics Trainings
- 14 Oracle Analytics Data Visualizations Challenge
- Find Partners
- For Partners
Merging the results of one analysis in to another
First time caller...
I've recently been given access to Oracle Business Intelligence 11.1.1.7 in order to pull off data and build analysis and dashboards etc I'm new to the world of OBIEE but have previous experience with SAP Business Objects.
What I want to do, and apologies if this has been asked on another post (I have searched), is pull in details from one analysis result in to another. Within Business Objects is what as simple as writing 2 queries, merging the common variables, and then pulling in data from both reports to combine.
What I have at the moment, as an example, is one analysis from subject area containing; Order Number, Order Value and Project Number. In another subject area I have my organisations hierarchy, for example; Project number, Business Unit and region. What I want to do is, based the common variable 'Project Number', is pull in the business unit and region from the second analysis in to the table on the first analysis so I have; Order number, order value, project number, business unit and region.
I don't want to export both analysis to Excel and do a vlookup, index/match etc as I want to pull everything in to one report from the start.
is this possible?
Answers
-
I think you are describing a union all - have a look at the subject area in answers analysis design, the control to add another data set from another subject area is there on the right hand side.
0 -
0 -
Sorry, on re-reading your requirement and don't think the union is what you are asking for.
You mean you have columns A,B,C,D and you want to add E,F,G from another subject area?
What you are asking for is only possible without changing the rpd if you have conformed dimensions between the two subject areas which allow you to add multiple subjects areas via the standard subject area selector to model the additional fields.
If this is not possible then it is possible via additional modelling in the rpd if you can add the group in question as a lookup table in the logical layer, provided there is clear logic to define the 'merge' / 'lookup' that you describe.
https://www.rittmanmead.com/blog/2010/08/oracle-bi-ee-11g-lookup-tables-sparse-and-dense-lookups/
https://gerardnico.com/dat/obiee/obis/logical_sql/lookup_table
Note - don't start to see lookup and particularly sparse lookup - as the answer to all your modelling problems - best is to solve the problem in the ETL and populate the fields into the dimensions, lookups WILL kill performance particularly if you start filtering on them in large data sets.
Note 2 - OBIEE is not query-centric nor should it ever be
On business objects versus OBIEE you are comparing what is essentially a sql tool with a Business Intelligence / data-centric tool. They are and always will be very different in functionality and philosophy.
0 -
Robert Angel wrote:On business objects versus OBIEE you are comparing what is essentially a sql tool with a Business Intelligence / data-centric tool. They are and always will be very different in functionality and philosophy.
Couldn't have said it better even if I tried. Never approach OBI like you were approaching BO - it simply is not the same thing at all
0 -
I do this type of union quite often, unless I'm not clear on the OP's requirements.
The first query brings in Project #, Order #, Order Value. Then add null columns of the data types matching Business Unit and Region, such as "cast(null as char)".
The second query brings in Project #, two null columns matching data types for Order # and Order Value, then Business Unit and Region.You can then either use summary Pivot Tables pivoted on Project # to produce a single line of results for each Project #, or create new columns at the header level.
(This is assuming that Project#, and Order# correspond to a single Business Unit and Region.)
0 -
Hi,
I see you "liked" some answers. The board convention is if your question is answered to Mark Correct / Helpful, or close your question using Assumed Answered if you are feeling less generous. Note 'helpful' does not close your question, only Correct or Assumed Answered does.
If you need further clarification on the question just ask.
0 -
Apologies, haven't had time to implement and/or test to make sure everything works as expected and therefore why I haven't closed the question. Both answers I have "liked" look promising and wanted to show my appreciation for the time & effort in trying to help me with my question.
0 -
Apologies, haven't had time to implement and/or test to make sure everything works as expected and therefore why I haven't closed the question. Both answers I have "liked" look promising and wanted to show my appreciation for the time & effort in trying to help me with my question.
0 -
Didn't mean to rush you, just thought you might not 'know the ropes' with your newbie status.
No apologies necessary!
0 -
Seems to me you need the dimensional hierarchy in the subject area that has the factual data ... use of information drives your design of both logical and physical data models.
Are you the BI developer or a business analyst? If the former, learn the modeling techniques needed ... if the latter, make the formal request to have the conformed dimension and it's hierarchy added to the subject area that has the facts. (why it's not there leads me to believe there are deficiencies in the BI layer of your implementation -- subject area planning is important and often overlooked)
0