Apparatus and method for recommending and maintaining analytical views
A system has a distributed database with database partitions distributed across worker nodes connected by a network. An analytical view recommendation engine defines an analytical view comprising attributes and measures defined prior to the receipt of a query. The analytical view is maintained as a data unit separate from the distributed database. The analytical view recommendation engine includes instructions executed by a processor to identify a poorly performing report, evaluate queries associated with the poorly performing report, create a recommended analytical view to enhance the performance of the poorly performing report, and deploy the recommended analytical view.
1. A system, comprising;
a distributed database with database partitions distributed across worker nodes connected by a network; and
an analytical view recommendation engine to define an analytical view comprising attributes and measures defined prior to the receipt of a query and to maintain as a data unit separate from the distributed database, wherein the analytical view recommendation engine includes instructions executed by a processor to:
identify a poorly performing report, wherein the poorly performing report includes queries to collect data from the distributed database or analytical view,
evaluate the queries associated with the poorly performing report,
create a recommended analytical view from a query of the evaluated queries,
determine whether the recommended analytical view overlaps with existing analytical views, and if so, merge overlapping analytical views to create the recommended analytical view, and
test the recommended analytical view to determine if performance of the report is improved, and if so, deploy the recommended analytical view.
2. The system of claim 1 wherein the recommended analytical view is different from an analytical view to which the query of the evaluated queries is routed.
3. The system of claim 1 wherein the analytical view recommendation engine applies system restrictions to the recommended analytical view.
4. The system of claim 3 wherein the system restrictions include one of a limit on disk space for recommended analytical views and a time window for refreshing recommended analytical views.
5. The system of claim 1 wherein the poorly performing report demonstrates inadequate performance relative to one or more metrics specifying rendering within a specified number of computation cycles or in relation to a threshold size.
6. The system of claim 5 wherein the test to determine if performance of the report is improved comprises determining an improvement of the one or more metrics.
7. The system of claim 1 wherein the overlapping analytical views are merged using cardinality and costing heuristics to decide when recommended analytical views should be merged.
8. The system of claim 1 wherein the analytical view recommendation engine is executed by an analytical view machine connected to the network.
9. The system of claim 1 wherein the analytical view recommendation engine is executed by a master machine connected to the network, wherein the master machine coordinates the operation of the plurality of worker nodes.
10. The system of claim 1 wherein the recommended analytical view incorporates data that is not required by a query in the poorly performing report.
11. The system of claim 10 wherein the data that is not required is eliminated based upon an evaluation of dimensions within the poorly performing report.
12. The system of claim 10 wherein the data that is not required is eliminated based upon an evaluation of filters within the poorly performing report.
13. The system of claim 10 wherein the data that is not required is eliminated based upon an evaluation of predicates within the poorly performing report.
14. The system of claim 1 wherein the recommended analytical view is based upon a split of existing analytical views.
15. The system of claim 1 wherein the analytical view recommendation engine is configured to selectively eliminate an analytical view.
16. The system of claim 15 wherein the analytical view recommendation engine selectively eliminates an analytical view based upon one of analytical view size and analytical view usage.
17. The system of claim 1 wherein the analytical view recommendation engine comprises a background process that continuously evaluates existing reports to determine whether performance can be enhanced through recommended analytical views.
18. The system of claim 1 further comprising a feedback loop between an analytical view module and the analytical view recommendation engine to monitor and accelerate performance of the poorly performing report.
19. A method, comprising;
accessing a distributed database with database partitions distributed across worker nodes connected by a network;
defining an analytical view comprising attributes and measures defined prior to the receipt of a query and to maintain as a data unit separate from the distributed database;
identifying a poorly performing report, wherein the poorly performing report includes queries to collect data from the distributed database or analytical view,
evaluating the queries associated with the poorly performing report,
creating a recommended analytical view from a query of the evaluated queries;
determining whether the recommended analytical view overlaps with existing analytical views, and if so, merging overlapping analytical views to create the recommended analytical view; and
testing the recommended analytical view to determine if performance of the report is improved, and if so, deploying the recommended analytical view.
20. The method of claim 19 wherein the recommended analytical view is different from an analytical view to which the query of the evaluated queries is routed.