WIXLIB

5.4. Defining a Fact Constellation in DMQLdefine cube sales [time, item, branch, location]:dollars sold sum(sales in dollars),avg sales avg(sales in dollars),units sold count(*)define dimension timeas (time key, day, day of week, month, quarter, year)define dimension itemas (item key, item name, brand, type, supplier type)define dimension branchas (branch key, branch name, branch type)define dimension locationas (location key, street, city, province or state, country)define cube shipping [time, item, shipper, from location,to location]:dollar cost sum(cost in dollars),unit shipped count(*)define dimension timeas timein cube salesdefine dimension itemas itemin cube salesA. BellaachiaPage: 19

define dimension shipperas ( shipper key,shipper name,location as location in cube sales,shipper type)define dimension from locationas locationin cube salesdefine dimension to locationas locationin cube salesA. BellaachiaPage: 20

5.5. Measures: Three Categories A data cube function is a numerical function that canbe evaluated at each point in the data cube space. Given a data point in the data cube space:Entry(v1, v2, , vn)where vi is the value corresponding to dimension di.We need to apply the aggregate measures to thedimonsion values v1, v2, , vn Distributive:o If the result derived by applying the function to naggregate values is the same as that derived byapplying the function on all the data withoutpartitioning.o Example: count(), sum(), min(), max(). Algebraic:o Use distributive aggregate functions.o If it can be computed by an algebraic functionwith M arguments (where M is a boundedinteger), each of which is obtained by applying adistributive aggregate function.A. BellaachiaPage: 21

o Example: avg(), min N(), standard deviation(). Holistic:o If there is no constant bound on the storage sizeneeded to describe a subaggregate.o E.g., median(), mode(), rank().5.6. How to compute data cube measures? How do evaluate the dollars sold and unit sold in the starschema of the previous example? Assume that the relation database schema correspondingto our example is the following:time (time key, day, day of week, month, quarter, year)item (item key, item name, brand, type, supplier(supplier key,supplier type))branch (branch key, branch name, branch type)location (location key, street, city, province or state, country)sales (time key, item key, branch key, location key,number of unit sold, price)A. BellaachiaPage: 22

Let us then compute the two measures we have in our datacube: dollars sold and units soldselect s.time key, s.item key, s.branch key,s.location key, sum(s.number of units sold*s.price),sum(s.number of units sold)from time t, item i, branch b, location l, sales swhere s.time key t.time keyand s.item key i.item keyand s.branch key b.branch keyand s.location key l.location keygroup by s.time key, s.item key, s.branch key,s.location key Relationship between “data cube” and “group by”? The above query corresponds to the base cuboid. By changing the group by clause in our query, wemay generate other cuboids. What is query for the 0-D cuboid or apex?A. BellaachiaPage: 23

6. A Concept Hierarchy A concept hierarchy is an order relation between a set ofattributes of a concept or dimension. It can be manually (users or experts) or automaticallygenerated (statistical analysis). Multidimensional data is usually organized into dimensionand each dimension is further defined into a lower level ofabstractions defined by concept hierarchies. Example: Dimension (location)allallregionNorth AmericaEurope.countrycityofficeA. Bellaachia.GermanyFrankfurtSpain.L. ChanCanadaVancouver.MexicoTorontoM. WindPage: 24

The order can be either partial or total:Location dimension: Street city state countryTime dimension: Day {month quarter ; week} otal order hierarchyPartial order hierarchy Set-grouping hierarchy: A. BellaachiaIt is a concept hierarchy among groups of values.Example: {1.10} inexpensivePage: 25

7. OLAP Operations in a Multidimensional Data Sales volume as a function of product, time, and region. Dimensions hierarchical concepts: Product, Location,TimeIndustry Æ Category Æ ProductRegion Æ Country Æ City Æ OfficeYear Æ Quarter Æ Month Æ DayWeek Sales volume as a function of product, month, andregion.regionProductMonthA. BellaachiaPage: 26

A Sample data cube:ProductTVPCVCR1QtrTotal aMexicosum Cuboids of the sample cube:all0-D(apex) y1-D cuboidsdate, country2-D cuboids3-D(base) cuboidproduct, date, countryA. BellaachiaPage: 27

Querying a data cubeA. BellaachiaPage: 28

8. OLAP Operations Objectives:o OLAP is a powerful analysis tool: Forecasting Statistical computations, aggregations, etc. Roll up (drill-up): summarize datao It is performed by climbing up hierarchy of adimension or by dimension reduction (reducethe cube by one or more dimensions).o The roll up operation in the example is basedlocation (roll up on location) is equivalent togrouping the data by country.New ameraroll upA. BellaachiaDate ofsaleVideoCamera CDNO22830VA231822Page: 29

Drill down (roll down):o It is the reverse of roll-upo It is performed by stepping down a concepthierarchy for a dimension or introducing newdimensions. Slice and Dice:o Project and Select operationso Check the example. Pivot (rotate):o Re-orient the cube for an alternativepresentation of the datao Transform 3D view to series of 2D planes. Other operationso Drill across: involving (across) more than onefact table.o Drill through: through the bottom level of thecube to its back-end relational tables (usingSQL)A. BellaachiaPage: 30

A. BellaachiaPage: 31

A. BellaachiaPage: 32

9. Starnet Query Model for Multidimensional Databases Each radial line represents a dimension Each abstraction level in a hierarchy concept is called afootprint Apply OLAP operations.Customer CKProductPRODUCT LINETimeANNUALY QTRLDAILCOUNTRYPRODUCT ITEMCITYREGIONA. BellaachiaSALESDISTRICTLocationEach circle is called a footprintPRODUCT GROUPDIVISIONPromotionOrganizationPage: 33

10. Data warehouse architecture The design of a successful DW requires the understandingand the analysis of business requirements: Competitive advantage Enhance business productivity Cost reduction Four views regarding the design of a data warehouse:o Top-down view: allows selection of the relevant informationnecessary for the data warehouse. It covers thecurrent and future business needs.o Data source view: This view exposes the information being captured,stored, and managed by operational systems. Usually modeled by traditional data modelingtechniques, e.g., ER model.o Data warehouse view: This view consists of fact tables and dimensiontables.o Business query view: This view sees the perspectives of data in thewarehouse from the view of end-userA. BellaachiaPage: 34

10.1. DW Design Process Top-down, bottom-up approaches or a combination ofboth Top-down: Starts with overall design and planning(mature) Bottom-up: Starts with experiments and prototypes (rapid)o From software engineering point of viewo Waterfall: structured and systematic analysis at eachstep before proceeding to the nexto Spiral: rapid generation of increasingly functionalsystems, short turn around time, quick turn around Typical data warehouse design processo Choose a business process to model, e.g., orders,invoices, etc.o Choose the grain (atomic level of data) of the businessprocesso Choose the dimensions that will apply to each fact tablerecordo Choose the measure that will populate each fact tablerecordA. BellaachiaPage: 35

Multi-Tiered ouseOLAPServerServeAnalysisQueryReportsDataData MartsData SourcesA. BellaachiaData StorageOLAPEngineFront-End ToolsPage: 36

10.2. Three Data Warehouse models Enterprise warehouseo Collect all of the information about subjectsspanning the entire organization. Data Marto a subset of corporate-wide data that is of value toa specific groups of users. Its scope is confined tospecific, selected groups, such as marketing datamart Independent vs. dependent (directly fromwarehouse) data mart. Virtual warehouseo A set of views over operational databaseso Only some of the possible summary views maybe materializedA. BellaachiaPage: 37

A Recommended ApproachMulti-Tier DataWarehouseDistributed DataMartsDataMartEnterprise DataWarehouseDataMartModel refinementModel refinementDefine a high-level corporate data model Build the data warehouse incrementally, data marts Æ datawarehouse:o Start with a data modelo Build each data mart in the organization in parallelo Integrate the data martsA. BellaachiaPage: 38

10.3. OLAP Server Architectures Relational OLAP (ROLAP)o Use relational or extended-relational DBMS tostore and manage warehouse data and OLAPmiddle ware to support missing pieceso Include optimization of DBMS backend,implementation of aggregation navigation logic,and additional tools and serviceso greater scalability Multidimensional OLAP (MOLAP)o Array-based multidimensional storage engine(sparse matrix techniques)o fast indexing to pre-computed summarized data Hybrid OLAP (HOLAP)o User flexibility, e.g., low level: relational, highlevel: arrayo Specialized SQL serverso specialized support for SQL queries overstar/snowflake schemas How data is actually stored in ROLAP and MOLAB?o Two methods: Base cuboid data is stored in a base facttable Aggregate data: Data can be stored in the base facttable (Summary Fact table), or Data can be stored in a separatesummary fact tables to store eachlevel of abstraction.A. BellaachiaPage: 39

11. Data Warehouse Implementation Objectives: Monitoring: Sending data from sources Integrating: Loading, cleansing,. Processing: Efficient cube computation, andquery processing in general, indexing, . Cube Computationo One approach extends SQL using compute cubeoperatoro A cube operator is the n-dimensional generalization ofthe group-by SQL clause.o OLAP needs to compute the cuboid corresponding eachinput query.o Pre-computation: for fast response time, it seems agood idea to pre-compute data for all cuboids or at leasta subset of cuboids since the number of cuboids is: 2nIf no hierarchy ifhierarchyand number of cuboids n( Li 1) Li is number of levels i 1 associated with d dim ension i11.1. Materialization of data cube Store in warehouse results useful for common queries Pre-compute some cuboidsA. BellaachiaPage: 40

This is equivalent to the define new warehouse relationsusing SQL expressions Materialize every (cuboid) (full materialization), none (nomaterialization), or some (partial materialization) Selection of which cuboids to materialize Based on size, sharing, access frequency, etc. Define new warehouse relations using SQLexpressions11.2. Cube Operation Cube definition and computation in DMQLdefine cube sales[item, city, year]: sum(sales in dollars)compute cube sales Transform it into a SQL-like language (with a new operatorcube by, introduced by Gray et al.’96)SELECT item, city, year, SUM (amount)FROM SALESCUBE BY item, city, year Need compute the following Group-Bys(date, product, customer),(date,product),(date, customer), (product,customer),(date), (product), (customer)()A. BellaachiaPage: 41

()(city)(city, item)(item)(city, year)(year)(item, year)(city, item, year)A. BellaachiaPage: 42

11.3. Cube Computation Methods ROLAP-based cubingo Sorting, hashing, and grouping operationsare applied to the dimension attributes inorder to reorder and cluster related tupleso Grouping is performed on somesubaggregates as a “partial grouping step”o Aggregates may be computed frompreviously computed aggregates, rather thanfrom the base fact table MOLAP Approacho Uses Array-based algorithmo The base cuboid is stored asmultidimensional array.o Read in a number of cells to compute partialcuboidsA. BellaachiaPage: 43

11.4. Indexing OLAP Data: Bitmap Index Approach:o Index on a particular columno Each value in the column has a bit vector: bit-op isfasto The length of the bit vector: # of records in the basetableo The i-th bit is set if the i-th row of the base table hasthe value for the indexed columno Not suitable for high cardinality domains Example:Base iaDealerC4AmericaRetailC5EuropeDealerIndex on ndex on Type:RecID12345A. BellaachiaRetail10010Dealer01101Page: 44

11.5. Indexing OLAP Data: Join Indices Join index:JI(R-id, S-id)where R (R-id, ) S (S-id, ) Traditional indices map the values to a list of record ids It materializes relational join in JI file and speeds uprelational join — a rather costly operation In data warehouses, join index relates the values of thedimensions of a star schema to rows in the fact table.o E.g. fact table: Sales and two dimensions city andproduct A join index on city maintains for each distinctcity a list of R-IDs of the tuples recording theSales in the city Join indices can span multiple dimensionsA. BellaachiaPage: 45

11.6. Efficient Processing OLAP Queries Determine which operations should be performed onthe available cuboids:o transform drill, roll, etc. into corresponding SQLand/or OLAP operations, e.g, dice selection projection Determine to which materialized cuboid(s) therelevant operations should be applied. Exploring indexing structures and compressed vs.dense array structures in MOLAP11.7. Data Warehouse Usage Three kinds of data warehouse applicationso Information processing supports querying, basic statistical analysis, andreporting using crosstabs, tables, charts andgraphso Analytical processing multidimensional analysis of data warehouse data supports basic OLAP operations, slice-dice,drilling, pivotingo Data mining knowledge discovery from hidden patterns supports associations, constructing analyticalmodels, performing classification and prediction,and presenting the mining results usingvisualization tools. Differences among the three tasksA. BellaachiaPage: 46

11.8. Why online analytical mining? High quality of data in data warehouseso DW contains integrated, consistent, cleaned data Available information processing structure surroundingdata warehouseso ODBC, OLEDB, Web accessing, service facilities,reporting and OLAP tools OLAP-based exploratory data analysiso mining with drilling, dicing, pivoting, etc. On-line selection of data mining functionso Integration and swapping of multiple mining functions,algorithms, and tasks. Architecture of OLAMA. BellaachiaPage: 47

12. An OLAM ArchitectureLayer4User InterfaceMining resultMining queryUser GUI APIOLAMEngineLayer3OLAP/OLAMOLAPEngineData Cube APILayer2MDDBMDDBMeta DataFiltering&IntegrationDatabase APIData cleaningDatabasesA. BellaachiaData integrationFilteringDataWarehouseLayer1Data RepositoryPage: 48

2.9. Why Separate Data Warehouse? High performance for both systems o DBMS— tuned for OLTP: access methods, indexing, concurrency control, recovery o Warehouse—tuned for OLAP: complex OL