System and method for providing multi-hub datasets for use with data analytics environments
In accordance with an embodiment, described herein is a system and method for providing multi-hub and/or multi-table datasets with a computing environment such as, for example, a business intelligence environment, database, data warehouse, or other type of environment that supports data analytics. An analysis can be used to query data to provide information in the form of tables, graphs, pivot tables, or other data views. A hub table operates as fact table and carries the data metrics for analysis, enabling a user to join two tables, the data from which can be further transformed or enriched to prepare it for analysis.
1 . A system for providing multi-hub and/or multi-table datasets with a computing environment that supports data analytics, comprising:
a computer having a processor, memory, and analytics environment operating thereon;
a business intelligence (BI) server that describes data available as subject areas for queries, and receives and processes incoming queries; and
a user interface provided by the BI server;
wherein the user interface displays a dataset selector comprising a dataset canvas, the dataset selector providing a drag and drop interface allowing a user to drag a plurality of datasets onto the dataset canvas, wherein each dataset comprises data tables;
wherein an instruction is received via the user interface, the instruction comprising an instruction to mark a plurality of the data tables at the dataset canvas as a plurality of hub tables and an instruction to mark another plurality of the data tables at the dataset canvas as a plurality of dimension tables;
wherein the plurality of the data tables is marked as hub tables and the another plurality of the data tables is marked as dimension tables;
wherein a further instruction is received via the user interface, the further instruction specifying a plurality of joins to be performed between specific ones of the plurality of hub tables and specific ones the plurality of dimension tables, the specific ones of the plurality of hub tables and the specific ones of the plurality of dimension tables being specified within the further instruction, the specified plurality of joins comprising an instruction to join a first hub table with two or more of the plurality of dimension table and another instruction to join a second hub table with one or more of the plurality of hub tables;
wherein, based upon the joins between the plurality of hub tables and the plurality of dimension tables, a multi-hub dataset is defined, the multi-hub dataset comprising metadata indicative of the joins between the plurality of hub tables and the plurality of dimension tables;
wherein upon a query for metrics being received directed to the multi-hub dataset, a query plan is constructed based upon the received query for metrics; and
wherein the constructed query plan is run to provide a response to the query by stitching together via a full outer join at least two of the plurality of hub tables together with their respective joins.
2 . The system of claim 1 , wherein a user can create a data set that uses tables from different connections, wherein the system uses relationships defined between the tables to create joins in the data set, for use in providing data analytics or visualizations.
3 . The system of claim 1 , wherein a user defines one or more join types and/or join columns for use with the multi-hub dataset; wherein in response to a query directed to the multi-hub dataset, the system determines an execution plan for the query shape, and accesses data sources and returns a result or dataset in response to the query.
4 . The system of claim 1 , wherein upon receiving the instruction to mark a plurality of the data tables as hub tables, said marking of the plurality of hub tables is persisted in metadata.
5 . The system of claim 1 , wherein the system is provided within a cloud environment.
6 . The system of claim 1 ,
wherein, upon receiving the query for metrics, the query for metrics is determined to be directed to a subset of the plurality of hub tables;
wherein the constructed query plan prunes all of the plurality of hub tables except the subset of the plurality of hub tables to which the query for metrics is directed to; and
wherein only the subset of the plurality of hub tables are stitched together to provide a response to the query.
7 . The system of claim 1 ,
wherein an additional join is automatically determined and executed, the additional join automatically joining a third hub table of the plurality of hub tables with one or more of the plurality of dimension tables based upon an automatic determination of a common column name or compatible data type between the third hub table and the one or more of the plurality of dimension tables.
8 . A method for providing multi-hub and/or multi-table datasets with a computing environment that supports data analytics, comprising:
providing, at one or more computers having a processor, memory, for use with a data analytics environment, a business intelligence (BI) server that describes data available as subject areas for queries, and receives and processes incoming queries;
providing, by the BI server, a user interface;
displaying, at the user interface, a dataset selector comprising a dataset canvas, the dataset selector providing a drag and drop interface allowing a user to drag a plurality of datasets onto the dataset canvas, wherein each dataset comprises data tables;
receiving, via the user interface, an instruction comprising an instruction to mark a plurality of the data tables at the dataset canvas as a plurality of hub tables and an instruction to mark another plurality of the data tables at the dataset canvas as a plurality of dimension tables;
marking the plurality of the data tables as hub tables and the another plurality of the data tables as dimension tables;
receiving, via the user interface, a further instruction specifying a plurality of joins to be performed between specific ones of the plurality of hub tables and specific ones of the plurality of dimension tables, the specific ones of the plurality of hub tables and the specific ones of the plurality of dimension tables being specified within the further instruction, the specified plurality of joins comprising an instruction to join a first hub table with two or more of the plurality of dimension table and another instruction to join a second hub table with one or more of the plurality of hub tables;
defining, based upon the joins between the plurality of hub tables and the plurality of dimension tables, a multi-hub dataset, the multi-hub dataset comprising metadata indicative of the joins between the plurality of hub tables and the plurality of dimension tables;
upon receiving a query for metrics directed to the multi-hub dataset, constructing a query plan based upon the received query for metrics; and
running the constructed query plan to provide a response to the query by stitching together via a full outer join at least two of the plurality of hub tables together with their respective joins.
9 . The method of claim 8 , wherein a user can create a data set that uses tables from different connections, wherein the system uses relationships defined between the tables to create joins in the data set, for use in providing data analytics or visualizations.
10 . The method of claim 8 , wherein a user defines one or more join types and/or join columns for use with the multi-hub dataset; wherein in response to a query directed to the multi-hub dataset, the system determines an execution plan for the query shape, and accesses data sources and returns a result or dataset in response to the query.
11 . The method of claim 8 , wherein upon receiving the instruction to mark a plurality of the data tables as hub tables, said marking of the plurality of hub tables is persisted in metadata.
12 . The method of claim 8 , wherein the system is provided within a cloud environment.
13 . The method of claim 8 ,
wherein, upon receiving the query for metrics, the query for metrics is determined to be directed to a subset of the plurality of hub tables;
wherein the constructed query plan prunes all of the plurality of hub tables except the subset of the plurality of hub tables to which the query for metrics is directed to; and
wherein only the subset of the plurality of hub tables are stitched together to provide a response to the query.
14 . A non-transitory computer readable storage medium having instructions thereon, which when read and executed by a computer including one or more processors cause the computer to perform a method comprising:
providing, at one or more computers having a processor, memory, for use with a data analytics environment, a business intelligence (BI) server that describes data available as subject areas for queries, and receives and processes incoming queries;
providing, by the BI server, a user interface;
displaying, at the user interface, a dataset selector comprising a dataset canvas, the dataset selector providing a drag and drop interface allowing a user to drag a plurality of datasets onto the dataset canvas, wherein each dataset comprises data tables;
receiving, via the user interface, an instruction comprising an instruction to mark a plurality of the data tables at the dataset canvas as a plurality of hub tables and an instruction to mark another plurality of the data tables at the dataset canvas as a plurality of dimension tables;
marking the plurality of the data tables as hub tables and the another plurality of the data tables as dimension tables;
receiving, via the user interface, a further instruction specifying a plurality of joins to be performed between specific ones of the plurality of hub tables and specific ones of the plurality of dimension tables, the specific ones of the plurality of hub tables and the specific ones of the plurality of dimension tables being specified within the further instruction, the specified plurality of joins comprising an instruction to join a first hub table with two or more of the plurality of dimension table and another instruction to join a second hub table with one or more of the plurality of hub tables;
defining, based upon the joins between the plurality of hub tables and the plurality of dimension tables, a multi-hub dataset, the multi-hub dataset comprising metadata indicative of the joins between the plurality of hub tables and the plurality of dimension tables;
upon receiving a query for metrics directed to the multi-hub dataset, constructing a query plan based upon the received query for metrics; and
running the constructed query plan to provide a response to the query by stitching together via a full outer join at least two of the plurality of hub tables together with their respective joins.
15 . The non-transitory computer readable medium of claim 14 , wherein a user can create a data set that uses tables from different connections, wherein the system uses relationships defined between the tables to create joins in the data set, for use in providing data analytics or visualizations.
16 . The non-transitory computer readable medium of claim 14 , wherein a user defines one or more join types and/or join columns for use with the multi-hub dataset; wherein in response to a query directed to the multi-hub dataset, the system determines an execution plan for the query shape, and accesses data sources and returns a result or dataset in response to the query.
17 . The non-transitory computer readable medium of claim 14 , wherein upon receiving the instruction to mark a plurality of the data tables as hub tables, said marking of the plurality of hub tables is persisted in metadata.
18 . The non-transitory computer readable medium of claim 14 , wherein the system is provided within a cloud environment.
19 . The non-transitory computer readable storage medium of claim 14 ,
wherein, upon receiving the query for metrics, the query for metrics is determined to be directed to a subset of the plurality of hub tables;
wherein the constructed query plan prunes all of the plurality of hub tables except the subset of the plurality of hub tables to which the query for metrics is directed to; and
wherein only the subset of the plurality of hub tables are stitched together to provide a response to the query.