Reporting and summarizing metrics in sparse relationships on an OLTP database
Storing and/or accessing data in a transactional database, with use of the following technique: (i) selecting a set of base tables in a transactional database; and (ii) creating a factless materialized query table, having maximum sparsity, for the set of base tables. The set of base tables includes at least two base tables. The set of base tables includes a set of keys including at least two distinct primary keys. The factless materialized query table includes one record associated with each record in the set of base tables. Each record in the materialized query table includes a value for every primary key in the set of base tables.
1. A computer system comprising:
a processor(s) set; and
a software storage device;
wherein:
the processor set is structured, located, connected and/or programmed to run software stored on the software storage device;
the software comprises:
first program instructions programmed to select a set of base tables in a transactional database; and
second program instructions programmed to create a factless materialized query table, having maximum sparsity, for the set of base tables;
the set of base tables includes at least two base tables;
the set of base tables includes a set of keys including at least two distinct primary keys;
the factless materialized query table includes one record associated with each record in the set of base tables; and
each record in the materialized query table includes a value for every primary key in the set of base tables.
2. The system of claim 1 wherein the transactional database is part of an online transaction processing system.
3. The system of claim 1 wherein:
the second program instructions are further programmed to assign a null flag for every primary key value in every record in the materialized query table where the corresponding record(s) in the set of base tables does not include that primary key.
4. The system of claim 3 wherein:
the second program instructions are further programmed to create each record of the materialized query table to include a determinant that uniquely identifies a level of granularity for that materialized query table record.
5. The system of claim 4 wherein the software further comprises:
third program instructions programmed to receive a user query; and
fourth program instructions programmed to respond to the user query based, at least in part, upon at least one determinant in the materialized query table.
6. The system of claim 1 wherein:
the first program instructions are further programmed to select base tables so that multiple dimensions will be joined by the materialized query table.