User driven warehousing
Approaches for a user-driven warehousing approach are provided, wherein usage patterns for business intelligence applications are gathered, for example in an automated recording fashion, allowing the automated scheduling of jobs in a manner that prioritizes jobs that populate the most-used tables and scheduling those jobs in a manner to ensure that the data is up-to-date prior to when it is generally accessed. The usage pattern analysis also allows for the automated identification of more focused data marts for particular situations. The usage pattern analysis also provides for automated data warehouse/data mart creation and customization based on usage patterns that may be used as a seed, as well as for on-the-fly latitudinal analysis across prepackaged domain-specific applications.
1. A non-transitory computer-readable storage medium storing one or more sequences of instructions for user driven warehousing, which when executed by one or more processors, causes:
storing usage data identifying usage patterns of business intelligence applications, wherein the business intelligence applications access data tables, and wherein the usage data describes which data tables are being accessed along with the time and frequency of the accessing;
accessing metadata describing the data model of the business intelligence applications;
determining a refresh priority for the data tables, wherein the refresh priority is based on usage patterns of the data tables over a predetermined period of time; and
automatically scheduling Extract, Transform, Load (ETL) jobs related to the data tables based on the refresh priority and clustering of the usage of tables by time to ensure that the data tables are refreshed before a time of their anticipated use.
2. The non-transitory computer-readable storage medium of claim 1 , wherein execution of the one or more sequences of instructions further comprises:
accessing metadata describing relationships between business intelligence application reports, data queries used by the reports, and the set of data tables referenced by the queries to translate usage data on reports to usage data on data tables.
3. The non-transitory computer-readable storage medium of claim 1 , wherein execution of the one or more sequences of instructions for automatically scheduling ETL jobs further comprises:
automatically creating a data mart based on the usage data, wherein the data mart comprises a subset of data tables stored in a data warehouse; and
automatically providing access to the data mart, wherein the access is provided independent of manual configuration.
4. The non-transitory computer-readable storage medium of claim 3 , wherein execution of the one or more sequences of instructions further comprises:
identifying functional dependencies between the business intelligence applications and the data tables stored in a data warehouse; and
preserving the functional dependencies between the business intelligence applications and data tables in the newly created data mart.
5. The non-transitory computer-readable storage medium of claim 1 , wherein execution of the one or more sequences of instructions further comprises:
receiving a request to create a new business intelligence system, wherein the new system comprises business intelligence applications, business intelligence reports, ETL jobs, and a data warehouse;
analyzing the stored usage data to determine a previously-created business intelligence system to use as a template for the new business intelligence system;
creating the new business intelligence system; and
automatically configuring the new business intelligence system based on the stored usage data.
6. The non-transitory computer-readable storage medium of claim 5 , wherein execution of the one or more sequences of instructions further comprises:
gathering new usage data on the new business intelligence system over a period of time; and
modifying the new business intelligence system based on the new usage data, wherein the modifying includes creating data marts and scheduling ETL jobs operable to provide data to the data marts.
7. The non-transitory computer-readable storage medium of claim 1 , wherein execution of the one or more sequences of instructions further comprises:
receiving a request for latitudinal analysis;
automatically creating a data mart based on the request, wherein the data mart comprises data tables from a plurality of data warehouses;
analyzing the metadata describing the data model of the data tables from the plurality of data warehouses; and
automatically creating ETL jobs to populate the data mart.
8. The non-transitory computer-readable storage medium of claim 1 , wherein execution of the one or more sequences of instructions further comprises:
receiving a request for ad-hoc latitudinal analysis;
analyzing the metadata describing the data model of the data tables from the plurality of data warehouses;
automatically creating ETL jobs to load a subset of the data tables from the plurality of data warehouses; and
synchronizing the ETL jobs such that the data tables are loaded by a preferred point in time to ensure consistent results for the ad-hoc latitudinal analysis.
9. An apparatus for user driven warehousing, comprising:
one or more processors; and
one or more non-transitory computer-readable storage mediums storing one or more sequences of instructions, which when executed by the one or more processors, cause:
storing usage data identifying usage patterns of business intelligence applications, wherein the business intelligence applications access data tables, and wherein the usage data describes which data tables are being accessed along with the time and frequency of the accessing;
accessing metadata describing the data model of the business intelligence applications;
determining a refresh priority for the data tables, wherein the refresh priority is based on the usage patterns of the data tables over a predetermined period of time; and
automatically scheduling Extract, Transform, Load (ETL) jobs related to the data tables based on the refresh priority and clustering of the usage of tables by time to ensure that the data tables are refreshed before a time of their anticipated use.
10. The apparatus of claim 9 , wherein execution of the one or more sequences of instructions further causes:
accessing metadata describing relationships between business intelligence application reports, data queries used by the reports, and the set of data tables referenced by the queries to translate usage data on reports to usage data on data tables.
11. The apparatus of claim 9 , wherein execution of the one or more sequences of instructions for automatically scheduling ETL jobs further comprises:
automatically creating a data mart based on the usage data, wherein the data mart comprises a subset of data tables stored in a data warehouse; and
automatically providing access to the data mart, wherein the access is provided independent of manual configuration.
12. The apparatus of claim 11 , wherein execution of the one or more sequences of instructions further causes:
identifying functional dependencies between the business intelligence applications and the data tables stored in a data warehouse; and
preserving the functional dependencies between the business intelligence applications and data tables in the newly created data mart.
13. The apparatus of claim 9 , wherein execution of the one or more sequences of instructions further causes:
receiving a request to create a new business intelligence system, wherein the new system comprises business intelligence applications, business intelligence reports, ETL jobs, and a data warehouse;
analyzing the stored usage data to determine a previously-created business intelligence system to use as a template for the new business intelligence system;
creating the new business intelligence system; and
automatically configuring the new business intelligence system based on the stored usage data.
14. The apparatus of claim 13 , wherein execution of the one or more sequences of instructions further causes:
gathering new usage data on the new business intelligence system over a period of time; and
modifying the new business intelligence system based on the new usage data, wherein the modifying includes creating data marts and scheduling ETL jobs operable to provide data to the data marts.
15. The apparatus of claim 9 , wherein execution of the one or more sequences of instructions further causes:
receiving a request for latitudinal analysis;
automatically creating a data mart based on the request, wherein the data mart comprises data tables from a plurality of data warehouses;
analyzing the metadata describing the data model of the data tables from the plurality of data warehouses; and
automatically creating ETL jobs to populate the data mart.
16. The apparatus of claim 9 , wherein execution of the one or more sequences of instructions further causes:
receiving a request for ad-hoc latitudinal analysis;
analyzing the metadata describing the data model of the data tables from the plurality of data warehouses;
automatically creating ETL jobs to load a subset of the data tables from the plurality of data warehouses; and
synchronizing the ETL jobs such that the data tables are loaded by a preferred point in time to ensure consistent results for the ad-hoc latitudinal analysis.
17. A method for user driven warehousing, comprising:
storing usage data identifying usage patterns of business intelligence applications, wherein the business intelligence applications access data tables, and wherein the usage data describes which data tables are being accessed along with the time and frequency of the accessing;
accessing metadata describing the data model of the business intelligence applications;
determining a refresh priority for the data tables, wherein the priority is based on usage patterns of the data tables over a predetermined period of time; and
automatically scheduling Extract, Transform, Load (ETL) jobs related to the data tables based on the refresh priority and clustering of the usage of tables by time to ensure that the data tables are refreshed before a time of their anticipated use.
18. The method of claim 17 , further comprising: accessing metadata describing relationships between business intelligence application reports, data queries used by the reports, and the set of data tables referenced by the queries to translate usage data on reports to usage data on data tables.
19. The method of claim 17 , wherein automatically scheduling ETL jobs further comprises:
automatically creating a data mart based on the usage data, wherein the data mart comprises a subset of data tables stored in a data warehouse; and
automatically providing access to the data mart, wherein the access is provided independent of manual configuration.
20. The method of claim 19 , further comprising:
identifying functional dependencies between the business intelligence applications and the data tables stored in a data warehouse; and
preserving the functional dependencies between the business intelligence applications and data tables in the newly created data mart.
21. The method of claim 17 , further comprising:
receiving a request to create a new business intelligence system, wherein the new system comprises business intelligence applications, business intelligence reports, ETL jobs, and a data warehouse;
analyzing the stored usage data to determine a previously-created business intelligence system to use as a template for the new business intelligence system;
creating the new business intelligence system; and
automatically configuring the new business intelligence system based on the stored usage data.
22. The method of claim 21 , further comprising:
gathering new usage data on the new business intelligence system over a period of time; and
modifying the new business intelligence system based on the new usage data, wherein the modifying includes creating data marts and scheduling ETL jobs operable to provide data to the data marts.
23. The method of claim 17 , further comprising:
receiving a request for latitudinal analysis;
automatically creating a data mart based on the request, wherein the data mart comprises data tables from a plurality of data warehouses;
analyzing the metadata describing the data model of the data tables from the plurality of data warehouses; and
automatically creating ETL jobs to populate the data mart.
24. The method of claim 17 , further comprising:
receiving a request for ad-hoc latitudinal analysis;
analyzing the metadata describing the data model of the data tables from the plurality of data warehouses;
automatically creating ETL jobs to load a subset of the data tables from the plurality of data warehouses; and
synchronizing the ETL jobs such that the data tables are loaded by a preferred point in time to ensure consistent results for the ad-hoc latitudinal analysis.