Focal Point Banner
Community Center Education Summit Technical Support User Groups
Let's Get Social!

Facebook Twitter LinkedIn YouTube
Focal Point    Focal Point Forums  Hop To Forum Categories  WebFOCUS/FOCUS Forum on Focal Point     [SOLVED] Speeding up reports
Go
New
Search
Notify
Tools
Reply
  
[SOLVED] Speeding up reports
 Login/Join
 
Silver Member
posted
Hello,

I have done a join on two master files (FACT_INVENTORY_V01 and PLANT) and saved the resulting master file as INV_JOIN_2_PLANT(the two original master files were from SQL Server created by importing synonyms from SQL server and I haven't changed anything in them).

When I try to create a report in WF based on the master file created as a combination of the two other ones (INV_JOIN_2_PLANT) in the following format (I created it in gui but showing source):

TABLE FILE INV_JOIN_2_PLANT
SUM
INV_JOIN_2_PLANT.FACT_INVENTORY_V01.STOCK_WEIGHT
BY INV_JOIN_2_PLANT.FACT_INVENTORY_V01.PROFIT_CENTER
BY INV_JOIN_2_PLANT.FACT_INVENTORY_V01.PLANT
BY INV_JOIN_2_PLANT.PLANT.CITY
END

the report appears never ending, just keeps on running.

I have used the View Generated SQL button and get the following information :

SELECT
T1."Plant",
T1."Profit_Center",
T1."Stock_Weight"
FROM
GDW.dbo.fact_inventory_v01 T1;
SELECT
T2."City"
FROM
dbo.Dim_Plant T2
WHERE
(T2."Plant_Code" = ?);

I'm a bit confused by this as the following is the SQL that I thought would be created(or something similar) :

select fi.Profit_Center, fi.Plant, p.city, sum(stock_Weight)
from dbo.fact_inventory_v01 fi inner join dbo.dim_plant p
on fi.plant = p.plant_code
group by fi.Profit_Center, fi.Plant, p.city

and runs in no time at all when run on SQL server.

Any thoughts at how I can improve the reports running time, I am trying to attach a screen print of the master file synonym screen but not sure how to
(it is just an insert two master files and drag from plant of fact_inventory_v01 to plant_code of dim_Plant (the names are different, plant_code in 1 table should match plant in the other table))

Regards



MasterFileJoin

Note further to the above - there were some things automatically done to the 2 individual master files when I created them, a number of defines were automatically created using DTPART to get year number, quarter number etc. I took these entries out and the result was the same.

If I base the two individual master files on sql scripts 1) select * from dbo.dim_plant and 2) select * from dbo.fact_inventory_v01 then the report runs much quicker and produces the View Generated SQL as below :

SELECT
T1."Profit_Center",
T1."Plant",
T2."City",
SUM(T1."Stock_Weight")
FROM
( /* vvv */
select * from gdw.dbo.fact_inventory_v01
) /* ^^^ */ T1,
( /* vvv */
select * from gdw.dbo.dim_plant
) /* ^^^ */ T2
WHERE
(T2."Plant_Code" = T1."Plant")
GROUP BY
T1."Profit_Center",
T1."Plant",
T2."City"
ORDER BY
T1."Profit_Center",
T1."Plant",
T2."City";

which is more of what I expected. So I'm not sure how I should deal with reports to help speed them up.

This message has been edited. Last edited by: FP Mod Chuck,


WF 8201, Windows Server 2012 R2, SQL Server 2014, HTML, PDF, Excel, Powerpoint, Active Report
 
Posts: 37 | Location: All over Europe | Registered: October 23, 2009Reply With QuoteReport This Post
Guru
posted Hide Post
The generated SQL shows that the request you've made was not optimized. Basically it's now loading fact_inventory on row level and doing lookups to get plant after that it aggregates the data so you can imagine why it's slow.

How do fi.plant and p.plant_code look like in your masterfiles? Do they have same specification? Are there any defines being done on those fields?


Test: WF 8.2
Prod: WF 8.2
DB: Progress, REST, IBM UniVerse/UniData, SQLServer, MySQL, PostgreSQL, Oracle, Greenplum, Athena.
 
Posts: 415 | Location: Europe | Registered: February 05, 2007Reply With QuoteReport This Post
Silver Member
posted Hide Post
Frans sorry I added the bit at the end while you were replying, does the extra info help at all


WF 8201, Windows Server 2012 R2, SQL Server 2014, HTML, PDF, Excel, Powerpoint, Active Report
 
Posts: 37 | Location: All over Europe | Registered: October 23, 2009Reply With QuoteReport This Post
Silver Member
posted Hide Post
Frans, no defines after I removed the DTPART defines that were automatically created.
The datatypes were originally different A4V vs A20V so I changed them to match, both A4V and result was the same, ie SQL didn't look optimized to me.


WF 8201, Windows Server 2012 R2, SQL Server 2014, HTML, PDF, Excel, Powerpoint, Active Report
 
Posts: 37 | Location: All over Europe | Registered: October 23, 2009Reply With QuoteReport This Post
Guru
posted Hide Post
These DATEPART things are added there by default for your convenience. If you don't use them, they don't have any effect.

Could you also check your access file, are both using the same adapter?


Test: WF 8.2
Prod: WF 8.2
DB: Progress, REST, IBM UniVerse/UniData, SQLServer, MySQL, PostgreSQL, Oracle, Greenplum, Athena.
 
Posts: 415 | Location: Europe | Registered: February 05, 2007Reply With QuoteReport This Post
Silver Member
posted Hide Post
Frans,

They are not using the same adapter.

Regards


WF 8201, Windows Server 2012 R2, SQL Server 2014, HTML, PDF, Excel, Powerpoint, Active Report
 
Posts: 37 | Location: All over Europe | Registered: October 23, 2009Reply With QuoteReport This Post
Guru
posted Hide Post
OK that is the reason, for optimization they need to be on the same adapter. Can you adjust it to let them use the same?


Test: WF 8.2
Prod: WF 8.2
DB: Progress, REST, IBM UniVerse/UniData, SQLServer, MySQL, PostgreSQL, Oracle, Greenplum, Athena.
 
Posts: 415 | Location: Europe | Registered: February 05, 2007Reply With QuoteReport This Post
Silver Member
posted Hide Post
Frans,

When I did the test that worked quicker (based the master files on sql queries) the two individual acx's were both using the same adapter


WF 8201, Windows Server 2012 R2, SQL Server 2014, HTML, PDF, Excel, Powerpoint, Active Report
 
Posts: 37 | Location: All over Europe | Registered: October 23, 2009Reply With QuoteReport This Post
Silver Member
posted Hide Post
Frans,

Fantastic that you have spotted what problem is.
I'm not sure how this fits in with our plan to allow users to access only what they should see stop them seeing what they shouldn't. I did a post on Friday asking about the method I was proposing to handle security here and this issue sounds like it might well impact on that.


WF 8201, Windows Server 2012 R2, SQL Server 2014, HTML, PDF, Excel, Powerpoint, Active Report
 
Posts: 37 | Location: All over Europe | Registered: October 23, 2009Reply With QuoteReport This Post
Silver Member
posted Hide Post
Fridays post was called:

Adapters and profiles - limiting users access


WF 8201, Windows Server 2012 R2, SQL Server 2014, HTML, PDF, Excel, Powerpoint, Active Report
 
Posts: 37 | Location: All over Europe | Registered: October 23, 2009Reply With QuoteReport This Post
Guru
posted Hide Post
You're welcome, I'll have a look at your other post.


Test: WF 8.2
Prod: WF 8.2
DB: Progress, REST, IBM UniVerse/UniData, SQLServer, MySQL, PostgreSQL, Oracle, Greenplum, Athena.
 
Posts: 415 | Location: Europe | Registered: February 05, 2007Reply With QuoteReport This Post
Silver Member
posted Hide Post
Frans,

Thanks for your help


WF 8201, Windows Server 2012 R2, SQL Server 2014, HTML, PDF, Excel, Powerpoint, Active Report
 
Posts: 37 | Location: All over Europe | Registered: October 23, 2009Reply With QuoteReport This Post
  Powered by Social Strata  
 

Focal Point    Focal Point Forums  Hop To Forum Categories  WebFOCUS/FOCUS Forum on Focal Point     [SOLVED] Speeding up reports

Copyright © 1996-2018 Information Builders, leaders in enterprise business intelligence.