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))
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
January 15, 2018, 05:06 AM
Frans
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?
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
January 15, 2018, 06:11 AM
otto
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
January 15, 2018, 06:17 AM
Frans
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?
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
January 15, 2018, 07:40 AM
otto
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
January 15, 2018, 07:44 AM
otto
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
January 15, 2018, 08:06 AM
Frans
You're welcome, I'll have a look at your other post.