Creating Advanced Queries

On this page:

Star Schemas

Your query is called a Star Schema. The reason for this name is that your query takes on the shape of a star. The Fact table is the body of the star and the Dimension tables are the points of the star. Understanding stars, facts, and dimensions will help you know your data.

Sometimes you will do simple queries that do not take on the star shape. For example, the Chart of Cost Collectors query is not in the shape of a star because it requires only one table.

The All Data Warehouse Tables page is your reference for identifying which tables are Fact tables and which ones are Dimension tables (see the Type column).

Fact Tables

• Fact tables contain the detail information you want to look at, such as numbers, or line items.
• Access control to sensitive information is maintained on fact tables.
• These tables can be very large: as much as several million rows of data.
• The numbers you are going to sum (or put in the lower right box in the Detail Outliner) are usually located in the Fact tables.
• Two rules to remember:
  1. In general, do not put limits directly on a field in a Fact table.
  2. Never do a "Show Values" on a Fact table field, because it will take forever. "Show Values" lists all the possible values for a specific field. If you need to know the possible values, use "Show Remarks.
• Tables with the words DETAIL, BALANCES or HISTORY in the title are fact tables.

Dimension Tables

• Dimension tables are designed especially for selection and grouping. For financial data, information here is equivalent to master data.
• In general, there is no access control on dimension table.
• These tables are much smaller than Fact tables: on average, a Dimension table contains about 10,000 rows of data.
• Dimension tables contain fields that can be used to limit your queries or group numbers in your report.
• Use "Show Values" for Dimension tables (in contrast to never using "Show Values" for a Fact table) because Dimension tables are smaller.
• Examples of Dimension tables are GL ACCOUNT REPORT, VENDOR, COST COLLECTOR, TIME MONTH, ACADEMIC TERMS, SUBJECT OFFERED.

Rules About Joining

• Join Dimension tables to a Fact table(s); do not join Fact tables to Fact tables.
• All tables in a query must be joined. If the Process button is greyed out, check that all tables are properly joined and reprocess.
• There should be only one join between any two tables.
• Most tables contain fields that can make joining information easy. These fields are generally listed at the top of the tables and have names with the word "Key" as the suffix, e.g. Cost Collector Key, Time Month Key, etc.
• Look at keys in a Fact table to learn which types of Dimension tables it may be joined to. For example, the Financial Detail table contains the Cost Collector Key, which means that it can be joined to other Cost Collector Dimension tables.
• Join Dimension tables to a Fact table using the same name keys. For example, join Cost Collector Key on the Dimension table to Cost Collector key on the Fact table.
• Dimension tables can be reused with different Fact tables. For example, once you're familiar with the GL ACCOUNT REPORT table, you can use it again when creating many different stars.

Applying Limits

• As Oracle is case sensitive, be sure that your limit uses the correct case for that data item.
• Limits should generally only be applied on Dimension tables.
• Multiple limits are permitted. They may use or be grouped with parens (highlight the limits to group and click on the parens button).
• Check to see that you are using the correct limit operator: AND or OR. Change the operator by highlighting it; click to toggle between the choices.
• Null values will not automatically be returned. If you wish to have results returned to you including null values for the data item you are limiting, be sure to check the Include Nulls box.

Guidelines to Produce Efficient Queries

• Ask only for information that you need. Don't bring up extra or unneeded items to the request line.
• Only use the tables that you need. For example, if you are making a report about Cost Collectors and want to include Profit Center information, use the field "Profit Center Name" in the Cost Collector table instead of bringing in the Profit Center table for that information.
• Have only one join between each set of two tables.
• Narrow your selection by using limits (search criteria). Limits are usually done on Dimension tables. If you are not sure what the limits are, use the Show Values option on the Limit screen. The limit operator "= equal" will be faster than any other comparison types, like "less than" or "contains."
• If you do not need line item detail, use the data function "sum" on the request line whenever possible. This action will make your query run much faster. Fields appropriate for this data function often have the word amount or counter in the field name.