1 / 14

Advanced Topics: Business Intelligence Features

Advanced Topics: Business Intelligence Features. Data Warehousing, Materialized View, Partitioning, SQL Analytics. Data Warehousing. Purpose: Query and Analysis of Data Usually contains historical data derived from transaction data (OnLine Trans. Processing or OLTP)

kaden
Download Presentation

Advanced Topics: Business Intelligence Features

An Image/Link below is provided (as is) to download presentation Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author. Content is provided to you AS IS for your information and personal use only. Download presentation by click this link. While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server. During download, if you can't get a presentation, the file might be deleted by the publisher.

E N D

Presentation Transcript

  1. Advanced Topics:Business Intelligence Features Data Warehousing, Materialized View, Partitioning, SQL Analytics

  2. Data Warehousing • Purpose: Query and Analysis of Data • Usually contains historical data derived from transaction data (OnLine Trans. Processing or OLTP) • OLAP (OnLine Analytical Processing) • Enable Fast execution of ad-hoc queries

  3. Data Warehouse Architecture Data Mart: small, specialized DW for a dept or so DW Purpose: Enable analysis using Unified Data

  4. OLAP example • Assume one (real-valued) measure value eg Sales Amount and several finite dimensional attributes e.g. Item, Place, Month. • Example fact: “Iced Tea was sold in Auckland in January”. Measure: $20k • Maybe no fact about “Iced Tea in Auckland in August”. • Mapping Item×Place×Time  Sales Amount called a cube. (Think “array”.) • Answer queries based on dimensions time, place group by month, country or year, state, etc.) • All possible combinations need to be fast.

  5. Logical Design: Star Schema Central “Fact” table storing “measure” attributes such as “sales” Dimension tables store information about the dimension attributes Queries: Find “sales” by product, region, customers, time (year),..

  6. Dimensions • Hierarchy of attributes • Analysis based on any level in hierarchy • sales on region, subregion, country_name,…

  7. Logical Design: Snowflake Schema • Snowflake: complex improvisation of star. • Products: divided into categories and suppliers (more normalized) • Queries: will be slower

  8. Physical Design Tables: Can be regular tables or Partitioned Tables Indexes: Specialized indexes like bitmap Additional Objects: Materialized views, Dimensions

  9. Partitioned Tables • Behave like regular tables • Query on entire table or on a specific partition • Parallelism on multiple partitions • Can load/modify multiple partitions concurrently

  10. Query & Analysis Functions • All SQL analysis functions • SELECT SUM(amout_sold), p.pname FROM sales s, products p, times t WHERE s.product_id = p.product_id GROUP BY p.pname;

  11. NUMERICABSACOSASINATANATAN2BITANDCEILCOSCOSHEXPFLOORLNLOGMODNANVLPOWERREMAINDERROUND (number)SIGNSINSINHSQRTTANTANHTRUNC (number)WIDTH_BUCKET CHARACATERCHRCONCATINITCAPLOWERLPADLTRIMNLS_INITCAPNLS_LOWERNLSSORTNLS_UPPERREGEXP_REPLACEREGEXP_SUBSTRREPLACERPADRTRIMSOUNDEXSUBSTRTRANSLATETREATTRIMUPPER More SQL Analytic Functions Conversion Functions ASCIISTRCASTCHARTOROWIDCOMPOSECONVERTDECOMPOSEHEXTORAWNUMTODSINTERVALNUMTOYMINTERVALRAWTOHEXRAWTONHEXROWIDTOCHARROWIDTONCHARSCN_TO_TIMESTAMPTIMESTAMP_TO_SCNTO_BINARY_DOUBLETO_BINARY_FLOATTO_CHAR (character)TO_CHAR (datetime)TO_CHAR (number)TO_CLOBTO_DATETO_DSINTERVALTO_LOBTO_MULTI_BYTETO_NCHAR (character)TO_NCHAR (datetime)TO_NCHAR (number)TO_NCLOBTO_NUMBER,… • DATEADD_MONTHSCURRENT_DATECURRENT_TIMESTAMPDBTIMEZONEEXTRACT (datetime)FROM_TZLAST_DAYLOCALTIMESTAMPMONTHS_BETWEENNEW_TIMENEXT_DAYNUMTODSINTERVALNUMTOYMINTERVALROUND (date)SESSIONTIMEZONESYS_EXTRACT_UTCSYSDATESYSTIMESTAMPTO_CHAR (datetime)TO_TIMESTAMPTO_TIMESTAMP_TZTO_DSINTERVALTO_YMINTERVALTRUNC (date)TZ_OFFSET

  12. Analytic Functions • AVG, CORR, COVAR_SAMP, COVAR_POP, COUNT, CUME_DIST, DENSE_RANK, FIRST, LAST, LAG, LEAD, MAX, MIN, PERCENTILE_RANK, RANK, ROW_NUMBER, STDDEV, SUM, VARIANCE, …

  13. Materialized Views • Queries joining fact and dimension tables • Precompute the join and store as Materialized Views • Denormalized and so not in 3NF • Queries are automatically ‘rewritten’ using Materialized view • Materialized views • Facilitate fast execution of queries

  14. Summary • Fast Query and Analysis • Denormalization into Materialized Views • Queries implicitly ‘rewritten’ under the covers • Research issues: • What queries can be rewritten using the MV • Pushing the updates to the MVs • Refreshing the MVs

More Related