I have created a simple webi report with only two objects(System ID and System name) but when i triying to run the query it take so long to run approx 40 min. as it is a simple query it should not take this much of time.
I have alreadt checked the query in sql serer it hardly took 10 sec.
Note:- there is two system ID objects available in universe pane one is diemnsion and one is Fact.
When i take the dimension one the execution time is fine but this is not that i want.
when i take Fact one it creating one where condition .
Please check bellow:
Query with Fact table:
SELECT
DMN.DMN_SOURCE_SYSTEM.SOURCE_SYSTEM_NAME,
FI.FCT_ACCT_BASED_PROFIT_LINE.SOURCE_SYSTEM_ID
FROM
DMN.DMN_SOURCE_SYSTEM,
FI.FCT_ACCT_BASED_PROFIT_LINE
WHERE
( DMN.DMN_SOURCE_SYSTEM.SOURCE_SYSTEM_ID(+)=FI.FCT_ACCT_BASED_PROFIT_LINE.SOURCE_SYSTEM_ID )
Query with Dimension table:
SELECT
DMN.DMN_SALES_PERSON_HIERARCHY.SOURCE_SYSTEM_ID,
DMN.DMN_SALES_PERSON_HIERARCHY.SOURCE_SYSTEM_NAME
FROM
DMN.DMN_SALES_PERSON_HIERARCHY
I don’t understand this + symbol, is it a left outer join (all rows from dimension and matching rows from fact), why don’t you use ANSI_92 syntax in your universe.
By the way, go through this link, must help in future if not now.
The + symbol in the join is typical of Oracle. That Oracle refuses to use ANSI standards for declaring joins is an ongoing pet peeve of mine.
When you are testing your query’s performance, are you copying the SQL out of Query Builder, or re-creating it? Also, are you positive that the test is returning all the data, and not just a sample? (I know that TOAD, for instance, only returns the first 500 rows, making it look like the query is running much faster there.)
Have you tried adding any additional conditions, say to look for one specific SOURCE_SYSTEM_ID? If the query is restricted in this way and is still taking a long time, then you have a communication issue with your database. I’d get with your DBA, check which ODBC or OLE DB you are using, and check your connection settings.
Are you having issues with any other reports against this universe? If not, it is likely something peculiar about that query. Double check your joins.
Hi,
As it is a WebI report i have copied that query from SQL viewer.
and also i have tried with condition (Source system Id In list 10) and the query ran in some seconds.
but the concern whaen i am addingonly Source system ID and Source system name why itis creating a wherecondition is there any conflicts with any other universe or objects.
In your case, the WHERE condition is specifying the join between the tables. Rather than using the JOIN keyword, it simply brings in both tables and uses the WHERE clause to restrict the results.
As it is performing an outer join on fact table it depends on number of rows in fact table and the dimension table.
Can you also check whether if FCT_ACCT_BASED_PROFIT_LINE is a table or a view. Check the number of rows when compared the the query you ran in SQL Server and in BO
You said you have 2 system Ids one in Dimension and other in Fact table. Are they any way related. Or else you will get cartesean product of both the ids.
If you have found that the query returns the full set in less than 10 seconds, then similar runtime should be expected in WEBi.
Please confirm that the full set is being returned when you run directly against SQL server: perhaps wrap a select count(*) from ({your sql query} around your query.
Next, please provide your system information. These issues are often relative to the particular version of the enterprise that you are using.
I have checked the report universe and found there is dimension table called DMN.DMN_SOURCE_SYSTEM which is having Source system id and source system name and there is a fact table called FI.FCT_ACCT_BASED_PROFIT_LINE which is having only source system id. so there is left outer join between these table.so it will retrive the data for source system id and source system name.
could you please suggest is this might be the issue to take long time to execute the query in report level.
(BOB member since 2010-07-13)
