- Which of the following statements does not apply to multidimensional analysis:
- Provides the most flexible type of reporting.
- Allows for drill up, drill down, and iterative data analysis.
- Divides analysis into dimensions, by which users may view data.
- Allows data to be aggregated by different aggregation functions by dimensions.
- None of the above.
- Online transaction processing and online analytical processing have which of the following in common:
- Orientation or alignment of data
- Data access and manipulation
- Design and functionality
- Usage patterns
- None of the above.
- Which of the following statements about multidimensional models is not true:
- Organize and present the data by primary lines of the users.
- Allow the user to select the qualification of the return data set
- Filter one dimension independent of other dimensions
- Allow the user to perform current transactions.
- Which of the following statements is true:
- Multidimensional analysis consists of defining the aggregation level and qualifying the return set depending on all dimensions.
- Multidimensional analysis consists of defining the aggregation level and qualifying the return set independent of all other dimensions.
- Which of the following statements about dimensional attributes is not true:
- Allow the users to define the level of aggregation at an attribute level.
- Would likely be entities in an entity-relationship diagram.
- Would be related to attributes in other dimensions.
- Allow users to present data grouped by one or more dimension.
- None of the above.
- Which of the following considerations does not need to be balanced (or traded off) when developing a data warehouse:
- Data warehouse maintenance
- Source data
- Query performance
- User requirements
- None of the above
- Which of the following statements is true:
- Source systems can possess overlapping data
- Data must always be calculated using a consistent algorithm across operational source systems
- Users of the warehouse will primarily be the same as users of source systems
- Data warehouses cannot reside in one or more databases operating on one or more servers
- All of the above.
- Which of the following statements about aggregation is true:
- Aggregate calculations involve sorts that are costly
- Aggregation increases the number of physical disk reads.
- The amount of data that is aggregated and must be stored is reduced, reducing swap requirements.
- Data is summarized with more granularity.
- All of the above.
9. Which of the following is an advantage of a denormalized schema?
a. Fewer indexes.
b. Ease of restructuring the database.
c. Simpler access method.
d. All of the above.
10. Which are the best attributes of a dimension:
a. Additive attributes.
b. Numeric attributes.
c. Continuously valued attributes.
d. Textual attributes.
e. All of the above.
Company ABC wants to analyze his margins and how they vary over time, by region, by industry, by product, and by customer. Margins are revenue minus production, development, distribution, office, and sales costs. Company ABC also wants to know market, industry, and customer trends. Company ABC also wants to know which sales reps are high achievers. Develop the multidimensional model for a data warehouse to satisfy Company ABC's analysis requirements.
The following are the sources of the data:
Sales Tracking System: An Oracle database that helps the marketing group track their orders, costs, and expenses. Orders are tracked for each customer, and customers are categorized by industry and country. The system also tracks the contacts made by the sales force in terms of customer visits and customer contacts. Sales orders are recorded for each customer with references to the product information that was sold. Information about the sales reps is tracked to record their expenses, commissions, and salaries. Because sales reps are assigned to sales regions, office costs for each region are also tracked.
It contains the following tables and attributes:
PRODUCT_PRICE TABLE
Start_date
End_date
Price
SALES_ORDER_DETAIL TABLE
Item_number
Quantity
Discount_rate
Quanitity_backorder
Description
PRODUCT TABLE
Product_number
Product_name
Model
Size
Description
PRODUCT_TYPE TABLE
Product_type
Description
SALES_ORDER TABLE
Order_id
Order_date
Delivery_date
Description
CUSTOMER_CONTACT TABLE
Contact_id
Last_name
First_name
Phone_number
CUSTOMER_VISIT TABLE
Date_of_visit
Hours_spent
CUSTOMER TABLE
Customer_id
Customer_name
Address
City
State
Zip_code
INDUSTRY TABLE
Industry_code
Description
COUNTRY TABLE
Country_id
CUSTOMER_SALES_REP TABLE
Start_date
End_date
COMMISSION_RATE TABLE
Start_date
End_date
Commission_rate
SALARY TABLE
Start_date
End_date
Amount
EXPENSE TABLE
Expense_id
Period_start_date
Period_end_date
Effective_date
Amount
Description
EXPENSE_CODE TABLE
Expense_code
Description
SALES_REP TABLE
Rep_number
Last_name
First_name
Hire_date
Termination_date
Title
SALES_REGION TABLE
Region_name
Description
OFFICE_COST TABLE
Effective month
Amount
Description
OFFICE_CODE TABLE
Office_cost_code
Description
SALES_AREA TABLE
Area
Description
General Ledger Cost System: Cost reports are run against records from the General Ledger application, which consists of flat file records. Information is extracted based on cost codes (production, distribution, etc.) and product codes. From this information, it is possible to determine the total cost to develop, produce, and distribute each product. Information about sales and marketing costs are captured in the Sales Tracking System
The General Ledger System contains the following:
GENERAL_LEDGER TABLE
General_ledger_transaction_id
Cost_code
Effective_date
Product_code
Facility_type
Facility_name
Location
Customer_name
Quantity
Amount
Market Estimate: Information about market estimates come from an external source, Market Estimating Company, in a flat file format. The information is broken down by industry group. Spending estimates are provided for each industry and region. The regions are the same regions as in the Sales Tracking System.
The Market Estimate data is:
MARKET_FORECAST_DATA TABLE
Start_period_date
End_period_date
Industry
Region
Product_type
Spending_estimate
Quantity_purchase_estimate
Explain the following data warehousing terms.
- Data warehouse
- Data mart
- Granularity
- Roll up
- Star schema
- Drill down
- Aggregation
- Snowflake schema
- Dimension
- Fact