BusinessObjects Board

Linking Data Providers: "Inner Join"

building reports desktopintelligence
#8

Hi T. Blom,

If you’re linking several dataproviders- you’ll have null values generated by several dataproviders. So what are we filtering out? Is it behaving as expected? Here’s how I resolve this questions:

  1. one create a tab with no filters and place a count on the main linked object (say customer id). Figure out what you want to filter out (nulls generated from what query).

  2. Duplicate the tab, create a filter using logic that removes the desired nulls; for example, if I wanted to remove nulls generated by dataprovider 4 and 5 I would use the following syntax:
    NotIsNull(<measure dataprovider 1>) And NotIsNull(<measure dataprovider 2>) and NotIsNull(<measure dataprovider 3>). Place a count on customer.

  3. compare tab 1 to tab 2.

You should only see customer id’s that are have values from queries 1,2 & 3, effectively removing null values generated by unique values from queries 4 and 5 .

I completely agree with you on the benefits on a union query. Likewise a minus and intersection are valuable tools, but often we want to cross dataproviders that don’t share the same connection string. And if time is of the essence, synchronization is the way to go.

Hope this helps,

Steve

SteveBickerton :canada: (BOB member since 2002-08-15)

#9

Hi folks,
I have two rather strange problems that came up and I am not sure if this is a version related bug. I do understand how reports with linked DP’s work and behave. The situations below just don’t seem to fall under the “normal” behaviour pattern of such reports.

Universe:
Relational, but it is kind of a “reverse star” since I have common dimension tables, but multiple fact tables, each supplying a fact/measure, thereby making contexts necessary for each measure (till we go to a true dimensional model).

Situation 1:

I’m running FC 5.1.6. One of my reports has data from two contexts and all common dimensions, except for one non-matching dimension. BO automatically creates two synchronized queries and shows the data in the crosstab as required. I get to see ALL the data from both queries, even though the product numbers in both queries don’t match 100%. In the data mgr, all the dimensions are linked automatically and the “unlink” button is greyed out for all my linked dimensions. Perhaps this is because these are synchronized queries.

My users whom I sent the report are running FC 5.1.4 and are seeing data filtered by an ‘inner join’ ie. they only see quantities of Products existing in both contexts (products in DP2 that also exist in DP1). This is a display issue only as the data for all products is visible in the Data Mgr for the second DP. What is going on here?:confused: There are NO filters in this report.

Situation 2:
Similar situation with a variation. I created a report with with 2 queries, each having 2 contexts i.e. in total we have 4 DP corresponding to the 4 measures. The data in the report is shown by each context, i.e. there are 4 crosstabs in the report built around a measure. Each block does NOT include any dimensions outside the defined context. Again, except for 1 or 2 dimensions, the other 7-8 are all common dimensions in the entire report. In this report, however the “Unlink” button is not greyed out for some of my dimensions. There’s no logic/pattern as to which ones are unlinkable and which are not. :confused:

Well, this report does use Global Filters on all the common dimensions like Product, Region, Channel. My user sent a slightly modified version to me complaining that even though they selected all values for each dimension in the filter, they could only see quantities of products that exist in both contexts. Data does exist in the Data Mgr for ALL products, it just doesn’t display in the block. I see their screen via NetMeeting and true enough all values are highlighted for all dimensions in the Format Global Filter window.

Here comes the strange part: I opened the same report they sent me and I can see that the data is missing. However, when I look at Global Filters, only some of the Product Numbers have been selected. I do a “select all” and I see the missing data in the report. I share my screen with the users and they are shocked! They exit out of BO, reopen the report they sent me, check global filters again, still same problem.

Users are convinced this is a bug…perhaps it is…I don’t know how this “inner join” is happening on their side, without the use of any Intersection queries or IsNull filters etc…

Anyone else with similar problem? Should I upgrade the users to 5.1.6 too?

regards,
Prakash

prakash (BOB member since 2003-03-19)

#10

That is correct.

Just to be more specific regarding BO terms:
You created 2 data providers (each of which spans 2 contexts and therefore BO creates 2 SQL statements for each data provider). Because of dimensions in the data provdirs, which are not common across the 2 contexts within each data provider, BO synchronizes the SQL statements within each data provider.

Whenever BO synchronizes SQL statements for a data provider you will see cubes in the Data manager, for example:
DP1.Cube1 and DP1.Cube2

In your scenario you end up with 2 data providers, 4 data cubes, and 4 SQL statements:
DP1.Cube1 and DP1.Cube2
DP2.Cube1 and DP2.Cube2

You should be able to manually link/unlink dimensions from DP1 to DP2

I am wondering if the same hapens when you just display an ordinary table instead of a crosstab? Or if you change the crosstab to be displayed in BusinessObejcts 3.1 crosstab format (under Format Crosstab, tab general)?

Andreas :de: (BOB member since 2002-06-20)

#11

That’s true, I guess I was equating cubes with data providers which is not correct.

I realize that, however I am concerned about the fact that all of the common dimensions are indeed linked in the data mgr across all 4 cubes, but only some can be unlinked. For example, the following objects (common dimensions) exist in all 4 contexts/cubes and appear as linked in the data mgr:
Product Number, Inv Org, Region, Product Model, Cycle Start Month, UDG, Sales Channel.
UDG and Cycle Start Month have values that comes from a common report prompt, so I can understand why they are unlinkable. The remaining objects can all be unlinked, except for Sales Channel dimension. There’s nothing special about the Sales Channel dimension, it is also the SAME object (table.column) existing in all cubes, like the other dimensions (product, org etc), yet it is unlinkable. What could be the reason for this?
Also, based on the behavior observed so far and what you confirmed, we seem to have more control over the linking/unlinking if we have two separate queries i.e. DP’s rather than two cubes within the same query, selected dimensions being the same.

The user with the strange display problem is in Taiwan, so I’m afraid I can’t get back to you regarding whether this works or not till Monday. Will keep you posted…

Thanks for your input - I learn something new everyday
Prakash

prakash (BOB member since 2003-03-19)

#12

Prakash,

Are you sure the user is using v5.1.4 and not v5.1.3? Because BusObj has a whitepaper on changes to some calculations involving multiple DPs/global filters/complex filters, and supposedly starting with v5.1.4 - and of course you KNOW you can trust BO’s documentation completely :wink: :roll_eyes: .

There is a download link (“calculator enhancements”) on the main page of techsupport.businessobjects.com, on the right-hand side, buried in text about “New Service Packs Available”.

Good luck!

JP Brooks :us: (BOB member since 2002-10-22)

#13

Well, here’s the update and unfortunately it is not very confidence-inspiring , esepecially to the users who will make a decision to go-ahead or not with the project, based on whether these issues can be resolved satisfactorily. If the numbers on the screen cannot be trusted, then it is a fairly serious issue, its no consolation that the SQL engine is pulling in the right information, only to have the calculation engine screw things up.

The users are using 5.1.4 not 5.1.3. However some of the data display problems we have encountered are present even in 5.1.6/7 and will not be fixed until SP8 according to the Tech Support site.

Situation 1 with 1 DP, 2 synchronized queries, no filters:
[list=1:dadd18539f][:dadd18539f] Tried to convert the crosstab (with data from both cubes) into regular table, no luck, quantities from second cube were still not showing completely for some months. These months it turns out had about 8-15 detail records that it had to aggregate over and it only aggregated the first 6 quantities in the data mgr! Very strange!
[:dadd18539f]Converted crosstab into 3.1 crosstab, still no luck in 5.1.4. What did work was to creating a table with only quantity from second cube by month. The aggregation then was correct for ALL months. But putting this quantity in the first blocks footer by month created problems in 5.1.4
[:dadd18539f] Well, the problem is definitely some improper synchronization - this does seem like a bug between versions 5.1.4 and 5.1.6. Since these are synchronized queries, we cannot unlink the dimensions to see if that helps. The workaround for 5.1.4 users is to either upgrade to 5.1.6 or split up the queries into two, instead of having one query with 2 cubes, the results i.e data display is then correct for all the months in the combination crosstab.[/list]
Situation 2 - with 2 queries/DP’s, 4 cubes total and Global Filters.
[list=1:dadd18539f][:dadd18539f]Here discovered some problems with Global Filters and Synchronization even with 5.1.6. A search of the Tech support site turned up about 4-5 cases related to 5.1.6 and this issue of global filters not working correctly when multiple DP’s are present. Found two workarounds after much investigation.
[:dadd18539f]Out of the 8 Global Filters being used, narrowed down the problem to one global filter on the common Sales Channel Dimension that created the inner-join effect, even though ALL values were selected for that filter. Removing that global filter correctly displayed all the inv orgs irrespective of whether data was present or not for that particular org in that block (what you would expect out of a UNION query or proper linking)
[:dadd18539f] Since there were two DP’s, tried manually unlinking some of the key dimensions and it did help solve the display/calculation problem in some cases.
[/list]
Overall, never expected to find such serious display/calculation issues, but I guess the more we work on different projects, the more we learn…

Thanks to all those who responded…
Prakash

prakash (BOB member since 2003-03-19)

#14

Hi,

I was interested in a similar option, but failed to see it working on my report. I require to filter out records, which are aggregated at lets say Student Name column, with a list of Students obtained from an external Excel sheet. The Main data is retrieved using the Universe/SQL Query. Now I will be calculating and aggregating on Student Name on the report side. How Do I use the filter option to get the same results?
Does the filteration actually happen at the cube level, before It reaches the report?
Would there be a method to Hide the record on the report, looking into the list coming from the Excel?

gagandeep_grover :india: (BOB member since 2003-06-17)

#15

Yes, this would be cool. if I could get it to work. :hb:
Let me preface this post by saying Yes I am aware of Steve Bickerton’s presentation, unfortunately I can not get to it. The link gives me a “Page can not be displayed” and when I searched BO support for “data provider synchronization” and “bickerton” I still could not locate it. I am assuming it is now off-line. Anyway, this seem to be a pretty common problem but here is my spin on it and maybe someone can point out what I doing wrong. 2 dp’s, 1 Oracle (dp1), 1 Excel (dp2). Excel source is static and contains many rows but I want to display only those returned by dp1. They are linked by a common id. When I put the global filter on the linked dimension and “select all” it works as expected. But… I need this to be dynamic based on the input parameter of dp1, which is the linked dimension. When I define the filter Not IsNull(dp1 linked dimension) this will not work. And what I mean by “will not work” is it continues to show all dp2 or excel records. I have tried many variations of this in my filter definition using both dp1 and dp2, but in theory I should only need to apply this on dp1 linked dimension since I only want to see the relevant data returned based on values of dp1. I’ve got to be missing something here :confused: or is this some type of sick v5.1.2 bug playing tricks on me (although I could find no documentation of this)

jswoboda :us: (BOB member since 2002-06-20)

#16

Nevermind. :slight_smile:

Our issue was resolved by defining the global filter based on the user response or input variable. We are displaying our data in 2 different blocks/tables on the same report. -thx

jswoboda :us: (BOB member since 2002-06-20)

#17

Does linking two data providers create an outer join or an Inner join. Can the way it joins be changed?

BOB User :us: (BOB member since 2003-06-03)

#18

Essentially linking of data providers creates a double-outer join at the report level.

If you search and read through some posts here at BOB (try keywords: Bickerton data provider synchronization) you should get a pretty good idea.

You can apply report filters to “emulate” an “inner-join” when linking data providers.

Andreas :de: (BOB member since 2002-06-20)

#19

:confused: Hello, have you found a solution to your filtering problem with two DP’s, one Oracle and the other Excel. I have the exact same problem and would love to hear of any solution. Current version of BO is V5.16.

Ta
Frank

Pace (BOB member since 2004-08-27)

#20

I am not sure if this is possible but if anyone can help it would be appreciated.

Say I have three data providers.

DP1 - Location
Policy No
State

DP2 - Loaded
Policy No
Premium (measure)

DP3 - Suspense
Policy No
Premium (measure)

What I am trying to get is a Count of the Policy No for each state in both categories using the Count function. e.g.
count(<Policy No(Loaded)>)
count(<Policy No(Suspense)>)

State Loaded Suspense
State1 22 8
State2 18 3

Unfortunately due to there is a link between DP2 and DP3. and this means that the figures aggregate.

Is there a way that I can have a outer link between DP1 and DP2 as well as DP1 and DP3 but no link between DP2 and DP3?

Rampage (BOB member since 2007-11-16)

#21

Using Deski 12.1 and cannot get this cool inner-join trick to work.

I read the presentation and it makes sense, but I cannot get it to work. I ended up having to manually select over 234 store numbers.

I have two data providers from the same universe (one for this year, one for last year) and one data provider that is an Excel Document containing the store numbers I want the other two data providers to filter by.

I tried defining a filter on the <Store Number(This Year)> where =Not IsNull(<AccountStore(Excel File)>)

Can some sweet soul help me? Every week when I run the report, this filter list will change.

Thank you.

Suzan

suzan24k (BOB member since 2003-03-06)

#22

Susan,

Do you want to email your report saved for all users? What’s your email, and I will ping you.

SteveBickerton :canada: (BOB member since 2002-08-15)

#23

Hi Steve,

My email is suzan24k a t Hotmail . com.

Thank you.

suzan24k (BOB member since 2003-03-06)

#24

That link no longer works.

But the presentation is available here

kbrazell :us: (BOB member since 2003-08-19)

#25

Here is an external link to the presentation:

http://www.noscentia.com/reference/

nice to know that after 7 years it is still used…

SteveBickerton :canada: (BOB member since 2002-08-15)

#26

I’ve gotten closer to getting this inner join to work. I think the change I needed to make to get the filter to work was to filter against a “linked” dimension field.

For some reason, now, all my inner join rows do not make it to the report. I work with multiple accounts and stores and only two of the five accounts correctly filter through to the report. I’ve looked at the data that joins the data providers and they appear to be in the correct formats and values. Ah, to be a report writer. Guess I’ll keep looking at the data.

Thanks for the help.

Suzan

suzan24k (BOB member since 2003-03-06)

#27

Just in case someone had the same problem I want to report that I now have my inner join working. It turned out the Account-Door data in the Universe had an blanks on the end if the Account-Door was less than 11 characters. I had to create a User Defined Object that did an rtrim against the Account-Door.

I linked my Account-Doors from the Excel Data Provider to the Universe data provider and applied the complex filter to the <Account-Door (Universe )>) defined as =Not IsNull(<Account Door(Excel Doc)> . Now life is good again. :+1:

Suzan

suzan24k (BOB member since 2003-03-06)