OBIEE 11g additional table in the generated sql
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!
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
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
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
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
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
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
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. _
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
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
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 126.96.36.199 64bit, on 64 bit Red Hat Linux Enterprise Linux 5.6 , MS SQL Server 2008 on 64 bit Microsoft Windows Ser
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
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
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
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
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
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
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
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
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
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_
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