Method, system, and computer readable medium for autonomous testing of logical model inconsistencies

Abstract

Embodiments autonomously test a logical model for inconsistencies. For example, metadata describing a logical model can be received, where the logical model includes an abstraction of a database schema, the database schema is implemented at a database, and the database schema includes fact tables and dimension tables. Logical queries including at least a first logical query and a second logical query can be automatically generated based on the retrieved metadata, where the first logical query and the second logical query target logical objects of the logical model. The at least first logical query and the second logical query can be issued to a server hosting the logical model, where, at the server, the first logical query and the second logical query are transformed into first database queries and second database queries, and the first database queries and the second database queries target at least fact tables and dimension tables from the database schema. Query results received from execution of the first database queries and the second database queries can be compared. When the comparison of the query results does not satisfy a criterion, an inconsistency can be identified.

Description

TECHNICAL FIELD

[0001] Embodiments of the present disclosure generally relate to autonomously testing inconsistencies of a logical model. BACKGROUND

[0002] The proliferation of computing and connected devices has generated a large amount of data that needs to be managed. Challenges to aspects of data management and access persist, such as efficient querying of complex data schemes. Some modern database implementations include layers of abstraction of complex data schemes into logical models, for example, to support logical queries in a more simplified form. Further, extract, transform, and load (“ETL” or “ELT”) processes can be used to populate databases that implement complex schemes. Data management and access aspects can be cumbersome when utilizing traditional techniques due to the large number and variety of issues that arise based on the complexity of these components and their interactions. SUMMARY

[0003] Embodiments of the present disclosure generally relate to systems and methods for autonomously testing inconsistencies of a logical model, which substantially improve the pertinent art.

[0004] Metadata describing a logical model can be received, where the logical model includes an abstraction of a database schema, the database schema is implemented at a database, and the database schema includes a fact table and one or more dimension tables. A plurality of logical queries including at least a first logical query and a second logical query can be automatically generated based on the retrieved metadata, where the first logical query and the second logical query target logical objects of the logical model. The at least first logical query and the second logical query can be issued to a server hosting the logical model, where, at the server, the first logical query is translated into a first database query and the second logical query is translated into a second database query, and the first database query and the second database query target at least the fact table and the dimension tables from the database schema. Query results received from execution of the first database query and the second database query can be compared. One or more inconsistencies can be identified when the comparison of the query results of the first database query and the second database query does not satisfy a criterion, where the one or more inconsistencies include an inconsistency with the logical model as defined by the metadata or an inconsistency at the database.

[0005] The features and advantages of the embodiments will be set forth in the descriptions below, and in part will be apparent from the description, or can be learned by practice of the disclosure. BRIEF DESCRIPTION OF DRAWINGS

[0006] Further embodiments, details, advantages, and modifications will become apparent from the following detailed description, figures, and claims, or can be learnt by practice of the disclosure.

[0007] Figure 1 A system for autonomously testing inconsistencies in a logical model is illustrated in accordance with example embodiments.

[0008] Figure 2 A block diagram of a computing device operably coupled to an autonomous tester is illustrated in accordance with example embodiments.

[0009] Figure 3 A sample star data schema is illustrated in accordance with example embodiments.

[0010] Figure 4A And Figure 4B An implementation of a system for autonomously testing inconsistencies in a logical model is illustrated in accordance with example embodiments.

[0011] Figure 5 And Figure 6 A pair of database queries and their results are illustrated in accordance with example embodiments.

[0012] Figure 7 And Figure 8 Another pair of database queries and their results are illustrated in accordance with example embodiments.

[0013] Figure 9 An example flowchart for autonomously testing inconsistencies in a logical model is illustrated in accordance with example embodiments. DETAILED DESCRIPTION

[0014] Embodiments enable autonomous testing of inconsistencies in a logical model. For example, data schemas sometimes require complex queries that use joins and other complex query structures to target related data from multiple tables / fields. To mitigate this query burden, some database implementations and reporting tools include a logical model or a layer that maps the underlying complex data schema to a more simplified logical model. For example, the logical model can then be queried using simpler logical queries that can be transformed into queries that target the underlying data schema (e.g., into complex queries that can access / retrieve data from the underlying data schema).

[0015] Some embodiments of a logical model can include a multi-layered complexity, including various aggregation functions, drill-down functions, join behavior, and the like. For example, one or more tools can be used to quickly develop a complex logical model, such as by defining metadata that can be used to deploy the model. In some embodiments, a tool can be used to define a conceptual data model through a user interface, and the output of such a tool can be metadata that stores the conceptual relationships of the components of the logical data model.

[0016] In some embodiments, these tools can improve the efficiency of deploying a complex database, however the deployment can sometimes include low-level inconsistencies and / or defects. For example, aggregation functions defined in a logical model can not align with the underlying data schema, one or more tables can not be properly loaded, configurations of data fields (e.g., cannot be null) can not be properly set, and more. These inconsistencies and / or defects often require detailed manual effort to debug.

[0017] Embodiments generate queries based on the metadata of a logical model that autonomously test the implementation of the logical model for inconsistencies and / or defects in the underlying data schema. For example, the metadata of a logical model can be retrieved and analyzed to determine the conceptual relationships between the components of the model. In some embodiments, queries can be generated based on the expected behavior of the data schema and the logical model given the analyzed metadata. For example, a pair of queries that test expected aggregation functions (e.g., across one or more dimensions of the data schema) can be generated.

[0018] Embodiments analyze the results of these queries. For example, two or more queries can be designed to return similar result sets (e.g., the same data values) when the logical data model accurately reflects the data relationships in the data schema. For these two or more queries, a returned dissimilar result set can indicate an inconsistency with the logical model. In other examples, a query error can be returned due to an improper loading of data, a missing database table or column, an incorrect embedded SQL expression, and / or an improper configuration of a data field. In some embodiments, these results that deviate from the expected results can be used to identify one or more of these inconsistencies.

[0019] A detailed reference will now be made to embodiments of the present disclosure, examples of which are illustrated in the accompanying drawings. In the following detailed description, numerous specific details are set forth in order to provide a thorough understanding of the present disclosure. However, it will be apparent to one of ordinary skill in the art that the present disclosure can be practiced without these specific details. In other instances, well-known methods, procedures, components, and circuits have not been described in detail so as not to unnecessarily obscure aspects of the embodiments. Like reference numerals will be used to refer to like elements throughout.

[0020] Figure 1 A system for autonomously testing a logical model for inconsistencies is illustrated in accordance with an embodiment. The system 100 includes metadata retrieval 102, metadata analysis 104, query generation 106, query execution 108, result comparison 110, and discovered inconsistencies 112. Metadata retrieval 102 can include retrieval of metadata for a logical data model, such as metadata defining conceptual relationships between components of the logical model (e.g., data tables, columns, keys, joins, etc.). Metadata analysis 104 can analyze the retrieved metadata to identify these conceptual relationships. Query generation 106 can generate queries, such as pairs of queries, series of related queries, etc., based on data patterns and expected behavior of the logical model given the analyzed metadata. For example, a pair of queries testing expected aggregation functionality (e.g., across one or more dimensions of the logical model) can be generated, and other suitable queries can be generated.

[0021] Query execution 108 can execute the queries using a server implementing the logical model and at a database implementing the data pattern. For example, query execution can include the server transforming the generated queries (e.g., logical queries) into transformed queries (e.g., data pattern queries) such that the transformed queries can be used to retrieve data from an implementation of the data pattern (e.g., a database). In some embodiments, a result set from executing the transformed queries at the database can be returned. Result comparison 110 can compare the results of the queries to identify inconsistencies or other unexpected behavior (e.g., given an expectation of consistency).

[0022] For example, the generated pair (or set) of queries can be designed to return similar results (e.g., result sets with one or more values that differ by a threshold value) such as when the logical data model accurately reflects the data relationships in the data schema. When the result comparison 110 determines that the two or more queries designed to return similar results have actually returned dissimilar result sets, an inconsistency can be identified. Further, some queries can return errors due to improper loading of data or improper configuration of data fields. In some embodiments, these results that deviate from expected results can be used to identify inconsistencies (e.g., between the data model and the implemented data schema, based on incorrectly loaded data, etc.).

[0023] In some embodiments, the underlying data schema (e.g., implemented at the database) can be any suitable schema for storing data, such as a set of relational data tables, a multidimensional data schema, a set of relational tables configured according to a set of rules or standards, such as a third normal form ("3NF") schema, and any other suitable schema. Generally, the schema will include data tables having one or more data columns. The schema is defined not only by the tables and the data they store, but also by the relationships between the tables. For example, the relationship between a first table and a second table can be defined by a foreign key that links the data stored in each of the tables. In some embodiments, two tables can share multiple relationships (e.g., can have multiple foreign keys that define the relationship between the tables). Different types of relationships between tables will be further disclosed herein.

[0024] The design of the data schema and the logical model can generally differ based on the designer. For example, a given set of data having a given set of relationships can be successfully represented by multiple data schemas having different designs and / or logical models. Some designs can require table joins to retrieve a particular set of data, while others do not. Accordingly, the data schema and logical model being tested can include any suitable design choice, and the queries generated by the query generation 106 and executed by the query execution 108 can be designed to test for inconsistencies and / or defects for a variety of different data schemas and logical models.

[0025] Figure 2 is a block diagram of a computer server / system 210 according to embodiments. As Figure 2As shown in FIG. 2, system 210 can include bus device 212 and / or other communication mechanisms providing communications between various components of system 210, such as processor 222 and memory 214. Furthermore, communication device 220 can enable wired or wireless communications to other devices, which can be the same as or different than system 210.

[0026] For example, communication device 220 can include a network interface card configured to provide wireless network connectivity to system 210. Various wireless communication techniques can be employed including, for example, infrared, radio frequency Wi-Fi, and / or cellular communication. Alternatively, communication device 220 can be configured to provide wired network connectivity to system 210, such as an Ethernet connection.

[0027] Processor 222 can include one or more general purpose or special purpose processors to execute the computing and control functionality of system 210. Processor 222 can include a single integrated circuit, such as a microprocessor device, or can include multiple integrated circuit devices and / or circuit boards that work in concert to fulfill the functionality of processor 222. Furthermore, processor 222 can execute computer programs stored within memory 214, such as operating system 215, autonomous tester 216, and other applications 218.

[0028] System 210 can include memory 214 for storing information and instructions for execution by processor 222. Memory 214 can include various components for retrieving, presenting, modifying, and storing data. For example, memory 214 can store software modules that provide functionality when executed by processor 222. These modules can include operating system 215 that provides operating system functionality for system 210. These modules can include operating system 215, autonomous tester 216, and other application modules 218. Operating system 215 provides operating system functionality for system 210. Autonomous tester 216 can provide system functionality for autonomously testing data patterns for inconsistencies, or can further provide any other functionality of the present disclosure. In some cases, autonomous tester 216 can be implemented as an in-memory configuration.

[0029] Non-transitory memory 214 can include various computer-readable media capable of being accessed by processor 222. For example, memory 214 can include any combination of random access memory (“RAM”), dynamic RAM (“DRAM”), static RAM (“SRAM”), read-only memory (“ROM”), flash memory, cache memory, and / or any other type of non-transitory computer- readable medium.

[0030] The processor 222 is also coupled via the bus 212 to the display 224, such as a liquid crystal display ("LCD"). The keyboard 226 and the cursor control device 228, such as a computer mouse, are further coupled to the communication device 220 to enable a user to interface with the system 210.

[0031] In some embodiments, the system 210 can be part of a larger system. Thus, the system 210 can include one or more additional functional modules 218 to include additional functionality. Other application modules 218 can include various components of a data warehouse including, for example, an operating system and data warehouse targets, business intelligence ("BI"), analytics cloud, analytics server, and other suitable components. The database 217 is coupled to the bus 212 to provide centralized storage for the modules 216 and 218 and to store, for example, wireless device activity, and in some embodiments, user profiles, transaction history, etc. The database 217 can store data in an integrated collection of logically-related records or files. The database 217 can be an operational database, an analytical database, a data warehouse, a distributed database, an end-user database, an external database, a navigational database, an in-memory database, a document-oriented database, a real-time database, a relational database, an object-oriented database, a Hadoop Distributed File System ("HDFS"), or any other database known in the art.

[0032] Although illustrated as a single system, the functionality of the system 210 can be implemented as a distributed system. For example, the memory 214 and the processor 222 can be distributed across multiple different computers collectively representing the system 210. In one embodiment, the system 210 can be part of a device (e.g., a smartphone, a tablet, a computer, etc.). In embodiments, the system 210 can be separate from the device and can remotely provide the described functionality for the device. Further, one or more components of the system 210 can not be included. For example, for functionality as a user or consumer device, the system 210 can be a smartphone or other wireless device including a processor, memory, and display, not including one or more of the other components shown in Figure 2 and including additional components not shown in Figure 2

[0033] Referring back to Figure 1 ​Embodiments use queries from the master to test the logical model for inconsistencies, using the metadata of the logical model to form the queries. For example, one or more tools, such as a tool that defines a repository file (e.g., an RPD file), can be used to generate metadata that defines the logical model. The repository file can define data structures (e.g., relational tables and / or logical objects), data fields (e.g., columns), relationships between data structures (e.g., foreign keys, joins, etc.), functions of data (e.g., aggregate functions, drill keys, etc.), and other information related to the logical model.

[0034] Embodiments of a logical model are a logical abstraction of an underlying data schema. Figure 3 A sample star data schema is illustrated in accordance with example embodiments. The data schema 300 includes a fact table 302, a dimension_1 table 304, a dimension_2 table 306, and a dimension_3 table 308. Generally, in a star schema, a fact table holds facts about a domain, while dimension tables hold the attributes of those facts. As a result, the fact table 302 has various foreign key relationships with the dimension_1 table 304, the dimension_2 table 306, and the dimension_3 table 308. In other words, some of the rows in the fact table are foreign keys ("FKs"). The foreign keys can be links to the dimension tables. The dimension tables can be tables that store context associated with events referenced by one or more fact tables.

[0035] Star data schemas are similar to snowflake data schemas, but also have some differences. For example, a snowflake data schema includes dimensions that are normalized into multiple related tables, while a star schema has denormalized dimensions, each represented by a single table. Each of these schemas provides different advantages related to data redundancy, simplicity of query design, etc. For example, the storage efficiency advantage of normalization can result in a trade-off to the efficiency of querying the normalized data schema. Embodiments of data schemas that are tested can include fact tables with connections to dimension tables, tables organized as star schemas, tables organized as snowflake schemas, and any other suitable data schema structure.

[0036] Embodiments of a logical model can be used to abstract an underlying data schema, such that clients (e.g., end users) can issue logical queries (e.g., using metadata of the logical model) that are transformed. For example, the transformed queries can be used to query against a database (e.g., an implementation of the data schema with populated data) to retrieve a result set. Embodiments of a logical model are defined using metadata, and associated business intelligence functionality can be generated based on the metadata. For example, the business intelligence functionality can include multiple layers, such as one or more of a physical layer, a business model / mapping layer, and a presentation layer.

[0037] An example physical layer can define objects and relationships that are used to write native queries for each physical data source (e.g., used to transform logical queries). For example, the physical layer can be created by importing tables, cubes, and flat files from the data sources. Separating logical behavior from the physical model provides the ability to federate multiple physical sources into the same logical object, enabling aggregate navigation and partitioning, as well as dimension conformance and isolation from changes to the physical sources.

[0038] An example business model / mapping layer can define a business or logical model of the data and specify mappings between the logical model and the physical schema. For example, this layer can determine the analytic behavior seen by the client / end user and can define a superset of the objects and relationships available to the client / end user. In some embodiments, each column in the business model can map to one or more columns in the physical layer. At runtime, logical SQL requests can be evaluated against the business model and these mappings can be used to determine a set of physical tables, files, and cubes to generate the relevant physical queries. The mappings can contain calculations and transformations and in some implementations can combine multiple physical tables.

[0039] An example presentation layer provides a mechanism to present a customized, secure, role-based view of the business model to the user. For example, the presentation layer can add an abstraction level on top of the business model and mapping layer and provide a view of the data seen by the user building the request. In some embodiments, multiple subject areas mapped to a single business model can be created by the presentation layer, effectively breaking the business model into multiple manageable pieces.

[0040] To illustrate query transformation, consider the following sample logical query that can be received at a server hosting a logical model:

[0041] SELECT

[0042] "D0 Time".″T02 Per Name Month"saw_0,

[0043] "D4Product". "P01Product"saw_1,

[0044] "F2 Units". "2-01Billed Qty(Sum All)"saw_2

[0045] FROM "Sample Sales"

[0046] ORDER BY saw_0, saw_1

[0047] In some embodiments, the server can transform such a logical query into one or more transformed queries, or in other words, into one or more queries against the underlying data schema / database design. Consider the following example query that the server transforms based on the received logical query.

[0048]

[0049] As transformed, the simplified logical query targeting logical objects in the logical model is transformed into a database query targeting specific components of the data schema. In particular, certain elements of the data schema are abstracted as logical components (e.g., logical objects) in the logical query, and in the transformed query these abstractions are mapped to their underlying physical components. This example demonstrates why the definitions in the logical model have an impact on querying the database (which implements the data schema), as problems with the query transformation or other components of the logical model can result in flawed or erroneous results.

[0050] Traditional functional testing of logical models often relies on patterns for verifying that the metadata is correctly transformed (e.g., into an RPD model). While such testing can verify that the RPD model works as designed, these tests do not guarantee that the created solution will work effectively with the underlying data schema (e.g., will generate the expected results). For example, due to the size of complex models, a comprehensive verification of the generated model would require a significant amount of manual work.

[0051] Logical models created using traditional generators often result in model defects and performance issues that are detected as side effects of other development activities. These findings suggest that traditional models can have consistency issues that are not detected. For example, example categories of potential errors that can lead to incorrect behavior include:

[0052] • The RPD model can not be well suited for some unexpected cases, leading to model defects.

[0053] • Dimensions can be described in the metadata as needed, but the underlying data indicates that the data is optional or nullable (e.g., can be caused by a fact FK

[0054] column metadata defined in error).

[0055] • Tables are not loaded correctly / fully, which can be a common problem for calendar and time dimensions, and can be a problem for improperly loaded aggregate tables.

[0056] • Performance of some queries can unexpectedly degrade when attributes are added to the query (e.g., possibly due to not modeling or implementing the description column correctly).

[0057] Embodiments demonstrate that systematic and automated testing of logical data models (e.g., RPDs) can be performed to enhance model quality and / or alignment of logical models with data schemas. For example, embodiments of an Audit tool can autonomously detect data or logical model inconsistencies based on one or more predefined testing strategies. In some embodiments, one or more metadata services can be used to retrieve metadata describing a logical model (e.g., via a web service), including subject areas, tables, and columns. The metadata of the logical model can be used by embodiments to generate a series of logical queries issued (e.g., issued against a server using a web service). For example, a logical layer can transform the logical queries into transformed queries (e.g., transformed queries designed to query an underlying data schema), which are ultimately used to query a database. Query results can be captured and compared for consistency, and those with inconsistent results can be flagged.

[0058] Figure 4A and Figure 4B FIG. 1 illustrates an implementation of a system for autonomously testing inconsistencies of a logical model, according to example embodiments. For example, Figure 4A The system of FIG. 1 depicts a testing strategy 102, an autonomous dimensional tester (“Audit”) 104, a server 106, a web service 108, a semantic model 110, and a database 112. The testing strategy 102 can include software functionality that generates test queries based on metadata defining a data schema. For example, based on relationships between data structures and structures indicated by the retrieved metadata, the testing strategy 102 can implement software functionality that generates Audit queries.

[0059] The Audit 104 can implement the testing strategy 102 to generate Audit queries, issue the queries to be executed, receive results of the Audit queries, and analyze the results to detect inconsistencies and / or defects in the semantic model 110. In some embodiments, the testing strategy 102 and the Audit 104 can be implemented at a client device, a server, any cloud computing device, or any other suitable computing device.

[0060] The server 106 can be one or more servers (e.g., web servers, cloud servers, virtual machines, etc.) that host the web service 108, store the semantic model 110, and / or implement the database 112. For example, the server 106 can include a business intelligence product (e.g., Oracle® Essbase®, Oracle® OBIEE®, Oracle® Exalytics®, Oracle® Analytics Cloud, etc.). business intelligence), analytic products (e.g., analytics servers and / or data warehouses (e.g., data warehouses), etc. In some embodiments, the servers 406 implement query transformation techniques that are used to transform logical queries into queries of the underlying database / data schema design.

[0061] For example, the servers 406 / semantic models 410 can include associated business intelligence functionality as shown in Figure 4B FIG. 4B. Figure 4B A logical query 420, a presentation layer 422, a business model / mapping layer 424, a physical layer 426, and a data source 428 are depicted. In some embodiments, the Audit 404 can issue a logical query 420 to the servers 406 (based on retrieved metadata), which can in turn transform the logical query via the presentation layer 422, the business model / mapping layer 424, and the physical layer 426, and ultimately issue the transformed query against the data source 428 (e.g., the database 412 of Figure 4A Then the result set from querying the data source 428 using the transformed query can be returned to the Audit 404.

[0062] In some embodiments, the web services 408 can be configured to provide metadata about the semantic models 410 to the Audit 404, e.g., in response to an application programming interface (“API”) call from the Audit 404. For example, the Audit 404 can include a web service API abstraction that abstracts the web service calls to the servers 406. In some embodiments, this abstraction provides for extensibility, such that alternatives can be used (e.g., REST-based APIs).

[0063] In some embodiments, the web services 408 include metadata services that use enriched data structures to describe subject areas, which describe tables (e.g., facts, dimensions, time dimensions) and dimension details (e.g., describe columns, bins and binned columns, hierarchies, and details such as levels, drill keys, description keys, etc.). For example, the web services 408 can include Oracle Analytics Cloud (“OAC”) web services and / or Oracle Analytics Server (“OAS”) web services. analytics cloud (“OAC”) web services and / or analytics server (“OAS”) web services.

[0064] In some embodiments, the web services provided by the OAC can be accessed through a simple object access protocol ("SOAP") client (e.g., to reduce overhead). For example, the SOAP client API at Audit 404 can be encapsulated in a class to minimize the dependency of other logic on the specific SOAP client implementation. In some embodiments, the web services 408 can provide technology to log in and log out of the web services, retrieve semantic model 410 metadata (e.g., RPD metadata) for different objects, and issue logical SQL queries. In some embodiments, the web services 408 can return objects that hide some of the details of the implementation of the API. For example, extensible markup language ("XML") documents can be converted into object representations, such as lists or strings, dictionaries, or other objects.

[0065] In some embodiments, the web services 408 can support the following web services:

[0066] • SAWSessionService to log in to the web service and log out after using it,

[0067] • MetadataService to retrieve subject area names and subject area, table, and column descriptions (indicating column type, whether hidden, and whether described column),

[0068] • XMLViewService to issue logical SQL queries and retrieve result sets,

[0069] • NQSQueryMetadataObjects to retrieve metadata as XUDML fragments.

[0070] In some embodiments, the retrieved XML is parsed to extract the relevant details.

[0071] In some embodiments, basic metadata such as the names of subject areas, details of the presentation tables, and their columns can be retrieved using the OAC MetadataService. In some embodiments, detailed information about dimensions and columns can be retrieved using the NQSQueryMetadataObjects web service. In some implementations, there can be variations in the XUDML returned by the NQSQueryMetadataObjects web service for each type of information, and in this case, the Audit 404 can include one or more specific APIs configured to retrieve metadata for different object types.

[0072] In some embodiments, the web service 408 can represent subject areas, presentation tables, and presentation columns by a simple object model that augments the basic information of the model (e.g., retrieved using the OAC Metadata Service).

[0073] • Tables can be classified as fact tables or dimension tables.

[0074] • A dimension table model can provide a representation of the associated hierarchy, levels, drill keys, and associated columns.

[0075] • Dimension and fact attribute columns can be extended to include flags that indicate whether the column is a description or binned column, whether it is calculated, whether it is a primary key ("PK") column, and the like.

[0076] • If a column is calculated, a fact measure can provide details about the high-level aggregation rules (e.g., semi-additive measures), as well as certain details about level-based measures.

[0077] In some embodiments, the Audit 404 can include software for identifying fact tables based on the presence of measures (e.g., columns with aggregation functions). The web service 408 can also include a NQSQueryMetadataObjects service that can return XUDML fragments that describe the presentation, logical, and / or physical RPD model.

[0078] In some embodiments, metadata queries from the Audit 404 can be resolved with the help of the NQSQueryMetadataObjects service of the web service 408. For example, the NQSQueryMetadataObjects service can return:

[0079] • PK columns and drill keys,

[0080] • levels and hierarchies (including level order),

[0081] • attributes associated with levels,

[0082] • identification of calculated columns (pre-aggregated or post-aggregated),

[0083] • level-based measures, and

[0084] • measures with dimension-based aggregation rules.

[0085] In some embodiments, to retrieve detailed table and column characteristics, the algorithm can use references or fully qualified names in the parent to trace the rendered object to the associated logical (or even physical) object. In some embodiments, the logical table name includes certain naming conventions to be processed, such as procedures for removing the prefixes "Dim-", "Fact", and "Hier-" to define the English name of the matching rendered object. In some embodiments, the trade-off is a reduction in the round-trip time for retrieving XUDML fragments, thereby increasing test throughput.

[0086] Implementations of Audit 404 and Web Service 408 are designed to balance functionality and performance using one or more of the following techniques:

[0087] • Use the OAC metadata service to retrieve the subject area name.

[0088] • Retrieve metadata by topic area:

[0089] o Use the OAC metadata service to retrieve metadata for a subject area within a description scope, its rendering table, and rendering columns.

[0090] o Use web service to call NQSQueryMetadataObjects

[0091] ('4008','

[0092]

[0093]

[0094]

[0095]

[0096]

[0097]

[0098]

[0099]

[0100]

[0101]

[0102]

[0103]

[0104]

[0105]

[0106]

[0107]

[0108]

[0109]

[0110]

[0111]

[0112]

[0113]

[0114]

[0115]

[0116]

[0117]

[0118]

[0119] ，

[0120]

[0121]

[0122]

[0123]

[0124]

[0125]

[0126]

[0127]

[0128]

[0129]

[0130]

[0131]

[0132]

[0133]

[0134]

[0135]

[0136]

[0137]

[0138]

[0139]

[0140]

[0141]

[0142]

[0143]

[0144]

[0145]

[0146]

[0147]

[0148] Figure 5 Figure 6 Figure 5 6

[0149]

[0150]

[0151] Figure 7 Figure 8 Figure 7 Figure 8

[0152] Figure 7 Figure 8

[0153]

[0154]

[0155]

[0156]

[0157]

[0158]

[0159]

[0160]

[0161]

[0162]

[0163]

[0164]

[0165]

[0166]

[0167]

[0168]

[0169]

[0170]

[0171]

[0172]

[0173]

[0174]

[0175]

[0176]

[0177]

[0178]

[0179]

[0180]

[0181]

[0182]

[0183]

[0184]

[0185]

[0186]

[0187]

[0188]

[0189]

[0190]

[0191]

[0192]

[0193]

[0194]

[0195]

[0196]

[0197]

[0198]

[0199]

[0200]

[0201]

[0202]

[0203]

[0204]

[0205]

[0206]

[0207]

[0208]

[0209]

[0210]

[0211]

[0212] Figure 9 Figure 9

[0213]

[0214]

[0215]

[0216]

[0217]

[0218]

[0219]

[0220]

[0221]

[0222]

[0223]

[0224]

[0225]

[0226]

[0227]

[0228]

[0229]

[0230]

[0231]

[0232] ','"<SA Name>".""','true') to retrieve the XUDML metadata for each subject area table column in the range for each presentation table in the subject area. This information can be used to determine references to logical columns. The logical keys are retrieved using the call NQSQueryMetadataObjects('2008') to get the association of drill keys to logical columns. In some embodiments, these columns can be matched to the presentation columns (e.g., previously retrieved information) to determine which presentation column is the drill key or PK for the table. In some embodiments, the retrieval of the logical keys includes a batch operation, for example, because logical dimensions (e.g., hierarchy objects) are related by their drill keys. For example, sometimes the number of (drill) keys is relatively small compared to the number of columns, so a batch operation based on the drill keys can improve performance. Also, performance issues can be mitigated by caching the results to eliminate additional round trips when other dimension tables are analyzed. In some embodiments, an alternative to this approach is to determine the logical dimension objects referenced from the presentation dimension tables. This would allow retrieval of the logical levels. However, in some implementations, this involves two service calls per presentation table. Embodiments can similarly implement other suitable techniques to retrieve the metadata. For example, one or more other APIs (or other interfaces / retrieval abstractions) can be used to retrieve the metadata, the metadata can be retrieved based on other aspects (e.g., other than subject areas), and / or any other suitable techniques can be implemented. In some embodiments, for additional hierarchy and level details, Audit 404 can use the call NQSQueryMetadataObjects('2019') to issue a query for the dimension, and then issue the query NQSQueryMetadataObjects('2019',",", 'true') to retrieve the level details. This retrieved data can be used to reconstruct the hierarchy and order the keys (drill) by level (e.g., from detail to grand total). In some embodiments, Audit 404 can run this as a batch operation and cache the results. In some embodiments, an alternative can be to use the presentation table metadata and the logical dimension (e.g., hierarchy) objects referenced from the presentation dimension tables. In some embodiments, the metadata retrieved using the web service 408 (e.g., from the server 406) does not indicate whether the tables in the subject area are dimensions or fact tables. However, the table columns can indicate the name, description, data type, and aggregation rules for the columns. In some embodiments, a table that contains at least one column with a defined aggregation type can be considered a fact table, and a column in a fact table with a defined aggregation rule can be considered a measure. In some embodiments,Calendar dimension table names can end in "Date" and time dimension table names end in "Time." Implementations can also include shrunken calendar dimensions that can end in "Month," "Quarter," "Year," "Period," "Fiscal Quarter," or "Fiscal Year." In some embodiments, Audit 404 can identify certain qualities of the data schema based on the naming convention. Audit 404 can determine the relationships in the data schema based on metadata retrieved from the OAC metadata web service of web services 408. In some embodiments, XUDML fragments retrieved through the NQSQueryMetadataObjects web service of web services 408 are analyzed to determine the relationships for use in query generation. For example, these analyses can rely on mappings between presentation levels and logical levels that define drill keys and other related functionality for query generation. The XUDML portion that describes the presentation hierarchy can be retrieved by issuing the command "call NQSQueryMetadataObjects('4028')." The results can be provided in multiple loggers with a sequence of levels from summary to detail, such as the following example: In some embodiments, Audit 404 can dynamically generate test cases (e.g., Python test cases) and run them with a suitable tool (e.g., XMLRunner, which creates test results in Junit-style XML format). Implementations can take advantage of certain advantageous infrastructure by generating separate test cases. Because the tests are determined by the structure of the semantic model 410 (e.g., RPD), which is only known after the model metadata is retrieved from web services 408, implementations can dynamically create classes and class methods (e.g., Python classes and methods) based on the retrieved metadata. For example, a subclass of unittest.TestCase can be created for each subject area, and one or more test methods of each test case can be defined in that subject area. In some embodiments, a separate test case class can be created for each subject area, and unit test cases can be created and added (e.g., represented by functions that begin with the name "test"). For example,This organization can group test results related to a certain topic area into one report. The following example function illustrates this technique of dynamically generating sample classes / methods: class DynamicTest(unittest.TestCase): client = None session = None @classmethod def make_test_class(cls, className, testFunctions): return type(className, (DynamicTest,), testFunctions) @classmethod def make_oas_test_function(cls, sa): # returns a method with the test def test(self): # simple test that checks if service call returns a non null SA description # and contains 'Asset' in its name without the prefix (demo purpose only) saDescr = cls.client.getServiceAgreementDescription(sa, DynamicTest.session) self.assertIsNotNone(saDescr) self.assertIn("Asset", sa.displayName[6:]) "Unerwarteter Name wurderendeckt!" return test @classmethod def make_oas_test_functions(cls, sas): functions = dict() for i in [1, 2]: index = randint(0, len(sas) - 1) sa = sas[index] test_func = cls.make_oas_test_function(sa) functions[f'test_(sa.displayName}'] = test_func return functions if name_ == "_main_": DynamicTest.client = OasWSClient('http: / / slc15smu.us.oracle.com',9502)DynamicTest.session = DynamicTest.client.login('weblogic', 'weblogic123') sas = DynamicTest.client.getSAs(DynamicTest.session) for sai in sas: saFunctions = DynamicTest.make_oas_test_functions(sa) name = sa.displayName.replace('', '_') globals()[name] = DynamicTest.make_test_class(name, saFunctions) unittest.main() The above example generates a class for each subject area and executes two tests for each unit test implemented in two dynamically generated class methods. Test cases can be defined as descriptions / parameters and functions to run each test case can be generated. Embodiments can implement this organization because Audit tests are defined by subject area, metric, and the property or properties by which the metric is grouped. Embodiments can run the standard Python unit test framework with the command unittest.main(), which can automatically discover test classes and test methods and then run them. In some embodiments, test cases are run and test results can be captured in Junit XML format, which can be converted to HTML. Test cases can be specified as test suite descriptions created according to one or more test strategies. In some embodiments, a runtime component can create the specified test cases and run them in the unit test framework. For example, the functionality of Audit 404 can be implemented using a Jenkins build process. In some embodiments, Audit 404 retrieves metadata describing a logical model, analyzes the metadata to determine relationships between data structures comprising the logical model, and generates logical queries to test the logical model for inconsistencies. For example, one or more pairs of queries (e.g., a reference query and a test query) can be generated to test for inconsistencies in aggregation functionality, queries can be generated to enumerate joins of data schemas, queries of increasing complexity can be generated to detect performance problems indicative of schema flaws, and other query strategies. Embodiments can generate one or more logical queries using the following algorithm. The metadata retrieved and analyzed can be used to select one or more metrics. For example, the implemented test strategy (e.g., software functionality for a given test strategy) can select metrics based on the analyzed metadata. In some embodiments, the implemented test strategy can then define groups of properties by which the metrics can be grouped. For example,The test strategy can determine the attributes to group by based on an analysis of metadata related to the selected measures. In some embodiments, the test strategy can select one attribute per dimension based on a criterion (e.g., a PK column), can enumerate drill keys or description columns to be tested as a single grouping criterion, or can implement any other suitable technique to determine the attributes for grouping. In some embodiments, the test strategy can generate a list of data structures based on the selected measures and the determined groupby attributes. For example, each data structure can define a measure and group by attributes (e.g., attributes related to a test pair (or reference and test queries)). In some embodiments, the query generator can use the data structures to issue queries. For example, a reference query can sum the measures (e.g., all relevant measures) according to the aggregation rules for the measures (e.g., SUM, MIN, MAX, etc. as defined in the metadata). The results from the reference query can be captured for comparison. A second test query can also be generated that performs the same operations on the measures and groups them based on a supplied list of one or more attributes. The results from the test query can also be captured for comparison. For example, Audit 404 can generate and issue reference and test queries such that if the RPD model is inconsistent (e.g., does not align with the data in the physical data schema, or is otherwise inconsistent), the queries produce inconsistent results. For example, given a measure and a list of attributes (e.g., a generated data structure), Audit 404 can create a pair of logical SQL queries that can be run (e.g., using separate APIs). In some embodiments, the reference query can create a sum for each provided measure (e.g., roll up all measures to a total for all dimensions). In some embodiments, the test query can report the same measures and group them by one or more attributes. The resulting records can be aggregated back to a single record and compared to the results from the reference query. In some embodiments, the reference query can query for one, some, or all measures of a fact table and capture a single record as a reference, while the test query can aggregate the same measures and group them by one or more attributes. In some implementations, if the sum of the measures in the record returned by the test query is not the same as the result of the reference query, it indicates that the semantic model 410 (e.g., the RPD model) is inconsistent with the underlying data (e.g., the database 412 that implements the data schema). In some embodiments, if one or both of the queries fail due to a structured query language ("SQL") error, the model has a defect. In some embodiments, Audit 404 can generate one logical query per subject area that returns one or a set of measures aggregated to a total for a plurality of dimensions (e.g., a star in a star schema). For example,Results rows can be used to compare the result sets of each other test case in this subject area. In some embodiments, the query can include additive (and not level-based) fact measures. In other embodiments, the query can include any suitable fact measure. In some embodiments, the underlying data schema can be a star schema, which can dictate query details such that the type of structured query can be represented by a simple data structure. For example, most of the query complexity (how facts and dimensions join and how normalized dimensions are structured) can be contained in the logical model (e.g., based on metadata representing schema relationships). Embodiments of the test strategy function can provide options on which test strategy to manage. In some embodiments, the number of test cases can be limited to a few hundred or a few thousand. In theory, given some data schema / logical model relationships, millions of possible test cases can be generated, which is beyond practical needs when run. Embodiments of the test strategy function use practical resource constraints to efficiently structure queries to test. A pair of database queries and their results are illustrated in accordance with example embodiments. In some embodiments, queries can be generated that group the same measure by one or more dimension attributes and aggregate the measure against the resulting rows of results using the aggregation rules of the model. In the illustrated embodiment, query 502 is a reference query issued for the measure "Count of Service Histories" at the subject area "Asset-Service History." The SQL of query 502 indicates that the measure is summed without any grouping. Results 504 depict the results of query 502, which has a data value of 3502. In the depicted embodiment, query 602 is a test query issued for the same measure "Count of Service Histories" at the same subject area "Asset-Service History." Query 602 differs from query 502 in that the SQL of query 602 indicates that the measure is grouped by the attribute "Permit ID" (which has the referenced logical "Permission" dimension). Results 604 depict the results of query 602, which has 16 rows that group the "Count of Service Histories" measure data values by the "Permit ID" attribute of the "Permission" dimension. In the depicted embodiment, the sum of the 16 rows of results 604 of the "Count of Service Histories" measure data values is 3502. This equals the summed measure data value returned for query 502 in results 504 without grouping. Because these values are equal,So query results 504 and 604 do not indicate logical model inconsistency. Another pair of database queries and their results are illustrated in accordance with example embodiments. In the illustrated embodiment, query 702 is a reference query issued against the metrics "Timesheet Detail Count" and "Total Cost" in the subject area "Asset-Timesheet Detail." The SQL of query 702 indicates that the metrics are summed without any grouping. Result 704 depicts the results of query 702, which has a data value of 1662 for the "Timesheet Detail Count" metric and 511369.45 for the "Total Cost" metric. In the illustrated embodiment, the SQL of query 802 (test query) groups the additive metrics by the PK column "Timesheet ID" (of the referenced timesheet dimension). Result 804 depicts the results of query 802, which includes multiple results that the metrics have been aggregated by (because query 802 uses the SUM aggregation rule). In some embodiments, logical SQL is used to aggregate metrics to reduce logic in business logic, minimize rounding errors, and improve query performance. This can be done, for example, by aggregating the metrics with supported aggregation functions. Query 802 repeats the aggregated metrics for each grouping attribute (e.g., "Timesheet Detail Count" and "Total Cost"), and so the first row can be returned (e.g., by database 412 of FIG. 4). In some embodiments, result 804 is reduced to one row by defining RANK() on the first dimension attribute and by limiting the row to the first row with the condition CI = 1 in the WHERE clause of query 802. In the depicted embodiment, the sum in result 804 of the related metrics is not equal to the sum in result 704. Because these values are not equal, query results 704 and 804 indicate inconsistency. The illustrated inconsistency can be caused, for example, by an undefined key, i.e., the fact view references an undefined key in the timesheet dimension (e.g., data inconsistency). In some embodiments, aggregation functions are used within one or more of the reference queries and / or test queries generated for the logical model. The following aggregation functions can be used when aggregating metrics after grouping with a specified aggregation role: In some implementations, inconsistency or defects can be caused by one or more of the following: • For the following issues, the reported metric value of the reference query is greater than the summed metric value of the test query: o The join between fact and dimension tables uses an inner join but should be an outer join. This is likely the case because of incorrect metadata at the fact table FK column. o The fact table refers to a dimension with a FK value,It has no matching PK in the target dimension. This can be the result of an incorrect data load operation.o The fact table or dimension table refers to a lookup with an incorrect join. This is most likely the case because of incorrect metadata at the fact table flag (Flag) column.o The results are incorrect if a binned column is used to group the results. This can mean that the bucket definition is incomplete and does not cover the range of possible binned column values. It can also mean that the selected bucket type is incorrect. • For the following issues, the reported metric value of the reference query can be less than the summed metric value of the test query: o Incorrectly defined calculated metrics or incorrect cross-drilling of measures can be the root cause for this case. In some embodiments, the query issued by Audit 404 can also experience errors, such as SQL errors. For example, the SQL error can indicate the following issues: • The physical fact or dimension view in the database (e.g., DB) is not defined correctly and triggers a SQL exception. • The physical table or column does not exist (e.g., due to Utilities Application Framework ("OUAF") metadata issues) • The RPD created a virtual table with a defect that causes a SQL exception (e.g., fact wrapper, feature table mapping, binning table) • Connection issues with the server 406, which can be network issues, and can be attempted to be recovered by logging in again and retrying the issued query. In some embodiments, Audit 404 can implement one or more query strategies to enhance resource efficiency and inconsistency discovery. For example, to efficiently reach multiple issued queries, the reference query and the test query can include all the measures of the fact table under verification / test that can be aggregated (e.g., using SUM, MIN, and MAX aggregation functions). In some embodiments, the aggregation rules defined in the column metadata can define how each measure column is aggregated against the data records created by the test query. In some embodiments, limiting the query against the time dimension can speed up the runtime of the test strategy and can help minimize the returned result set. For example, the time dimension can be grouped based on the highest level (e.g., AM / PM drill down key), or the grouping on the time dimension attribute can be skipped. For example, the grouping on the time dimension attribute can be skipped after the initial test verifies that the time dimension is loaded correctly. Additionally, there can be a correlation between the inconsistencies of the calendar and time dimensions (e.g., because both are based on the same metadata), so testing the time dimension individually can be redundant. If the PK regular expression excludes the time dimension PK and the drill down key regular expression excludes the drill down key of the time dimension, the embodiment can break the time dimension without consideration. In some embodiments, Audit 404 can generate a query to enumerate the joins indicated by the retrieved metadata. For example,One or more of the PKs, drill keys, binned and description columns associated with the presentation dimensions of the subject area can be identified. Audit queries can be grouped based on one or more of the PKs, drill keys, binned and description columns (e.g., to ensure that parent and grandparent representatives are included). Audit queries can also be grouped based on the binned and description columns on the fact tables. In some embodiments, drill keys of the calendar dimension can be targeted to ensure that joins between aggregate tables involving collapsed dimensions are tested. In some embodiments, attribute columns of child tables integrated into logical dimensions can also be targeted by the generated queries to enumerate joins. In some embodiments, if a test query takes longer than a specified timeout, the query generated to enumerate joins will return an error for that test query. For example, the timeout can be a fixed duration (e.g., 2 minutes, 5 minutes, etc.) or can be configured as a timeout that is X times larger than a typical query time (e.g., 5 times larger than a default query time per query of 1 minute). In some embodiments, one or more queries generated to enumerate joins can be configured to limit the columns to be grouped to a subset of the available features: • PKs, • drill keys on dimensions (no PKs), • table description columns, • code description columns, • binned columns on dimensions, • code descriptions on facts, • binned columns on facts, and • child table attributes (feature to be enabled in future releases). In some embodiments, a series of queries can be generated by Audit 404 with increasing test query complexity. For example, a series of queries can be added that are sequentially added as grouping criteria: • PKs, • drill keys on dimensions (no PKs), • table description columns, • code description columns, or • binned columns on dimensions, • drill keys on facts, • binned columns on facts, and • child table attributes on dimensions. In some embodiments, a defect can be detected based on the results of one or more of these emitted Audit queries if the query duration is longer than a specified timeout, has an inconsistent result, or the time difference between the current query in the series and the previous query in the series is greater than a configurable threshold. Some embodiments for generating queries of test complexity can target the description and binned columns on the fact tables and the other available dimension features (e.g., listed above) of N dimensions with the highest number of such features of the fact. By default, N can be any default number (e.g., 3) and the value can be changed by a configuration option. In some embodiments, the calendar and time dimensions are excluded from consideration due to their low complexity. In the case where two dimensions have the same number of features and N-1 dimensions have been processed, the first dimension based on an alphanumeric ordering criteria can be selected in some embodiments. Embodiments can run multiple test cases concurrently. In some embodiments,Audit 404 includes software tools that support the following: Usage: Audit.py [-h] --url URL [--port PORT] --user USER --pass PASSWORD [-l] [-s START] [-e END] [-pk] [-desc] [-bin] [-drill] [-noCalendar] [-one] Autonomous Dimensional Tester (AUDIT) 1.0.0 Optional arguments: -h, --help show this help message and exit --url URL BI Server URL. --port PORT BI Server port --user USER BI Server administrative user --pass PASSWORD BI Server administrator password -l, --list List of available subject areas in the BI Server -s START, --startinterval START Starting index of the subject area list -e END, --endinterval END Ending index of the subject area list -pk, --primarykeys Group by primary keys -desc, --descriptionColumns Group by description columns -bin, --binColumns Group by binned columns -drill, --drillKeyColumns Group by drill key columns -noCalendar, --noCalendarAndTime Skip group by calendar and time dimension properties -one, --oneMeasure Create query instances with one measure Improving the quality of implemented logical models and / or databases without excessive manual testing. For example, the following can be identified by an embodiment: inconsistencies between the database schema, its sample data, and the RPD model; FKs that are declared as not nullable and required in the RPD model, but contain NULLs or strings with spaces (which is often a problem with fact views); tables that have not been fully loaded or updated; and queries with unexpected performance degradation that indicate modeling or view definition problems. An example flowchart for autonomously testing inconsistencies in a logical model is illustrated in accordance with an example embodiment. In one embodiment, the functionality of Audit is implemented by software stored in memory or other computer readable or tangible media, and executed by a processor. In other embodiments, each of the functionality can be performed by hardware (e.g., through the use of application specific integrated circuits ("ASICs"), programmable gate arrays ("PGAs"), field programmable gate arrays ("FPGAs"), etc.), or any combination of hardware and software. At 902, metadata describing a logical model can be retrieved, where the logical model is an abstraction of a database schema, the database schema is implemented at a database, and the database schema includes a fact table and one or more dimension tables. For example, a portion of the logical model (e.g.,Metadata for the subject area) can be retrieved from a server implementing the logical model. In some embodiments, the metadata is retrieved using a web service API. In some embodiments, the metadata describes the logical schema that logical queries can be issued against. For example, a server managing metadata and / or implementing a logical model can transform logical queries issued against the logical model into database queries issued against the underlying database. In some embodiments, metadata can be managed in the form of an RPD. At 904, a plurality of logical queries including at least a first logical query and a second logical query can be automatically generated based on the retrieved metadata, where the first logical query and the second logical query target logical objects of the logical model. For example, logical queries issued against a logical model defined by RPD metadata can be generated based on the retrieved metadata. At 906, at least the first logical query and the second logical query can be issued to a server hosting the logical model, where, at the server, the first logical query is transformed into a first database query and the second logical query is transformed into a second database query, and the first database query and the second database query target fact tables and dimension tables from a database schema. In some embodiments, the first logical query and the second logical query can be generated based on metadata associated with the logical objects targeted, and the first database query and the second database query can target fact tables and dimension tables from the database schema based on mappings for the logical objects targeted that were used to transform the first logical query and the second logical query. In some embodiments, the first database query and the second database query are a pair of database queries configured to test the logical model using aggregation functions associated with the logical objects targeted. In some embodiments, the first database query aggregates metric data values along one or more dimensions based on aggregation function definitions for the metric data defined in the metadata, and the second database query groups a plurality of the metric data values by one or more dimension attributes. In some embodiments, the first database query aggregates metric data values along all dimensions of the metric based on aggregation function definitions for the metric data defined in the metadata and dimension definitions for the metric. At 908, query results received from execution of the first database query and the second database query can be compared. For example, comparing query results of the pair of database queries can include comparing aggregated metric data values (e.g., returned by the first database query) to a sum of grouped metric data values (e.g., returned by the second database query). At 910, when the comparison of the query results of the first database query and the second database query does not satisfy a criterion, one or more inconsistencies can be identified, where the one or more inconsistencies can be inconsistencies with the logical model defined by the metadata or inconsistencies at the database. In some embodiments,At least one inconsistency can be identified when a difference between the aggregated measure data value and the sum of the grouped measure data values is greater than a threshold. The at least one inconsistency can be identified with respect to one or more of: relationships between fact and dimension tables targeted in the metadata and logical objects targeted, data in the fact and dimension tables targeted loaded into the database, and column configuration metadata defined in the metadata for the fact and dimension tables targeted. In some embodiments, automatically generating the plurality of database queries includes automatically generating a plurality of pairs of database queries such that a first database query in each pair of database queries includes an aggregation of a given measure data value across one or more dimensions and a second database query in each pair of database queries groups a plurality of measure data values in the given measure data value by one or more dimension attributes. For example, the plurality of pairs of logical queries can be issued to a server hosting the logical model, where at the server, each pair of logical queries can be transformed into a pair of database queries targeting at least fact and dimension tables from a database schema. In some embodiments, query results received from execution of the pairs of database queries can be compared and one or more inconsistencies are identified when the comparison of query results of each pair of database queries does not satisfy a criterion, where the one or more inconsistencies include inconsistencies with the logical model defined by the metadata or inconsistencies at the database. In some embodiments, the automatically generated pairs of database queries are configured to test the logical model using aggregation functions associated with at least one logical object targeted by each pair. For example, for the issued pairs of logical queries, a given pair of logical queries can be generated based on metadata associated with at least one logical object targeted by the given pair and a given pair of database queries transformed based on the given pair of logical queries target at least fact and dimension tables from a database schema based on mappings used to transform the given pair of logical queries for the given logical objects targeted. In some embodiments, the automatically generated pairs of database queries test a plurality of logical objects from the logical model and the transformed pairs of database queries test a plurality of fact and dimension tables from the database. Embodiments enable autonomous testing of inconsistencies with the logical model. For example, data schemas sometimes require complex queries that use joins and other complex query structures to target related data from multiple tables / fields. To mitigate this query burden, some database implementations and reporting tools include a logical model or a layer that maps the underlying complex data schema to a more simplified logical model. For example, the logical model can then be queried using simpler logical queries that can be transformed to queries targeting the underlying data schema (e.g., to complex queries that can access / retrieve data from the underlying data schema). Some embodiments of the logical model can include multiple layers of complexity,include various aggregation functions, drill-down functions, join behavior, and so on. For example, one or more tools can be used to quickly develop a complex logical model, such as by defining metadata that can be used to deploy the model. In some embodiments, tools can be used to define a conceptual data model through a user interface, and the output of such tools can be metadata that stores the conceptual relationships of the components of the logical data model. In some embodiments, these tools can improve the efficiency of deploying a complex database, yet the deployment can sometimes include low-level inconsistencies and / or defects. For example, aggregation functions defined in the logical model can not align with the underlying data schema, one or more tables can not be properly loaded, configurations of data fields (e.g., cannot be null) can not be properly set, and more. These inconsistencies and / or defects often require detailed manual work to debug. Embodiments generate queries based on the metadata of the logical model that autonomously test the implementation of the logical model for inconsistencies and / or defects in the underlying data schema. For example, the metadata of the logical model can be retrieved and analyzed to determine the conceptual relationships between the components of the model. In some embodiments, queries can be generated based on the expected behavior of the logical model and the data schema given the analyzed metadata. For example, a pair of queries can be generated that test the expected aggregation functions (e.g., across one or more dimensions of the data schema). Embodiments analyze the results of these queries. For example, two or more queries can be designed to return similar result sets (e.g., the same data values) when the logical data model accurately reflects the data relationships in the data schema. An unlike result set returned for the two or more queries can indicate an inconsistency with the logical model. In other examples, query errors can be returned due to improper loading of data, missing database tables or columns, incorrect embedded SQL expressions, and / or improper configuration of data fields. In some embodiments, these results that deviate from the expected results can be used to identify one or more of these inconsistencies. The features, structures, or characteristics of the disclosure described throughout this specification can be combined in any suitable manner in one or more embodiments. For example, use of the terms “one embodiment,” “some embodiments,” “certain embodiments,” “certain embodiments” or other similar language throughout this specification refers to the fact that a particular feature, structure, or characteristic described in connection with an embodiment can be included in at least one embodiment of the disclosure. Thus, appearances of the phrases “one embodiment,” “certain embodiments,” or other similar language throughout this specification do not necessarily refer to the same group of embodiments, and the described features, structures, or characteristics can be combined in any suitable manner in one or more embodiments. One of ordinary skill in the art will readily recognize a method, device, or apparatus (or any combination thereof) that is seemingly directly adaptive for or most effectively accomplishing the performance of the functionality in accordance with the embodiments described herein will readily recognize a method, device, or apparatus (or any combination thereof) that is seemingly directly adaptive for or most effectively accomplishing the performance of the functionality in accordance with the embodiments described herein.It will be apparent, however, to one skilled in the art that certain modifications, changes, and alternative constructions can be made to the described embodiments without departing from the spirit and scope of the disclosure in its broader aspects. Accordingly, the appended claims shall cover all such changes and modifications as fall within the true scope of the disclosure.

Claims

1. A method for autonomously testing a logical model for inconsistencies, the method comprising: retrieving metadata describing a logical model, wherein the logical model comprises an abstraction of a database schema, the database schema is implemented at a database, and the database schema comprises a fact table and one or more dimension tables; automatically generating a plurality of logical queries comprising at least a first logical query and a second logical query based on the retrieved metadata, wherein the first logical query and the second logical query target logical objects of the logical model; issuing the at least first logical query and the second logical query to a server hosting the logical model, wherein, at the server, the first logical query is transformed into a first database query and the second logical query is transformed into a second database query, and the first database query and the second database query target at least a fact table and dimension tables from the database schema; comparing query results received from execution of the first database query and the second database query; and identifying one or more inconsistencies when the comparison of the query results of the first database query and the second database query does not satisfy a criterion, wherein the one or more inconsistencies comprise inconsistencies with the logical model defined by the metadata or inconsistencies at the database.

2. The method of claim 1, wherein, The first database query and the second database query comprise a pair of database queries configured to test the logical model using aggregation functions associated with the logical objects targeted.

3. The method of claim 2, wherein, The first logical query and the second logical query are generated based on metadata associated with the logical objects targeted, and the first database query and the second database query target the fact table and the dimension tables from the database schema based on mappings for the logical objects targeted that are used to transform the first logical query and the second logical query.

4. The method of claim 3, wherein, The first database query aggregates metric data values along one or more dimensions based on an aggregation function definition of the metric data defined in the metadata, and the second database query groups a plurality of the metric data values by one or more dimension attributes.

5. The method of claim 4, wherein, The first database query aggregates metric data values along all dimensions of the metric based on the aggregation function definition of the metric data defined in the metadata and dimension definitions of the metric.

6. The method of claim 4, wherein, The comparing of the query results of the pair of database queries comprises comparing the aggregated metric data values to a sum of the grouped metric data values.

7. The method of claim 6, wherein, The one or more inconsistencies are identified when a difference between the aggregated metric data values and the sum of the grouped metric data values is greater than a threshold.

8. The method of claim 7, wherein the one or more inconsistencies are identified for one or more of: relationships between fact tables and dimension tables targeted in the metadata and the logical objects targeted, data in the fact tables and dimension tables targeted loaded into the database, and column configuration metadata defined in the metadata for the fact tables and the dimension tables targeted.

9. The method of claim 3, wherein, The automatically generating a plurality of database queries includes automatically generating a plurality of pairs of database queries such that a first database query in each pair of database queries includes an aggregation across one or more dimensions of a given measure data value and a second database query in each pair of database queries groups a plurality of measure data values of the given measure data value by one or more dimension attributes.

10. The method of claim 9, further comprising: issuing a plurality of pairs of logical queries to the server hosting the logical model, wherein, at the server, each pair of logical queries is transformed into a pair of database queries targeting at least fact tables and dimension tables from the database schema; comparing query results received from execution of the pairs of database queries; and identifying one or more inconsistencies when the comparison of query results of each pair of database queries does not satisfy a criterion, wherein the one or more inconsistencies include an inconsistency with the logical model defined by the metadata or an inconsistency at the database.

11. The method of claim 10, wherein, The automatically generated pairs of database queries are configured to test the logical model using aggregation functions associated with at least one logical object targeted by each pair.

12. The method of claim 11, wherein, For the issued pairs of logical queries, a given pair of logical queries is generated based on metadata associated with at least one logical object targeted by the given pair of logical queries and a given pair of database queries transformed based on the given pair of logical queries targets at least fact tables and dimension tables from the database schema based on mappings for the at least one logical object targeted being used to transform the given pair of logical queries.

13. The method of claim 12, wherein, The automatically generated pairs of database queries test a plurality of logical objects from the logical model and the transformed pairs of database queries test a plurality of fact tables and dimension tables from the database.

14. A system for autonomously testing inconsistencies of a logical model, the system comprising: a processor; and a memory storing instructions for execution by the processor, the instructions configuring the processor to: retrieve metadata describing a logical model, wherein the logical model includes an abstraction of a database schema, the database schema is implemented at a database, and the database schema includes fact tables and one or more dimension tables; based on the retrieved metadata, automatically generate a plurality of logical queries including at least a first logical query and a second logical query, wherein the first logical query and the second logical query target logical objects of the logical model; issue the at least first logical query and the second logical query to a server hosting the logical model, wherein, at the server, the first logical query is transformed into a first database query and the second logical query is transformed into a second database query, and the first database query and the second database query target at least fact tables and dimension tables from the database schema; compare query results received from execution of the first database query and the second database query; and identify one or more inconsistencies when the comparison of the query results of the first database query and the second database query does not satisfy a criterion, wherein the one or more inconsistencies include an inconsistency with the logical model defined by the metadata or an inconsistency at the database.

15. The system of claim 14, wherein, The first database query and the second database query include a pair of database queries configured to test the logical model using aggregate functions associated with the logical objects targeted.

16. The system of claim 15, wherein, The first logical query and the second logical query are generated based on metadata associated with the logical objects targeted, and the first database query and the second database query target the fact tables and the dimension tables from the database schema based on mappings for the logical objects targeted, the mappings being used to transform the first logical query and the second logical query.

17. The system of claim 16, wherein, The first database query aggregates the measure data values along one or more dimensions based on an aggregate function definition of the measure data defined in the metadata, and the second database query groups the plurality of measure data values by one or more dimension attributes.

18. The system of claim 17, wherein, The first database query aggregates the measure data values along all dimensions of the measure based on the aggregate function definition of the measure data defined in the metadata and the dimension definitions of the measure.

19. The system of claim 17, wherein, The comparison of the query results of the pair of database queries includes comparing the aggregated measure data values to a sum of the grouped measure data values.

20. A non-transitory computer-readable medium having stored thereon instructions that, when executed by a processor, cause the processor to autonomously test a logical model for inconsistencies, wherein, When executed, the instructions cause the processor to: retrieve metadata describing a logical model, wherein the logical model includes an abstraction of a database schema, the database schema being implemented at a database, and the database schema including fact tables and one or more dimension tables; automatically generate a plurality of logical queries including at least a first logical query and a second logical query based on the retrieved metadata, wherein the first logical query and the second logical query target logical objects of the logical model; issue the at least first logical query and the second logical query to a server hosting the logical model, wherein, at the server, the first logical query is transformed into a first database query and the second logical query is transformed into a second database query, and the first database query and the second database query target at least fact tables and dimension tables from the database schema; compare query results received from execution of the first database query and the second database query; and identify one or more inconsistencies when the comparison of the query results of the first database query and the second database query does not satisfy a criterion, wherein the one or more inconsistencies include an inconsistency with the logical model defined by the metadata or an inconsistency at the database.

Images