Obiee Error 14023
Contents |
OBIEE Architect 1 Comment Dimensional modeling is very important in OBIEE. Many mistakes happen because of incorrectly defined time dimension or other dimensions. This service request below is a demo of how an incorrectly defined key in the beginning can block the development efforts in the future.
None Of The Fact Tables Are Compatible With The Query Request Obiee 11g
Overview: ======== Modified rpd and added a new dimension for Key Accounts (Sales."Key Account"."Key Acct") obiee none of the dimension tables are compatible with the query request into the Sales subject area . After this change when building a report with Sales.Brand and drillingdown on Brand to show Brand_SKU (
Nqserror 14077 None Of The Dimension Tables Are Compatible With The Query Request
a different dimension throws an error in Answers. ERROR ===========- [nQSError: 14020] None of the fact tables are compatible with the query request Brand.Brand. STEPS =========== By following these steps the issue can be reproduced: 1. Add dimension [nqserror: 14020] none of the fact tables are compatible with the query request called Sales."Key Account"."Key Acct into sales subject area in rpd 2. save rpd and checkin changes 3. create answers report with a different dimension Brand. 4. Drilldown on Brand to show the next level "Brand"."Brand_SKU". 5. Get the error mentioned above EXPECTED BEHAVIOR =========== The report should work fine because the "Brand"."Brand_SKU" was not modified at all. Business Impact =========== This is a very important issue that is holding up all BI projects. You cannot move forward none of the fact tables are compatible with the detail filter until this issue is fixed. Cause The error thrown when drillingdown from Brand to Brand_SKU is caused because of incorrect definition of level keys in your rpd. The issue is, that by definition, a key is the lowest level of a table. Any higher levels are made up of aggregating the lowest or detail level. Since Brand and Product have the same detail key, there should be some consistency between the detail or lowest level of their respective hierarchies. There is not. The lowest level of the Brand hierarchy has a level key of CHILD_KEY_WID. The lowest level of the Product hierarchy has a level key of ‘SKU Nbr' which is mapped to LVL6_KEY. It seems that the navigator is expecting the same detail key but is not seeing it. This seems to be the cause of the navigation error. As confirmed by reviewing your rpd inhouse, the level keys are not properly defined for Brand and Product dimensions. The levels were not defined according to best practices. The document from Engineering that lists the issues in your rpd that dont meet best practice is attached to the SR. Solution Please follow these steps in order to fix the error "[nQSError: 14020] None of the fact tables are compatible with the query request Brand.Brand" 1) Open rpd using Admin tool 2) Select BMM layer and delete the cur
OBIEE - Query Compiler OBIEE - Query Compiler Table of Contents 1 - About 2 - Articles Related 3 - Steps 3.1
No Fact Table Exists At The Requested Level Of Detail
- Parsing 3.2 - Logical Request Generation 3.3 - Two major phases: Navigation and Rewrite 3.3.1 - Navigation 3.3.2 - Rewrite 3.4 - Code Generation (Compiler) 4 - The statistics 5 - Support 5.1 - None of the fact tables are compatible with the query request 1 - About The query compiler is responsible of http://obiee-blog.info/administration-tool/importance-of-defining-dimensional-hierarchies/ the compilation of a logical sql in the query processing process of BI Server. The query compilation is composed of the following five phases: parsing, logical request generation, navigation, rewrites, and code generation. The final output of the query compiler is an executable code. This code is passed to the execution engine that is then http://gerardnico.com/wiki/dat/obiee/query_compiler responsible for executing the code in parallel. You can then have two SQL statement at the same time against one database. 2 - Articles Related OBIEE - BI Server (Obis) (Architecture|Overview)OBIEE - Database Features ParametersOBIEE - Reporting on Multiple Subject Area (Advanced Logical SQL)OBIEE - Physical SQLOBIEE - Query Processing 3 - Steps 3.1 - Parsing In the first compilation phase, the multi-threaded parser accepts the logical sql and generates a parse tree as its output. 3.2 - Logical Request Generation Subsequently, the logical request generation component is responsible for instantiating the inferred aggregation in the simplified SQL supported by the Oracle BI Server. OBIEE - Logical Request (before navigation) 3.3 - Two major phases: Navigation and Rewrite The navigation and rewrite phases do the bulk of the work in compiling a query. The output of these two major phases is an execution plan that is then fed into the code generation phase. 3.3.1 - Navigation The navigator is responsible for the conten
Services Startup Failure By Giri Mandalika-Oracle on May 08, 2012 ISSUE: Starting Presentation Services fail with the error: [OBIPS] [ERROR:1] [] [saw.security.odbcuserpopulationimpl.getbisystemconnection] [ecid: ] [tid: ] Authentication Failure. Odbc driver https://blogs.oracle.com/mandalika/entry/obiee_11g_resolving_presentation_services returned an error (SQLDriverConnectW). State: 08004. Code: 10018. [NQODBC] [SQL_STATE: 08004] [nQSError: 10018] Access for the requested connection is refused. [nQSError: 43113] Message returned from OBIS. [nQSError: 43126] Authentication failed: invalid http://obiee-oracledb.blogspot.com/2015/05/understanding-obiee-rpd-chapter-1.html user/password. (08004)[[ Also connecting to the metadata repository (RPD) in online mode fails with similar error. Looking through the BI server log, nqserver.log, you may find an error message similar to none of the following: [OracleBIServerComponent] [ERROR:1] [] [] [ecid: 0001J1LfUetFCC3LVml3ic0000pp000000] [tid: 1] [13026] Error in getting roles from BI Security Service: 'Error Message From BI Security Service: [nQSError: 46164] HTTP Server returned 404 (Not Found) for URL .' ^M RESOLUTION: Connect to WebLogic Server (WLS) Console -> Deployments. Ensure that all deployed components are in 'Active' state. If any of the components is in 'Prepared' none of the state, select that application and then click on "start servicing all requests" Restart BI Server and Presentation Services In some cases, the following additional step might be needed to resolve the issue. Access the Enterprise Manager Fusion Middleware control: http://
joins and hierarchies, the RPD is essentially a query generating tool. The query generated by the tool depends on a sound understanding of the business model and data warehousing concepts. The most trickiest part of RPD development is to ensure that OBIEE generates a tuned physical query. I wish to write a series of articles that show the impact of coding something in the RPD on the Physical query and would hence enable the developers to find out the best things to do from a performance stand point. Please note that this article does not recommend one query over the other as the efficiency of the query will depend on the data structures themselves. The intention of this blog is to study the impact of changing the RPD on the Physical SQL. This is the first article in this series and we will be talking about the scenario where we pull columns from 2 different physical sources and 1 common dimension in the same analysis. I selected the following columns Let me now share the Aggregation rule and content level for both of the measure columns So we see that none of the 2 measures are tagged to any level in the dimension and the aggregation rule is Sum. Also note that both the measure columns are from the same logical fact. Let us now look at the column source mappings of each of these measures. We note that these columns are mapped to different tables/aliases in the physical layer. Let us now look at the physical layer joins of the tables involved in this Analysis. Let us now look at the physical query generated by this analysis. WITH SAWITH0 AS (select sum(T42433.Cost_Fixed) as c1, T42409.Prod_Dsc as c2, T42409.Prod_Key as c3 from BISAMPLE.SAMP_PRODUCTS_D T42409 /* D10 Product (Dynamic Table) */ , BISAMPLE.SAMP_REVENUE_F T42433 /* F10 Billed Rev */ where ( T42409.Prod_Key = T42433.Prod_Key ) group by T42409.Prod_Dsc, T42409.Prod_Key), SAWITH1 AS (select sum(T68770.Revenue) as c1, T42409.Prod_Dsc as c2, T42409.Prod_Key as c3 from BISAMPLE.SAMP_PRODUCTS_D T42409 /* D10 Product (Dynamic Table) */ , BISAMPLE.SAMP_REVENUE_F T68770 /* F14 Rev. (Ship Dt Join) */ where ( T42409.Prod_Key = T68770.Prod_Key ) group by T42409.Prod_Dsc, T42409.Prod_Key), SAWITH2 AS (select D1.c1 as c1, D1.c2 as c2, D1.c3 as c3, D1.c4 as c4, D1.c5 as c5 from (select 0 as c1, case when D1.c2 is not null then D1.c2 when D2.c2 is not null then D2.c2 end as c2, D1.c1 as c3, D2.c1 as c4, case when D1.c3 is not null then D1.c3 when D2.c3 is not null then D2.c3 end as c5, ROW_NUMBER() OVER (PARTITION BY case when D1.c2 is not null then D1.c2 when D2.c2 is not null then D2.c2 end , case when D1.c3 is not null then D1.c3 when D2