The Vital Signs of Oracle Analytics: Understand, Measure and Improve Performance

Performance issues in Oracle Analytics are like diseases: whether you are an administrator or an end-user, they will spread if you ignore them, and eventually kill your systems.

This session aims at demystify performance tuning myths by revealing the truth behind commonly held beliefs or ideas that are not based on facts or evidence. It looks at the practical elements of diagnosing the causes of performance issues in Oracle Analytics, and discusses good practices to observe when developing new systems. It also includes a focus on the new capabilities of Data Visualization and their impact on performance.

Transcript

1. fed.venturin@gmail.com fventurin.hashnode.dev The Vital Signs of Oracle Analytics Understand, Measure and Improve Performance
2. fed.venturin@gmail.com fventurin.hashnode.dev Federico Venturin o Analytics Consultant o Oracle ACE Associate ♠ o Oracle Analytics Ambassador 2024 o ITOUG Board member o Based in Trebaseleghe Venice, Italy o fventurin.hashnode.dev o fed.venturin@gmail.com
3. fed.venturin@gmail.com fventurin.hashnode.dev I was raised by my parents to believe that you had a moral obligation to try and help save the world Anne Lamott Why Am I Here?
4. fed.venturin@gmail.com fventurin.hashnode.dev Performance Issues
5. fed.venturin@gmail.com fventurin.hashnode.dev Understand Performance
6. fed.venturin@gmail.com fventurin.hashnode.dev The Bad
7. fed.venturin@gmail.com fventurin.hashnode.dev The Worse
8. fed.venturin@gmail.com fventurin.hashnode.dev The Worse
9. fed.venturin@gmail.com fventurin.hashnode.dev The Ugly
10. fed.venturin@gmail.com fventurin.hashnode.dev o Infrastructure capacity o Configuration settings o Data model design o Semantic model (RPD) design o Dashboard design o Other / external factors What Causes Performance Issues?
11. fed.venturin@gmail.com fventurin.hashnode.dev Add capacity to fix any Oracle Analytics performance issue Myth #1
12. fed.venturin@gmail.com fventurin.hashnode.dev o Adding capacity alone is generally not sensible o Fix issues at root cause and you might offset the need for capacity at all Infrastructure Capacity
13. fed.venturin@gmail.com fventurin.hashnode.dev Play around with configuration settings to fix any Oracle Analytics performance issue Myth #2
14. fed.venturin@gmail.com fventurin.hashnode.dev o Default values are generally good o Unnecessary fiddling without good reason should be avoided Configuration Settings
15. fed.venturin@gmail.com fventurin.hashnode.dev The overall performance improvement gained by optimising a single part of a system is limited by the fraction of time the improved part is actually used Gene Amdahl Amdahl's Law
16. fed.venturin@gmail.com fventurin.hashnode.dev o Generic performance diagnosis tool o Shows the time for each step within an action o To understand why is it slow, we first must understand where is it slow Time Profile
17. fed.venturin@gmail.com fventurin.hashnode.dev o End user reports a response time of 40 seconds o Where did the time get spent? o What should we do? Time Profile in Action
18. fed.venturin@gmail.com fventurin.hashnode.dev Measure Performance
19. fed.venturin@gmail.com fventurin.hashnode.dev o Enables administrators to collect statistics for each query o Number of rows returned from the database o SQL statements executed on the database o Execution time o Cache hits Usage Tracking
20. fed.venturin@gmail.com fventurin.hashnode.dev o Usage Tracking is not enabled by default o Specify usage tracking database, schema and connection pool in the RPD o Set properties in the System Settings page Enabling Usage Tracking F
21. fed.venturin@gmail.com fventurin.hashnode.dev o Facility for logging query activity at the individual user level o Gives more information than Usage Tracking o Writes to DOMAIN_HOME/servers/obis1/logs/obis1-query.log Query Logging
22. fed.venturin@gmail.com fventurin.hashnode.dev o Query logging is not enabled by default o Requires LOGLEVEL to be greater than 0 (2 is recommended) Enabling Query Logging
23. fed.venturin@gmail.com fventurin.hashnode.dev Switch off the BI Server query logging if you are having performance issues in Oracle Analytics Myth #3
24. fed.venturin@gmail.com fventurin.hashnode.dev o Query logging is needed to trace and diagnose performance issues o The overhead is neglectable when LOGLEVEL is less than or equal to 2 Query Logging
25. fed.venturin@gmail.com fventurin.hashnode.dev o Provide information about Fusion Middleware (FMW) components o View DMS metrics using DMS Spy (http://HOST:9500/dms) or WLST Dynamic Monitoring Service (DMS) Metrics
26. fed.venturin@gmail.com fventurin.hashnode.dev o Helpful in monitoring the database for real time performance o V$ (Oracle), dm_exec_query_stats (SQL Server), etc. o Consider instrumenting connection pools o Correlate recent database activity with Oracle Analytics dashboards and users Dynamic Performance Views
27. fed.venturin@gmail.com fventurin.hashnode.dev o What? o CPU, disk, memory, network, etc. o Where? o Oracle Analytics o Data sources o How? o *nix: collectl, iostat, vmstat, etc. o Windows: PerfMon, Telegraf, etc. Operating System Metrics
28. fed.venturin@gmail.com fventurin.hashnode.dev Full-Stack Performance Monitoring Usage Tracking Log files Dynamic performance views DMS metrics OS metrics
29. fed.venturin@gmail.com fventurin.hashnode.dev Improve Performance
30. fed.venturin@gmail.com fventurin.hashnode.dev Enable Cache to fix any Oracle Analytics performance issue Myth #4
31. fed.venturin@gmail.com fventurin.hashnode.dev o Enables the BI Server to store query precomputed results in a local cache and satisfy subsequent queries without accessing back-end data sources o Can result in dramatic improvements in the average query response time o Can be particularly effective when federated data sources are used o It must be proactively designed and managed BI Server Cache
32. fed.venturin@gmail.com fventurin.hashnode.dev o Mechanism for caching file-based and connection-based datasets o Totally different compared to the BI Server cache o In principle all rows and columns for each table are cached o Aggregation and filtering on data gets done by the BI Server o By default it is stored on the file system o DOMAIN_HOME/servers/obis1/cache/xsastorage o May be adequate for small to medium sized data sources such as spreadsheet files o Storing it on a database may provide better performance for larger data sources XSA Cache
33. fed.venturin@gmail.com fventurin.hashnode.dev o Do not use cache as a mask for bad design o The actual problem is never addressed and will persist Cache
34. fed.venturin@gmail.com fventurin.hashnode.dev Enable Cache to allow for all necessary data transformations in Oracle Analytics Myth #5
35. fed.venturin@gmail.com fventurin.hashnode.dev o Data transformations should happen once at ETL time o Widespread usage at query time is indicative of suboptimal design, it's difficult to maintain, and result in less efficient and complex SQL Data Transformations
36. fed.venturin@gmail.com fventurin.hashnode.dev Oracle Analytics Datasets can effectively replace traditional Semantic Models Myth #6
37. fed.venturin@gmail.com fventurin.hashnode.dev o Self-service data models o Can contain multiple tables with relationships between them o Can contain data from files, SaaS applications, Oracle Analytics reports, and many relational and big data data sources o Data enrichment and transformation through a powerful editor o But they are not a replacement for the semantic model (RPD) o Vertical Federation and Double Column features are not supported o Generate different physical queries Datasets
38. fed.venturin@gmail.com fventurin.hashnode.dev o Used for combining sources of different granularity in a semantic model o Reports that require summarised data will perform better if the summary has been calculated in advance, in an aggregate table Vertical Federation
39. fed.venturin@gmail.com fventurin.hashnode.dev o Provides a mechanism for associating two columns o One column provides description values, the second corresponding IDs o Enables the query engine to generate more efficient physical SQL Double Column Support
40. fed.venturin@gmail.com fventurin.hashnode.dev Example: Semantic Model Vs Dataset o Built on the same database tables
41. fed.venturin@gmail.com fventurin.hashnode.dev Example: Semantic Model Vs Dataset o Same logical SQL o Show me MEZCAL Revenue by County and Vendor in 2022 SELECT 0 s_0, "Iowa Liquor Sales"."Product"."Vendor Name" s_1, "Iowa Liquor Sales"."Store"."County" s_2, "Iowa Liquor Sales"."Measures"."Revenue $" s_3 FROM "Iowa Liquor Sales" WHERE (("Date"."Year" = 2022) AND ("Product"."Category Name" = 'MEZCAL')) ORDER BY 3 ASC NULLS LAST, 2 ASC NULLS LAST FETCH FIRST 65001 ROWS ONLY SELECT 0 s_0, XSA('weblogic'.'Iowa Liquor Sales')."D_ITEM"."VENDOR_NAME" s_1, XSA('weblogic'.'Iowa Liquor Sales')."D_STORE"."COUNTY" s_2, XSA('weblogic'.'Iowa Liquor Sales')."F_IA_LIQUOR_SALES"."BOTTLES_SOLD" s_3 FROM XSA('weblogic'.'Iowa Liquor Sales') WHERE (("D_DATE"."YEAR_NUMBER" = 2022) AND ("D_ITEM"."CATEGORY_NAME" = 'MEZCAL')) ORDER BY 3 ASC NULLS LAST, 2 ASC NULLS LAST FETCH FIRST 65001 ROWS ONLY
42. fed.venturin@gmail.com fventurin.hashnode.dev Example: Semantic Model Vs Dataset o Different physical SQL WITH SAWITH0 AS (select sum(T110.SALE_DOLLARS) as c1, T93.VENDOR_NAME as c2, T100.COUNTY as c3 from DWH.D_DATE T70 /* Dim Date */ , DWH.D_ITEM T93 /* Dim Product */ , DWH.D_STORE T100 /* Dim Store */ , DWH.F_IA_LIQUOR_SALES T110 /* Fact Iowa Liquor Sales */ where ( T70.DAY_DATE = T110.INVOICE_DATE and T93.ITEM_ID = T110.ITEM_ID and T70.YEAR_NUMBER = 2022 and T93.CATEGORY_NAME = 'MEZCAL' and T100.STORE_ID = T110.STORE_ID ) group by T93.VENDOR_NAME, T100.COUNTY) select D1.c1 as c1, D1.c2 as c2, D1.c3 as c3, D1.c4 as c4 from ( select 0 as c1, D1.c2 as c2, D1.c3 as c3, D1.c1 as c4 from SAWITH0 D1 order by c3, c2 ) D1 where rownum <= 65001 WITH SAWITH0 AS (select T1000005.SALE_DOLLARS as c1, T1000005.STORE_ID as c2, T1000005.ITEM_ID as c3, T1000005.INVOICE_DATE as c4 from DWH.F_IA_LIQUOR_SALES T1000005), SAWITH1 AS (select T1000008.COUNTY as c1, T1000008.STORE_ID as c2 from DWH.D_STORE T1000008), SAWITH2 AS (select T1000011.VENDOR_NAME as c1, T1000011.CATEGORY_NAME as c2, T1000011.ITEM_ID as c3 from DWH.D_ITEM T1000011 where ( T1000011.CATEGORY_NAME = 'MEZCAL' ) ), SAWITH3 AS (select T1000014.YEAR_NUMBER as c1, T1000014.DAY_DATE as c2 from DWH.D_DATE T1000014 where ( T1000014.YEAR_NUMBER = 2022 ) ), SAWITH4 AS (select D3.c1 as c1, D2.c1 as c2, D1.c1 as c5 from ( ( SAWITH0 D1 inner join SAWITH1 D2 On D1.c2 = D2.c2) inner join SAWITH2 D3 On D1.c3 = D3.c3) inner join SAWITH3 D4 On D1.c4 = D4.c2), SAWITH5 AS (select D1000001.c1 as c1, D1000001.c2 as c2, sum(D1000001.c5) as c3 from SAWITH4 D1000001 group by D1000001.c1, D1000001.c2) select D1.c1 as c1, D1.c2 as c2, D1.c3 as c3, D1.c4 as c4 from ( select 0 as c1, D116.c1 as c2, D116.c2 as c3, D116.c3 as c4 from SAWITH5 D116 order by c3, c2 ) D1 where rownum <= 65001
43. fed.venturin@gmail.com fventurin.hashnode.dev Example: Semantic Model Vs Dataset o Performance (BI Server cache and XSA cache disabled) Query Status: Successful Completion Rows 297, bytes 111672 retrieved from database query id: <<50068>>, physical request hash 77df9598 Physical query response: Total time 4.114 (seconds), Fetch time 0.000 (seconds), Execute time 4.113 (seconds), id <<50068>>, physical request hash 77df9598 Physical Query Summary Stats: Number of physical queries 1, Cumulative time 4.114, DB-connect time 0.000 (seconds) Rows returned to Client 297 Logical Query Summary Stats: Elapsed time 4.144, Total time in BI Server 4.127, Execution time 4.135, Response time 4.143, Compilation time 0.007 (seconds), logical request hash 8fea7779 Num of Result Cache Hit:0, Num of XSA Cache Hit:0. Deferred init block execution time for logical request hash 8fea7779 : 0.000 (seconds) Query Status: Successful Completion Rows 297, bytes 116424 retrieved from database query id: <<93648>>, physical request hash 403c0537 Physical query response: Total time 3.234 (seconds), Fetch time 0.000 (seconds), Execute time 3.233 (seconds), id <<93648>>, physical request hash 403c0537 Physical Query Summary Stats: Number of physical queries 1, Cumulative time 3.234, DB-connect time 0.000 (seconds) Rows returned to Client 297 Logical Query Summary Stats: Elapsed time 4.139, Total time in BI Server 4.128, Execution time 4.130, Response time 4.138, Compilation time 0.891 (seconds), logical request hash f06be6ba Num of Result Cache Hit:0, Num of XSA Cache Hit:0. Deferred init block execution time for logical request hash f06be6ba : 0.000 (seconds)
44. fed.venturin@gmail.com fventurin.hashnode.dev Example: Semantic Model Vs Dataset o Performance (BI Server cache and XSA cache enabled, first run) Total Cache elapsed time 5.446 (seconds) Query Status: Successful Completion Rows 297, bytes 111672 retrieved from database query id: <<20134>>, physical request hash 77df9598 Physical query response: Total time 5.442 (seconds), Fetch time 0.000 (seconds), Execute time 5.441 (seconds), id <<20134>>, physical request hash 77df9598 Physical Query Summary Stats: Number of physical queries 1, Cumulative time 5.442, DB-connect time 0.001 (seconds) Rows returned to Client 297 Logical Query Summary Stats: Elapsed time 5.475, Total time in BI Server 5.466, Execution time 5.467, Response time 5.474, Compilation time 0.015 (seconds), logical request hash 8fea7779 Num of Result Cache Hit:0, Num of XSA Cache Hit:0. Deferred init block execution time for logical request hash 8fea7779 : 0.000 (seconds) Total Cache elapsed time 53.861 (seconds) Native storage data summary stats for <<46612>>: file size = 6032624 bytes, row count = 2972, column count = 9 Native storage data summary stats for <<46682>>: file size = 4721480 bytes, row count = 12781, column count = 6 Native storage data summary stats for <<46713>>: file size = 4721480 bytes, row count = 96, column count = 2 Native storage data summary stats for <<46807>>: file size = 9967108 bytes, row count = 4261, column count = 22 Query Status: Successful Completion Rows 27277229, bytes 3164158564 retrieved from database query id: <<41299>>, physical request hash b886e1f9 Physical query response: Total time 36.792 (seconds), Fetch time 36.772 (seconds), Execute time 0.018 (seconds), id <<41299>>, physical request hash b886e1f9 Rows 96, bytes 14976 retrieved from database query id: <<46713>>, physical request hash 0 Physical query response: Total time 0.000 (seconds), Fetch time 0.000 (seconds), Execute time 0.000 (seconds), id <<46713>>, physical request hash 0 Rows 4261, bytes 85220 retrieved from database query id: <<46807>>, physical request hash 0 Physical query response: Total time 0.000 (seconds), Fetch time 0.000 (seconds), Execute time 0.000 (seconds), id <<46807>>, physical request hash 0 Rows 2972, bytes 23776 retrieved from database query id: <<46612>>, physical request hash 0 Physical query response: Total time 0.000 (seconds), Fetch time 0.000 (seconds), Execute time 0.000 (seconds), id <<46612>>, physical request hash 0 Rows 12781, bytes 153372 retrieved from database query id: <<46682>>, physical request hash 0 Physical query response: Total time 0.000 (seconds), Fetch time 0.000 (seconds), Execute time 0.000 (seconds), id <<46682>>, physical request hash 0 Physical Query Summary Stats: Number of physical queries 5, Cumulative time 36.792, DB-connect time 0.000 (seconds) Rows returned to Client 297 Logical Query Summary Stats: Elapsed time 96.571, Total time in BI Server 96.559, Execution time 96.553, Response time 96.562, Compilation time 42.700 (seconds), logical request hash 41e4c4d0 Num of Result Cache Hit:0, Num of XSA Cache Hit:3. Deferred init block execution time for logical request hash 41e4c4d0 : 0.000 (seconds)
45. fed.venturin@gmail.com fventurin.hashnode.dev Example: Semantic Model Vs Dataset o Performance (BI Server cache and XSA cache enabled, first run) Total Cache elapsed time 5.446 (seconds) Query Status: Successful Completion Rows 297, bytes 111672 retrieved from database query id: <<20134>>, physical request hash 77df9598 Physical query response: Total time 5.442 (seconds), Fetch time 0.000 (seconds), Execute time 5.441 (seconds), id <<20134>>, physical request hash 77df9598 Physical Query Summary Stats: Number of physical queries 1, Cumulative time 5.442, DB-connect time 0.001 (seconds) Rows returned to Client 297 Logical Query Summary Stats: Elapsed time 5.475, Total time in BI Server 5.466, Execution time 5.467, Response time 5.474, Compilation time 0.015 (seconds), logical request hash 8fea7779 Num of Result Cache Hit:0, Num of XSA Cache Hit:0. Deferred init block execution time for logical request hash 8fea7779 : 0.000 (seconds) Total Cache elapsed time 53.861 (seconds) Native storage data summary stats for <<46612>>: file size = 6032624 bytes, row count = 2972, column count = 9 Native storage data summary stats for <<46682>>: file size = 4721480 bytes, row count = 12781, column count = 6 Native storage data summary stats for <<46713>>: file size = 4721480 bytes, row count = 96, column count = 2 Native storage data summary stats for <<46807>>: file size = 9967108 bytes, row count = 4261, column count = 22 Query Status: Successful Completion Rows 27277229, bytes 3164158564 retrieved from database query id: <<41299>>, physical request hash b886e1f9 Physical query response: Total time 36.792 (seconds), Fetch time 36.772 (seconds), Execute time 0.018 (seconds), id <<41299>>, physical request hash b886e1f9 Rows 96, bytes 14976 retrieved from database query id: <<46713>>, physical request hash 0 Physical query response: Total time 0.000 (seconds), Fetch time 0.000 (seconds), Execute time 0.000 (seconds), id <<46713>>, physical request hash 0 Rows 4261, bytes 85220 retrieved from database query id: <<46807>>, physical request hash 0 Physical query response: Total time 0.000 (seconds), Fetch time 0.000 (seconds), Execute time 0.000 (seconds), id <<46807>>, physical request hash 0 Rows 2972, bytes 23776 retrieved from database query id: <<46612>>, physical request hash 0 Physical query response: Total time 0.000 (seconds), Fetch time 0.000 (seconds), Execute time 0.000 (seconds), id <<46612>>, physical request hash 0 Rows 12781, bytes 153372 retrieved from database query id: <<46682>>, physical request hash 0 Physical query response: Total time 0.000 (seconds), Fetch time 0.000 (seconds), Execute time 0.000 (seconds), id <<46682>>, physical request hash 0 Physical Query Summary Stats: Number of physical queries 5, Cumulative time 36.792, DB-connect time 0.000 (seconds) Rows returned to Client 297 Logical Query Summary Stats: Elapsed time 96.571, Total time in BI Server 96.559, Execution time 96.553, Response time 96.562, Compilation time 42.700 (seconds), logical request hash 41e4c4d0 Num of Result Cache Hit:0, Num of XSA Cache Hit:3. Deferred init block execution time for logical request hash 41e4c4d0 : 0.000 (seconds)
46. fed.venturin@gmail.com fventurin.hashnode.dev Example: Semantic Model Vs Dataset o Performance (BI Server cache and XSA cache enabled, second run) Query Status: Successful Completion Physical Query Summary Stats: Number of physical queries 1, Cumulative time 0.000, DB-connect time 0.000 (seconds) Rows returned to Client 297 Logical Query Summary Stats: Elapsed time 0.021, Total time in BI Server 0.004, Execution time 0.006, Response time 0.020, Compilation time 0.004 (seconds), logical request hash 8fea7779 Num of Result Cache Hit:1, Num of XSA Cache Hit:0. Deferred init block execution time for logical request hash 8fea7779 : 0.000 (seconds) Query Status: Successful Completion Physical Query Summary Stats: Number of physical queries 1, Cumulative time 0.000, DB-connect time 0.000 (seconds) Rows returned to Client 297 Logical Query Summary Stats: Elapsed time 0.647, Total time in BI Server 0.630, Execution time 0.635, Response time 0.646, Compilation time 0.629 (seconds), logical request hash 41e4c4d0 Num of Result Cache Hit:1, Num of XSA Cache Hit:0. Deferred init block execution time for logical request hash 41e4c4d0 : 0.000 (seconds)
47. fed.venturin@gmail.com fventurin.hashnode.dev Oracle Analytics Data Flows can effectively replace ETL tools and procedures Myth #7
48. fed.venturin@gmail.com fventurin.hashnode.dev o Allow visual data manipulation without requiring manual coding skills o But they are not a replacement for ETL tools and procedures Data Flows
49. fed.venturin@gmail.com fventurin.hashnode.dev If it cuts development time in Oracle Analytics, it must improve performance as well Myth #8
50. fed.venturin@gmail.com fventurin.hashnode.dev Overcrowded Analyses o Analyses with multiple views containing many excluded columns
51. fed.venturin@gmail.com fventurin.hashnode.dev o Oracle Analytics retrieves results for all columns listed in Criteria tab Overcrowded Analyses select sum(T42433.Units) as c1, sum(T42433.Revenue) as c2, T42428.Name as c3, T42412.Office_Dsc as c4, T42412.Company as c5, T42433.Order_Status as c6, T42409.Prod_Dsc as c7, T42409.Brand as c8, T42419.Employee_Key as c9, T42404.Calendar_Date as c10, T42404.Per_Name_Year as c11, T42428.Cust_Key as c12, T42409.Prod_Key as c13, T42412.Office_Key as c14 from BISAMPLE.SAMP_OFFICES_D T42412 /* D30 Offices */ , BISAMPLE.SAMP_EMPL_D_VH T42419 /* D50 Sales Rep (Parent Child Hierarchy) */ , BISAMPLE.SAMP_EMPL_PARENT_CHILD_MAP T42420 /* D51 Closure Table Sales Rep Parent Child */ , BISAMPLE.SAMP_PRODUCTS_D T42409 /* D10 Product (Dynamic Table) */ , BISAMPLE.SAMP_CUSTOMERS_D T42428 /* D60 Customers */ , BISAMPLE.SAMP_TIME_DAY_D T42404 /* D01 Time Day Grain */ , BISAMPLE.SAMP_REVENUE_F T42433 /* F10 Billed Rev */ where ( T42412.Office_Key = T42433.Office_Key and T42409.Prod_Key = T42433.Prod_Key and T42419.Employee_Key = T42420.Ancestor_Key and T42404.Calendar_Date = T42433.Bill_Day_Dt and T42420.Member_Key = T42433.Empl_Key and T42428.Cust_Key = T42433.Cust_Key ) group by T42404.Calendar_Date, T42404.Per_Name_Year, T42409.Brand, T42409.Prod_Dsc, T42409.Prod_Key, T42412.Office_Dsc, T42412.Office_Key, T42412.Company, T42419.Employee_Key, T42428.Cust_Key, T42428.Name, T42433.Order_Status
52. fed.venturin@gmail.com fventurin.hashnode.dev o Create multiple smaller analyses and remove excluded columns Overcrowded Analyses select sum(T42442.Units) as c1, sum(T42442.Revenue) as c2, T42406.PER_NAME_YEAR as c3 from BISAMPLE.SAMP_TIME_QTR_D T42406 /* D03 Time Quarter Grain */ , BISAMPLE.SAMP_REVENUE_FA2 T42442 /* F21 Rev. (Aggregate 2) */ where ( T42406.QTR_KEY = T42442.Bill_Qtr_Key ) group by T42406.PER_NAME_YEAR -99.99% rows returned -99.33% elapsed time
53. fed.venturin@gmail.com fventurin.hashnode.dev View Prompts o Provide an interactive result set for users to select the data to view
54. fed.venturin@gmail.com fventurin.hashnode.dev o Do not append any WHERE conditions to the query View Prompts select sum(T42433.Units) as c1, sum(T42433.Revenue) as c2, T42412.Company as c3, T42409.Prod_Dsc as c4, T42409.Brand as c5, T42406.PER_NAME_YEAR as c6, T42409.Prod_Key as c7, T42412.Company_Key as c8 from BISAMPLE.SAMP_OFFICES_D T42412 /* D30 Offices */ , BISAMPLE.SAMP_PRODUCTS_D T42409 /* D10 Product (Dynamic Table) */ , BISAMPLE.SAMP_TIME_QTR_D T42406 /* D03 Time Quarter Grain */ , BISAMPLE.SAMP_REVENUE_F T42433 /* F10 Billed Rev */ where ( T42406.QTR_KEY = T42433.Bill_Qtr_Key and T42409.Prod_Key = T42433.Prod_Key and T42412.Office_Key = T42433.Office_Key ) group by T42406.PER_NAME_YEAR, T42409.Brand, T42409.Prod_Dsc, T42409.Prod_Key, T42412.Company, T42412.Company_Key
55. fed.venturin@gmail.com fventurin.hashnode.dev Use dashboard prompts rather than view prompts View Prompts select sum(T42433.Units) as c1, sum(T42433.Revenue) as c2, T42409.Prod_Dsc as c3, T42409.Prod_Key as c4 from BISAMPLE.SAMP_OFFICES_D T42412 /* D30 Offices */ , BISAMPLE.SAMP_PRODUCTS_D T42409 /* D10 Product (Dynamic Table) */ , BISAMPLE.SAMP_TIME_QTR_D T42406 /* D03 Time Quarter Grain */ , BISAMPLE.SAMP_REVENUE_F T42433 /* F10 Billed Rev */ where ( T42406.QTR_KEY = T42433.Bill_Qtr_Key and T42409.Prod_Key = T42433.Prod_Key and T42406.PER_NAME_YEAR = '2010' and T42409.Brand = ‘BizTech’ and T42412.Office_Key = T42433.Office_Key and T42412.Company = 'Genmind Corp' ) group by T42409.Prod_Dsc, T42409.Prod_Key -95.56% rows returned -25.80% elapsed time
56. fed.venturin@gmail.com fventurin.hashnode.dev o Links views so that one view (master) drives changes in others (detail) o Requires to place at least a master column in the Prompts drop target o Does not append any WHERE condition to the query o Use actions rather than sending master-detail events Master-Detail Linking of Views
57. fed.venturin@gmail.com fventurin.hashnode.dev If it looks cool in Oracle Analytics, it will likely not impact performance Myth #9
58. fed.venturin@gmail.com fventurin.hashnode.dev o Encapsulate the presentation of a hierarchy within a single column o Generate very complex and long SQL o Do not use more than 1 hierarchical column at a time Hierarchical Columns 211 lines of SQL!!!
59. fed.venturin@gmail.com fventurin.hashnode.dev o When a user selects data points in a visualisation, related data points are automatically highlighted in other visualisation o Executes additional queries that may have an impact on performance Brushing
60. fed.venturin@gmail.com fventurin.hashnode.dev o Switch it off by default in the System Settigs page o Authors can still enable it on demand at canvas level when required Brushing F
61. fed.venturin@gmail.com fventurin.hashnode.dev o Auto-Insights generates visualisations automatically o Explain uses ML to automatically explain a metric or attribute o Advanced Analytics creates statistics and ML-generated insights o Different built-in algorithms to train (and apply) predictive models o Python scripts are executed behind the scenes o ORACLE_HOME/bi/bifoundation/advanced_analytics o Loading and manipulating large amounts of data may require a lot of resources Embedded AI and ML Capabilities
62. fed.venturin@gmail.com fventurin.hashnode.dev Myth #10
63. fed.venturin@gmail.com fventurin.hashnode.dev o Do not use Oracle Analytics as a feed for Excel-marts o Can you achieve the same within Oracle Analytics? o Exports to CSV should be used for data sets of > 100K rows o Do not use Excel formats for data sets larger than 100K rows o Exports of >500K rows should only use Publisher capabilities o Exports to CSV of 1M to 10M rows should be scheduled using Publisher o End users should not request large data set downloads on demand o Doc ID 1558070.1 Data Export
64. fed.venturin@gmail.com fventurin.hashnode.dev o Start editing on Criteria tab in Classic Analytics o Toggle off Auto Apply Data while editing workbooks o Make sure dashboards/workbooks have default/mandatory prompts o Be smart about dashboard/workbook design o Database optimisation (partitioning, indexing, parallelism, statistics, etc.) o Balanced hardware configuration o Keep access permissions to the minimum necessary Not Forgetting
65. fed.venturin@gmail.com fventurin.hashnode.dev What About OAC? o Definitely not a collaborative patient! o No DMS metrics o No OS metrics o Query timeout limits o Limited control over cache
66. fed.venturin@gmail.com fventurin.hashnode.dev Treat performance as a feature to be designed from the start