反正规化.PPT

有效的分析規格 資料結構與呈現方式 交叉分析範例 聚總與下鑽 * * * * * CUBE結構 事實與維度資料 事實資料： 銷售量 維度資料： 車型 維度資料： 銷售年度 星狀綱目 銷售事實 ProductId TimeId CustomerId StoreId SalesQty SalesAmount 產品維度 ProductId BrandName ProductName SKU 顧客維度 CustomerId AccountNum Name Address 時間維度 TimeId TheYear TheMonth TheDay 門市維度 StoreId StoreName StoreType StoreAddress 雪花狀綱目 銷售事實 ProductId TimeId CustomerId StoreId SalesQty SalesAmount 產品維度 ProductId BrandName ProductName SKU 顧客維度 CustomerId AccountNum Name Address 時間維度 TimeId TheYear TheMonth TheDay 門市維度 StoreId StoreName StoreType StoreAddress 產品類別維度 ProductClassId ProdSubcategory ProdCategory ProdDepartment 門市區域維度 RegionId SalesCity SalesRegion SalesCountry 維度資料表的建立 產品資料表 ProductId ProductName ColorId TypeId SizeId 顏色資料表 ColorId ColorName 尺寸資料表 SizeId SizeUnit SizeQty 型別資料表 TypeId TypeName 產品維度資料表 ProductId ProductName ColorName TypeName SizeUnit SizeQty 反正規化 塑模步驟三 銷售事實 ProductId TimeId CustomerId StoreId SalesQty SalesAmount 產品維度 ProductId BrandName ProductName SKU 顧客維度 CustomerId AccountNum Name Address 時間維度 TimeId TheYear TheMonth TheDay 門市維度 StoreId StoreName StoreType StoreAddress * * * p.21 Efficient specification of dimensions and calculations: (p. 21) there is more to analysis than simply aggregating numbers. Sure, it is important to be able to correctly sum and average large quantities of data, but most of the important information to be gleaned from the intelligent comparison of ratios and inferred trends over time and other dimensions. Separation of structure and representation: The degree to which grid-like views (or any other kind of external representation) are flexible is in great part a function of the degree of separation between data structure and representation. Having this separation means that views can be reorganized by an end-user without making any structural changes to the data. If data needs to be restructured to support a new view, chances are the new won’t be created –