|
|
|
General Information about the Data Warehouse
- Star Schemas
- Fact Tables
- Dimension Tables
- Rules about joining tables
- Applying limits (search criteria)
- Guidelines to produce efficient queries
- Getting the results that you expect
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 Institution CIP Cross Walk for Faculty Salary 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).
The All Stars page lists some of the Star Schemas you may construct using tables in the Data Warehouse.
[Top]
- Fact tables contain the detail information you want to look at, such as numbers, or line items.
- 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:
- Do not put limits directly on a field in a Fact table
- 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 word DETAIL in the title are usually fact tables.
[Top]
- Dimension tables are designed especially for selection and grouping. Information in a dimension table is usually equivalent to master data.
- These tables are much smaller than Fact tables: on average, a Dimension table contains about 500 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 INSTITUTION, ACADEMIC YEAR and CIP.
[Top]
- 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 an easy step. These fields are generally listed at the top of the tables and have names with the word "Key" as the suffix, for example: Institution Key, CIP Key, Academic Year Key, etc.
- Look at keys in a Fact table to learn which types of Dimension tables it may be joined to. For example, the AAUDE Faculty Salary Detail table contains the CIP Key, which means that it can be joined to other CIP Dimension tables.
- Join Dimension tables to a Fact table using the same name keys. For example, join CIP Key on the Dimension table to CIP key on the Fact table.
[Top]
Applying limits (search criteria)
- 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 parentheses (highlight the limits to group and click on the parentheses 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.
[Top]
Guidelines to produce efficient queries
- Ask only for information that you need.
- Only use the tables that you need.
- 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. The limit operator "= equal" will be faster than any other comparison types, like "< less than" or "contains". If you are not sure what the limits are, use the Show Values option (in BrioQuery) on the Limit screen. Or, you may look up in the Metadata.
[Top]
Getting the results that you expect - What to do when things go wrong
If, after processing a query in the Data Warehouse, you get zero rows or do not get the results that you expect, try checking the following:
Contact aaude-warehouse@mit.edu if you are still not able to get the results you anticipate.
- Limits: check that the limits are applied properly. Remember to check that the case and formats for the field that is being limited are chosen correctly.
- Joins: double-check that the tables are joined properly, with only one join between any two tables. Try deleting the join and re-joining. Incorrect joins can sometimes be made by joining to the field above or below your intended field.
- Timing of data: Are you certain that there is data in the Data Warehouse for the time period(s) you have requested in your query? For example, the Data Warehouse does not have any Faculty Salary data prior to academic year 1994-1995. Requesting a date prior to then would result in zero rows.
[Top]
Warehouse Guide
| e-mail aaude-warehouse
| Warehouse home
| AAUDE home
last updated: