OBIEE 11g additional table in the generated sql

November 30 0
Advertisement
Hi gurus,
In OBIEE 11g RPD, we have a fact table whose source table is consisted by table A, B, C. Table A joins to table C while table B joins to tables C. And in the content tab, table C is used to filter out some records . If we select a column of A(The aggregation rule of this column is SUM) in the answers only, then table A, B, C are in the generated sql. If we remove the aggregation rule of this column, then there're only table A, C in the generated sql. We're wondering why this happened, but can't figure out.
In this case, the relationship between table B and C is many to one. So additional table B in the generated sql may cause wrong sum. And we need the aggregation rule to sum the number. On the other hand, we can't remove the join between table B and C, since other reports need the join.
Could anyone please provide a solution to remove the additional table B in the generated sql?
Thanks & regards!
shell
Advertisement
Are you using in one report data from tables A, B, C at the same time? If not:
1) Try separating the logical schema by:
- Create an alias table for A and B in the physical layer. Then join these 2 tables. No create another Alias table for B (with a slightly different name) and one Alias table for C. Join these 2 tables (B+ and C)
- Then put everything in your BMM and presentation layer.
- Test your results. Take into account that if you are working with data in tables A and B you have to use the "fact table" B but if you use data in table C, use instead fact table from table B+.
Your logical schema would be like A-->B B+-->C
J.
OBIEE 11g additional table in the generated sql

Hi gurus, In OBIEE 11g RPD, we have a fact table whose source table is consisted by table A, B, C. Table A joins to table C while table B joins to tables C. And in the content tab, table C is used to filter out some records . If we select a column of

Obiee 11g Noob question - bring in one sql table

I am a new to obiee. Using version 11g and trying to bring in one sql table. I created my db and table, then I create the data source in odbc. Everything tests fine. I then open up the oracle BI administration tool, and 'import metadata'. Everything

How to move excluded items into Measures in OBIEE 11g Pivot Table

Hi All, Need your help. Previously I have about 25 facts in my pivot table as rows. Now I need to add about 10 more. Adding new criteria only put the items into Excluded section. How can I move all these new items at once to the end of the existing f

OBIEE 11g: Fact table does not have  a properly defined primary key

Hi, We have two fact tables and two dimension tables. We have joined the tables like D1-->F1 D2-->F2 D1-->D2 We dont have any hieracies. It is throwing error in consistency check, [nQSError: 15001] Could not load navigation space for subject area

Obiee 11g external table authentication

Hi, I try to create external table authentication but it is not working. I have created this table CREATE TABLE OBI_SECURITY_USERS USER_NAME VARCHAR2(100 BYTE), USER_PASSWORD VARCHAR2(20 BYTE) NOT NULL and I defined Initialization Block it is below s

OBIEE 11g "WITH SAWITH0 AS" subquery factoring clause in the generated sql

I've observed that the OBIEE 11g generates in the query log physical query using the WITH (sub-query factoring) clause to make the generated sql elegantly readable. This is great! Thanks for the developers. However I have some questions about this. _

OBIEE generated SQL differs if it's a Physical Table or Select Table...

Hi! I have some tables defined in the Physical Layer, which some are Physical Tables and others are OBIEE "views" (tables created with a Select clause). My problem is that the difference in the generated SQL for the same table, differs (as expec

Error while using between operator with sql stmts in obiee 11g analytics

Hi All, when I try to use between operator with two select queries in OBIEE 11g analytics, I'm getting the below error: Error Codes: YQCO4T56:OPR4ONWY:U9IM8TAC:OI2DL65P Location: saw.views.evc.activate, saw.httpserver.processrequest, saw.rpc.server.r

Unable to connect to SQL Server 2008 from OBIEE 11G(on 64 bit Linux box)

We are unable to connect to connect to SQL Server 2008 from OBIEE 11G which is on 64 bit Red Hat Enterprise Linux. Infrastructure: OBIEE 11.1.1.5 64bit, on 64 bit Red Hat Linux Enterprise Linux 5.6 , MS SQL Server 2008 on 64 bit Microsoft Windows Ser

OBIEE 11g - serious problem when rotating columns into "table prompt" area

Hi, I'm hitting an OBIEE 11g issue that's causing huge problems on reports. Essentially, the problem is this - if I dump all the columns of a query to a table, everything looks fine. But if I rotate one of the columns into the "table prompts" ar

SSO in Microsoft Application, SQL Server 2005 & OBIEE 11g

Dear Gurus, Any experienced how to enable SSO(Single Sign On) in OBIEE 11g, when the application platform is microsoft instead of Oracle EBS and the data source from SQL server 2005? I am new on this. Any help will be appreciated Regards JOEHi, Try b

OBIEE 11g - How to create clean looking pivot tables

Hi, does anyone have a good way to clean up the appearance of pivot tables in OBIEE 11g? I'm a big fan of Stephen Few and firmly believe in the "minimize non-data ink" theory. Unfortunately, there seems to be a lot of stuff on OBIEE pivots that

Displaying of charts as a separate column in table view view in obiee 11g

Hi all, we have a requiremnet where i need to display charts in a separate column in a table view in OBIEE 11g. can any body help in achieving this requirement? Thanks in advance. RegardsHI All, Can any body let me know how to achieve this requiremen

External Table Authentication in OBIEE 11g

Hi , I have a security table, which contains userid,displayname,group . I have imported Security table in Physical Layer. I'm creating session variables based on condition. When am trying to logging into analytic s getting an error, invalid username

Unable to import table or columns in OBIEE 11G RPD

Hi All, We are unable to import table into the RPD using OCI 10G/11G to import the table then the import box open but we are unable to see the tables below the schema name. When we use ODBC 3.5 and created a DSN for the database then we are able to s

Unable to import tables through ODBC in obiee 11g

Hi all, In obiee 11g, OBIEE 11g is successfully installed but when i am trying to import the tables in RPD through odbc then it is giving a problem. I have tried using the OCI and it is working fine but can anyone tell me why it is not happening thro

Confused about Mapviewer and SQL in OBIEE 11g.

I read in another post that you cant use mapviewer unless you have an oracle database. Here is my confusion. The mapviewer product comes with OBIEE 11g, Correct? If I use Google Maps, and load the map information into an SQL data base, why wouldnt I

Use of lookup table in obiee 11g

Hi All, I am working on OBIEE 11g RPD. Under BMM layer, when select logical table and go to there properties, In general section we have lookup table check box. what is use of that lookup table check box in obiee 11g. Thanx and Regards PrashantPlease

How map to my particular table to the fact table in obiee 11g...

Hi friends, I did this simple report in obiee 11g(i.e) "NATIONALITY COUNT IN DEPARTMENT WISE" For that i used the following tables: per_all_assignments_f----->fact table hr_all_organization_units----->dim table(containing departments) per_

Integrating SQL Server 2005 with OBIEE 11g

Dear Gurus, Is there any documentation about how to using SQL Server 2005 as datasource combined with OBIEE 11g? Clear steps with pictures will be very helpful. Thank so much Regards JOEHi, Please refer this link. http://rnm1978.wordpress.com/2009/08