Transcript Document
H@TF!LE
IS NOT A 4-LETTER WORD
What is a Hotfile?
A local database table created from the results of an Impromptu report
Hotfiles created from reports
Use Save As functionality to create hotfiles
Hotfile as a local file
Hotfile as a table
Can be used as a data source for subsequent reporting
Why should I use them?
• Poor Performance • Useful in prompt picklist situations • Allows reporting against multiple databases • Use a smart hotfile to resolve hotfiles on demand • The ultimate problem solver
Performance
• Aggregation, aggregation, aggregation
Performance
• Summaries provided at multiple hierarchy levels, ie sums of sums Database SQL select T1."Track" c1, datepart(year,T1."RaceDate") c2, T1."Gait" c3, T1."RaceType" c4, T1."Driver" c5, avg(T1."Purse") c6, avg(T1."TimeFirstQuarter") c11, avg(T1."TimeHalf") c16, avg(T1."TimeThreeQuarters") c21, avg(T1."TimeFinish") c26, avg(T1."PositionFinish") c31, avg(T1."LengthsFinish") c36, avg(T1."Odds") c41, count(T1."Driver") c46 from "Harness"."dbo"."F_Results" T1 where not T1."Driver" is null group by T1."Track", datepart(year,T1."RaceDate"), T1."Gait", T1."RaceType", T1."Driver" order by 1 asc, 2 asc, 3 asc, 4 asc, 5 asc
Cognos SQL select c1 as c1, c2 as c2, c3 as c3, c4 as c4, c5 as c5, c6 as c6, RAVG(c6 at c1,c2,c3,c4,c5 for c1) as c7, RAVG(c6 at c1,c2,c3,c4,c5 for c1,c2) as c8, RAVG(c6 at c1,c2,c3,c4,c5 for c1,c2,c3) as c9, RAVG(c6 at c1,c2,c3,c4,c5 for c1,c2,c3,c4) as c10, c11 as c11, RAVG(c11 at c1,c2,c3,c4,c5 for c1) as c12, RAVG(c11 at c1,c2,c3,c4,c5 for c1,c2) as c13, RAVG(c11 at c1,c2,c3,c4,c5 for c1,c2,c3) as c14, RAVG(c11 at c1,c2,c3,c4,c5 for c1,c2,c3,c4) as c15, c16 as c16, RAVG(c16 at c1,c2,c3,c4,c5 for c1) as c17, RAVG(c16 at c1,c2,c3,c4,c5 for c1,c2) as c18, RAVG(c16 at c1,c2,c3,c4,c5 for c1,c2,c3) as c19, RAVG(c16 at c1,c2,c3,c4,c5 for c1,c2,c3,c4) as c20, c21 as c21, RAVG(c21 at c1,c2,c3,c4,c5 for c1) as c22, RAVG(c21 at c1,c2,c3,c4,c5 for c1,c2) as c23, RAVG(c21 at c1,c2,c3,c4,c5 for c1,c2,c3) as c24, RAVG(c21 at c1,c2,c3,c4,c5 for c1,c2,c3,c4) as c25, c26 as c26, RAVG(c26 at c1,c2,c3,c4,c5 for c1) as c27, RAVG(c26 at c1,c2,c3,c4,c5 for c1,c2) as c28, RAVG(c26 at c1,c2,c3,c4,c5 for c1,c2,c3) as c29, RAVG(c26 at c1,c2,c3,c4,c5 for c1,c2,c3,c4) as c30, c31 as c31, RAVG(c31 at c1,c2,c3,c4,c5 for c1) as c32,
Performance
RAVG(c31 at c1,c2,c3,c4,c5 for c1,c2) as c33, RAVG(c31 at c1,c2,c3,c4,c5 for c1,c2,c3) as c34, RAVG(c31 at c1,c2,c3,c4,c5 for c1,c2,c3,c4) as c35, c36 as c36, RAVG(c36 at c1,c2,c3,c4,c5 for c1) as c37, RAVG(c36 at c1,c2,c3,c4,c5 for c1,c2) as c38, RAVG(c36 at c1,c2,c3,c4,c5 for c1,c2,c3) as c39, RAVG(c36 at c1,c2,c3,c4,c5 for c1,c2,c3,c4) as c40, c41 as c41, RAVG(c41 at c1,c2,c3,c4,c5 for c1) as c42, RAVG(c41 at c1,c2,c3,c4,c5 for c1,c2) as c43, RAVG(c41 at c1,c2,c3,c4,c5 for c1,c2,c3) as c44, RAVG(c41 at c1,c2,c3,c4,c5 for c1,c2,c3,c4) as c45, c46 as c46, RSUM(c46 at c1,c2,c3,c4,c5 for c1) as c47, RSUM(c46 at c1,c2,c3,c4,c5 for c1,c2) as c48, RSUM(c46 at c1,c2,c3,c4,c5 for c1,c2,c3) as c49, RSUM(c46 at c1,c2,c3,c4,c5 for c1,c2,c3,c4) as c50 from (select T1."Track" as c1, (datepart({year},T1."RaceDate")) as c2, T1."Gait" as c3, T1."RaceType" as c4, T1."Driver" as c5, AVG(T1."Purse") as c6, AVG(T1."TimeFirstQuarter") as c11, AVG(T1."TimeHalf") as c16, AVG(T1."TimeThreeQuarters") as c21, AVG(T1."TimeFinish") as c26, AVG(T1."PositionFinish") as c31, AVG(T1."LengthsFinish") as c36, AVG(T1."Odds") as c41, COUNT(T1."Driver") as c46 from "Harness"."dbo"."F_Results" T1 where (T1."Driver" IS NOT NULL) group by T1."Track",(datepart({year},T1."RaceDate")),T1."Gait",T1."RaceType",T1."Driver" order by c1 asc,c2 asc,c3 asc,c4 asc,c5 asc ) D1
Performance
• Aggregation, aggregation, aggregation • Impromptu functions used
Performance
• • Impromptu functions in filters require local processing, such as add-days(), integer-divide(), last-of-month(), etc.
Eg. Filter of: “Racedate between add-years(today(),-1) and today()” Database SQL select T1."RaceDate", -10000-(1-convert(float(53),-1)/abs(-1))/2, T1."WPSWinPayoff" from "Harness"."dbo"."C_Wps" T1
Performance
Cognos SQL select T1."RaceDate" as c1, T1."WPSWinPayoff" as c2 from "Harness"."dbo"."C_Wps" T1 where (T1."RaceDate" BETWEEN (cdatetime((DATE '2001-09-12') + ymdint_to_daysint((DATE '2001-09-12'),cinterval((-1) * 10000 - (1 - (-1) / absolute(-1)) / 2)))) AND (DATE '2001-09-12'))
Performance
• Pay attention to the function type
Performance
• Aggregation, aggregation, aggregation • Impromptu functions used • Extended summaries
Performance
• Summaries placed in headers are known as extended summaries, and are processed locally
Performance
• Aggregation, aggregation, aggregation • Impromptu functions used • Extended summaries • Impromptu summaries
Performance
• Summaries such as moving-average and running-total are Impromptu-based and are processed locally Database SQL select T1."RaceDate", -10000-(1-convert(float(53),-1)/abs(-1))/2, T1."Track", T1."EXPayoff" from "Harness"."dbo"."exactor" T1 Cognos SQL select c1 as c1, c2 as c2, RSUM(c2 for c1) as c3 from (select T1."Track" as c1, T1."EXPayoff" as c2 from "Harness"."dbo"."exactor" T1 where (T1."RaceDate" BETWEEN (cdatetime((DATE '2001-09-12') + ymdint_to_daysint ((DATE '2001-09-12'),cinterval((-1) * 10000 - (1 - (-1) / absolute(-1)) / 2)))) AND (DATE '2001-09-12')) order by c1 asc) D1
Picklist
• Use hotfiles as a look-up to populate your picklists • Most valuable when reporting against OLTP, or when picklist generated from large table
Multiple Databases
• What do you do when you need to report against different data sources • Report against spreadsheet in addition to DW • Report against Oracle and SQL Server DBs • Multiple DBs from the same vendor
Multiple Databases
• Solutions • Bite the bullet and build a datamart • Integrate data into an existing DB • Use MS Access link table technology • Hotfiles
Smart Hotfile
• What is a smart hotfile?
• New in Impromptu 6.0
• Similar to a regular hotfile, but not persisted to a file • Like a view, but not • Resolved in temp space
Smart Hotfile
• Why use them?
• Dynamic hotfiles that reflect user class attributes of the person running the report • Why not?
• No performance gains
Hotfiles as a Problem Solver
• Not all types of queries are conducive to Impromptu • Conditional aggregation – particularly if decode() doesn’t work • Correlated subqueries • Unions
Hotfiles as a Problem Solver
• Some examples … • Need to report on multiple point-in-time snapshots of information • Presenting details of top 10 customers, but summarizing all others • Apply conditional formatting after using direct entry SQL
Why Isn’t Everyone Using Them?
Why Isn’t Everyone Using Them?
• AKA • "data item" is invalid because "c:\path\filename" is invalid • DMS-E-RBI_TABLE The table or view T1 was not found in the dictionary • DMS-E-RBI_DUPSORTKEY, A duplicate column was found in the sort list
Why Isn’t Everyone Using Them?
• Concerns over a potential point of failure in refresh process • Useless in Transformer
Why Isn’t Everyone Using Them?
Properties of Hotfiles
• No indexes • Processor intensive • Memory intensive • Are processed locally
So What’s Changed?
IWR
So What’s Changed?
Hotfiles + Burst Reports
So What’s Changed?
• Powerful Application Servers • IWR deals with file location issues • Report dependencies simplifies update processes • High report request volumes
Gotcha!
• User class filtering is a pain in the … • Filters need to be added for all user classes on all hotfiles • Leverage a dimension table or build a custom table
Harvey’s Rules
• Burst reporting • High volume personal report • Lots of aggregation • Impromptu functions in the filter • Very complex queries (lots of tables) • Many outer joins • Report-to-report drilling • Joins are not required • High database volumes but small result set
A Case Study
• Report with 9 summary columns • Totals calculated for each of 6 hierarchy levels • No joins • Needs to be produced for 27 user classes with results sensitive to the user class privileges
Cognos SQL select c1 as c1, c2 as c2, c3 as c3, c4 as c4, c5 as c5, c6 as c6, RAVG(c6 at c1,c2,c3,c4,c5 for c1) as c7, RAVG(c6 at c1,c2,c3,c4,c5 for c1,c2) as c8, RAVG(c6 at c1,c2,c3,c4,c5 for c1,c2,c3) as c9, RAVG(c6 at c1,c2,c3,c4,c5 for c1,c2,c3,c4) as c10, c11 as c11, RAVG(c11 at c1,c2,c3,c4,c5 for c1) as c12, RAVG(c11 at c1,c2,c3,c4,c5 for c1,c2) as c13, RAVG(c11 at c1,c2,c3,c4,c5 for c1,c2,c3) as c14, RAVG(c11 at c1,c2,c3,c4,c5 for c1,c2,c3,c4) as c15, c16 as c16, RAVG(c16 at c1,c2,c3,c4,c5 for c1) as c17, RAVG(c16 at c1,c2,c3,c4,c5 for c1,c2) as c18, RAVG(c16 at c1,c2,c3,c4,c5 for c1,c2,c3) as c19, RAVG(c16 at c1,c2,c3,c4,c5 for c1,c2,c3,c4) as c20, c21 as c21, RAVG(c21 at c1,c2,c3,c4,c5 for c1) as c22, RAVG(c21 at c1,c2,c3,c4,c5 for c1,c2) as c23, RAVG(c21 at c1,c2,c3,c4,c5 for c1,c2,c3) as c24, RAVG(c21 at c1,c2,c3,c4,c5 for c1,c2,c3,c4) as c25, c26 as c26, RAVG(c26 at c1,c2,c3,c4,c5 for c1) as c27, RAVG(c26 at c1,c2,c3,c4,c5 for c1,c2) as c28, RAVG(c26 at c1,c2,c3,c4,c5 for c1,c2,c3) as c29, RAVG(c26 at c1,c2,c3,c4,c5 for c1,c2,c3,c4) as c30, c31 as c31, RAVG(c31 at c1,c2,c3,c4,c5 for c1) as c32,
A Case Study
RAVG(c31 at c1,c2,c3,c4,c5 for c1,c2) as c33, RAVG(c31 at c1,c2,c3,c4,c5 for c1,c2,c3) as c34, RAVG(c31 at c1,c2,c3,c4,c5 for c1,c2,c3,c4) as c35, c36 as c36, RAVG(c36 at c1,c2,c3,c4,c5 for c1) as c37, RAVG(c36 at c1,c2,c3,c4,c5 for c1,c2) as c38, RAVG(c36 at c1,c2,c3,c4,c5 for c1,c2,c3) as c39, RAVG(c36 at c1,c2,c3,c4,c5 for c1,c2,c3,c4) as c40, c41 as c41, RAVG(c41 at c1,c2,c3,c4,c5 for c1) as c42, RAVG(c41 at c1,c2,c3,c4,c5 for c1,c2) as c43, RAVG(c41 at c1,c2,c3,c4,c5 for c1,c2,c3) as c44, RAVG(c41 at c1,c2,c3,c4,c5 for c1,c2,c3,c4) as c45, c46 as c46, RSUM(c46 at c1,c2,c3,c4,c5 for c1) as c47, RSUM(c46 at c1,c2,c3,c4,c5 for c1,c2) as c48, RSUM(c46 at c1,c2,c3,c4,c5 for c1,c2,c3) as c49, RSUM(c46 at c1,c2,c3,c4,c5 for c1,c2,c3,c4) as c50 from (select T1."Track" as c1, (datepart({year},T1."RaceDate")) as c2, T1."Gait" as c3, T1."RaceType" as c4, T1."Driver" as c5, AVG(T1."Purse") as c6, AVG(T1."TimeFirstQuarter") as c11, AVG(T1."TimeHalf") as c16, AVG(T1."TimeThreeQuarters") as c21, AVG(T1."TimeFinish") as c26, AVG(T1."PositionFinish") as c31, AVG(T1."LengthsFinish") as c36, AVG(T1."Odds") as c41, COUNT(T1."Driver") as c46 from "Harness"."dbo"."F_Results" T1 where (T1."Driver" IS NOT NULL) group by T1."Track",(datepart({year},T1."RaceDate")),T1."Gait",T1."RaceType",T1."Driver" order by c1 asc,c2 asc,c3 asc,c4 asc,c5 asc ) D1
A Case Study
• Without hotfiles, each report version took about 1 ½ minutes • Total requirement took 38 minutes to finish • Including the hotfile build time, the total requirement using hotfiles was … • 6 ½ minutes