Systems, methods, and devices for generation of analytical data reports using dynamically generated queries of a structured tabular cube

• US 10,509,805 B2
• Filed: 03/13/2019
• Issued: 12/17/2019
• Est. Priority Date: 03/13/2018
• Status: Active Grant

• Current Assignee: deFacto Global, Inc.
• Original Assignee: deFacto Global, Inc.
• Inventors: Bedard, Robert, D'Anna, Anthony Henry, Galante, Mike
• Primary Examiner(s): Uddin, Mohammed R
• Application Number:
  US16/352,143
• Publication Number:
  US 20190286636A1
• Time in Patent Office: 279 Days
• Field of Search: 707803, 707804, 707807, 707811
• CPC Class Codes: G06F 16/242 Query formulation
• CPC Class Codes: G06F 16/245 Query processing
• CPC Class Codes: G06F 16/2465 Query processing support for facilitating data mining operations in structured databases
• CPC Class Codes: G06F 16/248 Presentation of query results
• CPC Class Codes: G06F 16/283 Multi-dimensional databases or data warehouses, e.g. MOLAP or ROLAP

Abstract

Certain embodiments described herein relate to data analysis systems, methods, and devices that comprise one or more processes and/or methodologies to add structure around a tabular database. By doing so, certain data analysis systems, methods, and devices can provide similar analytical functionalities as structured databases on one hand, as well as similar scalability and/or performance capabilities as tabular databases. In some embodiments, an add-in to a tabular data engine, such as Microsoft Excel, can be provided, through which a user can be allowed to input one or more dimensions and/or variables as desired for generating an analytical report. Based on such input, in certain embodiments, the system can be configured to build one or more data analysis expression (DAX) queries to dynamically generate a report, which can subsequently be displayed in the tabular data engine.

Claims

1. A computer-implemented method for dynamically generating an analytical report using one or more tabular databases queryable by one or more Data Analysis Expressions (DAX), wherein the one or more tabular databases are generated from one or more unstructured data stores, the computer-implemented method comprising:

receiving, via a report designer of a dynamic user interface of a client service application on a user access point, one or more analytical report parameters, wherein the one or more analytical report parameters define a configuration of a requested analytical report, wherein the client service application comprises a spreadsheet application;

receiving, via the dynamic user interface of the client service application, one or more structural component inputs of the requested analytical report, wherein the one or more structural component inputs comprise one or more requested data dimensions and/or one or more hierarchies within the one or more requested data dimensions, wherein the dynamic user interface is launched via the spreadsheet application;

transmitting the one or more analytical report parameters and the one or more structural component inputs to a reporting service, wherein the reporting service acts as a link between the client service application and a report generator;

constructing, by the report generator, in conjunction with a DAX query builder, one or more DAX queries based on the received one or more analytical report parameters and the one or more structural component inputs of the requested analytical report;

querying the one or more tabular databases using the one or more constructed DAX queries, wherein the one or more tabular databases comprise a Server Analysis Services (SSAS) tabular model and provides at least one functionality of a relational data structure and at least one functionality of a tabular data structure, wherein the Server Analysis Services (SSAS) tabular model is structured to be queried by the one or more DAX queries, and wherein the one or more tabular databases comprise a tabular cube operating within a Microsoft Structured Query Language (SQL) Server,

wherein the tabular cube is constructed by retroactively transforming and separating the one or more unstructured data stores accessible by the Microsoft Structured Query Language (SQL) Server into fact data and metadata using one or more relational star schemas, wherein the fact data and metadata are stored within the one or more tabular databases, and wherein the tabular cube comprises an organizational data structure comprising the one or more requested data dimensions and/or one or more hierarchies within the one or more requested data dimensions;

generating, by the report generator, the requested analytical report based on one or more DAX query results, wherein the one or more DAX query results are returned by the one or more tabular databases in response to the one or more constructed DAX queries;

transmitting, from the reporting service to the client service application, the requested analytical report; and

rendering the requested analytical report within the client service application,

wherein the user access point comprises a computer processor and an electronic storage medium.

2. The computer implemented method of claim 1, wherein the dynamic user interface is built into the tabular data engine.

3. The computer implemented method of claim 1, wherein the analytical report comprises a web-based, mobile-based, or Microsoft Excel-based report.

4. The computer implemented method of claim 1, wherein the analytical report comprises one or more visualization tools, charts, or dashboards.

5. The computer implemented method of claim 1, wherein the analytical report parameters comprise row parameters, column parameters, slicers, filters, or report properties.

6. The computer implemented method of claim 1, wherein the one or more relational star schemas comprise a Structured Query Language (SQL) Server star schema.

7. The computer implemented method of claim 1, wherein the one or more relational star schemas comprise relational data associated with tabular data stored in the one or more tabular databases.

8. The computer implemented method of claim 7, wherein the relational data comprises metadata.

9. The computer implemented method of claim 7, wherein the relational data is generated through an administrator interface and/or an integrated data source.

10. The computer implemented method of claim 9, wherein the integrated data source is integrated using Structured Query Language (SQL) Server Integration Services (SSIS).

11. The computer implemented method of claim 7, wherein the DAX query builder is in communication with the one or more relational star schemas, and wherein the one or more relational star schemas provides the relational data to the DAX Query builder.

12. The computer implemented method of claim 7, wherein the constructing the one or more DAX queries comprises utilizing the relational data.

13. The computer implemented method of claim 1, further comprising performing, by a business logic engine, one or more procedural calculations, wherein the one or more procedural calculations can be called on-demand via the dynamic user interface of a client service application.

14. The computer implemented method of claim 13, wherein the procedural calculations comprise at least one of:

write-backs, foreign currency conversions, allocations, intercompany matchings and/or eliminations, binary tuple calculations, copying data from one tuple to another, MDX and/or DAX to fact data, or custom user-defined calculations.

15. The computer implemented method of claim 1, further comprising obtaining approval, via the dynamic user interface, from one or more authorized users to generate the requested analytical report.

16. The computer implemented method of claim 1, wherein the tabular cube comprises fact data and relational data.

17. A system for dynamically generating an analytical report using one or more tabular databases queryable by one or more Data Analysis Expressions (DAX), wherein the one or more tabular databases are generated from one or more unstructured data stores, the system comprising:

a client service application comprising a spreadsheet application and a dynamic user interface, the dynamic user interface comprising a report designer configured to allow user input of one or more analytical report parameters and one or more structural component inputs of an analytical data report, wherein the one or more structural component inputs comprise one or more requested data dimensions and/or one or more hierarchies within the one or more requested data dimensions, wherein the dynamic user interface is launched via the spreadsheet application;

a Data Analysis Expression (DAX) query builder;

a report generator;

a reporting service that acts as a link between the client service application and the report generator;

one or more relational star schemas;

the one or more tabular databases, wherein the one or more tabular databases comprise a Server Analysis Services (SSAS) tabular model and provides at least one functionality of a relational data structure and at least one functionality of a tabular data structure, wherein the Server Analysis Services (SSAS) tabular model is structured to be queried by one or more DAX queries, and wherein the one or more tabular databases comprise a tabular cube operating within a Microsoft Structured Query Language (SQL) Server, wherein the tabular cube is constructed by retroactively transforming and separating one or more unstructured data stores accessible by the Microsoft Structured Query Language (SQL) Server into fact data and metadata using the one or more relational star schemas, wherein the fact data and metadata are stored within the one or more tabular databases, and wherein the tabular cube comprises an organizational data structure comprising the one or more requested data dimensions and/or one or more hierarchies within the one or more requested data dimensions;

one or more computer readable storage devices; and

one or more hardware computer processors configured to;

receive, via the report designer, the one or more analytical report parameters, wherein the one or more analytical report parameters define a configuration of a requested analytical data report;

receive, via the report designer, the one or more structural component inputs of the requested analytical data report;

transmit the one or more analytical report parameters and the one or more structural component inputs to the reporting service;

construct, by the report generator, in conjunction with the DAX query builder, one or more DAX queries based on the received one or more analytical report parameters and the one or more structural component inputs of the requested analytical report;

query the one or more tabular databases using the one or more constructed DAX queries;

generate, by the report generator, the requested analytical data report based on one or more DAX query results, wherein the one or more DAX query results are returned by the one or more tabular databases in response to the one or more constructed DAX queries;

transmit, from the reporting service to the client service application, the requested analytical report; and

render the requested analytical report within the client service application.

18. The system of claim 17, wherein the one or more hardware computer processors are further configured to perform a write-back on the one or more tabular databases through the tabular data engine.