BOB: Business Objects Board
Not endorsed by or affiliated with SAP

Register | Login 

Follow BOB on Twitter! 
Follow BOB on Twitter! (Opens a new window)  

General Notice: No events within the next 45 days.

[Webi 4.x] need to Union together a spreadsheet with database data


 
Search this topic... | Search Web Intelligence... | Search Box
Register or Login to Post    Forum Index -> Building Reports -> Web Intelligence  Previous TopicPrint TopicNext Topic
Author Message
gadsden_consulting
Principal Member
Principal Member



Joined: 17 Jun 2015
ASUG Icon
Posts: 210
Location: Baltimore, MD


flag
PostPosted: Thu Mar 14, 2019 1:42 pm 
Post subject: need to Union together a spreadsheet with database data

I have a list of international graduates before 1970 in Excel, and all the international graduates since 1970 in Oracle.

The pre-1970 list has Name, Year Grad, Country, and Region

Oracle data does not have Region. But I populate Region with a third dataset (spreadsheet) of Country / Region (with a merged dimension). At this point I have two data sets in WebI that can be (logically) UNION'ed

Is this possible? Hope this makes sense.
Back to top
isingdeadpeople
Forum Member
Forum Member



Joined: 28 Aug 2018

Posts: 21



PostPosted: Sun Mar 17, 2019 8:52 pm 
Post subject: Re: need to Union together a spreadsheet with database data

Hi gadsen_consulting

The only way you can do a true union is if the dataset was all coming from the same source - you could use a combined query.

So, a workaround is the following. I'm assuming you probably already know how to do all of the following, but this is here for those who are still coming to grips with Webi.

Forgive the assumptions here:
    you've got access to the query design via the webi app
    there's not point/you can't get all of this data into Oracle
    you've chosen not to dump your Oracle data into Excel & do it via Excel


So:

    you've loaded your pre-1970 spreadsheet into webi/business objects
    you've loaded your country/region spreadsheet into webi/business objects
    you've got a dataset in Oracle with your post 1970 data



You've obviously created a report in webi with your post-1970 data
I'm not sure how much of the following you've done - so bear with me.
You can create another query to access the excel spreadsheets. Note that you have to do this via the webi app & not the html version. From Data Access tab, click on Edit button. Click on Add Query and select From Excel. Do this for both spreadsheets.

If you've already created a merged dimension between the pre-1970 data & the Country/Region, you should be able to create merged dimensions for Year & Country between the pre-1970 data & the Oracle data.
Create a block with these new merged dimensions (eg comboYear and comboCountry).
Depending on the exact field types, you can either create variables to handle Region - note that the Qualification may need to be a Dimension or a Detail.
Back to top
gadsden_consulting
Principal Member
Principal Member



Joined: 17 Jun 2015
ASUG Icon
Posts: 210
Location: Baltimore, MD


flag
PostPosted: Mon Mar 18, 2019 8:54 am 
Post subject: Re: need to Union together a spreadsheet with database data

isingdeadpeople,

thank you! Great assessment with little info.

Quote:
The only way you can do a true union is if the dataset was all coming from the same source - you could use a combined query.

- yes, I came to that conclusion

Quote:
the assumptions:
1. you've got access to the query design via the webi app
2. there's not point/you can't get all of this data into Oracle
3. you've chosen not to dump your Oracle data into Excel & do it via Excel

1. correct
2. I could get it the pre-1970 data into Oracle, but given our environment not likely
3. This is the chosen option, since the "Oracle" data doesn't change often, basically at graduation times and when someone leaves prior to grad. This actually worked great, since it's a relatively static data source, and the user was ok with maintaining the post-1970 data in Excel

Quote:
you should be able to create merged dimensions for Year & Country between the pre-1970 data & the Oracle data.
Create a block with these new merged dimensions (eg comboYear and comboCountry).

- I did the merged dimensions, so I can populate WebI with Region (from spreadsheet # 2) to the Oracle data.
- BUT, I ran out of tricks here ... If I have a columns comboCountry and comboYear, then how do I add comboStudent? As I see it, it's two objects that can't be smashed into one.

I did complete the job with the one spreadsheet, but thanks for putting the effort into this, and would like to see if you have thoughts on comboStudent?
Back to top
isingdeadpeople
Forum Member
Forum Member



Joined: 28 Aug 2018

Posts: 21



PostPosted: Tue Mar 19, 2019 1:38 am 
Post subject: Re: need to Union together a spreadsheet with database data

Hi gadsden_consulting
Glad you've sorted it.

However just to revisit the 'student' part of it - I'm assuming that the student is in both the pre-1970 & the post-1970 data?

Since you've got a merged dimension between the pre-1970 & the post-1970 data (eg Year & Country), you should be able to create a variable to contain the student data.

To make it a bit clearer - once there's a link between the datasets, you can reference all the other fields in it.

For example, create another variable, comboStudent. It would read something like this:
if [comboYear]=[pre1970].[Year] then [pre1970].[student] elseif [comboYear]=[post1970].[Year] then [post1970].[student] else "weep into keyboard".

Sometimes if you've only got a few fields, Excel can be the easier solution.
Back to top
gadsden_consulting
Principal Member
Principal Member



Joined: 17 Jun 2015
ASUG Icon
Posts: 210
Location: Baltimore, MD


flag
PostPosted: Tue Mar 19, 2019 8:18 am 
Post subject: Re: need to Union together a spreadsheet with database data

isingdeadpeople,

that's it! I didn't even think of that, so thanks! good on the humor too icon_biggrin.gif

Quote:
I'm assuming that the student is in both the pre-1970 & the post-1970 data?


The object student is in both datasets, but they are mutually exclusive datasets.

But you got it, I'm going to give it a try ... thanks!
Back to top
Display posts from previous:   
Register or Login to Post    Forum Index -> Building Reports -> Web Intelligence  Previous TopicPrint TopicNext Topic
Page 1 of 1 All times are GMT - 5 Hours
 
Jump to:  

Index | About | FAQ | RAG | Privacy | Search |  Register |  Login 

Get community updates via Twitter:

Not endorsed by or affiliated with SAP
Powered by phpBB © phpBB Group
Generated in 0.0298 seconds using 17 queries. (SQL 0.0029 Parse 0.0009 Other 0.0260)
CCBot/2.0 (https://commoncrawl.org/faq/)
Hosted by ForumTopics.com | Terms of Service
phpBB Customizations by the phpBBDoctor.com
Shameless plug for MomentsOfLight.com Moments of Light Logo