Very Slow Report (Dashboard) (Urgent !!!!)

[issam]

Hi ,
I am new in Report studio, and i have to creat a Dashboard with many indicators, and each indicator is calculated differently with different filters (Periods, Collab Job, Clients sector, Prestations),(each indicator is a sort of a query) so what i did is i calculate all the indicators in FrameWork and i use them as Data to my Report Studio Table but the report is very very very slow (it take hours to be executed !!!) :-[
Is there any way to improve the performace or any way to creat the report without passing by the calculation of the indicators in FW ??

Thank U By advance

Notice : When i test a calculation in FW it s slow !!

An Example of one indacator calculation, SQl code :

with
DIM_TIERS6 as
    (select
           DIM_TIERS.SKEY  as  SKEY,
           DIM_TIERS.MARCHE_LOCAL_TIERS  as  Marche_Locale_tiers
     from
           "DW.PROD"..DW_KPMG.DIM_TIERS DIM_TIERS,
           "DW.PROD"..DW_KPMG.DIM_EXPERT_CAC DIM_EXPERT_CAC,
           "DW.PROD"..DW_KPMG.DIM_MARCHE DIM_MARCHE,
           "DW.PROD"..DW_KPMG.DIM_FORMES_JURIDIQUES DIM_FORMES_JURIDIQUES,
           "DW.PROD"..DW_KPMG.DIM_RESEAU_NAT DIM_RESEAU_NAT,
           "DW.PROD"..DW_KPMG.DIM_RIE DIM_RIE,
           "DW.PROD"..DW_KPMG.DIM_NAF DIM_NAF
     where
           (DIM_NAF.SKEY = DIM_TIERS.CODE_NAF) and
           (DIM_RIE.SKEY = DIM_TIERS.CODE_RIE) and
           (DIM_RESEAU_NAT.SKEY = DIM_TIERS.CODE_RESEAU_NAT) and
           (DIM_MARCHE.SKEY = DIM_TIERS.CODE_MARCHE_TIERS) and
           (DIM_EXPERT_CAC.SKEY = DIM_TIERS.CODE_EXPERT_CAC) and
           (DIM_FORMES_JURIDIQUES.SKEY = DIM_TIERS.CODE_FORME_JURIDIQUE)
    ),
Collaborateurs as
    (select
           DIM_COLLABORATEURS.SKEY  as  SKEY,
           DIM_METIER.LIBELLE_COURT_METIER  as  Libelle_court_metier_collab
     from
           "DW.PROD"..DW_KPMG.DIM_COLLABORATEURS DIM_COLLABORATEURS,
           "DW.PROD"..DW_KPMG.DIM_REGION DIM_REGION,
           "DW.PROD"..DW_KPMG.DIM_POLE DIM_POLE,
           "DW.PROD"..DW_KPMG.DIM_EQUIPE_BUREAU DIM_EQUIPE_BUREAU,
           "DW.PROD"..DW_KPMG.DIM_APPELLATION_PRO DIM_APPELLATION_PRO,
           "DW.PROD"..DW_KPMG.DIM_METIER DIM_METIER
     where
           (DIM_REGION.SKEY = DIM_COLLABORATEURS.CODE_REGION) and
           (DIM_POLE.SKEY = DIM_COLLABORATEURS.CODE_POLE) and
           (DIM_EQUIPE_BUREAU.SKEY = DIM_COLLABORATEURS.CODE_EQUIPE_BUREAU) and
           (DIM_APPELLATION_PRO.SKEY = DIM_COLLABORATEURS.CODE_APPELLATION_PRO_G) and
           (DIM_METIER.SKEY = DIM_COLLABORATEURS.CODE_METIER)
    )
select
       case when (((Collaborateurs.Libelle_court_metier_collab = 'KESM') and (DIM_TIERS6.Marche_Locale_tiers in ('NC','NE'))) and ((PERIODE.CODE_ANNEE_FISCALE = 2007) and (PERIODE.CODE_MOIS_CALENDAIRE < 200704))) then Honoraires.CA_BRUT else 0 end   as  c1
from
       DIM_TIERS6
        left outer join
       (
           Collaborateurs
            join
           "DW.PROD"..DW_KPMG.FACT_TPS_HON_CUBES Honoraires
            on (Collaborateurs.SKEY = Honoraires.CODE_COLLABORATEUR)
            join
           "DW.PROD"..DW_KPMG.DIM_PERIODE PERIODE
            on (PERIODE.CODE_JOUR = Honoraires.CODE_JOUR_FK)
       )
        on (Honoraires.CODE_TIERS = DIM_TIERS6.SKEY)

[cdallum]

One thought I would have is to speak with a DBA in your organization and send the SQL to him/her.  They could possibly optimize the database with appropriate indexing or at least help you identify what in your query is causing the worst portion of the performance.

Also, are you running this against a data warehouse or your operational data?  If it is against the data warehouse, you may want to consider placing some of the calculations in the fact tables persistently, rather than doing so many calculations real-time (process once, report many times).

Sorry I couldn't be more specific, but these are a couple of thoughts.

[Priest546]

what will happen if you replace your operational data?
_________________
Aprilaire

[david.stachon]

Another option would be to do your processing as database stored procedure(s)....then simply point to those SPs in FM.

This approach is often a necessary evil on OLTPs.

In reports, even what we think are the most innocent of calculations can end up causing huge performance impacts. Recently, I built a calculation in which all it was doing was concatenating a few text fields together. The report went from a run time of 3 seconds, up to 12 seconds.

[blom0344]

Another piece of 2 cents:

Always make sure that an outer join is really needed. Outer joins usually result in full table scans instead of index access of tables. If the table or dataset is very small this may not matter much, but it may be a killer in the end.

My suggestion would be to check if you need the outer join and what happens to performance if you change it to an inner join..