Insurance Model

Following is the OLTP data model for the insurance.

Exercise:

  • Populate the insurance data model with the test data.

  • Write query to retrieve the number of customers by policies

  • Write the query to get the amount by transaction type. (Premium, Claims)

Questions

  1. What is a Data Warehouse?
    • Used to run business
    • Used to analyze the data
    • Integrated platform
    • Design based on Subject oriented
  2. What is ETL used for?

    • To run the analysis

    • To interact with end users

    • Transforms the data based on the target system

    • To run the reports

  3. Dimensional Modeling follows
    • de-normalization method
    • normalized method
  4. Pick the correct answers for OLTP system
    • Run Business
    • Subject Oriented
    • Stores lot of history
    • Stores less history
  5. Which are the following components come under BI?
    • Source System
    • Data Mining
    • Ad hoc Reporting
    • Transform and Loading
    • Business Activity Monitoring
  6. What is a dimension table?
    • Used to store the number values
    • Few columns in the table
    • Stores only integer values for faster retrieval
    • Category through which you analyze the data in the schema
  7. TYPE1 Dimension
    • Stores complete history of changes
    • No history of changes
    • Partial History of changes
  8. TYPE2 Dimension
    • Stores complete history of changes
    • No history of changes
    • Partial History of changes
  9. We follow normalization process to
    • Improve transaction speed
    • Improve reporting performance
    • Reduce redundant information
  10. What are the data models we can have in DW
    • 3rd normal form
    • Snow flake
    • Star schema
    • All the above
  11. What is a fact table?
    • Contains measures which helps to measure the business performance
    • Fewer columns
    • Lots of character fields
    • Contains surrogate id
  12. What is metadata?
    • Used to run the DW every effectively
    • Used by business to analyze the information
  13. What is confirmed dimension?
    • Dimension which has association to one star schema
    • Dimension which has association to one more than one star schema
  14. What is hierarchy?
    • Used in ETL process
    • The way we implement dimensions in BI reporting
    • One dimension can have multiple hierarchies
  15. What is ODS?
    • Has high summary data
    • Stores lot of history
    • Less history (30 to 90 days)
  16. Do we have relationships between dimensions in star schma
    • Yes
    • No
  17. What is granularity of DW
    • Having summary data in Data mart
    • The level of details we have in DW
    • 3rd normal form Data model in DW will have more detailed level data
  18. What is OLAP used for?
    • Running static report
    • Ad hoc reporting
    • Especially used by operational resources
    • Used by analyst
  19. OLAP tools are
    • Informatica
    • Business Objects
    • Cognos
    • Data Stage
  20. Fact less fact tables are used
    • Store the measures to track the performance of the company
    • Is used to store the event driven data