1 / 38

H@TF!LE IS NOT A 4-LETTER WORD

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.

nayef
Download Presentation

H@TF!LE IS NOT A 4-LETTER WORD

An Image/Link below is provided (as is) to download presentation Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author. Content is provided to you AS IS for your information and personal use only. Download presentation by click this link. While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server. During download, if you can't get a presentation, the file might be deleted by the publisher.

E N D

Presentation Transcript

  1. H@TF!LE IS NOT A 4-LETTER WORD

  2. What is a Hotfile? A local database table created from the results of an Impromptu report

  3. Hotfiles created from reports Use Save As functionality to create hotfiles

  4. Hotfile as a local file

  5. Hotfile as a table Can be used as a data source for subsequent reporting

  6. 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

  7. Performance • Aggregation, aggregation, aggregation

  8. 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

  9. 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 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,

  10. Performance • Aggregation, aggregation, aggregation Impromptu functions used

  11. 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

  12. 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'))

  13. Performance Pay attention to the function type

  14. Performance • Aggregation, aggregation, aggregation Impromptu functions used Extended summaries

  15. Performance • Summaries placed in headers are known as extended summaries, and are processed locally

  16. Performance • Aggregation, aggregation, aggregation Impromptu functions used Extended summaries Impromptu summaries

  17. 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

  18. Picklist • Use hotfiles as a look-up to populate your picklists • Most valuable when reporting against OLTP, or when picklist generated from large table

  19. 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

  20. Multiple Databases • Solutions • Bite the bullet and build a datamart • Integrate data into an existing DB • Use MS Access link table technology • Hotfiles

  21. 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

  22. Smart Hotfile • Why use them? • Dynamic hotfiles that reflect user class attributes of the person running the report • Why not? • No performance gains

  23. 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

  24. 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

  25. Why Isn’t Everyone Using Them?

  26. 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

  27. Why Isn’t Everyone Using Them? • Concerns over a potential point of failure in refresh process • Useless in Transformer

  28. Why Isn’t Everyone Using Them?

  29. Properties of Hotfiles • No indexes • Processor intensive • Memory intensive • Are processed locally

  30. So What’s Changed? IWR

  31. So What’s Changed? Hotfiles + Burst Reports

  32. So What’s Changed? • Powerful Application Servers • IWR deals with file location issues • Report dependencies simplifies update processes • High report request volumes

  33. 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

  34. 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

  35. 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

  36. 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 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,

  37. 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

  38. Contact Harvey.Schnell@inbusiness.com

More Related