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,

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

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.

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?

Frans,

They are not using the same adapter.

Regards

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?

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

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.

Fridays post was called:

Adapters and profiles - limiting users access

You're welcome, I'll have a look at your other post.

Frans,

Thanks for your help