System and methods for modeling a report query database

• US 7,546,312 B1
• Filed: 09/23/2005
• Issued: 06/09/2009
• Est. Priority Date: 09/23/2005
• Status: Active Grant

• Current Assignee: Emc IP Holding Company LLC (Dell Technologies Inc.)
• Original Assignee: EMC Corporation (Dell Technologies Inc.)
• Inventors: Xu, Sarah Yongmei, Chaulk, Christopher A., Marokhovsky, Serge
• Primary Examiner(s): Alam; Hosain
• Assistant Examiner(s): Yen; Syling
• Application Number:
  US11/233,811
• Time in Patent Office: 1,355 Days
• Field of Search: 707/103.R, 707/2, 707/3, 707/102, 709/210, 709/213
• US Class Current: 1/1
• CPC Class Codes: G06F 11/3409 for performance assessment
• CPC Class Codes: G06F 11/3476 Data logging G06F11/14, G06F11/2205 take precedence
• CPC Class Codes: G06F 16/254 Extract, transform and load [ETL] procedures, e.g. ETL data flows in data warehouses
• CPC Class Codes: G06F 2201/80 Database-specific techniques
• CPC Class Codes: Y10S 707/99932 Access augmentation or optimizing
• CPC Class Codes: Y10S 707/99933 Query processing, i.e. searching
• CPC Class Codes: Y10S 707/99943 Generating database or data structure, e.g. via user interface
• CPC Class Codes: Y10S 707/99944 Object-oriented database structure

Abstract

A Decision Support System (DSS) coalescer offloads and transforms data from an online statistical gathering database to a report query database adapted for report generation. The coalescer generates and populates a query database structured for query data retrieval, in contrast to realtime access transaction storage as in the online database. Configurations transform the online database into a star join schema (table structure) that centralizes database “facts” in a central table and forms associations to database “dimensions” upon which a query is likely to be based. The facts refer to items which tend to change regularly over time, such as sales volumes and unit shipped. Conversely, the dimensions include more static values such as time (e.g. days, weeks, and quarters) and geographic regions, such as states and countries. The resulting query model (schema) includes relatively small dimensions tables associated with (i.e. having a schema association to) a relatively larger fact table.

Claims

1. A method of modeling a storage area network online database for report queries comprising:

identifying a plurality of tables in an object model;

enumerating tables for transformation, the tables having attributes operable to store values;

selecting a subset of fields from the storage area network online database corresponding to multiple fact tables expected to be queried in a report database, wherein the expected queries comprise SAN activity reports;

extracting the enumerated tables from the online database;

filtering, for each of the tables, unqueried fields to identify a transformation set of tables, the transformation set indicative of queryable values responsive to user queries;

transforming, via a set of views and tables, the queryable values of the transformation set;

for each attribute, classifying the attribute as a dimension or a fact

assigning, if the attribute is a fact, the attribute to a central metrics table; and

assigning, if the attribute corresponds to a dimension, the attribute to a dimension table having an association to the central metrics table; and

loading the transformed values into the corresponding tables in a report database, the report database strongly indexed on the attributes of the fact table, and having fewer records in the dimension tables than corresponding values in the online database.

2. The method of claim 1 wherein loading further comprises loading the corresponding tables into dimensional tables and a fact table, the dimension tables determined from query logic indicative of fields adapted to receive a query.

3. The method of claim 2 wherein the central metrics table is a common storage array metrics table indicative of facts concerning manageable entries in a storage area network, and wherein the facts comprise a numerical attribute including at least one of:

a storage device count;

a file system total; and

a file system used byte count.

4. The method of claim 3 wherein transforming further comprises

defining transformation logic indicative of a set of views and tables;

transforming, via the transformation logic, attributes in the online database to corresponding attributes in a query DB model, the corresponding attributes including at least one of facts and dimensions.

5. The method of claim 4 wherein the views are SQL views between a plurality of tables, the views indicative of join operations between the enumerated tables and operative to logically link the entries in the tables.

6. The method of claim 1 wherein enumerating the tables further comprises:

defining an offline set of tables operable to receive the identified plurality of tables, the offline set indicative of queryable values; and

discriminating, from the offline set, fact attributes and dimension attributes; and

generating the query model tables based on the discriminating.

7. The method of claim 6 wherein extracting further comprises:

extracting, in a nested scripting language, a scripted representation of the offline set of tables; and

parsing the scripted representations responsive to a query, the scripted representations having a hierarchical structure operable to designate delimited fields and entries.

8. The method of claim 5 further comprising rolling up the data on a periodic basis to consolidate counts and reduce the size of the data in the storage array metrics table.

9. The method of claim 8 wherein rolling up further comprises

aggregating the data in the storage array metrics table according to at least one of daily, weekly, and monthly reporting period;

computing a different resolution for the aggregated data indicative of the aggregated reporting period; and

storing the aggregated data as a historical data table.

10. A method of generating a query database model from an online SAN repository comprising:

identifying queryable fields in the online repository having a plurality of transactions adapted for realtime response;

selecting a subset of the queryable fields from the online repository corresponding to multiple fact tables expected to be queried in a report database, wherein the expected queries comprise SAN activity reports;

for each of the queryable fields, identifying whether the queryable field corresponds to a fact or a dimension of transactions stored in the online repository;

for each identified field, classifying the identified field as a dimension or a fact;

determining, if the identified field corresponds to a dimension, a corresponding dimensional table to receive the queryable field;

designating, if the identified field corresponds to a fact, a corresponding fact table to receive the queryable field;

computing a transformation from the identified queryable field to an enumerated field in the corresponding table in the query model;

defining a set of tables including the determined dimensional tables and the designated fact table, the set of tables collectively having fields corresponding to the computed transformations, the query database model strongly indexed on the identified fields of the fact table, and having fewer records in the dimension tables than the corresponding values in the online database.

11. The method of claim 10 further comprising defining a set of query tables, the query tables including a fact table and a plurality of dimensional tables.

12. The method of claim 11 wherein computing the transformation includes transformation logic, further comprising:

mapping attributes form the fields of the online repository to the fields of the query model, the fields corresponding to attributes of the databases; and

generating a command operative to transform the data in the mapped fields from the online repository to the fields of the query model.

13. The method of claim 12 further comprising, defining a star schema structure, the star schema having a central entity corresponding to the fact table, the central entity including a plurality of entries indicative of the designated facts, and further including associated entities representative of dimensions, the associated entities having a substantially smaller number if entries.

14. The method of claim 13 further comprising operating a query engine responsive to a user query and operable to access the generated tables responsive to a received user query.

15. A system including computer storage medium having instructions that, when executed by a processor, cause the computer to perform steps for querying a managed SAN object database comprising:

an online database operable for realtime transactions;

a query database having a fact table and a plurality of dimension tables;

a discriminator operable to select a subset of fields from the SAN object database corresponding to the fact table expected to be queried in a report database, wherein the expected queries comprise SAN activity reports;

a set of transformation routines, the transformation routing operable to map a subset of the attributes from the online database to the query database, further operable to, for each attribute, classify the attribute as a dimension or a fact and

assign, if the attribute is a fact, the attribute to a central metrics table; and

assign, if the attribute corresponds to a dimension, the attribute to a dimension table having an association to the central metrics table;

an extractor operable to periodically extract, from the online database, the data contained in the subset of fields; and

a transformer operable to load, via the transformations, the extracted data to corresponding attributes in the query database the query database strongly indexed on the attributes of the central metrics table, and having fewer records in the dimension tables than the corresponding values in the online database.

16. A data transformation device including computer storage medium for generating a report database comprising:

at least a memory;

at least a processor;

an interface to an online database having a plurality of tables corresponding to a SAN object model;

an extractor operable to enumerate tables for transformation and extract the enumerated tables from the database;

a discriminator operable to select a subset of the queryable fields from the online repository corresponding to multiple fact tables expected to be queried in a report database, wherein the expected queries comprise SAN activity reports and to filter, for each of the tables, unqueried fields to identify a transformation set of tables and attributes within the tables, the transformation set indicative of queryable values;

transformation logic operable to transform via a set of views, the attributes of the transformation set, further operable to, for each attribute, classify the attribute as a dimension or a fact and

assign, if the attribute is a fact, the attribute to a central metrics table; and

assign, if the attribute corresponds to a dimension, the attribute to a dimension table having an association to the central metrics table; and

a transformer operable to load the transformed attributes into the corresponding tables in a report database the report database strongly indexed on the attributes of the central metrics table, and having fewer records in the dimension tables than the corresponding values in the online database.

17. The device of claim 16 wherein the transformer includes a loader operable to load the corresponding tables into dimensional tables and a fact table, the dimension tables determined from transformation logic indicative of fields adapted to receive a query.

18. The device of claim 17 wherein the discriminator is further operable to

define transformation logic indicative of a set of views and tables;

and

transform, via the transformation logic, attributes in the online database to corresponding attributes in a query DB model, the corresponding attributes including at least one of facts and dimensions.

19. The device of claim 18 wherein the views are SQL views between a plurality of tables, the views indicative of join operations between the enumerated tables and operative to logically link the entries in the tables.

20. The device of claim 18 wherein the transformation logic is further operable to

define an offline set of tables operable to receive the identified plurality of tables, the offline set indicative of queryable values;

and

discriminate, from the offline set, fact attributes and dimension attributes; and

generate the query model tables based on the discriminating.

21. A computer program product having a computer storage medium operable to store computer program logic embodied in computer program instructions encoded thereon that, when executed by a processor, cause the computer to perform a method for modeling a storage area network database for report queries, the method comprising:

identifying a plurality of tables in an SAN object model;

enumerating tables for transformation;

selecting a subset of fields from the tables of the SAN object model corresponding to multiple fact tables expected to be queried in a report database, wherein the expected queries comprise SAN activity reports;

extracting the enumerated tables from the database;

identifying a transformation set of tables and attributes within the tables, the transformation set indicative of queryable values;

classifying, for each attribute, the attribute as a dimension or a fact;

assigning, if the attribute is a fact, the attribute to a central metrics table;

assigning, if the attribute corresponds to a dimension, the attribute to a dimension table having an association to the central metrics table;

transforming, via a set of views, the attributes of the transformation set; and

loading the transformed attributes into the corresponding tables in a report database, the report database strongly indexed on the attributes of the central metrics table, and having fewer records in the dimension tables than corresponding values in the online database.

22. A data transformation device for generating a report database comprising:

at least a memory;

at least a processor;

means for identifying queryable fields in an online SAN repository having a plurality of transactions adapted for realtime response;

means for selecting a subset of the queryable fields from the online repository corresponding to multiple fact tables expected to be queried in a report database, wherein the expected queries comprise SAN activity reports;

means for identifying, for each of the queryable fields, whether the queryable field corresponds to a fact or a dimension of transactions stored in the online repository and, for each identified field, classifying the identified field as a dimension or a fact;

means for determining, if the identified field corresponds to a dimension, a corresponding dimensional table to receive the queryable field;

means for designating, if the identified field corresponds to a fact, a corresponding fact table to receive the queryable field;

means for computing a transformation from the identified queryable field to an enumerated field in the corresponding table in the query model; and

means for defining a set of tables including the determined dimensional tables and the designated fact table, the set of tables collectively having fields corresponding to the computed transformations, the set of tables defining a star schema structure, the star schema having a central entity corresponding to the fact table, the central entity including a plurality of entries indicative of the designated facts, and further including associated entities representative of dimensions, the associated entities having a substantially smaller number if entries, the query model strongly indexed on the identified fields of the fact table, and having fewer records in the dimension tables than the corresponding values in the online repository.